http://msdn.microsoft.com/en-us/library/ms971557.aspx
1. Open the transaction using Connection.BeginTransaction().
1. Open the transaction using Connection.BeginTransaction().
2. Enlist statements or stored procedure calls in the transaction by setting the Command.Transaction property of the Command objects associated with them.
3. Depending on the provider, optionally use Transaction.Save() or Transaction.Begin() to create a savepoint or a nested transaction to enable a partial rollback.
4. Commit or roll back the transaction using Transaction.Commit() or Transaction.Rollback().
public void SPTransaction(int partID, int numberMoved, int siteID)
{
// Create and open the connection.
SqlConnection conn = new SqlConnection();
string connString = "connection string";
conn.ConnectionString = connString;
conn.Open();
// Create the commands and related parameters.
// cmdDebit debits inventory from the WarehouseInventory
// table by calling the DebitWarehouseInventory stored procedure.
SqlCommand cmdDebit = new SqlCommand("DebitWarehouseInventory", conn);
cmdDebit.CommandType = CommandType.StoredProcedure;
cmdDebit.Parameters.Add("@PartID", SqlDbType.Int, 0, "PartID");
cmdDebit.Parameters["@PartID"].Direction =ParameterDirection.Input;
cmdDebit.Parameters.Add("@Debit", SqlDbType.Int, 0, "Quantity");
cmdDebit.Parameters["@Debit"].Direction = ParameterDirection.Input;
// cmdCredit adds inventory to the SiteInventory
// table by calling the CreditSiteInventory stored procedure.
SqlCommand cmdCredit = new SqlCommand("CreditSiteInventory", conn);
cmdCredit.CommandType = CommandType.StoredProcedure;
cmdCredit.Parameters.Add("@PartID", SqlDbType.Int, 0, "PartID");
cmdCredit.Parameters["@PartID"].Direction =ParameterDirection.Input;
cmdCredit.Parameters.Add("@Credit", SqlDbType.Int, 0, "Quantity");
cmdCredit.Parameters["@Credit"].Direction = ParameterDirection.Input;
cmdCredit.Parameters.Add("@SiteID", SqlDbType.Int, 0, "SiteID");
cmdCredit.Parameters["@SiteID"].Direction = ParameterDirection.Input;
// Begin the transaction and enlist the commands.
SqlTransaction tran = conn.BeginTransaction();
cmdDebit.Transaction = tran;
cmdCredit.Transaction = tran;
try
{
// Execute the commands.
cmdDebit.Parameters["@PartID"].Value = partID;
cmdDebit.Parameters["@Debit"].Value = numberMoved;
cmdDebit.ExecuteNonQuery();
cmdCredit.Parameters["@PartID"].Value = partID;
cmdCredit.Parameters["@Credit"].Value = numberMoved;
cmdCredit.Parameters["@SiteID"].Value = siteID;
cmdCredit.ExecuteNonQuery();
// Commit the transaction.
tran.Commit();
}
catch(SqlException ex)
{
tran.Rollback();
// Additional error handling if needed.
}
finally
{ conn.Close(); }}
Using Transactions with a DataAdapter
public void CBTransaction()
{
// Create and open the connection.
SqlConnection conn = new SqlConnection();
string connString = "...";
conn.ConnectionString = connString;
conn.Open();
// Create the DataAdapters.
string cmdString = "Select WIID, PartID, Quantity from WarehouseInventory";
SqlDataAdapter daWarehouse = new SqlDataAdapter(cmdString,conn);
cmdString = "Select SiteID, PartID, Quantity from SiteInventory";
SqlDataAdapter daSite = new SqlDataAdapter(cmdString,conn);
// Create the DataSet.
DataSet ds = new DataSet();
// Create the CommandBuilders and generate
// the INSERT/UPDATE/DELETE commands.
SqlCommandBuilder cbWarehouse = new SqlCommandBuilder(daWarehouse);
SqlCommand warehouseDelete = cbWarehouse.GetDeleteCommand();
SqlCommand warehouseInsert = cbWarehouse.GetInsertCommand();
SqlCommand warehouseUpdate = cbWarehouse.GetUpdateCommand();
SqlCommandBuilder cbSite = new SqlCommandBuilder(daSite);
SqlCommand siteDelete = cbSite.GetDeleteCommand();
SqlCommand siteInsert = cbSite.GetInsertCommand();
SqlCommand siteUpdate = cbSite.GetUpdateCommand();
// Fill the DataSet.
daWarehouse.Fill(ds, "WarehouseInventory");
daSite.Fill(ds, "SiteInventory");
// Begin the transaction and enlist the commands.
SqlTransaction tran = conn.BeginTransaction();
warehouseDelete.Transaction = tran;
warehouseInsert.Transaction = tran;
warehouseUpdate.Transaction = tran;
siteDelete.Transaction = tran;
siteInsert.Transaction = tran;
siteUpdate.Transaction = tran;
// Modify data to move inventory from WarehouseInventory to SiteInventory.
try
{
//Execute the commands
daWarehouse.Update(ds, "WarehouseInventory");
daSite.Update(ds, "SiteInventory");
//Commit the transaction
tran.Commit();
}
catch(SqlException ex)
{
//Roll back the transaction.
tran.Rollback();
//Additional error handling if needed.
}
finally
{ conn.Close(); }}
Using Savepoints
public void SavepointTransaction()
{
// Create and open the connection.
SqlConnection conn = new SqlConnection();
string connString = ". . .";
conn.ConnectionString = connString;
conn.Open();
// Create the commands.
// cmdInsertCustomer creates a new customer record by calling the DebitWarehouseInventory
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
{ conn.Close(); }}
Using Nested Transactions
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
{ conn.Close(); }}
