ADO.NET - 全面梳理

目录：

1. 简单的介绍下ADO.NET
2. SqlConnection(连接对象)
3. SqlCommand(命令对象)
4. SqlParameter(Sql参数)
5. SqlDataReader(数据流读取器)
6. SqlTransaction(事务)
7. SqlDataAdapter(数据适配器)
8. DataSet，DataTable，DataRow，DataColumn
9. 封装数据库操作类(这才是精华) 

一丶简单的介绍下ADO.NET

 　　了解System.Data命名空间下我们常用的一些类：

 1 ①System.Data  → DataTable，DataSet，DataRow，DataColumn，DataRelation，Constraint，DataColumnMapping，DataTableMapping 2 ②System.Data.Coummon     → 各种数据访问类的基类和接口 3 ③System.Data.SqlClient   → 对Sql Server进行操作的数据访问类 4   主要有：   a) SqlConnection            → 数据库连接器 5             b) SqlCommand               → 数据库命名对象 6             c) SqlCommandBuilder        → 生存SQL命令 7             d) SqlDataReader            → 数据读取器 8             e) SqlDataAdapter           → 数据适配器，填充DataSet 9             f) SqlParameter             → 为存储过程定义参数10             g) SqlTransaction           → 数据库事物

  

二丶SqlConnection(连接对象)

　　1.连接字符串

　　　　基本语法：数据源(Data Source)+数据库名称(Initial Catalog)+用户名(User ID)+密码(Password)（这种方式比较安全）!

　　　　推荐文章 ：SQL Server 连接字符串和身份验证，你必须知道的ADO.NET（三） 连接字符串，你小觑了吗？，SQL Server 2008连接字符串写法大全，连接字符串有很多的写法，最保险的写法可以借助“SqlConnectionStringBuilder”类，它提供了全面的连接字符串的属性，以至于减少出错率（相关属性查MSDN），还有大多数连接字符串都写在配置文件里面了！

　　2.创建连接对象

View Code

1 SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder()2  {3      DataSource = "",4      InitialCatalog = "",5      UserID = "",6      Password = ""7  };8 SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

　　3.打开和关闭连接对象(使用Using来关闭连接)

View Code

1 using(SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString()))2 {3     connection.Open();4     connection.Close();5 }

 　　　　关于连接池的文章：你必须知道的ADO.NET（五） 细说数据库连接池(写的还真不赖啊)，顶...

 

 三丶SqlCommand(命令对象)

　　1.实例化的时候默认初始化的四个属性

　　2.创建命令对象

　　　　使用连接对象的“CreateCommand()”方法创建命令对象，也可以使用new来实例化对象！

1 SqlCommand command = connection.CreateCommand(); //这种方式比较好，也可以自己实例化一个对象！

　　3.几个重要属性

　　　　①CommandText：获取或设置要对数据源执行的 Transact-SQL 语句、表名或存储过程！

　　　　②CommandType：设置你执行的SQL语句是存储过程还是T-SQL(是一个枚举)！

　　　　

　　　　③Parameters：设置你T-SQL中你需要用到的参数(后面会讲到)，是一个“SqlParametersCollection”类型，这个属性很重要，是你通过代码给SQL语句传递参数的途径，所以记住语法，记住一些使用规则讲对编码有很大的帮助！

　　4.几个重要的方法(相信大家熟悉的不能再熟悉了)

　　　　①ExecuteNonQuery：返回是影响的行数(int)，主要执行更新，添加，删除等操作！

　　　　②ExecuteReader：执行SQL或存储过程，返回的是SqlDataReader类型，主要用来查询！

　　　　　　★  这边注意这个方法的重载 CommandBehaviour 枚举，成员如下：

　　　　

1 command.ExecuteReader(CommandBehavior.CloseConnection); //在执行读取之后会自动关闭连接对象

 　　　 ③ExecuteScalar：返回执行结果集中的第一行第一列，如果没有数据，则返回NULL！

　　　　 Note：因为可能会返回“Null”值，所以需要对结果进行判断，如下：

View Code

1         object my = cmd.ExecuteScalar();2                 if (object.Equals(my,null))  //可以使用Equals进行Null值的判断，易读性强3                     Console.WriteLine("Not Data");4                 else5                     Console.WriteLine("Yes");

　　　　④CreateParameter：创建SqlParameter实例

1 SqlParameter para = cmd.CreateParameter() //此方法适合SQL语句中只有一个参数的情况！

 　　　　推荐文章：你必须知道的ADO.NET（六） 谈谈Command对象与数据检索

　　　　　　　　　 你必须知道的ADO.NET（七） Wow！Command对象高级应用

 

四丶SqlParameter(Sql参数)

　　1.几个重要的属性　　　　

      　　ParameterName：   设置参数名

      　　Value：           　　 给参数设置值

　　　　Size:             　　  设置参数字节最大大小(以字节为但为)

　　　　SqlDbType: 　　　　参数在SQL中的类型　　

View Code

1             SqlParameter paras = new SqlParameter()2              {3                     ParameterName = "@name",4                     Value = 10,5                     SqlDbType = SqlDbType.Int,6                     Size = 47              };

　　2.命令对象添加参数集合的几种方法

　　　　①AddWithValue

　　　　②Add

　　　　③AddRange

　　　　推荐文章：SqlParameter的作用与用法，代码如下：

 1  using (SqlConnection connection = new SqlConnection("")) 2  { 3      SqlCommand command = connection.CreateCommand(); 4      command.CommandText = ""; 5  6      //可以使用这种方式添加多个参数，不过方式不够好 7      command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "yang"; //第一种方式 8      command.Parameters.Add("@age", SqlDbType.Int).Value = 888; 9      command.Parameters.Add("@address", SqlDbType.NVarChar, 100).Value = "Jiang Su";10 11      //这种方式直接给定参数名和参数就可以了，可操作性比较差12      command.Parameters.AddWithValue("@name", "yang");13      command.Parameters.AddWithValue("@age", 888).SqlDbType = SqlDbType.Int;14      command.Parameters.AddWithValue("@address", "Jiang su").SqlDbType = SqlDbType.NVarChar;15 16      //直接使用参数集合添加你需要的参数，推荐这种写法17      SqlParameter[] parameters = new SqlParameter[]18      {19          new SqlParameter("@name",SqlDbType.NVarChar,100){Value = "yang"},20          new SqlParameter("@age",SqlDbType.Int,2){Value = 888},21          new SqlParameter("@address",SqlDbType.NVarChar,20){Value = "Jiang Su"}, 22      };23      command.Parameters.AddRange(parameters);  //参数也可以是一个Array数组，如果采用数组参数代码的可读性和扩展性就不是那么好了24 25      //当我们把参数都添加好之后，会生成一个“SqlParameterCollection”集合类型，相当于参数的集合26      //那么我们就可以对这些参数进行修改和移除了27      //说穿了“SqlParameterCollection”内部其实是一个List<SqlParameter>的集合，只是它里面的复杂度比较高，考虑的很全面28      command.Parameters[0].Value = "hot girl";29      command.Parameters[0].Size = 200;30  }

　　3.说说“SqlParameterCollection”，参数集合

 　　　　上面添加的“SqlParameter”参数都被添加到了“SqlParameterCollection”集合中去了，所以我们才能够对它进行读取和修改！

　　4.定义适当的参数属性获取存储过程的返回值(return) → Direction = ParameterDirection.Output

　　　　代码如下：

View Code

 1  using (SqlConnection conn = new SqlConnection("")) 2  { 3      conn.Open(); 4      SqlCommand cmd = conn.CreateCommand(); 5      cmd.CommandText = "myProc"; 6      cmd.CommandType = CommandType.StoredProcedure; 7      SqlParameter[] paras = new[]  //定义参数数组 8      { 9          new SqlParameter("@Description",SqlDbType.NVarChar,50,"RegionDescription"),10          new SqlParameter("@RegionID",SqlDbType.Int,0,ParameterDirection.Output,false,0,0,"RegionID",DataRowVersion.Default,null)        //定义此参数是Output参数11      };12      cmd.Parameters.AddRange(paras);13      cmd.UpdatedRowSource = UpdateRowSource.OutputParameters; //这句话可以不要14      //给参数赋值15      cmd.Parameters["@Description"].Value = "Yang";16      cmd.ExecuteNonQuery();17      //获取返回值18      int myRegionID = (int)cmd.Parameters["@RegionID"].Value;19      Console.WriteLine("这是返回值：{0}", myRegionID.ToString());20  }

 

五丶SqlDataReader(数据流读取器)

 　　说实话，如果单单知道怎么使用读取器，那是非常好学的，如果深入了解，它里面的知识将会非常的吸引人，那么就以我小菜的经验来说说把，各位不要见怪啊！

 　　1.基本用法

View Code

 1             using (SqlConnection conn = new SqlConnection("")) 2             { 3                 conn.Open(); 4                 SqlCommand command = conn.CreateCommand(); 5                 command.CommandText = ""; 6                 using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection)) 7                 { 8                     while (dr.Read()) 9                     {10                         //开始读取数据了，接下来你想怎么样就怎么样了11                         string str = dr.GetSqlString(0).ToString();12                     }13                 }14             }

 　　2.常用方法

　　　　①GetOrdinal：获取指定列名的列序号(索引号)，使用这个方法可以把经常变动的列进行固定

1 int name = dr.GetOrdinal("name"); //通过列名来获取当前列的索引号，这样如果下次你列名顺序发生变化也没有关系

　　　　②GetName：  获取列名，参数为指定列名的序列号，返回string

1 string columnName = dr.GetName(name); //通过列名所处的索引号来获取列名名称 

　　　　③IsDBNull：判断当前读取的数据是否为Null，返回类型为Bool　　　　　

1   dr.IsDBNull(coContactID) ? "NULL" : dr.GetInt32(coContactID).ToString() //相信大家都会使用的

 　　　　④NextResult：当查询为批处理查询时，使用这个方法去读取下一个结果集，返回值为Bool，如果存在多个结果集，则为 true；否则为 false

1 //select * from Employee;select * from County，这样的话就可以采用这种方式2 dr.NextResult(); //记住这个要放在while(dr.Read())之后，因为读取一个数据集之后才能读取下一个数据集 

　　　　⑤Read：读取数据

　　　　　　读取数据最重要的方法，不说了！

　　3.常用属性

　　　　①HasRow：判断是否包含一行或多行，也就是判断有没有数据，返回类型为Bool

　　　　②FieldCount：获取读取的列数，返回类型为Int

　　　　③IsClosed：判断读取的数据流是否关闭

　　　　　　所以灵活运用上面的属性讲增强代码的可读性和健壮性，综合示例：

View Code 

　　　　Note：当 SqlDataReader 关闭后，只能调用 IsClosed 和 RecordsAffected 属性，如果调用其它方法或属性将会报错！

　　4.性能深入剖析

　　　　读取数据的时候会有很多种写法，如dr[0].ToString()，dr["Name"].ToString()，dr.GetString(0)，dr.GetSqlString(0)等等的读取方式的写法，如果大家去网上查资料就会很容易发现这几种写法存在着一些差异！

　　　　下面是读取数据性能的总结：

1     SqlDataReader读取方法：2     1. DataReader     索引   + 基于 [序列号] → dr[0].ToString        | Index-based access3     2. DataReader     索引   + 基于 [列名]  → dr["Name"].ToString     | 性能最差4     3. Get            开头的 + 基于 [序列号] → dr.GetString(0)      | type-access5     4. GetSql         开头的 + 基于 [序列号] → dr.GetSqlString(0)   | Provider-specific typed accessor6     5. GetOrdinal()   通过列名获取这个列的序列号                      | 这个方法在提高性能上面有作用7     6. 性能(4) --> (3) --> (1) --> (2)

 　　　　Note：所以在对数据进行读取时要有针对的使用一些性能高的方法，也不是说要追求性能，只是这是一种习惯，对于大多数读取数据库的方法使用索引来读取无疑是最快，记住一句话，“当性能没有成为问题的时候，不要过度的去优化它”，高效而又优美的使用这些方法，才是王道！

　　　　推荐文章：SqlDataReader 提前终止的性能问题

　　　　PS：我这里的总结其实是以前在园子看到一个人写的，找了半个小时都没找到，如果有人看到过，发个链接给我，我补上！

　　5.SqlDataReader和DataSet的讨论

　　　　推荐文章：谈谈Asp.net网站优化一：SqlDataReader和DataSet的选择

 

六丶SqlTransaction(事务)

 　　1.代码中的事务　　　　

　　　　现在代码中基本使用存储过程来控制事务的处理，通过代码进行控制事务也是我们学习ADO.NET的任务之一！

　　　　事务是在连接对象之后创建，并把它跟命令对象进行关联，使用try.....Catch捕获异常，然后调用RollBack方法回滚事务！

　　　　Commit：提交

　　　　RollBack：回滚

View Code

  1 /// <summary>  2   3        /// 使用事务  4   5        /// </summary>  6   7        public void TransactionConn()  8   9        { 10  11            try 12  13            { 14  15                SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(); 16  17                connBuilder.DataSource = @"PC2011052108IXK\SQLEXPRESS"; 18  19                connBuilder.InitialCatalog = "Northwind"; 20  21                connBuilder.IntegratedSecurity = true; 22  23                using (SqlConnection conn = new SqlConnection(connBuilder.ConnectionString)) 24  25                { 26  27                    conn.Open(); 28  29                    Console.WriteLine("打开成功"); 30  31                    SqlCommand cmd = conn.CreateCommand(); 32  33                    //定义事务 34  35                    SqlTransaction trans; 36  37                    //开始事务,创建事务对象，不可以实例化事务对象 38  39                trans = conn.BeginTransaction("MyTransaction");  //通过连接对象创建事务对象 40  41                    //把事务对象给执行命令对象  42  43                    cmd.Transaction = trans;  //把事务跟命令对象进行关联 44  45                    //通过异常来发现错误，以至让事务回滚 46  47                    try 48  49                    { 50  51                        //这个是正确 52  53                        cmd.CommandText = "INSERT INTO dbo.CustomerDemographics  VALUES('B','这是一个不错的东西')"; 54  55                        cmd.ExecuteNonQuery(); 56  57                        //由于主键约束，这是错的 58  59                        cmd.CommandText = "INSERT INTO dbo.CustomerDemographics  VALUES('A','这是一个不错的东西')"; 60  61                        cmd.ExecuteNonQuery(); 62  63                        Console.WriteLine("插入成功"); 64  65                        //提交事务 66  67                        trans.Commit();  //这一步很重要 68  69                    } 70  71                    catch (SqlException ex) 72  73                    { 74  75                        //失败了就进行回滚 76  77                        Console.WriteLine("Error: {0}", ex.Message); 78  79                        //回滚 80  81                        trans.Rollback();        //事务的重要特性      82  83                        Console.WriteLine("回滚成功"); 84                    } 85  86                } 87  88            } 89  90            catch (SqlException ex) 91  92            { 93  94                Console.WriteLine(ex.Message); 95  96            } 97  98            finally 99 100            { 101 102            }103 104        }

　　2.事务中的命名存储点　　　　　　

　　　　一旦你定义了命名存储点，只能回滚命名存储点之后的操作，这是要是情况而使用！

　　　　这种情况是当你调用RollBack方法并重载一个命名存储点的参数，如下代码所示：

 1     using (SqlConnection conn = new SqlConnection(str)) 2     { 3          conn.Open(); 4          SqlTransaction transaction = conn.BeginTransaction(); 5          SqlCommand cmd = conn.CreateCommand(); 6          cmd.CommandText = ""; 7          cmd.Transaction = transaction; 8          //使用命名存储点 9          transaction.Save("this is point");  //定义命名存储点，使用Save方法先保存存储点，定义回滚数据的开始位置10                 11          //这边是你要回滚的操作代码，TO DO...12 13          //把从命名存储点到这里的操作进行回滚14          transaction.Rollback("this is point");  //回滚命名存储点              15      }

　　3.SQL语句中的事务(SQL Server中的事务其实很复杂的)

1 BEGIN TRANSACTION2 3     --你需要执行的更新，删除，插入的语句4     5 IF(@@ERROR > 0) //这是系统变量，存储你在执行更新，删除，插入操作时发生错误的记录编号6     ROLLBACK7 ELSE8     COMMIT

 　　　　推荐文章：浅谈SQL SERVER中事务的ACID

　　　　　　　　　 深入sql server中的事务

　　4.说说“TransactionScope”，让事务更加的简单 

 1 using (TransactionScope transactionScope = new TransactionScope()) 2 { 3     try 4     { 5         using (SqlConnection connection = new SqlConnection()) 6         { 7             // TO DO 8             //提交事务，如果有异常，他会自动回滚的 9             transactionScope.Complete();10         }11     }12     catch (Exception)13     {14         //捕获异常15         throw;16     }17 }

 　　　　推荐文章：C#综合揭秘——细说事务