ProblemYou need to issue multiple database updates in the context of a single, atomic transaction. SolutionUse an ADO.NET transaction to envelop the various SQL statements that need to be processed as a unit. DiscussionThe following block of code connects to a database via ADO.NET and makes several database updates within a single transaction: ' ----- Connect to the database. Dim connectionString As String = _ "Data Source=MySystem\SQLEXPRESS;" & _ "Initial Catalog=MyDatabase;Integrated Security=true" Dim theDatabase As New SqlClient.SqlConnection(connectionString) theDatabase.Open( ) ' ----- Create a command object that will hold each ' processed SQL statement. Dim sqlStatement As New SqlClient.SqlCommand sqlStatement.Connection = theDatabase ' ----- Start the transaction. Dim theTransaction As System.Data.SqlClient.SqlTransaction theTransaction = theDatabase.BeginTransaction( ) sqlStatement.Transaction = theTransaction ' ----- Issue the first statement. sqlStatement.CommandText = _ "UPDATE Table1 SET Column2 = 25 WHERE Column1 = 0" sqlStatement.ExecuteNonQuery( ) ' ----- Issue the second statement. sqlStatement.CommandText = _ "UPDATE Table1 SET Column2 = 50 WHERE Column1 = 1" sqlStatement.ExecuteNonQuery( ) ' ----- Finish the transaction. theTransaction. Commit( ) ' ----- Clean up. theTransaction = Nothing sqlStatement = Nothing theDatabase.Close( ) theDatabase.Dispose( ) Transactions allow multiple SQL statements to exhibit all-or-nothing behavior. The ADO.NET transaction object is provider-specific and communicates with the target database to manage the atomic nature of the multi-statement transaction. The SqlTransaction object establishes a transaction for a set of statements in SQL Server. Instead of creating the object directly, use the connection's BeginTransaction() method to create it. This establishes the new transaction at the database level: Dim theTransaction As System.Data.SqlClient.SqlTransaction theTransaction = theDatabase.BeginTransaction() All commands issued while the transaction is in effect need to include the transaction object. Assign the object to each command's transaction property: sqlStatement.Transaction = theTransaction When you have issued all the commands needed for this transaction, use the transaction object's Commit() method to permanently write all updates to the database: theTransaction.Commit() If for any reason you need to cancel the changes in the middle of the transaction, use the Rollback() method instead: theTransaction.Rollback() |