Using the SQLCommand Object

In this chapter, you see how to use the .NET Framework Data Provider for SQL Server’s SqlCommand object to execute commands on a target SQL Server system. In the first part of this chapter, you learn how to execute simple SQL action queries that don’t return any values. Next, you see how to execute scalar queries using both SQL statements and stored procedures that return single values. The chapter closes with a discussion of how to use the SqlCommand object to commit and roll back transactions.

Executing dynamic SQL statements and stored procedures are two of the most common database actions that are required by an application. Dynamic SQL statements are SQL statements that are read by the database server and executed when they are sent to the database server from the client application. When the database receives these SQL statements, they are first parsed to ensure that their syntax is correct, and then the database engine creates an access plan—essentially determining the best way to process the SQL statement—and then executes the statements. Unlike dynamic SQL statements, which are often used for executing SQL DML operations like creating tables or for data access operations like performing ad hoc queries, stored procedures are typically used to perform predefined queries and database update operations. Stored procedures form the backbone of most database applications. The primary difference between dynamic SQL statements and stored procedures is that stored procedures are typically created before the application is executed and they reside in the database itself. This gives stored procedures a significant performance advantage over dynamic SQL statements because the jobs of parsing the SQL statement and creating the data access plan have already been completed. It’s worth noting that changes made to data contained in an ADO.NET DataSet either can be posted back to the database using dynamic SQL statements created by the SqlCommandBuilder class, or can be written back to the database using stored procedures. However, you don’t need to use the DataSet and DataAdapter in order to update the database. In cases where you don’t need the data binding and navigation functions provided by the DataSet, the Command objects can provide a much lighter-weight and more-efficient method of updating the database. In the next sections, you’ll see how to use the SqlCommand object to execute an ad hoc query, and then to execute a SQL DDL statement to build a table on the target database, followed by two examples using the stored procedure. The first stored procedure example illustrates passing parameters to a stored procedure, and the second example illustrates executing a stored procedure that supplies a return value.

The easiest place to get started with the SqlCommand object is probably using the visual component provided as a part of Visual Studio.NET. Using the same drag-and-drop paradigm that’s implemented for interface components, you can drag and drop an instance of the SqlCommand from the Visual Studio.NET Data Toolbox onto the visual designer. After you’ve done this, you’ll see a new SqlCommand object in the components pane, as shown in Figure 8-1.

Figure 8-1. Adding the Visual SqlCommand object to a Visual Studio.NET project

Dragging and dropping the SqlCommand component from the Data tab to the Design window results in the creation of the SqlCommand1 object that is displayed in the component window at the bottom of the screen. After that, the SqCommand1 component is ready to use. The follow example illustrates executing a simple ad hoc query with the visual SqlCommand component:

  Private Sub SQLCommandComponent(ByVal sServer As String, _ 
          ByVal sDB As String) 
      SqlConnection1.ConnectionString = "SERVER=" & sServer _
          & ";" & "INTEGRATED SECURITY=True;DATABASE=" & sDB
      'Set up a simple query
      SqlCommand1.CommandText = "SELECT * FROM customers"
      ' Set the active connection
      SqlCommand1.Connection = SqlConnection1
      Try
          ' Open a connection
          SqlConnection1.Open()
          ' Execute the query
          Dim dr As System.Data.SqlClient.SqlDataReader = _
              SqlCommand1.ExecuteReader()
          ' The reader can now be processed
      Catch ex As Exception
          MessageBox.Show("Connection error: :" & ex.ToString())
      End Try
      SqlConnection1.Close()
  End Sub

An example of the SQLCommandComponent subroutine written in C# is shown here:

  private void SQLCommandComponent(string sServer, string sDB)
  {
      sqlConnection1.ConnectionString = "SERVER=" + sServer + ";"
          + "INTEGRATED SECURITY=True;DATABASE=" + sDB;
      sqlCommand1.CommandText = "SELECT * FROM customers";
      // Set the active connection
      sqlCommand1.Connection = sqlConnection1;
      try
      {
          // Open a connection
          sqlConnection1.Open();
          // Execute the query 
          system.Data.SqlClient.SqlDataReader dr =  
              sqlCommand1.ExecuteReader();
          // The reader can now be processed
      }
      catch (Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
      sqlConnection1.Close();
  }

At the top of the SQLCommandComponent routine, you can see where two variables are passed in as parameters. The sServer variable contains the name of the SQL Server system that will be the database server; the sDB variable contains the name of the database that will be accessed. These examples all use the Northwind database that's supplied as a sample database with SQL Server.

The first action within the routine sets the SqlConnection1 object’s connection string using the server and database values that were passed into the routine. The Integrated Security keyword indicates that Windows authentication will be used for the connection. You should note that even though this example is using the visual SqlCommand component, it still requires a SqlConnection object to actually connect to the target database. Although this example uses the visual SqlConnection1 object, this could also use a code-based SqlConnection object as well.

Next the CommandText property of the SqlCommand1 object is assigned a simple SQL SELECT statement that will query all of the rows and columns from the Customers table found in the Northwind database. The next statement sets the Sql1Command1 object’s Connection property to the SqlConnect1 object. Then a Try-Catch block is set up to open the connection and execute the command. The SqlConnection1.Open method is used to initiate a connection to the target SQL Server system; then the SqlCommand1 object’s ExecuteReader command is used to create a SqlDataReader object named dr. The ExecuteReader method is used to return a fast forward-only data stream from the target data source. Table 8-1 lists all of the different SQL command execution methods supported by both the SqlCommand object and the OleDbCommand object.

After the ExecuteReader method has completed, the results of the query can then be accessed using the dr SqlDataReader object. The detailed usage of the SqlDataReader will be presented in the next chapter. If an error occurs during the execution of either the Open method or the ExecuteReader, a message box will be displayed to the user showing the exception text.

Table 8-1.  SqlCommand SQL Statement Execution Methods

Adding the System.Data.SqlClient Namespace

Although using the visual connection components provided by the Visual Studio.NET design environment is pretty easy, in the long run, you’ll probably find it simpler to use the ADO.NET objects by just writing code. To use the SqlCommand class in your project, you first need to add an import directive for the System.Data.SqlClient Namespace in your project. The following example illustrates how to do this for a VB.NET project:

  Imports System.Data.SqlClient

For a C# project, you need to add an import directive for the System.Data.SqlClient Namespace as follows:

  using System.Data.SqlClient;

After the import directive has been added, you’re ready to use code-based SqlCommand objects in your projects.

Dynamic SQL provides an extremely flexible mechanism for working with the database. Dynamic SQL allows you to execute ad hoc queries and return the results from action queries, as well as execute SQL DDL statements to create database objects. The following SQLCommandNonQuery subroutine provides an example illustrating how you can use dynamic SQL with the ADO.NET SqlCommand object to check for the existence of a table and conditionally create it if it doesn’t exist:

  Private Sub SQLCommandNonQuery(ByVal sServer As String, _   
          ByVal sDB As String)
      Dim cn As New SqlConnection("SERVER=" & sServer _
      & ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
      Dim sSQL As String
      Dim cmd As New SqlCommand(sSQL, cn)
      Try
          ' Open the connection
          cn.Open()
          ' First drop the table
          sSQL = "IF EXISTS " _
              & "(SELECT * FROM dbo.sysobjects WHERE id = " _
              & "object_id(N'[Department]') " _
              & "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) " _
              & "DROP TABLE [department]"
          cmd.CommandText = sSQL
          cmd.ExecuteNonQuery()
          ' Then create the table
          sSQL = "CREATE TABLE Department " _
             & "(DepartmentID Int NOT NULL, " _
             & "DepartmentName Char(25), PRIMARY KEY(DepartmentID))"
          cmd.CommandText = sSQL
          cmd.ExecuteNonQuery()
      Catch e As Exception
          MsgBox(e.Message)
      End Try
      ' Close the connection
      cn.Close()
  End Sub

The C# version of the SQLCommandNonQuery subroutine is shown here:

  private void SQLCommandNonQuery(string sServer, string sDB)
  {
      SqlConnection cn = new SqlConnection("SERVER=" + sServer
          + ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
      string sSQL;
      SqlCommand cmd = new SqlCommand("", cn);
      try
      {
          // Open the connection
          cn.Open();
          // First drop the table
          sSQL = "IF EXISTS "
              + "(SELECT * FROM dbo.sysobjects "
              + "WHERE id = object_id(N'[Department]') "
              + "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) "
              + "DROP TABLE [department]";
          cmd.CommandText = sSQL;
          cmd.ExecuteNonQuery();
          // Then create the table
          sSQL = "CREATE TABLE Department "
              + "(DepartmentID Int NOT NULL, "
              + "DepartmentName Char(25), "
              + "PRIMARY KEY(DepartmentID))"; 
          cmd.CommandText = sSQL;
          cmd.ExecuteNonQuery();
      }
      catch(Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
      // Close the connection
      cn.Close();
  }

In the first part of the SQLCommandNonQuery subroutine, you can see where the sServer and sDB variables are passed as parameters. The values of these two variables are used to specify the SQL Server database server and database that will be used. Next, a new SqlConnection object named cn is created. In this example, the connection string is passed in as the first parameter of the SqlConnection object’s constructor. The connection string uses Integrated Security to connect to the server and database specified using the server and database keywords. For more information about the Sql Connection object’s connection string keywords, you can refer to Chapter 4.

After the Connection object has been created, the sSQL variable that will be used to contain the dynamic SQL statements and an instance of the SqlCommand object named cmd are instantiated. In this example, the constructor of the cmd SqlCommand object uses two parameters—the first being a string containing the SQL statement that will be executed, and the second being the SqlConnection object that will provide the connection to the target database server. Here the sSQL string is initially empty. Next, a Try-Catch structure is set up to execute the SQL commands. The first action that you can see within the Try-Catch block uses the cn SqlConnection object’s Open method to open a connection to the SQL Server database that was specified earlier in the connection string used in the SqlConnection object’s constructor. Then, the sSQL variable is assigned a SQL statement that checks for the existence of the department table. In this SQL statement, you can see that a Select statement queries the SQL Server sysobjects table to determine if a User Table named Department exists. If the Department table is found, a DROP TABLE statement will be executed to remove the table from the target database. Otherwise, if the Department table isn’t found, no further action will be taken. In order to actually execute the SQL statement, that value in the sSQL variable is then assigned to the CommandText property of the cmd object, and then the ExcuteNonQuery method of the cmd SqlCommand object is used to send the command to the SQL Server system. The ExecuteNonQuery method is used to execute a SQL statement that doesn’t return a resultset or a specific return value.

After the first DROP TABLE SQL command has been issued, the same sequence is followed to execute a Create Table command. First, the sSQL variable is assigned a SQL CREATE TABLE statement that creates a table named Department that consists of two columns. The first column is an integer data type named DepartmentID, which is also the primary key, and the second column is a 25-character data type named DepartmentName. Then the value in the sSQL variable is copied to the cmd object’s CommandText property, and the ExecuteNonQuery method is called to execute the CREATE TABLE SQL statement. Following the successful completion of the ExecuteNonQuery method, the Department Table will exist in the database that was earlier identified in the sDB variable.

If an error occurs during any of these operations, the SqlConnection object’s Open method or either instance of the SqlCommand object’s ExecuteNonQuery method, then the code in the Catch block will be executed, and a message box will be displayed showing the text of the exception condition.

At the end of the CommandNonQuery subroutine, the SqlConnection object’s Close method is executed to end the connection to the SQL Server database.

In addition to executing dynamic SQL statements, the SqlCommand object can also be used to execute stored procedures and parameterized SQL statements. The primary difference between dynamic SQL and prepared SQL is that dynamic SQL statements must be parsed and an access plan must be created before each run. (Technically, some database systems like SQL Server are very smart about the way this is handled, and they will actually store dynamic statements for a period of time. Then when the statement is subsequently executed, the existing access plan will be used. Even so, this depends on the database activity; and with dynamic SQL, there’s no guarantee that the plan will be immediately available.) You can think of prepared SQL statements as sort of a cross between stored procedures and dynamic SQL. Like stored procedures, they can accept different parameter values at runtime. Like dynamic SQL, they are not persistent in the database. The SQL statement is parsed and the access plan is created when the application executes the SQL statements. However, unlike dynamic SQL, the prepared SQL is parsed and the access plan is only created once when the statement is first prepared. Subsequent statement execution takes advantage of the existing access plan. The access plan will typically remain in the procedure cache until the connection is terminated. The following example shows how to create and execute a prepared SQL statement using the ADO.NET SqlCommand object:

  Private Sub SQLCommandPreparedSQL(ByVal sServer As String, _
          ByVal sDB As String)
      Dim cn As New SqlConnection("SERVER=" & sServer _
          & ";INTEGRATED SECURITY=True;DATABASE=" & sDB) 
      'Set up the Command object's parameter types
      Dim cmd As New SqlCommand("INSERT INTO department VALUES" _
          & "(@DepartmentID, @DepartmentName)", cn)
      Dim parmDepartmentID = _
          New SqlParameter("@DepartmentID", SqlDbType.Int)
      parmDepartmentID.Direction = ParameterDirection.Input
      Dim parmDepartmentName = _
          New SqlParameter("@DepartmentName", SqlDbType.Char, 25)
      parmDepartmentName.Direction = ParameterDirection.Input
      ' Add the parameter objects to the cmd Parameter's collection
      cmd.Parameters.Add(parmDepartmentID) 
      cmd.Parameters.Add(parmDepartmentName)
      Try
          ' Open the connection & prepare the command 
          cn.Open()
          cmd.Prepare()
          ' Execute the prepared SQL statement to insert 10 rows
          Dim i As Integer
          For i = 0 To 10
              parmDepartmentID.Value = i 
              parmDepartmentName.Value = "New Department " & CStr(i)
              cmd.ExecuteNonQuery()
          Next
      Catch e As Exception
          MsgBox(e.Message)
      End Try
      cn.Close()
  End Sub

The C# version of the SQLCommandPrepareSQL subroutine is shown in the following listing:

  private void SQLCommandPreparedSQL(string sServer, string sDB)
  {
      SqlConnection cn = new SqlConnection("SERVER=" + sServer
          + ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
      //Set up the Command object's parameter types 
      SqlCommand cmd = new SqlCommand("INSERT INTO department VALUES" 
          + "(@DepartmentID, @DepartmentName)", cn);
      SqlParameter parmDepartmentID =
          new SqlParameter("@DepartmentID", SqlDbType.Int);
      parmDepartmentID.Direction = ParameterDirection.Input;
      SqlParameter parmDepartmentName =
          new SqlParameter("@DepartmentName", SqlDbType.Char, 25);
      parmDepartmentName.Direction = ParameterDirection.Input;
      // Add the parameter objects to the cmd Parameter's collection
      cmd.Parameters.Add(parmDepartmentID);
      cmd.Parameters.Add(parmDepartmentName);
      try
      {
          // Open the connection & prepare the command 
          cn.Open();
          cmd.Prepare();
          // Execute the prepared SQL statement to insert 10 rows
          for (int i = 1; i <= 10; i++)
          {
              parmDepartmentID.Value = i; 
              parmDepartmentName.Value = "New Department " + i;
              cmd.ExecuteNonQuery();
          }
      }
      catch (Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
      // Close the connection
      cn.Close();
  }

At the top of the CommandPrepareSQL subroutine, you can see where the target database server name and the database name are passed into the subroutine using the sServer and sDB variables. Next, a new SqlConnection object named cn is created, followed by a new SqlCommand object named cmd. In this example, the constructor takes two arguments. The first argument is used to assign a SQL statement to the cmd object. This can be either SQL statement or it can be the name of a stored procedure. Here, the SQL statement is an INSERT statement that adds that values of two columns to the Department table.

Note The Department table was created in the earlier section of this chapter.

The important point to note in this example is the format of the parameter markers that are used in the SQL statement. Parameter markers are used to indicate the replaceable characters in a prepared SQL statement. At runtime, these parameters will be replaced with the actual values that are supplied by the SqlCommand object’s Parameters collection. Unlike ADO or the OleDbCommand object, which uses the question mark character (?) to indicate replaceable parameters, the SqlCommand object requires that all parameter markers begin with the @ symbol. This example shows two parameter markers: @DepartmentID and @DepartmentName. The second argument of the SqlCommand constructor associates the cmd SqlCommand object with the cn SqlConnection object that was created earlier.

Next, you can see where two SqlParameter objects are created. The first parameter object named parmDepartmentID will be used to supply values to the first parameter marker (@DepartmentID). Likewise, the second parameter object named parmDepartmentName will supply the values used by the second replaceable parameter (@DepartmentName). The code example used in this subroutine shows three parameters being passed to the SqlParameter’s constructor. The first parameter supplies the parameter name. Here you need to make sure that the name supplied to the SqlParameter object’s constructor matches the name that was used in the parameter marker of the prepared SQL statement. The second parameter that’s passed to this overloaded version of the SqlParameter constructor specifies the parameter’s data type. Table 8-2 lists all of the valid DbType enumerations that can be used to specify the SqlParameter’s data type.

Here the Direction property is set to input using the ParameterDirection.Input enumeration. Table 8-3 lists the valid enumerations for the SqlParameter Direction property.

Table 8-2.  DbType Enumerations

Table 8-3.  SqlParameterDirection Enumeration

After the SqlParameter objects have been created, the next step is to add them to the SqlCommand object’s Parameters collection. In the previous listings, you can see that you use the Add method of the SqlCommand object’s Parameters collection to add both the parmDepartmentID and parmDepartmentName SqlParameter objects to the cmd SqlCommand object. The order in which you add the SqlParameter objects isn’t important. Next, within the Try-Catch block, the cn SqlConnection object’s Open method is used to open a connection to SQL Server and then the Prepare statement is used to prepare the statement. Note that the Prepare method is executed after all of the parameter attributes have been described.

NOTE  Using the Prepare operation provides an important performance benefit for parameterized queries because it instructs SQL Server to issue an sp_prepare statement, thereby ensuring that the statement will be in the Procedure cache until the statement handle is closed.

Next, a For-Next loop is used to add 10 rows to the newly created Department table. Within the For-Next loop, the Value property of each parameter object is assigned a new data value. For simplicity, the parmDepartmentID parameter is assigned the value of the loop counter contained in the variable i while the parmDepartmentName parameter is assigned a string containing the literal "New Department" along with the current value of the loop counter. Last, the SqlCommand object’s ExecuteNonQuery method is used to execute the SQL statement. In this case, ExecuteNonQuery was used because this example is using a SQL action query that doesn’t return any values. From the SQL Server perspective, running the ExecuteNonQuery method results in the server issuing an sp_execute command to actually perform the insert.

NOTE  If you need to pass a null value as a parameter, you need to set the parameter to the value DBNull.Value.

If an error occurs during any of these operations, the code in the Catch block will be executed and a message box will be displayed showing the text of the exception condition.

At the end of the subroutine, the SqlConnection object’s Close method is executed to end the connection to the SQL Server database.

Stored procedures are the core of most database applications—and for good reason. In addition to their performance benefits, stored procedures can also be a mechanism for restricting data access to the predefined interfaces that are exposed by the stored procedures. Similar to prepared SQL statements, stored procedures get significant performance benefits from the fact that they are compiled before they are used. This allows the database to forego the typical parsing steps that are required, skipping the need to create an access plan. Stored procedures are the true workhorse of most database applications, and they are almost always used for database insert, update, and delete operations, as well as for retrieving single values and result sets. In the following examples, you see how to execute SQL Server stored procedures using the SqlCommand object. In the first example that follows, you’ll see how to execute a stored procedure that accepts a single input parameter and returns a scalar value.

The following listing presents the T-SQL source code required to create the StockValue stored procedure that will be added to the sample Northwind database. You can create this stored procedure by executing this code using Query Analyzer.

Private Sub SQLCommandSPScalar(ByVal sServer As String, _
      ByVal sDB As String)
  Dim cn As New SqlConnection("SERVER=" & sServer _
      & ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
  ' Create the command object and set the SQL statement
  Dim cmd As New SqlCommand("StockValue", cn) 
  cmd.CommandType = CommandType.StoredProcedure
  'Create the parameter
  cmd.Parameters.Add("@ProductID", SqlDbType.Int) 
  cmd.Parameters("@ProductID").Direction = _
      ParameterDirection.Input
  cmd.Parameters("@ProductID").Value = 1
  Try
      Dim nStockValue As Decimal
      ' Open the connection and execute the command
      cn.Open()
      nStockValue = cmd.ExecuteScalar()
      txtMid.Text = nStockValue
  Catch e As Exception
      MsgBox(e.Message)
  End Try
  ' Close the connection
  cn.Close()
End Sub

Following is the C# version of the SQLCommandSPScalar subroutine that calls a SQL stored procedure and returns a scalar value:

private void SQLCommandPSScalar(string sServer, string sDB)
{
    SqlConnection cn = new SqlConnection("SERVER=" + sServer
        + ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
    // Create the command object and set the SQL statement  
    SqlCommand cmd = new SqlCommand("StockValue",  cn); 
    cmd.CommandType = CommandType.StoredProcedure;
    // Create the parameter
    cmd.Parameters.Add("@ProductID",  SqlDbType.Int); 
    cmd.Parameters["@ProductID"].Direction =
          ParameterDirection.Input;
    cmd.Parameters["@ProductID"].Value = 1;
    try
    {
          decimal nStockValue;
          // Open the connection and execute the command
          cn.Open();
          nStockValue = (decimal)cmd.ExecuteScalar();
          txtMid.Text = nStockValue.ToString();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    // Close the connection
    cn.Close();
}

After the SqlCommand object’s CommandType property is set to CommandType.StoredProcedure, the SqlParameter object used to supply the input value to the StockValue stored procedure is created. SqlParameter objects can be created either by using the SqlParameter class constructor or by executing the SqlCommand object’s Parameters collection Add method. In this example, the parameter is created using the Add method of the SqlCommand object’s Parameters collection. The first parameter supplied to the Add method is a string containing the name of the parameter. In this case "@ProductID". Again, note that replaceable parameters used by the SQLParameter object must begin with the at symbol (@). The second parameter uses the SqlDbType.Int enumeration to indicate that the parameter will contain an Integer value. The next line sets the Direction property to the value ParameterDirection.Input to indicate that this is an input parameter. Last, the SqlParameter object’s Value property is set to 1—storing a value of 1 to pass to the StockValue stored procedure.

The next section of code sets up a Try-Catch block to open the connection to the SQL Server system and then executes the StockValue stored procedure. The important point to note in the Try-Catch block is that the cmd SqlCommand object’s ExecuteScalar method is used to execute the StockValue stored procedure and the return value is assigned to the nStockValue variable. The contents of the nStockValue variable are then assigned to a text box. Like the earlier examples, if the connection or the stored procedure fails, a message box showing the error text will be displayed to the end user. Then the connection will be closed.

Transactions enable you to group together multiple operations that can be performed as a single unit of work, which helps to ensure database integrity. For instance, transferring funds from your savings account to your checking account involves multiple database operations, and the transfer cannot be considered complete unless all of the operations are successfully completed. A typical transfer from your savings account to your checking account requires two separate but related operations: a withdrawal from your savings account and a deposit to your checking account. If either operation fails, the transfer is not completed. Therefore, both of these functions would be considered part of the same logical transaction. From the database standpoint, to ensure database integrity, both the withdrawal and the deposit would be grouped together as a single transaction. If the withdrawal operation succeeded but the deposit failed, the entire transaction could be rolled back, which would restore the database to the condition it had before the withdrawal operation was attempted. Using transactions is an essential part of most production-level database applications.

ADO.NET supports transactions using the Transaction classes. In order to incorporate transactions into your ADO.NET application you first need to create an instance of the SqlTransaction object and then execute the BeginTransaction method to mark the beginning of a transaction. Under the covers this will cause the database server to begin a transaction. For instance, using the SqlTransaction object to issue a BeginTransaction statement will send a T-SQL BEGIN TRANSACTION command to SQL Server. After the transaction has started, the database update operations are performed and then the Commit method is used to actually write the updates to the target database. If an error occurs during the process, then the RollBack operation is used to undo the changes. The following SQLCommandTransaction subroutine shows how to start a transaction and then either commit the results of the transaction to the database or roll back the transaction in the event of an error:

Private Sub SQLCommandTransaction(ByVal sServer As String, _ 
        ByVal sDB As String)
    Dim cn As New SqlConnection("SERVER="  & sServer _
        &  ";INTEGRATED SECURITY=True;DATABASE="  & sDB)
    Dim cmd As New SqlCommand()
    Dim trans As SqlTransaction
    ' Start a local transaction
    cn.Open()
    trans = cn.BeginTransaction()
    cmd.Connection = cn
    cmd.Transaction = trans
    Try
        '  Insert a row  transaction
        cmd.CommandText = _
            "INSERT INTO Department VALUES(100,  'Transaction 100' )"
        cmd.ExecuteNonQuery()
        ' This will result in an error
        cmd.CommandText = _
                "INSERT INTO Department VALUES(100,  'Transaction 101' )"
        cmd.ExecuteNonQuery()
        trans.Commit()
    Catch e As Exception
        MsgBox(e.Message)
        trans.Rollback()
    Finally
        cn.Close()
    End Try  End Sub
end sub

private void SQLCommandTransaction(string sServer, string sDB)
{
    SqlConnection cn = new SqlConnection("SERVER=" + sServer
       + ";INTEGRATED SECURITY=True;DATABASE=" + sDB); 
    SqlCommand cmd = new SqlCommand();
    SqlTransaction trans;
    // Start a local transaction
    cn.Open();
    trans = cn.BeginTransaction();
    cmd.Connection = cn;
    cmd.Transaction = trans;
    try
    {
          // Insert a row transaction
          cmd.CommandText =
              "INSERT INTO Department VALUES(100, 'Transaction 100')";
          cmd.ExecuteNonQuery();
          // This will result in an error
          cmd.CommandText =
              "INSERT INTO Department VALUES(100, 'Transaction 101')";
          cmd.ExecuteNonQuery();
          trans.Commit();
    }
    catch (Exception ex)
    {
          MessageBox.Show(ex.Message);
          trans.Rollback();
    }
    finally
    {
          cn.Close();
    }
}

In the beginning of this subroutine, you can see where new instances of the SqlConnection and SqlCommand objects are created, followed by the definition of a SqlTransaction object named trans. Next, a local transaction is started by first opening the connection and then using the cn SqlConnection object’s BeginTransaction method to create a new instance of a SqlTransaction object. Note that the connection must be open before you execute the BeginTransaction method. Next, the cmd SqlCommand Connection property is assigned with the cn SqlConnection and the Transaction property is assigned with the trans SqlTransaction object.

Within the Try-Catch block, two commands are issued that are within the local transaction scope. The first command is an INSERT statement that inserts two columns into the Department table that was created previously in this chapter. The first insert statement adds the DepartmentID of 100 along with a DepartmentName value of 'Transaction 100'. The SqlCommand ExecuteNonQuery method is then used to execute the SQL statement. Next, the cmd object’s CommandText property is set to another SQL INSERT statement. However, this statement will cause an error because it is attempting to insert a duplicate primary key value. In this second case, the DepartmentID of 100 is attempted to be inserted along with the DepartmentName value of 'Transaction 101'. This causes an error because the DepartmentID of 100 was just inserted by the previous INSERT statement. When the ExecuteNonQuery method is executed, the duplicate primary key error will be issued and the code in the Catch portion of the Try-Catch block will be executed.

Displaying the exception message in a message box is the first action that happens within the Catch block. You can see an example of this message in Figure 8-2.

Figure 8-2.  A duplicate primary key error prevents the Commit operation.

After the message box is displayed, the trans SqlTransaction object’s RollBack method is used to roll back the attempted transaction. Note that because both insert statements were within the same transaction scope both insert operations will be rolled back. The resulting department table will not contain either DepartmentID 100 or DepartmentID 101.

In this example, a Finally block is used to close the cn SqlConnection object. If the Finally block is attached to the Try-Catch structure, the code in the Finally block will always be executed.

Summary