ADO.NET Rollback, Commit, and Savepoints in VB.NET

In this article I will explain about Rollback, Commit, and Savepoints in ADO.NET.
  • 8279
The Sql data provider provides some additional methods for dealing with transactions involving save points. Save points allow you to rollback to a "bookmarked" point in the transaction. Table 5-34 describes these methods.

Table 5-34. Transaction Methods in the Sql Data Provider




Performs a roll back on a transaction to the previousdatabasestate.


Begins a nested database transaction passing the isolation level.


Equivalent to the transaction-SQL SAVE TRANSACTION in the Sqlserverdatabase. Allows you to create a save point so that you can roll back to a particular saved state of the database.


Listing 5-58 shows an example of how savepoints are used in Sql server. As you can see from Listing 5-58, first you establish a connection with the Northwind database and open the connection. After that, by calling BeginTransaction on the connection. You can return a SqlTransaction object, which you can use together with your Command object. To establish the relationship with the command object, you then pass the Transaction object in the constructor of Command.

Now that the transaction is tied to the Command object, you'll save the initial save point to the transaction and then execute the first insertion into the database. After that you assign a new SQL Insert to the CommandText and save the current transaction savepoint before executing the query. This Insert puts "Bob Hope" into the database. 

Finally, you assign a new SQL Insert to the CommandText and save the current transaction save point before executing the query. This Insert Puts "Fred" into the database.

Listing 5-58. Using save points in the Sql data provider

    Private Sub button1_Click(ByVal sender As ObjectByVal e As System.EventArgs)
        ' create a connection object
        Dim ConnectionString As String = "Integrated Security = SSPI;" & "Initial Catalog = Northwind;" & "Data Source = localhost;"
        Dim tran As SqlTransaction = Nothing
        Dim conn As New SqlConnection(ConnectionString)

            tran = conn.BeginTransaction("Transaction1")
            Dim cmd As New SqlCommand("INSERT INTO Customers (Customer ID, ContactName, CompanyName)" & "VALUES (516, 'Tim Howard', 'FireCon')", conn, tran)
            MessageBox.Show("Tim is in the Data base")
            cmd.CommandText = "INSERT INTO Customers (CustomerID, ContactName, CompanyName)" & "VALUES (517, 'Bob Hope', 'Hollwood')"
            MessageBox.Show("Bob is in the Database")
            cmd.CommandText = "INSERT INTO Customers(CustomerID, ContactName, CompanyName)" & "Values (518, 'Fred Astaire', 'Hollywood')"
            MessageBox.Show("Fred is in the Database")
            MessageBox.Show("Transaction Rolledback, only Tim made it.")
        Catch exp As Exception

            If tran IsNot Nothing Then
            End If
            MessageBox.Show(exp.Message.ToString() & vbLf & "Transaction Rolledback, Tim didn't make it.")

        End Try
    End Sub

By rolling back to the second savepoint, it's as if the second and third ExecuteNonQuery never happened, so the FirstExecuteNonQuerty that puts "Tim" in the database is the only one that actually gets committed. If there's an exception, then you can roll back the whole transaction (see figure 5-44).


Figure 5-44. Message after rolling back the entire transaction when an exception is thrown

Catching Errors

The Error class is an aggregate of Exception and can be utilized by a try-catch block when an exception is thrown to get information about the error. The Error is populated as a collection in the Exception object. All provider Error objects have a Message property, which is a line of text describing the error. However the SqlServer provider has a richer group of properties describing each error. For example, Sql server has a LineNumber property that lists the line number of a stored procedure causing an error. The appendix contains a list of other useful error properties contained by Sql server if you want to really take advantage of this property. 

In the example of the OleDbExecution object in Listing 5-59, the Error collection of the exception is looped through after the exception is thrown, and both the error messages and the error codes are placed in a string to be printed out in a MessageBox control.

Listing 5-59. Utilizing the OleDbError Collection

        Catch e As OleDbException

            Dim strMessage As String = " "
            For i As Integer = 0 To ae.Errors.Count - 1
                strMessage += (e.Errors(i).Message & " - ") + e.Errors(i).SQLState & vbLf
        End Try

Table 5-35 describes Message, SQLState, and other properties in OleDbError that contain information after the error that is caught.

Table 5-35. The OleDbError Class Properties





Gives a brief description of the error


Gives error information specific to the particular database being accessed through ADO.NET


Name of the object producing the error


A five- character error code representing the standard ANSI SQL error code for the Particular error

Listing 5-60 used the SqlServer Exception class. You print the server name, message, and error code in a message box.

Note: The StringWriter Class is defined in the System.I0 namespace. You need to add using System.I0; to your project. 

Listing 5-60. Catching error using the SqlException class

        ' create a connection object
        Dim ConnectionString As String = "Integrated Security = SSPI;" & "Initial Catalog = Northwind1;" & "Data Source = localhost;"
        Dim conn As New SqlConnection(ConnectionString)

            ' open the connection
        Catch ae As SqlException

            Dim sw As New StringWriter()
            sw.WriteLine("{0}: Threw an Error:***{1}***" & "with SqlServer code {2}", ae.Errors(0).Server, ae.Errors(0).Message, ae.Errors(0).Number)
        End Try

Table 5-36 shows a list of properties that can be accessed in the SqlError to give you information about your error. The SqlError is a bit more extensive in that it can tell you more an error than the OleDbError.

Table 5-36. SqlError Properties





Gives description of the error


Line number within the list of Sql commands or stored procedure causing the error


Line of source code producing the error


the number modifying the error in order to provide some more information about the error


Gets the number (integer) identifying the error


Name of the stored procedure causing the error (string)

InfoMessageEventHandler: Listening to Warnings

Information or warning message is sometimes produced after a query is executed on a database. If you need to "listen" for these messages, .NET provides a mechanism for doing this. The event for listening to information messages in the Northwind database is trapped in the Connection object for this database and can be delegated with the following line of code in the InitializeComponent() method for your .NET project:

Dim + As Me.NorthwindConnection.InfoMessage = NewSystem.Data.OleDb.OleDbInfoMessageEventHandler(Me.NorthwindConnection_infoMessage)

You also need to create the NorthwindConnection-InfoMessage method, to which the event is delegated:

Private Property sender,() As NorthwindConnection_InfoMessage(object
    End Property
Private Function e)() As System.Data.OleDb.OleDbMessageEventArgs
    End Function

The message Box in this code shows the first information message passed in form the InfoMessageEvent argument. This event argument contains an ErrorCollection much the same way an Exception object contains an ErrorColelction. The errors for this information message are warnings and information message, as opposed to the more serious database error such as bad queries.


Hope this article would have helped you in understanding 
Rollback, Commit, and Savepoints in ADO.NET. See my other articles on the website on ADO.NET.


More Articles

© 2020 DotNetHeaven. All rights reserved.