使用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
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;");
sb.Append(@"Data Source=C:\Databases\orders.mdb;");
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
sb.Append("User ID=" + UID + ";Password=" + pwd);
string connString = sb.ToString();
conn.ConnectionString = connString;
conn.Open();
// Create the commands.
string cmdString = "Insert into Orders"
+ " (OrderID, OrderDate, CustomerID)"
+ " values('ABC60', #4/14/04#, 456)";
OleDbCommand cmdInsertOrder = new OleDbCommand(cmdString,conn);
//No need to insert OrderLineID, as that is an AutoNumber field.
cmdString = "Insert into OrderLines (OrderID, PartID, Quantity)"
+ " values('ABC60', 25, 10)";
OleDbCommand cmdInsertOrderLine =
new OleDbCommand(cmdString,conn);
cmdString = "Insert into PickList (OrderID, PartID, Quantity)"
+ " values('ABC60', 25, 10)";
OleDbCommand cmdCreatePickList =
new OleDbCommand(cmdString,conn);
// Begin the outer transaction and
// enlist the order-related commands.
OleDbTransaction tran = conn.BeginTransaction();
cmdInsertOrder.Transaction = tran;
cmdInsertOrderLine.Transaction = tran;
try
{
// Execute the commands
// to create the order and order
// line items.
cmdInsertOrder.ExecuteNonQuery();
cmdInsertOrderLine.ExecuteNonQuery();
// Create a nested transaction
// that allows the pick list
// creation to succeed or fail
// separately if necessary.
OleDbTransaction nestedTran = tran.Begin();
// Enlist the pick list command.
cmdCreatePickList.Transaction = nestedTran;
try
{
// Execute the pick list command.
cmdCreatePickList.ExecuteNonQuery();
// Commit the nested transaction.
nestedTran.Commit();
}
catch(OleDbException ex)
{
//Roll back the transaction.
nestedTran.Rollback();
// Add code to notify user or otherwise handle
// the fact that the procedure was only
// partially successful.
}
// Commit the outer transaction.
tran.Commit();
}
catch(OleDbException ex)
{
//Roll back the transaction.
tran.Rollback();
//Additional error handling if needed.
}
finally
{
// Close the connection.
conn.Close();
}
}
注：引擎提供者为Microsoft OLE DB，其支持内嵌事务．
总结
事务是一个强有力的方式，用来保证数据修改任务得到合适的处理，同时ado.net让我们处理数据存取的能力更容易．当你构建你的数据读取和使用方案的时候考虑到这些情况，你将会保证你数据的完整性．
