Use SavePoints
When using savepoints, remember that just rollback SavePoints is not enough. When the local rollback, the transaction must still be submitted. Also, if you choose to implement them, how to inform users or deal with local completion transactions is important.
The next code description creates a new customer while processing customer request. You have to perform a slow connection, or some reason is prohibited by the operating inventory level cost before the check library. The 99.9% of time should be successfully concluded. However, if the SiteIndory table remains the least inventory level restriction, it will fail. You can take a look at the code:
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;
Cn.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 massials requester 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 UpdatesInventory Stored Procedure.
SQLCommand cmdupdatesite =
New SQLCommand ("UpdateSiteIndory", 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 transportion
// to the savepoint.
Console.writeLine (Sqlex.Message);
Tran. Rollback ("Customer");
TRAN.COMMIT ();
// add code to notety user or ostherwise 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 ();
}
}
Use embedded transactions
Embedded matters consider the same considerations like SavePoints. To confirm that local rollback still needs to be submitted, handle local completion processes.
The following code sample description creates a new order and creates an order item. If you create an order list fails - may be invalid for PartiD to the location of the selected location - you still need to enter the order, select it in other respects. The embedded TRY / CATCH block encapsulates the embedded transaction to handle the selection list, allowing internal transactions to commit or fail to depend on external transactions. The external Catch block is rolled back to the entire transaction if the order creation process fails.
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;
Cn.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 this is an autonumber field.
cmdstring = "INSERT INTO ORDERLINES (ORDERID, PARTID, QUANTITY"
"VALUES ('ABC60', 25, 10)";
OLEDBCOMMAND CMDISERTORDERLINE =
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
// creection 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 notety user or ostherwise handle
// the fact this the processure 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 ();
}
}
Note: The engine provider is Microsoft OLE DB that supports embedded transactions.
to sum up
