Rollback, Commit, and Savepoints in ADO.NET

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

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 Object, ByVal 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)

        Try
            conn.Open()
            tran = conn.BeginTransaction("Transaction1")
            Dim cmd As New SqlCommand("INSERT INTO Customers (Customer ID, ContactName, CompanyName)" & "VALUES (516, 'Tim Howard', 'FireCon')", conn, tran)
            tran.Save("save1")
            cmd.ExecuteNonQuery()
            MessageBox.Show("Tim is in the Data base")
            cmd.CommandText = "INSERT INTO Customers (CustomerID, ContactName, CompanyName)" & "VALUES (517, 'Bob Hope', 'Hollwood')"
            tran.Save("save2")
            cmd.ExecuteNonQuery()
            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")
            tran.Save("save3")
            cmd.ExecuteNonQuery()
            tran.Rollback("save2")
            tran.Commit()
            MessageBox.Show("Transaction Rolledback, only Tim made it.")
        Catch exp As Exception

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

            conn.Close()
        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

        Try
            'Something
        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
            Next
            MessageBox.Show(e.Errors(0).Message.ToString())
        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

 

        Try
            ' open the connection
            conn.Open()
        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)
            MessageBox.Show(sw.GetStringBuilder().ToString())
        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

 

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 = New System.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
        MessageBox.Show(e.Errors(0).Message.ToString())
    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.

Conclusion

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.