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.jpg

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.

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


Similar Articles
Mindcracker
Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.