|
|
|
| Title | Use database transactions in VB .NET |
|---|
| Keywords | transaction, database, VB.NET |
|---|
| Categories | Database, VB.NET |
|---|
|
|
|
A transaction makes the database perform a series of actions as a unit. The classic example is moving money from one bank account to another. These two tasks must either both occur or neither occur. If the program removes the money from the first account and then crashes, the system loses money. If you perform the operations in the other order so the program adds money to the second account before crashing, the system "invents" money. Either way the database is no longer consistent.
Operations in a transaction are guaranteed to either all occur or all not occur. Your program can also "rollback" a transaction to cancel it.
In VB .NET, you start a transaction by calling a connection object's BeginTransaction method. Then you create command objects to perform the tasks, passing their constructors the transaction object. You execute the commands and call the transaction object's Commit method to make the actions permanent or you call its Rollback method to cancel the transaction's actions.
The following code makes a transaction containing two record updates that modify some records' FirstName fields. It executes the commands and calls the transaction's Commit method.
|
|
' Make a transaction containing two actions and commit it.
Private Sub btnTransactionCommit_Click(ByVal sender As _
System.Object, ByVal e As System.EventArgs) Handles _
btnTransactionCommit.Click
' Open the connection.
connUsers.Open()
' Make the transaction.
Dim trans As OleDb.OleDbTransaction = _
connUsers.BeginTransaction(IsolationLevel.ReadCommitted)
' Make a Command for this connection
' and this transaction.
Dim cmd As New OleDb.OleDbCommand( _
"UPDATE People SET FirstName=? WHERE LastName=?", _
connUsers, _
trans)
' Create parameters for the first command.
cmd.Parameters.Add(New _
OleDb.OleDbParameter("FirstName", _
txtFirstName1.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", _
txtLastName1.Text))
' Execute the second command.
cmd.ExecuteNonQuery()
' Create parameters for the second command.
cmd.Parameters.Clear()
cmd.Parameters.Add(New _
OleDb.OleDbParameter("FirstName", _
txtFirstName2.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", _
txtLastName2.Text))
' Execute the second command.
cmd.ExecuteNonQuery()
' Commit the transaction.
trans.Commit()
' Close the connection.
connUsers.Close()
' Update the DataGrid.
dsUsers.Clear() ' Remove the old data.
daUsers.Fill(dsUsers) ' Reload the data.
DataGrid1.ResetBindings() ' Redisplay the data.
MsgBox("OK")
End Sub
|
|
|
The following code performs the same actions except it calls the transaction's Rollback method to cancel the actions.
|
|
' Make a transaction containing two actions and roll it
' back.
Private Sub btnTransactionRollback_Click(ByVal sender As _
System.Object, ByVal e As System.EventArgs) Handles _
btnTransactionRollback.Click
' Open the connection.
connUsers.Open()
' Make the transaction.
Dim trans As OleDb.OleDbTransaction = _
connUsers.BeginTransaction(IsolationLevel.ReadCommitted)
' Make a Command for this connection
' and this transaction.
Dim cmd As New OleDb.OleDbCommand( _
"UPDATE People SET FirstName='?' WHERE " & _
"LastName='?'", _
connUsers, _
trans)
' Create parameters for the first command.
cmd.Parameters.Add(New _
OleDb.OleDbParameter("FirstName", _
txtFirstName1.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", _
txtLastName1.Text))
' Execute the second command.
cmd.ExecuteNonQuery()
' Create parameters for the second command.
cmd.Parameters.Clear()
cmd.Parameters.Add(New _
OleDb.OleDbParameter("FirstName", _
txtFirstName2.Text))
cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", _
txtLastName2.Text))
' Execute the second command.
cmd.ExecuteNonQuery()
' Roll the transaction back.
trans.Rollback()
' Close the connection.
connUsers.Close()
' Update the DataGrid.
dsUsers.Clear() ' Remove the old data.
daUsers.Fill(dsUsers) ' Reload the data.
DataGrid1.ResetBindings() ' Redisplay the data.
MsgBox("OK")
End Sub
|
|
|
For lots more information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
|
| |
|
| |
|
|
