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();

            }