ado.net 学习小结

浏览数：25 / 时间：2015年06月09日

连接数据源

Connection对象。Connection对象处于最顶层，是所有数据访问请求的关口。我们通过其暴露的属性进行配置。下面是一段连接字符串的示例。

 1 if (string.IsNullOrEmpty(con.ConnectionString))
 2             {
 3                 con.ConnectionString = "Data Source=localhost\\sql08; Initial Catalog=ado_test;user ID=sa;password=test;";// Integrated Security=SSPI
 4             }
 5 
 6             if (con.State != ConnectionState.Open)
 7             {
 8                 con.Open();
 9             }
10             if (con.State == ConnectionState.Open)
11                 lbConnect.Text = "CONNECTED NOW!";

View Code

其中Data Source属性指连接的数据库实例；Initial Catalog或Database用于指定数据库的名称（SQLSERVER一个实例可以打开多个数据库）；Integrated Security或Trusted_Connection，如果为false（默认），则输入登录名和密码，可识别的值有True,False,Yes,No和SSPI，其中SSPI与True等效；Password和User ID不必多言。

以上几个属性是比较常见的属性。

连接字符串应该存储在项目的App.config文件中，这样如果修改连接，则无需更改程序内容。

考虑到各种处理和进程间通信的使用，创建到数据源的连接的代价是高昂的。为了解决这个问题，ADO.NET提出了连接池(connection pooling)的概念。

在每次连接请求时，如果连接字符串的值不发生改变，且连接池中包含现有的连接，那么ADO.NET则会重用现有连接，而不会创建新的连接。连接池的创建取决于连接字符串的值、事务和集成安全标识。因此强烈建议：在每次操作结束后，将Connection对象关闭，这样该连接会返回连接池。

方法 BeginTransaction:用于开始数据库事务，返回一个SqlTransaction对象。

Commit:提交

RollBack:回滚

Command对象。Command对象要与Connection对象配合使用才能执行对数据源的查询。可以把Connection对象赋给Command.Connection属性。

CommandText: 要执行的SQL文本。

CommandType:用于指定对CommandText的解释方式，值是枚举System.Data.CommandType类型，有Text,StoredProcedure和TableDirect。

Parameters:参数集合

Transaction:执行的事务

方法 ExecuteNonQuery:更新插入删除，选用此方法。以整数形式返回受影响行数。

ExecuteReader: 读取，返回一个就绪的SqlDataReader对象。

DataReader对象。

DataReader用于从数据源获取只读且只进的数据集。DataReader是在查询的过程中获取数据，而不是等待查询结束。这对提高应用程序的性能发挥着积极的作用。

DataReader要通过Command.ExecuteReader()获取，一旦获得便可以使用它的Read方法来获取数据记录。下面是一个示例，可参考：

http://hi.baidu.com/wcsjsdn/item/988454942293dad01b49dfb5

 1 command.CommandText = "select * from dbo.demo;";
 2             command.Parameters.Clear();
 3             command.Connection = con;
 4             if(con.State!=ConnectionState.Open)
 5             {
 6                 MessageBox.Show("PLZ open database!");
 7                 return;
 8             }
 9             sdr = command.ExecuteReader();
10             txtDataReader.Text=string.Empty;
11             if (sdr.HasRows)//判断是否有值
12             {
13                 while (sdr.Read())//调用Read方法，开始向前读取
14                     for (int i = 0; i <= sdr.FieldCount - 1; i++)//DataReader相当于一行数据
15                     {
16                         txtDataReader.Text += sdr[i].ToString().Trim() + " ;";
17                     }
18             }
19 
20             sdr.Close();

View Code

DataAdapter对象。

DataAdapter是客户端的DataSet和数据源之间的桥梁。我们可以通过它来增删查改数据。根据数据方向的不同，可以使用Fill（多用）方法和Update方法（少用）。

在SqlDataAdapter实例化时，可以为其提供一个Command对象。在默认情况下，这个Command会被赋给SqlDataAdapter.SelectCommand，在DataAdapter上的Fill方法调用后，该对象会使用SelectCommand属性的内容获取数据。此外还有DeleteCommand, InsertCommand,UpdateCommand属性。

 1 if (string.IsNullOrEmpty(con.ConnectionString))
 2             {
 3                 con.ConnectionString = "Data Source=localhost\\sql08; Initial Catalog=ado_test;user ID=sa;password=test;";// Integrated Security=SSPI
 4             }
 5             if (con.State != ConnectionState.Open)
 6                 con.Open();
 7             command.CommandText = "select * from dbo.demo;";
 8             command.Parameters.Clear();
 9             command.Connection = con;
10             sda = new SqlDataAdapter();
11             sda.SelectCommand = command;
12             ds = new DataSet();
13             sda.Fill(ds);
14             if (ds.Tables.Count > 0)
15                 this.dgvDemoData.DataSource = ds.Tables[0];
16             con.Close();

View Code

查看工作接手的项目源代码，发现其三层架构中数据库方法，如果是查询返回一个DataTable，使用DataAdapter.Fill()方法；如果是执行增加删除更改，则使用Command.ExecuteNonQuery()方法。如果使用实体类，其实可以返回DataReader对象，因为DataReader比DataTable轻量。

下面是我做的一个帮助类，欢迎大家指出问题！

应该注意以下两点：

1. SqlParameterCollection类型没有定义其构造函数，因此传参数只能使用集合类（本例使用Dictionary<string,string>），再将其一个个添加到Command对象的Parameters里面去。

2.使用帮助类的帮助方法，返回一个SqlDataReader对象，这时将Connection对象关闭，会发现SqlDataReader随之关闭无法使用。如果在DataReader使用后手动将其关闭，不仅需要暴露Connection对象且用户可能没有关闭。正确的做法是在执行Command的ExecuteReader时传入枚举值CommandBehavior.CloseConnection，在使用完DataReader并将其关闭，关联的Connection对象也随之关闭。

 1         /// <summary>
 2         /// 查询
 3         /// </summary>
 4         /// <param name="queryString">查询语句</param>
 5         /// <param name="parameters">参数集合</param>
 6         /// <returns>DataReader</returns>
 7         public static SqlDataReader Query(string queryString, IDictionary<string, string> parameters)
 8         {
 9             _command.CommandText = queryString;
10             _command.CommandType = System.Data.CommandType.Text;
11             _command.Parameters.Clear();
12             foreach (KeyValuePair<string, string> item in parameters)
13             {
14                 _command.Parameters.AddWithValue(item.Key, item.Value);
15             }
16             _command.Connection = _con;
17             _con.Open();
18             _dataReader = _command.ExecuteReader(CommandBehavior.CloseConnection);//DataReader关闭后，关联的Connection对象也随之关闭
19             //_con.Close();不能关闭，关闭后DataReader对象无法使用，将其关闭的方法见上一语句
20             return _dataReader;
21         }
22 
23         /// <summary>
24         /// 执行事务操作
25         /// </summary>
26         /// <param name="transactionString">执行语句集合</param>
27         /// <param name="parameters">对应的参数集合的集合</param>
28         /// <returns>bool，执行是否成功</returns>
29         public static bool ExecuteNonQuery(IList<string> transactionString, IList<Dictionary<string, string>> parameters)
30         {
31             _command.Connection = _con;
32             _con.Open();
33             SqlTransaction transaction = _command.Connection.BeginTransaction();
34             _command.Transaction = transaction;//ExecuteNonQuery要求命令拥有事务，必须赋予它一个事务，否则报错
35             try
36             {
37                 for (int i = 0; i <= transactionString.Count - 1; i++)
38                 {
39                     _command.CommandText = transactionString[i];
40                     _command.CommandType = CommandType.Text;
41                     _command.Parameters.Clear();
42                     foreach (KeyValuePair<string, string> item in parameters[i])
43                     {
44                         _command.Parameters.AddWithValue(item.Key, item.Value);
45                     }
46                     _command.ExecuteNonQuery();
47                 }
48 
49                 transaction.Commit();
50             }
51             catch (Exception ex)
52             {
53                 transaction.Rollback();
54                 throw new Exception(ex.Message);
55             }
56             finally
57             {
58                 transaction.Dispose();
59                 _command.Connection.Close();
60             }
61 
62             return true;
63         }

View Code

下面是操作代码

 1 string queryString = "select * from dbo.demo d where d.id = @id";
 2             Dictionary<string,string> paras=new Dictionary<string,string>();
 3             paras.Add("@id","1");
 4 
 5             using (SqlDataReader reader = DataHelper.Query(queryString, paras))//DataReader对象将在本块结束后释放，同时关闭Connection对象
 6             {
 7                 if (reader.HasRows)
 8                 {
 9                     while (reader.Read())
10                     {
11                         Console.WriteLine(reader[0] + "," + reader[1].ToString().Trim() + "," + reader[2]);
12                     }
13                 }
14                 Console.WriteLine(DataHelper._con.State.ToString());
15             }
16             Console.WriteLine(DataHelper._con.State.ToString());
17 
18             List<string> listTransactionString = new List<string>();
19             List<Dictionary<string,string>> listParameters = new List<Dictionary<string,string>>();
20 
21             string transactionString = "insert into dbo.demo(ID,Name,Remark) values(@id,@name,@remark)";
22             Dictionary<string, string> parasTransaction = new Dictionary<string, string>();
23             parasTransaction.Add("@id", "5");
24             parasTransaction.Add("@name", "jack");
25             parasTransaction.Add("@remark", "cleaner");
26             listTransactionString.Add(transactionString);
27             listParameters.Add(parasTransaction);
28 
29             string transactionString2 = "insert into dbo.demo(ID,Name,Remark) values(@id,@name,@remark)";
30             Dictionary<string, string> parasTransaction2 = new Dictionary<string, string>();
31             parasTransaction2.Add("@id", "6");
32             parasTransaction2.Add("@name", "mike");
33             parasTransaction2.Add("@remark", "cleaner");
34             listTransactionString.Add(transactionString2);
35             listParameters.Add(parasTransaction2);
36             
37             if(DataHelper.ExecuteNonQuery(listTransactionString,listParameters))
38             {
39                 Console.WriteLine("transaction succeed!");
40             }
41             Console.Read();