ADO.NET for SqlServer初学

string connString = @"server = (local)/SQLEXPRESS; Initial Catalog=Northwind; user id = sa; password = 12345;";

string sql = @"select * from employees";

SqlConnection

l? SqlConnection conn = new SqlConnection( connString );

l? SqlConnection conn = new SqlConnection();

???? conn.ConnectionString = connString;

Sample:

??????????? string connString = @"server = (local)/SQLEXPRESS; Initial Catalog=Northwind; user id = sa; password = 12345;";

??????????? SqlConnection conn = new SqlConnection();

??????????? conn.ConnectionString = connString;

??????????? try

??????????? {

??????????????? conn.Open();

??????????????? Console.WriteLine("Connection opened.");

??????????????? Console.WriteLine("Connection Properties:");

??????????????? Console.WriteLine("/tConnection String:{0}",conn.ConnectionString);

??????????????? Console.WriteLine("/tDatabase:{0}",conn.Database);

??????????????? Console.WriteLine("/tDataSource:{0}",conn.DataSource);

??????????????? Console.WriteLine("/tServerVersion:{0}",conn.ServerVersion);

??????????????? Console.WriteLine("/tState:{0}",conn.State);

??????????????? Console.WriteLine("/tWorkstationID:{0}",conn.WorkstationId);

??????????? }

??????????? catch (SqlException e)

??????????? {

??????????????? Console.WriteLine("Error:" + e);

??????????? }

??????????? finally

??????????? {

??????????????? conn.Close();

??????????????? Console.WriteLine("Connection closed.");

??????????? }

SqlCommand

l?? SqlCommand cmd = new SqlCommand();

???? cmd.CommandText = sql;

???? cmd.Connection = conn;

l?? SqlCommand cmd = new SqlCommand(@"select * from employees");

l?? SqlCommand cmd = new SqlCommand(@"select * from employees",conn);

l?? SqlCommand cmd = new SqlCommand(sql,conn);

l? SqlCommand cmd = conn.CreateCommand();

ExecuteNonQuery????????????????????? 返回受影响的行数

ExecuteScalar????????????????????????????? 单个值，返回第一行第一列的Object类型

ExecuteReader?????????????????????????? 0个或多个行，返回一个数据读取器，它是SqlDataReader类的一????????????????????????????????????????????????????????? 个实例

ExecuteXmlReader??????????????????? XML

SqlDataReader

不能直接实例化数据读取器，而是通过执行命令对象的ExecuteReader方法创建它的实现。

为了能够把连接用于另一个目的，或在数据库上执行另一查询，调用SqlDataReader的Close方法显示关闭读取器。这是因为，一旦把读取器附着到活动的连接上，连接就会一直忙于为读取器获取数据，而不能用于另一目的，直至端口读取器为止。

Depth()??????????????????????????????? 该属性表示当前行的嵌套深度

FieldCount()??????????????????????? 该属性表示结果集中的列数

GetDataTypeName()??????? 这个方法接受索引，返回含有列数据类型名称的字符串

GetFieldType()??????????????????? 这个方法接受索引，返回对象的.NET Frameword数据类型

GetName????????? ()???????????????????????? 这个方法接受索引，返回指定列的名词

GetOrdinal()?????????????????????? 这个方法接受列名，返回列的索引

Sample:

???????? ?? string sql = @"select * from employees";

??????????? string connString = @"server = (local)/SQLEXPRESS; Initial Catalog = Northwind; user id = sa; password = 12345;";

??????????? SqlConnection conn = new SqlConnection();

??????????? conn.ConnectionString = connString;

??????????? SqlCommand cmd = conn.CreateCommand();

??????????? SqlDataReader reader = null;

??????????? try

??????????? {

??????????????? conn.Open();

??????????????? cmd.CommandText = @"select count(*) from employees";

??????????????? Console.WriteLine("Number of Employees is: {0}",cmd.ExecuteScalar());

??????????????? cmd.CommandText = sql;

??????????????? reader = cmd.ExecuteReader();

??????????????? //Console.WriteLine("First Name|Last Name");

??????????????? Console.WriteLine(reader.GetDataTypeName(1).PadLeft(10) + "|" + reader.GetDataTypeName(2));

??????????????? Console.WriteLine(reader.GetName(1).PadLeft(10) + "|" + reader.GetName(2));//列名

??????????????? while (reader.Read())

??????????????? {

??????????????????? Console.WriteLine("{0}|{1}",

??????????????????????? reader["FirstName"].ToString().PadLeft(10),

????????? ??????????????reader["LastName"].ToString().PadRight(10)

??????????????????? );

??????????????? }

??????????????? Console.WriteLine("Number of columns in a row: {0}",reader.FieldCount);

??????????????? Console.WriteLine("/"FirstName/" is at index {0} and its type is: {1}",

??????????????????? reader.GetOrdinal("FirstName"),

??????????????????? reader.GetFieldType(reader.GetOrdinal("FirstName")));

??????????? }

??????????? catch (SqlException e)

??????????? {

??????????????? Console.WriteLine("Error:" + e);

??????????? }

??????????? finally

??????????? {

??????????????? reader.Close();

??????????????? conn.Close();

??????????? }

SqlDataAdapter

做为数据源与数据集的桥梁。

l?? SqlDataAdapter da= new SqlDataAdapter();

l?? SqlDataAdapter da= new SqlDataAdapter(sql) ;

l?? SqlDataAdapter da= new SqlDataAdapter(sql,conn);

Sample:

??????????? string connString = "Data Source=IBM-2E7EC1F0E54//SQLEXPRESS;Initial Catalog=Northwind;User ID=sa;Password=12345";

??????????? string sql = @"select productname,unitprice from products where unitprice < 20";

??????????? SqlConnection conn = new SqlConnection(connString);

??????????? try

??????????? {

??????????????? conn.Open();

??????????????? SqlDataAdapter da= new SqlDataAdapter(sql,conn);

??????????????? DataSet ds = new DataSet();

??????????????? da.Fill(ds,"products");

??????????????? DataTable dt = ds.Tables["products"];

??????????????? foreach(DataRow row in dt.Rows)

??????????????? {

??????????????????? foreach(DataColumn col in dt.Columns)

??????????????????? {

??????????????????????? Console.WriteLine(row[col]);

??????????????????? }

??????????????????? Console.WriteLine("".PadLeft(20,'-'));

??????????????? }

??????????? }

??????????? catch(SqlException e)

??????????? {

??????????????? Console.WriteLine(e);

??????????? }

??????????? finally

??????????? {

??????????????? conn.Close();

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!