Visual Studio 2005 Cookbook – Database, Using Transactions

Recipe 13.6. Using Transactions

Problem

You need to issue multiple database updates in the context of a single, atomic transaction.

Solution

Use an ADO.NET transaction to envelop the various SQL statements that need to be processed as a unit.

Discussion

The 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=MySystemSQLEXPRESS;" & _
	   "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 Read More >>