Rollback, Commit, and Savepoints in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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

METHOD	DESCRIPTION
Rollback(SavePoint)	Performs a roll back on a transaction to the previous database state.
Begin(IsolationLevel)	Begins a nested database transaction passing the isolation level.
Save(SavePointName)	Equivalent to the transaction-SQL SAVE TRANSACTION in the Sql server database. 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 void button1_Click(object sender, System.EventArgs e)
        {
            // create a connection object
            string ConnectionString = "Integrated Security = SSPI;" + "Initial Catalog = Northwind;" +
            "Data Source = localhost;";
            SqlTransaction tran = null;
            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();
                tran = conn.BeginTransaction("Transaction1");
                SqlCommand cmd = 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 (Exception exp)
            {
                if (tran != null)
                    tran.Rollback();
                MessageBox.Show(exp.Message.ToString() +
                "\nTransaction Rolledback, Tim didn't make it.");
            }

            finally
            {
                conn.Close();
            }
        }

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(OleDbException e)
      {
            string strMessage = " ";
            for(int i =0; i< ae.Errors.Count; i++)
            {
                  strMessage += e.Errors [i].Message +" - " + e.Errors[i].SQLState + "\n";
            }
              MessageBox.Show(e.Errors[0].Message.ToString());
      }

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

PROPERTY	DESCRIPTION
Message	Gives a brief description of the error
NativeError	Gives error information specific to the particular database being accessed through ADO.NET
Source	Name of the object producing the error
SqlState	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
      string ConnectionString = "Integrated Security = SSPI;" + "Initial Catalog = Northwind1;" + "Data Source = localhost;";
      SqlConnection conn = new SqlConnection(ConnectionString);

      try
      {
      // open the connection
      conn.Open();
      }

      catch(SqlException ae)
      {
      StringWriter sw = 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());
      }

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

PROPERTY	DESCRIPTION
Message	Gives description of the error
LineNumber	Line number within the list of Sql commands or stored procedure causing the error
Source	Line of source code producing the error
State	the number modifying the error in order to provide some more information about the error
Number	Gets the number (integer) identifying the error
Procedure	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:

        this.NorthwindConnection.InfoMessage + = new System.Data.OleDb.OleDbInfoMessageEventHandler (this.NorthwindConnection_infoMessage);

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

          private void NorthwindConnection_InfoMessage(object sender,
          System.Data.OleDb.OleDbMessageEventArgs e)
          {
              MessageBox.Show(e.Errors[0].Message.ToString());
          }

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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.