ADO.NET 的DataReader 和事务处理
在做数据库课程设计的时候遇到两个问题,第一个是如何在打开一个数据库连接的情况下如何执行两个select语句,第二个是借书的过程中要涉及多个表,如何在借书失败时,将已经修改的表恢复呢?
解决方法原来是这样的:
对于第一个问题:我们可以使用DataReader的NextResult()方法.只需要复给SqlCommand对象两个SQL SELECT语句,调用DataReader的俄ExecuteReader方法时将返回两个结果集,再使用SqlDataReader的Nextresult方法,就可以使数据读取器前进到下一个结果集,这样就实现了一次执行了多条SQL语句.最后关闭SQL连接就OK了.
看看例子:
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "数据库连接字符串";
![](/Images/OutliningIndicators/None.gif)
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT CustomerCode,CustomerName FORM Customer;" + "SELECT EmployeeCode,EmployeeName FORM Employee";
cmd.Connection = sqlconn;
sqlconn.Open();
SqlDataReader rd = cmd.ExecuteReader();
try
{
do
{
while (rd.Read())
{
Console.WriteLine("{0}-{1}", rd.GetString(0), rd.GetString(1));
}
rd.Close();
![](/Images/OutliningIndicators/InBlock.gif)
}
while (rd.NextResult());
rd.Close();
}
catch
{
throw;
}
finally
{
sqlconn.Close();
}
第二个问题:借书过程中,我们要修改图书信息,读者借阅列表,如果中途出错,可数据库已经修改了部分,这显然是不行的.用一个更简单的例子比喻,就像是银行转帐,A先在A账户上划走100元,B再在B帐户上添加100元,可是B银行由于某种原因添加失败,这样转账没有成功,那就需要回滚,将A的操作也注销掉.这样就需要用到事务(Transaction).
1.使用Connection对象的BeginTransaction方法创建事务;
2.通过设置Command对象的Transaction属性来把需要在事务中执行的数据库操作命令登记到事务中;
3.执行Command对象;
4.Commit事务或者Rollback事务;
代码:
解决方法原来是这样的:
对于第一个问题:我们可以使用DataReader的NextResult()方法.只需要复给SqlCommand对象两个SQL SELECT语句,调用DataReader的俄ExecuteReader方法时将返回两个结果集,再使用SqlDataReader的Nextresult方法,就可以使数据读取器前进到下一个结果集,这样就实现了一次执行了多条SQL语句.最后关闭SQL连接就OK了.
看看例子:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
第二个问题:借书过程中,我们要修改图书信息,读者借阅列表,如果中途出错,可数据库已经修改了部分,这显然是不行的.用一个更简单的例子比喻,就像是银行转帐,A先在A账户上划走100元,B再在B帐户上添加100元,可是B银行由于某种原因添加失败,这样转账没有成功,那就需要回滚,将A的操作也注销掉.这样就需要用到事务(Transaction).
1.使用Connection对象的BeginTransaction方法创建事务;
2.通过设置Command对象的Transaction属性来把需要在事务中执行的数据库操作命令登记到事务中;
3.执行Command对象;
4.Commit事务或者Rollback事务;
代码:
1
SqlConnection sqlconn = new SqlConnection("数据库连接字符串");
2
sqlconn.Open();
3![](/Images/OutliningIndicators/None.gif)
4
string insertStr="Inser into Customers(CustomerID,CompanyName) VALUES ('CITIC','农业银行')";
5
SqlCommand insertCmd = new SqlCommand(insertStr, sqlconn);
6![](/Images/OutliningIndicators/None.gif)
7
string updateStr = "UPDATE Customers SET CompanyName='中国银行' WHERE CustomerID='CITIC'";
8
SqlCommand updateCmd = new SqlCommand(updateStr, sqlconn);
9![](/Images/OutliningIndicators/None.gif)
10
//创建事务
11
SqlTransaction myTrans = sqlconn.BeginTransaction();
12
//将Cmd对象登记到事务中
13
insertCmd.Transaction = myTrans;
14
updateCmd.Transaction = myTrans;
15![](/Images/OutliningIndicators/None.gif)
16
try
17
{
18
insertCmd.ExecuteNonQuery();
19
updateCmd.ExecuteNonQuery();
20
myTrans.Commit();
21![](/Images/OutliningIndicators/InBlock.gif)
22
}
23
catch (Exception ex)
24
{
25
myTrans.Rollback();
26
}
27
finally
28
{
29
sqlconn.Close();
30
}
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/InBlock.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/InBlock.gif)
22
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
25
![](/Images/OutliningIndicators/InBlock.gif)
26
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)