|
使用Savepoints
当使用savepoints, 记住仅仅是回滚savepoints并不是足够的．当局部回滚后事务必须仍被提交．同时，如果你选择实施它们，如何通知用户或处理局部完成事务是非常重要的．
接下来的代码描述创建一个新的客户，同时处理客户请求．你不得不执行慢的连接，或某些原因在检查库以前的操作库存等级成本是被禁止的．时间的99.9%整个过程应该被成功结束．然而，如果在SiteInventory表 保持最小的库存等级限制，它将会失败．你可以看一下的代码：
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
…public void SavepointTransaction()
{
// Create and open the connection.
SqlConnection conn = new SqlConnection();
string connString = "Server= SqlInstance;Database=Test;"
+ "Integrated Security=SSPI";
conn.ConnectionString = connString;
conn.Open();
// Create the commands.
// cmdInsertCustomer creates a new customer record
// by calling the DebitWarehouseInventory
// stored procedure.
SqlCommand cmdInsertCustomer =
new SqlCommand("CreateCustomer", conn);
cmdInsertCustomer.CommandType = CommandType.StoredProcedure;
cmdInsertCustomer.Parameters.Add
("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
cmdInsertCustomer.Parameters.Add
("@LastName", SqlDbType.NVarChar, 50, "LastName");
cmdInsertCustomer.Parameters.Add
("@Email", SqlDbType.NVarChar, 50, "Email");
cmdInsertCustomer.Parameters.Add("@CID", SqlDbType.Int, 0);
cmdInsertCustomer.Parameters["@FirstName"].Direction =
ParameterDirection.Input;
cmdInsertCustomer.Parameters["@LastName"].Direction =
ParameterDirection.Input;
cmdInsertCustomer.Parameters["@Email"].Direction =
ParameterDirection.Input;
cmdInsertCustomer.Parameters["@CID"].Direction =
ParameterDirection.Output;
// cmdRequestMaterials creates a pick list
// of the materials requested by the customer
// by calling the InsertMaterialsRequest
// stored procedure.
SqlCommand cmdRequestMaterials =
new SqlCommand("InsertMaterialsRequest", conn);
cmdRequestMaterials.CommandType = CommandType.StoredProcedure;
cmdRequestMaterials.Parameters.Add
("@CustomerID", SqlDbType.Int, 0, "CustomerId");
cmdRequestMaterials.Parameters.Add
("@RequestPartID", SqlDbType.Int, 0, "PartId");
cmdRequestMaterials.Parameters.Add
("@Number", SqlDbType.Int, 0, "NumberRequested");
cmdRequestMaterials.Parameters["@CustomerID"].Direction =
ParameterDirection.Input;
cmdRequestMaterials.Parameters["@RequestPartID"].Direction =
ParameterDirection.Input;
cmdRequestMaterials.Parameters["@Number"].Direction =
ParameterDirection.Input;
// cmdUpdateSite debits the requested materials
// from the inventory of those available by calling
// the UpdateSiteInventory stored procedure.
SqlCommand cmdUpdateSite =
new SqlCommand("UpdateSiteInventory", conn);
cmdUpdateSite.CommandType = CommandType.StoredProcedure;
cmdUpdateSite.Parameters.Add
("@SiteID", SqlDbType.Int, 0, "SiteID");
cmdUpdateSite.Parameters.Add
("@SitePartID", SqlDbType.Int, 0, "PartId");
cmdUpdateSite.Parameters.Add
("@Debit", SqlDbType.Int, 0, "Debit");
cmdUpdateSite.Parameters["@SiteID"].Direction =
ParameterDirection.Input;
cmdUpdateSite.Parameters["@SitePartID"].Direction =
ParameterDirection.Input;
cmdUpdateSite.Parameters["@Debit"].Direction =
ParameterDirection.Input;
// Begin the transaction and enlist the commands.
SqlTransaction tran = conn.BeginTransaction();
cmdInsertCustomer.Transaction = tran;
cmdUpdateSite.Transaction = tran;
cmdRequestMaterials.Transaction = tran;
try
{
// Execute the commands.
cmdInsertCustomer.Parameters["@FirstName"].Value
= "Mads";
cmdInsertCustomer.Parameters["@LastName"].Value
= "Nygaard";
cmdInsertCustomer.Parameters["@Email"].Value
= "MadsN@AdventureWorks.com";
cmdInsertCustomer.ExecuteNonQuery();
tran.Save("Customer");
cmdRequestMaterials.Parameters["@CustomerID"].Value
= cmdInsertCustomer.Parameters["@CID"].Value;
cmdRequestMaterials.Parameters["@RequestPartID"].Value
= 3;
cmdRequestMaterials.Parameters["@Number"].Value
= 22;
cmdRequestMaterials.ExecuteNonQuery();
cmdUpdateSite.Parameters["@SitePartID"].Value
= 3;
cmdUpdateSite.Parameters["@Debit"].Value
= 22;
cmdUpdateSite.Parameters["@SiteID"].Value
= 4;
cmdUpdateSite.ExecuteNonQuery();
// Commit the transaction.
tran.Commit();
}
catch(SqlException sqlEx)
{
try
{
// Roll back the transaction
// to the savepoint.
Console.WriteLine(sqlEx.Message);
tran.Rollback("Customer");
tran.Commit();
// Add code to notify user or otherwise handle
// the fact that the procedure was only
// partially successful.
}
catch(SqlException ex)
{
// If the partial rollback fails,
// roll back the whole transaction.
Console.WriteLine(ex.Message);
tran.Rollback();
// Additional error handling if needed.
}
}
finally
{
// Close the connection.
conn.Close();
}
}
使用内嵌事务
内嵌事务如savepoints一样考虑同样的注意事项．为了确认局部回滚仍然需要提交，处理局部完成过程．
以下的代码示例描述创建一个新的订单，同时创建了订单项目． 如果创建订单列表失败－可能是PartID无效的对于选择的位置来说－你仍然需要进入订单，在其它方面选取． 内嵌的try/catch 块封装了内嵌事务用来处理选取列表，允许内部事务提交或失败依赖于外部的事务．外部的catch 块回滚整个事务如果订单创建过程失败．
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Text;
…public void NestedTransaction(string UID, string pwd)
{
// Create and open the connection.
OleDbConnection conn = new OleDbConnection();
StringBuilder sb = new StringBuilder();
sb.Append("Jet OLEDB:System database=");
sb.Append(@"C:\Databases\system.mdw;");
&nbs [1] [2] 下一页
[C语言系列]NET 中C#的switch语句的语法 [系统软件]托拽Explore中的文件到VB.net的窗口
[系统软件]Boost库在XP+Visual C++.net中的安装 [常用软件]新配色面板：Paint.Net3.0RC1官方下载
[常用软件]用内建的“Net Meeting”聊天 [VB.NET程序]Henry的VB.NET之旅（三）—共享成员
[VB.NET程序]Henry的VB.NET之旅（二）—构造与析构 [VB.NET程序]Henry的VB.NET之旅（一）—失踪的窗体
[VB.NET程序]在托盘上显示Balloon Tooltip（VB.NET） [VB.NET程序]Henry手记－VB.NET中动态加载Treeview节点（二）
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
