Protecting Your Data Integrity with Transactions in ADO.NET

Protecting Your Data Integrity with Transactions in ADO.NET

Christa Carpentiere

May 2004

Applies TO: Microsoft? ADO.NETMICROSOFT? NET Framework

Summary: Transactions are a key feature for maintaining data integrity when modifying data Get an overview of transactions and their importance, then learn how to use them to protect data in your .NET applications (19 printed pages)..

Contents

IntroductionAtomy of a TransactionsTransactions with addAnetimplementing TransactionsConclusion

Introduction

Transactions are a feature offered by most enterprise-class databases for making sure data integrity is maintained when data is modified Maintaining data integrity protects the quality of the data that your organization relies upon;. After all, when you generate a report or engage in some Other Data-Driven Procedure, You Want to Know That The Information You are Working with is.

A Successful Transaction Must Pass The "Acid" TEST, THAT IS, IT MUST BE:

· Atomic - All statements in the transaction either completed successfully or they were all rolled back The task that the set of operations represents is either accomplished or not, but in any case not left half-done, to the detriment of your data..

· Consistent - All data touched by the transaction is left in a logically consistent state For example, if inventory numbers were decremented in one table, there has to be a related order that consumed that inventory The inventory can not just disappear...

· Isolated - The transaction must affect data without interfering with other concurrent transactions, or being interfered with by them This prevents transactions from making changes to data based on uncommitted information, for example changes to a record that are subsequently rolled back Most databases use.. Locking to maintain Transaction. Durable - The Data Changes Enacted by The Transaction Arector, And Will Persist Through A System Failure.

The classic example is submitting an order to an order entry system-you have to check the customer's credit level, create an order record, create order line item records, decrement the order items from inventory, create a ship request, and more. If one or more of those steps fail but the rest succeed, you can end up with inconsistent data in the form of orphaned records, unreliable inventory numbers, and so forth. Using transactions appropriately prevents this kind of thing from happening. and that's good for your DBA Your Company, and your job security.

That said, remember to only use transactions where necessary. Managing transactions will add some overhead to the system. Using transactions can also increase the number and extent of locks (or other isolation mechanism, depending on the database) used in the system, which can lead to concurrency issues. Consider such things as whether the work encapsulated in the transaction must be performed as an atomic unit, whether there are consequences to it being performed in stages, and what will happen if it only partially completes.

Transactions can be implemented on the server side, using whatever variant of SQL is native to the database, or they can be handled using ADO.NET in the calling application. This article will address implementing database transactions with the ADO.NET data providers. I 'll start by taking a look at what transactions are and how they affect your interactions with the database. I'll then take a look at the classes that the .NET Framework provides to assist you in creating and managing transactions in your data access code , And some Common Scenarios for their using.anatomy of a transaction

A transaction at its most basic level consists of two required steps-Begin, and then either Commit or Rollback. The Begin call defines the start of the transaction boundary, and the call to either Commit or Rollback defines the end of it. Within the transaction boundary, all of the statements executed are considered to be part of a unit for accomplishing the given task, and must succeed or fail as one. Commit (as the name suggests) commits the data modifications if everything was successful, and Rollback undoes the data ............... ..

Isolation level

Isolation levels are used by transactions to determine how restrictive the transaction is in regards to its isolation behavior. The more restrictive the isolation level, the more stringently the data affected by the transaction is isolated from other transactions. Most databases enforce isolation by means of locking ;. you should double-check what method your targeted DBMS uses The tradeoff is between performance and concurrency-the more locks the system has to maintain, the more likely it is that there will be conflicts and slowdowns as different processes try to access the same Resources.The Following is a list of the isolation level supported in ado.net, Taken from The .NET Framework SDK. The Isolation Levels Are Listed from Least Restrict Restrict To Most Restrictive:

MEMBER NAME

Description

Value

Unspecified

Supported by the .NET Compact Framework.

A Different Isolation Level Than The One Specified Is Being Used, But The Level Cannot Be Determined.

-1

Chaos

Supported by the .NET Compact Framework.

The Pending Changes from Morely Isolated Transactions Cannot Be overwritten.

16

ReadunCommitted

Supported by the .NET Compact Framework.

A Dirty Read Is Possible, Meaning That No Shared Locks Are Issued and No Exclusive Locks Are Honored.

256

Readcommitted

Supported by the .NET Compact Framework.

SHARED LOCKS Are Held While The Data IS Being Read To Avoid Dirty Reads, But The Data Can Be Changed Before The end of the transaction, resulting in non-review

4096

REPEATABLEREAD

Supported by the .NET Compact Framework.

LOCKS Are Placed On All Data That Is Used In a Query, Preventing Other Users from Updating The Data. Prevents Non-Repeatable Reads But Phantom Rows Are Still Possible.65536

Serializable

Supported by the .NET Compact Framework.

.

1048576

Note: The Chaos isolation level is not supported by SQL Server or Oracle, and only the OLE DB data provider will accept it as a valid isolation level to set on a transaction Attempting to set it for the SQL Server or Oracle provider will kick up. An ArgumentException INDICATION An Invalid IsolationLetlevel Parameter.

The SQL Server, Oracle, and OLE DB data providers all default to the ReadCommitted isolation level. The ODBC data provider isolation level default varies, depending on the ODBC driver used. If you think ReadCommitted will not provide the isolation level you want for a given transaction, you can use the Transaction.IsolationLevel property to specify a different level. you should think very carefully about changing this setting, though, and perform adequate testing to ensure that the change gives you the behavior that you want without negative side effects like data inconsistency on one hand or concurrency issues on the other. If you have a database administrator, you should definitely consult with her about the appropriate level to use for a given task. you should also consult your database documentation to see exactly how your target database handles Isolation level and loacking.

Partial Rollbacks

Occasionally you'll find a situation where you want to rollback some but not all of a transaction if an error arises. Generally you'll want to do this only when you expect the process to complete successfully the great majority of the time, and checking for success conditions for some part of it beforehand would be expensive. You are balancing the cost of doing the data modification and then having to do a rollback some small percentage of the time against the benefit of avoiding the expense of the verification routine all of the time.Partial rollbacks are usually implemented by using either savepoints or nested transactions, depending on the database you are working with. Some DBMSs do not offer either mechanism, so consult your database documentation to see whether and how partial rollbacks are implemented.

SQL Server and Oracle allow the use of named savepoints that you can refer to when issuing a rollback, to stop the rollback at a predetermined point. They do not permit true nested transactions, which some other DBMSs use to accomplish the same task. SQL Server allows nested transactions in the simplest sense, in that you can nest BEGIN ... COMMIT blocks in T-SQL However, only the outer COMMIT will actually commit all of the transactions;. the inner COMMITs only decrement a system variable so you can keep track of How much open transportions you have.

Some databases do permit true nested transactions. For these types of databases, inner COMMITs will actually commit the corresponding transaction, and even if the outer transaction is rolled back the inner one will remain committed.

Transactions with ado.net

ADO.NET supports two transaction models, which are defined in the .NET Framework docs as manual and automatic. Manual transactions map to what are commonly known as local or database transactions, which affect operations targeting a single database. A class can provide manual transactions by using the transaction classes and methods provided by ADO.NET to explicitly define transaction boundaries. I'll focus on manual (database) transactions in the following sections.Automatic transactions are also available, to enable a class to enlist and participate in a distributed transaction that coordinates changes across multiple data sources. The transaction itself in such a case is handled externally, for example by COM and the Distributed Transaction Manager (DTC). To learn more about automatic transactions, see Automatic Transactions.

Note: You should also be aware of the default database transaction behavior when none is specified in your code For example, SQL Server defaults to autocommit mode, where each Transact-SQL statement is either committed (if successful) or rolled back (if not. ) as soon as it completes. Knowing what type of default behavior to expect from the data source you are working with can help you determine whether you need to define transactions explicitly, and also can be more generally helpful in understanding your system's behavior.

An overview of the transfer-related classs

THE ADO.NET DATA Providers Offer Transaction, Command, And Transaction Classes. A Typical Transaction Would Follow a Process Similar To this:

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 TOEABLE A Partial Rollback.

4. Commit or roll back the Transaction Using Transaction.commit () or transaction.rollback ().

All of the data providers offer the same basic structure for handling transactions-begin the transaction off of the connection, enlist the commands, use the Transaction object to commit or rollback-but with some slight tweaks in each case to correspond with the functionality offered by The Databases Thedy Target.

SQL Server Transactions

The SQL Server Data Provider Provides Transaction Functionality Through The Following Classes and Members:

Class

MEMBER

FUNCTION

SqlConnection

BeGintransaction

Open a new transaction.

SQLCommand

TRANSACTION

Enlist the command associated with the object in an open transaction.

SQLTransaction

Save

Create a savepoint to enable a partial rollback.

SQLTransaction

Rollback

Roll Back a Transaction.

SQLTransaction

Commit

Commitive

The SQL Server data provider (and the OLE DB Provider for SQL Server as well) does not provide a way to construct nested transactions in code, since nested transactions are not truly supported in SQL Server, as discussed in the "Partial Rollbacks" section above . Therefore there is only one way of beginning a transaction in SqlClient-using SqlConnection.BeginTransaction (). You can not open another transaction on the same connection until the prior one has been committed or rolled back, so there is always only one open transaction per CONNECTION AT Any Given Time. To Partially Roll Back a Transaction, Use SqlTransaction.save (). OLE DB Transactions

The OLE DB DATA Provider Provides Transaction Functionality Through The Following Classes and Members:

Class

MEMBER

FUNCTION

OLEDBCONNECTION

BeGintransaction

Open a new transaction.

OLEDBCommand

TRANSACTION

Enlist the command associated with the object in an open transaction.

OLEDBTRANSACTION

Begin

Create a nested transaction, to enable a partial rollback.

OLEDBTRANSACTION

Rollback

Roll Back a Transaction.

OLEDBTRANSACTION

Commit

Commitive

The OLE DB data provider does not offer any way to set savepoints, but instead has a Begin () method on the OleDbTransaction object. This allows you to create nested transactions (provided they are supported by your targeted database) to provide similar functionality. In .

Keep in mind that the behavior of the nested transactions with the .NET OLE DB data provider will depend on the behavior of both your target database and the OLE DB provider that you specify. Nested transactions must be supported by both in order to be successfully implemented .Oacle Transactions

The Oracle Data Provider Provides Transaction Functionality Through The Following Classes and MEMBERS:

Class

MEMBER

FUNCTION

OracleConnection

BeGintransaction

Open a new transaction.

OracleCommand

TRANSACTION

Enlist the command associated with the object in an open transaction.

OracleTransaction

Rollback

Roll Back a Transaction.

OracleTransaction

Commit

Commitive

The Oracle provider does not allow you to specify either savepoints or nested transactions Savepoints are supported in Oracle, but you can not implement them in code using the current version of the Oracle data provider Transactions against an Oracle database must either rollback or commit completely..; Partial Rollbacks Are Not An Enabled Scenario.

Implementing Transactions

Now that we've reviewed what classes and members are involved, let's take a look at a basic implementation The following code illustrates a simple scenario;. Using a transaction to ensure that two stored procedures-one that removes inventory from a table, and one That Adds Inventory To Another-Execute Together OR Not At All:

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 SPTRANSACTION (int Partid, int Numbermoved, int sitei)

{

// 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 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 add inventory to the siteinventory

// Table by Calling the CreditsiteInventory

// stored procedure.

SQLCommand cmdcredit =

New SqlCommand ("CreditsiteIndory", 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)

{

// roll back the transaction.

TRAN. ROLLBACK ();

// Additional Error Handling if Needed.

}

Finally

{

// close the connection.

CONN.CLOSE ();

}

}

As you can see, a Nice Way to Handle Transactions In .Net Code Is To Put The Commit () in A Try Block, Using Rollback () in a catch block to undo the change.

Using Transactions with a DataAdapter

It is also easy to implement transactions so that they can be used with a DataAdapter.Update () call, whether you choose to create your INSERT / UPDATE / DELETE statements manually or use a CommandBuilder. You just need to set the Command.Transaction property To the appropriate Transaction Object:

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 cbtransaction ()

{

// CREATE AND Open the Connection.

SqlConnection conn = new sqlConnection ();

String connString = "server = SQLINSTANCE; DATABASE = TEST;"

"Integrated Security = SSPI";

Conn.connectionstring = 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 warehouse.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, "SiteIndory");

// 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, "SiteIndory");

// commit the TransactionsTran.commit ();

}

Catch (SQLException EX)

{

// roll back the transaction.

TRAN. ROLLBACK ();

// Additional Error Handling if Needed.

}

Finally

{

// close the connection.

CONN.CLOSE ();

}

}

Using savepoints

When using savepoints, it is important to remember that simply rolling back to the savepoint is not enough. The transaction must still be committed after it is partially rolled back. Also, it is important to plan for how you will notify the user or otherwise Handle Partially Completed Transactions If You Choose TO IMPLEMENT THEM.

The following code sample illustrates a scenario where you are creating a new customer, and also handling the customer's request for some materials that your company provides. Let's say that you have to perform this operation over a slow connection, or for some other reason the cost of checking the inventory levels prior to the operation is prohibitive. 99.9% of the time, the whole process should complete successfully. However, if the request for materials violates a constraint that maintains minimum inventory levels in the SiteInventory table, it will fail. in that case, the outer catch block will partially roll back and then commit the transaction, thereby saving the new customer record. The inventory issue and the request for materials will then need to be handled in some way. The nested catch block rolls back the whole Transaction if for Some Reason The Partial Rollback and Commit Sequence Fails.

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 this the processure 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 ();

}

}

Using nested transactions

NESTED Transactions Require The Same Considerations As SavePoints, AS Far As Making SurePoints, and Handling Any Partial completed processes.

The following code sample illustrates a scenario where you are creating a new order, and also creating a pick list for selection of the ordered items from the warehouse. If the creation of the pick list fails-maybe the PartID is invalid for the selected location- you still want the order to be entered, and then have the pick list generated in some other fashion. The nested try / catch block encapsulates the nested transaction for the pick list processing, permitting this inner transaction to commit or fail independent of the outer transaction .............................

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 Jet Provider is The Only Microsoft OLE DB Provider That Supports Nested Transactions.conclusion

Transactions are a powerful way to ensure that data modification tasks get handled appropriately, and ADO.NET gives you the ability to easily implement them in your data access code. Think about them when you are building your data access solution and use them where necessary, And You'll Help Ensure The Integrity of The Data You Work with.