Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

Properly executing database operations

Rate me:
Please Sign up or sign in to vote.
4.91/5 (40 votes)
10 Jul 2017CPOL14 min read 53.7K   889   60   23
This article discusses some basic requirements for successful database operations such as error handling, use of parameters and transactions.

Introduction

The purpose of this article is to show five things that should always be considered when creating code that access and modifies a database. I’ve used WPF as the user interface technology but the code principles concerning the database are the same regardless whether you use ASP.NET, WPF, Windows Forms etc.

The topics covered in this article are:

Version 0: The initial code

Version 1: Add error handling

Version 2: Close and dispose database objects

Version 3: Use parameters, always

Version 4: Use prepare for repeated statements

Version 5: Use transactions (SqlTransaction)

Version 6: Use transaction (TransactionScope)

Version 0: The initial code

Let’s jump directly to the code. The code example is simple. Based on the user input, the code will add one order to the database and three order items in a loop. The user interface looks like this

Image 1

In order to use the application, you need to provide correct connection string that points to your SQL Server database. In many cases it's enough to just change the server name, instance name and the database name. If you use for example SQL Server authentication or want to modify other properties of the connection string, please refer to SqlConnection.ConnectionString Property.

After defining the correct connection string, with "Create tables" button you create the tables needed for the code to run. After that you can input the values into Order data fields and select the operation version you want to use. By pressing "Execute statements" button the selected code version is run.

On the second tab you can browse the data in the tables and truncate the tables if needed.

Image 2

So what does the code look like?

C#
namespace ProperlyExecutingSqlCommands_CSharp {
   /// <summary>
   /// Class to execute version 0
   /// </summary>
   internal class Version0 : VersionBase, IVersion {

      // used to hold the once opened connection
      private static System.Data.SqlClient.SqlConnection connection;

      /// <summary>
      /// Add the order to the database
      /// </summary>
      /// <param name="connectionString">Connection string to use</param>
      /// <param name="orderNumber">Order number as text from the user interface</param>
      /// <param name="price">Price as text from the user interface</param>
      /// <param name="product">Product from the user interface</param>
      /// <param name="orderDate">Order date as text from the user interface</param>
      /// <param name="generateError">Is an errorneous statement executed in the end</param>
      /// <param name="errorText">Explanation for an error (if any)</param>
      /// <returns>True if succesful</returns>
      public override bool AddOrder(string connectionString, string orderNumber, string price
                                   , string product, string orderDate, bool generateError
                                   , out string errorText) {
         string sql;
         System.Data.SqlClient.SqlCommand command;

         errorText = null;
         // Check if the connection is created and if not create and open
         if (connection == null) {
            connection = new System.Data.SqlClient.SqlConnection(connectionString);
            connection.Open();
         }

         sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" 
             + orderNumber + ",'" + orderDate + "')";
         command = new System.Data.SqlClient.SqlCommand(sql, connection);
         command.ExecuteNonQuery();

         for (int counter = 1; counter <= 3; counter++) {
            sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" 
                + orderNumber + "," + counter + ", '" + product + "'," + price + ")";
            command = new System.Data.SqlClient.SqlCommand(sql, connection);
            command.ExecuteNonQuery();
         }

         if (generateError) {
            this.GenerateError(connection);
         }

         return true;
      }

      /// <summary>
      /// Returns the version number
      /// </summary>
      public override int VersionNumber {
         get {
            return 0;
         }
      }

      /// <summary>
      /// Returns an explanation for a version
      /// </summary>
      public override string Explanation {
         get {
            return
@"This is the basic version of the execution. The code will insert an order and three order items in a loop based on the user input in the text boxes. 

If ""Cause an SQL execution error in the end"" is selected then an incorrect statement is executed after adding the orders and order item. This is for testing error handling.";
         }
      }
   }
}
VB.NET
    '*** Class to execute version 0
Public Class Version0
   Inherits VersionBase
   Implements IVersion

   ' used to hold the once opened connection
   Private Shared connection As System.Data.SqlClient.SqlConnection

   '*** Add the order to the database
   Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                     , price As String, product As String, orderDate As String _
                                     , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim command As System.Data.SqlClient.SqlCommand

      errorText = Nothing
      ' Check if the connection is created and if not create and open
      If (connection Is Nothing) Then
         connection = New System.Data.SqlClient.SqlConnection(connectionString)
         connection.Open()
      End If

      sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" _
          & orderNumber & ",'" & orderDate & "')"
      command = New System.Data.SqlClient.SqlCommand(sql, connection)
      command.ExecuteNonQuery()

      For counter As Integer = 1 To 3 Step 1
         sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" _
             & orderNumber & "," & counter & ", '" & product & "'," & price & ")"
         command = New System.Data.SqlClient.SqlCommand(sql, connection)
         command.ExecuteNonQuery()
      Next counter

      If (generateError) Then
         Me.GenerateError(connection)
      End If

      Return True
   End Function

   '*** Returns the version number
   Public Overrides ReadOnly Property VersionNumber() As Integer _
      Implements IVersion.VersionNumber
      Get
         Return 0
      End Get
   End Property

   '*** Returns an explanation for a version
   Public Overrides ReadOnly Property Explanation() As String _
      Implements IVersion.Explanation
      Get
         Return _
"This is the basic version of the execution. The code will insert an order and three order items in a loop based on the user input in the text boxes. " & vbCrLf & _
"" & vbCrLf & _
"If ""Cause an SQL execution error in the end"" is selected then an incorrect statement is executed after adding the orders and order item. This is for testing error handling."
      End Get
   End Property

End Class

The AddOrder method receives the inputs from the user interface as text, just as they were entered in the first place. In this initial version the connection is a static variable which is opened once if not already open. After checking the connection a SqlCommand is created in order to add the PurchaseOrder row. After adding the order three OrderItem rows are inserted using new SqlCommands.

As you probably already noticed there are a lot of problems with this code. However, code similar to this does exist in the real world, unfortunately. The key thing is to understand what are the problems and how they should be fixed. We’ll go through those in each version.

The error generation part in the end of the AddOrder is mainly usable in testing the higher versions. Most likely you don’t need error generation in version 0 since you’re lucky if you get it working properly in the first place. :)

Version 1: Add error handling

From the user point of view the perhaps most annoying thing with version 0 is that if an error occurs, the program crashes. All the modifications are lost and you need to start from the beginning. But it’s not only for the user. As a programmer you would love to have more information about the error occurred in order to fix it.

If I define a proper connection string and just press Execute statements without providing any data I get the following screen and the program crashes.

Image 3

So the first modification is to add proper error handling, in other words use try…catch. The code looks now like this

C#
      public override bool AddOrder(string connectionString, string orderNumber, string price 
                                   , string product, string orderDate, bool generateError 
                                   , out string errorText) {
         string sql;
         System.Data.SqlClient.SqlCommand command;
         bool returnValue = false;

         errorText = null;

         try {
            // Check if the connection is created and if not create and open
            if (connection == null) {
               connection = new System.Data.SqlClient.SqlConnection(connectionString);
               connection.Open();
            }

            sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" 
                + orderNumber + ",'" + orderDate + "')";
            command = new System.Data.SqlClient.SqlCommand(sql, connection);
            command.ExecuteNonQuery();

            for (int counter = 1; counter <= 3; counter++) {
               sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" 
                   + orderNumber + "," + counter + ", '" + product + "'," + price + ")";
               command = new System.Data.SqlClient.SqlCommand(sql, connection);
               command.ExecuteNonQuery();
            }

            if (generateError) {
               this.GenerateError(connection);
            }

            returnValue = true;
         } catch (System.Data.SqlClient.SqlException sqlException) {
            errorText = string.Format("Error {0} in line {1}:\n{2}", 
                                      sqlException.Number, 
                                      sqlException.LineNumber, 
                                      sqlException.Message);
         } catch (System.Exception exception) {
            errorText = exception.Message;
         }

         return returnValue;
      }
VB.NET
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                  , price As String, product As String, orderDate As String _
                                  , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim command As System.Data.SqlClient.SqlCommand
      Dim returnValue As Boolean = False

      errorText = Nothing

      Try
         ' Check if the connection is created and if not create and open
         If (connection Is Nothing) Then
            connection = New System.Data.SqlClient.SqlConnection(connectionString)
            connection.Open()
         End If

         sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" _
             & orderNumber & ",'" & orderDate & "')"
         command = New System.Data.SqlClient.SqlCommand(sql, connection)
         command.ExecuteNonQuery()

         For counter As Integer = 1 To 3 Step 1
            sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" _
                & orderNumber & "," & counter & ", '" & product & "'," & price & ")"
            command = New System.Data.SqlClient.SqlCommand(sql, connection)
            command.ExecuteNonQuery()
         Next counter

         If (generateError) Then
            Me.GenerateError(connection)
         End If

         returnValue = True
      Catch sqlException As System.Data.SqlClient.SqlException
         errorText = String.Format("Error {0} in line {1}:\n{2}", _
                                   sqlException.Number, _
                                   sqlException.LineNumber, _
                                   sqlException.Message)
      Catch exception As System.Exception
         errorText = exception.Message
      End Try

      Return returnValue
   End Function

The code is basically the same but now all the operations are done inside a try-block. If an exception is thrown the catch blocks handle the situation. I’ve separated SqlExceptions from other exceptions because in SqlException there is extra information in Number and LineNumber properties which don’t exist in other exceptions. The error and the line number help you to locate the problem, especially with larger SQL statements.

The information about the exception is added to the errorText parameter and returned to the caller. The return value of the method is Boolean so that the calling side can easily find out if the method succeeded or not and show appropriate messages to the user.

If I now again press the Execute statements button without providing any data I get much more information and the program continues to run.

Image 4

From the user point of view this error text doesn’t say much but for the programmer it’s vital information in order to fix the program. So instead of just showing the error text you would probably want to write the error in a log file, Windows Event Log or similar.

Version 2: Close and dispose database objects

In the previous versions the connection is opened once and it remains open until the application is closed. Also note that the SqlCommands are created but they are not properly disposed anywhere. Both SqlConnection and SqlCommand use unmanaged resources so they should be released as soon as possible in order to save the resources.

You can of course write code that disposes the object when it’s not needed anymore but it would be much easier to place the object in a using block. So what comes to the SqlCommand objects, in this version they are simply defined in a using block. This way the object will be disposed in all situations, even if an exception is thrown.

The SqlConnection is also wrapped in a using block but this modification requires a bit more discussion. In the initial version the once opened connection was stored for the lifetime of the application so there was no reason to open the connection again. Now the connection is placed in a using block and the connection is closed and disposed when the code exits from the block.

So what about performance? Now the connection is opened and closed each time an order is added. Won’t this severely affect the performance of the application? You’re right, sort of.

It’s true that opening a connection is a slow operation and it requires a few roundtrips to the database server and back. However, the SQL Server ADO NET provider offers connection pooling to address this concern. When pooling is on and an application requests an open connection it will be returned from the pool to the calling application. If no open connection exist in the pool and the Max pool size isn’t met yet a new connection is created and returned.

When the application closes the connection, it isn’t actually closed but reset and returned to the pool to wait for the next request. This behavior ensures that constantly re-opening connections doesn’t degrade the performance.

So now the code looks like this

C#
      public override bool AddOrder(string connectionString, string orderNumber, string price
                                   , string product, string orderDate, bool generateError
                                   , out string errorText) {
         string sql;
         bool returnValue = false;

         errorText = null;

         using (System.Data.SqlClient.SqlConnection connection 
         = new System.Data.SqlClient.SqlConnection()) {
            try {
               connection.ConnectionString = connectionString;
               connection.Open();

               sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" 
                   + orderNumber + ",'" + orderDate + "')";
               using (System.Data.SqlClient.SqlCommand command 
               = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                  command.ExecuteNonQuery();
               }

               for (int counter = 1; counter <= 3; counter++) {
                  sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" 
                      + orderNumber + "," + counter + ", '" + product + "'," + price + ")";
                  using (System.Data.SqlClient.SqlCommand command 
                  = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                     command.ExecuteNonQuery();
                  }
               }

               if (generateError) {
                  this.GenerateError(connection);
               }

               returnValue = true;
            } catch (System.Data.SqlClient.SqlException sqlException) {
               errorText = string.Format("Error {0} in line {1}:\n{2}", 
                                         sqlException.Number, 
                                         sqlException.LineNumber, 
                                         sqlException.Message);
            } catch (System.Exception exception) {
               errorText = exception.Message;
            }

            if (connection.State == System.Data.ConnectionState.Open) {
               connection.Close();
            }
         }

         return returnValue;
      }
VB.NET
   Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                     , price As String, product As String, orderDate As String _
                                     , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim returnValue As Boolean = False

      errorText = Nothing

      Using connection As System.Data.SqlClient.SqlConnection _
      = New System.Data.SqlClient.SqlConnection()
         Try
            connection.ConnectionString = connectionString
            connection.Open()

            sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" _
                & orderNumber & ",'" & orderDate & "')"
            Using command As System.Data.SqlClient.SqlCommand _
            = New System.Data.SqlClient.SqlCommand(sql, connection)
               command.ExecuteNonQuery()
            End Using

            For counter As Integer = 1 To 3 Step 1
               sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" _
                   & orderNumber & "," & counter & ", '" & product & "'," & price & ")"
               Using command As System.Data.SqlClient.SqlCommand _
               = New System.Data.SqlClient.SqlCommand(sql, connection)
                  command.ExecuteNonQuery()
               End Using
            Next counter

            If (generateError) Then
               Me.GenerateError(connection)
            End If

            returnValue = True
         Catch sqlException As System.Data.SqlClient.SqlException
            errorText = String.Format("Error {0} in line {1}:\n{2}", _
                                      sqlException.Number, _
                                      sqlException.LineNumber, _
                                      sqlException.Message)
         Catch exception As System.Exception
            errorText = exception.Message
         End Try

         If (connection.State = System.Data.ConnectionState.Open) Then
            connection.Close()
         End If
      End Using

      Return returnValue
   End Function

Version 3: Use parameters, always

This is a topic I can’t emphasize enough:

  1. Use parameters.
  2. Always use parameters.
  3. Never ever add user input directly to the SQL statement.

SQL injection

So why is this so important; there are a few reasons but the most important one is SQL injection. As in all versions so far the input from the user is directly concatenated to the SQL statement. This means that the user directly affects how the SQL statement syntactically looks like. This also means that the user is capable of affecting the SQL statement by injecting for example extra commands into the statement.

SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable, and become administrators of the database server.

Let’s test this. If I input the following data into the form

  • Order number: 1
  • Order date: 8/18/2015'); DROP TABLE OrderItem; --
  • Product: A
  • Price: 1

And then hit the Execute statements button I get the following error message

Image 5

The OrderItem table exists no more. The statement executed when the order was added was actually the following:

SQL
INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (1,'8/18/2015'); DROP TABLE OrderItem; --')

So the user managed to drop a database object just by adding a command to one of the inpute fields. You can recreate the missing table by pressing the Create tables button again. 

If I repeat the test with a parameterized version I would get an error “Failed to convert parameter value from a String to a DateTime.” because now the user input cannot be converted to a date. When the values are provided as parameters the there is no way for the user altering the syntax. This way you're safe from SQL injections but also from unintentional mistakes in the values.

In order for the program to behave more robust I’ve added checks for the parameter values in the beginning of the method. Each parameter is converted to the correct underlying data type and if the conversion fails, the user will be informed.

Type conversions

Another benefit of using parameters is the conversions. Previously I needed to input all the values into the text fields in such format that the database would understand them. Since the values were directly added to the SQL statement and the statement was sent as-is to the database server I needed to use date format and decimal separators as they are expected at the database server. 

Being a user of the application, how would I know what is the correct format? Or should I even care about the format at the database server since I want to use the format I recognize.

Few examples:

  • The format for the date needed to be mm/dd/yyyy in order for the insert to succeed. If I would use Finnish format (dd.mm.yyyy) the statement would fail because of an implicit conversion error.
  • The same type of problem happens with decimal numbers. When the values were concatenated to the SQL statement I would have to use point (.) as the decimal separator. Again if I would use Finnish format and comma (,) as the decimal separator the SQL statement fail since it would have extra values compared to the column list. For example 1,23 would be interpreted as 1 as a value for one column and 23 as a value for another column.
  • The third thing is the NULL values. Regardless how the values are supplied to the statement, NULL values have to be considered separately. This is because null in C#  (Nothing in VB)is different than NULL in SQL. If a parameter has a null value, the provider thinks the value hasn’t been supplied and throws an exception. To properly add a NULL value to a column a System.DBNull.Value needs to be set for a parameter. With parameters this is more simple since we're just setting the value, not altering the syntax of the statement. For example with a string value you need to add quotation marks around the value unless the value is set to NULL.
  • Also with strings you don't have to worry about escaping special characters. For example if the text contains a quuotation mark, it's perfectly alright to set it as a paramter value. If you would concatenate it to the statement you'd need t escape it.

So the code looks now like this

C#
      public override bool AddOrder(string connectionString, string orderNumber, string price
                                   , string product, string orderDate, bool generateError
                                   , out string errorText) {
         string sql;
         bool returnValue = false;
         int orderNumber_validated;
         decimal price_intermediate;
         decimal? price_validated = null;
         System.DateTime orderDate_validated;

         errorText = null;

         // Data validations
         if (!int.TryParse(orderNumber, out orderNumber_validated)) {
            errorText = "Invalid order number";
            return false;
         }
         if (!string.IsNullOrEmpty(price)) {
            if (!decimal.TryParse(price, out price_intermediate)) {
               errorText = "Invalid price";
               return false;
            }
            price_validated = price_intermediate;
         }
         if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
            errorText = "Invalid order date";
            return false;
         }

         // Insert the data
         using (System.Data.SqlClient.SqlConnection connection 
         = new System.Data.SqlClient.SqlConnection()) {
            try {
               connection.ConnectionString = connectionString;
               connection.Open();

               sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
               using (System.Data.SqlClient.SqlCommand command 
               = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                  command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
                  command.Parameters.AddWithValue("@orderDate", orderDate_validated);

                  command.ExecuteNonQuery();
               }

               for (int counter = 1; counter <= 3; counter++) {
                  sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
                  using (System.Data.SqlClient.SqlCommand command 
                  = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber",
                                           System.Data.SqlDbType.Int));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow", 
                                           System.Data.SqlDbType.Int));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product", 
                                           System.Data.SqlDbType.VarChar, 100));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price", 
                                           System.Data.SqlDbType.Decimal));

                     command.Parameters["@orderNumber"].Value = orderNumber_validated;
                     command.Parameters["@orderRow"].Value = counter;
                     command.Parameters["@product"].Value = product;
                     command.Parameters["@price"].Value = price_validated.HasValue 
                                                        ? (object)price_validated 
                                                        : System.DBNull.Value;

                     command.ExecuteNonQuery();
                  }
               }

               if (generateError) {
                  this.GenerateError(connection);
               }

               returnValue = true;
            } catch (System.Data.SqlClient.SqlException sqlException) {
               errorText = string.Format("Error {0} in line {1}:\n{2}", 
                                         sqlException.Number, 
                                         sqlException.LineNumber, 
                                         sqlException.Message);
            } catch (System.Exception exception) {
               errorText = exception.Message;
            }

            if (connection.State == System.Data.ConnectionState.Open) {
               connection.Close();
            }
         }

         return returnValue;
      }
VB.NET
   Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                     , price As String, product As String, orderDate As String _
                                     , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim returnValue As Boolean = False
      Dim orderNumber_validated As Integer
      Dim price_intermediate As Decimal
      Dim price_validated As Decimal? = Nothing
      Dim orderDate_validated As System.DateTime

      errorText = Nothing

      ' Data validations
      If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
         errorText = "Invalid order number"
         Return False
      End If
      If (Not String.IsNullOrEmpty(price)) Then
         If (Not Decimal.TryParse(price, price_intermediate)) Then
            errorText = "Invalid price"
            Return False
         End If
         price_validated = price_intermediate
      End If
      If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
         errorText = "Invalid order date"
         Return False
      End If

      ' Insert the data
      Using connection As System.Data.SqlClient.SqlConnection _
      = New System.Data.SqlClient.SqlConnection()
         Try
            connection.ConnectionString = connectionString
            connection.Open()

            sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
            Using command As System.Data.SqlClient.SqlCommand _
            = New System.Data.SqlClient.SqlCommand(sql, connection)
               command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
               command.Parameters.AddWithValue("@orderDate", orderDate_validated)

               command.ExecuteNonQuery()
            End Using

            For counter As Integer = 1 To 3 Step 1
               sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
               Using command As System.Data.SqlClient.SqlCommand _
               = New System.Data.SqlClient.SqlCommand(sql, connection)
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber", 
                                         System.Data.SqlDbType.Int))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow", 
                                         System.Data.SqlDbType.Int))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product", 
                                         System.Data.SqlDbType.VarChar, 100))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price", 
                                         System.Data.SqlDbType.Decimal))

                  command.Parameters("@orderNumber").Value = orderNumber_validated
                  command.Parameters("@orderRow").Value = counter
                  command.Parameters("@product").Value = product
                  command.Parameters("@price").Value = If(price_validated.HasValue, 
                                                          price_validated, System.DBNull.Value)

                  command.ExecuteNonQuery()
               End Using
            Next counter

            If (generateError) Then
               Me.GenerateError(connection)
            End If

            returnValue = True
         Catch sqlException As System.Data.SqlClient.SqlException
            errorText = String.Format("Error {0} in line {1}:\n{2}", _
                                      sqlException.Number, _
                                      sqlException.LineNumber, _
                                      sqlException.Message)
         Catch exception As System.Exception
            errorText = exception.Message
         End Try

         If (connection.State = System.Data.ConnectionState.Open) Then
            connection.Close()
         End If
      End Using

      Return returnValue
   End Function

As you can see I’ve used two alternative ways to provide parameter values. For the PurchaseOrder row I’ve used AddWithValue method which is the easiest way to set a value for the parameter. For OrderItem I’ve created the parameters separately and then set the values. This is discussed in more detail in next the version.

Version 4: Use prepare for repeated statements

The next version change handles a situation where the same statement is repeated several times but with different values. Consider for example a situation where the user can make modifications to several rows in a GridView. When these modifications are updated to the database you would need to repeat the same update for each modified row. In this example for a single order row three items are added in a loop.

The version looks like following

C#
      public override bool AddOrder(string connectionString, string orderNumber, string price
                                   , string product, string orderDate, bool generateError
                                   , out string errorText) {
         string sql;
         bool returnValue = false;
         int orderNumber_validated;
         decimal price_intermediate;
         decimal? price_validated = null;
         System.DateTime orderDate_validated;

         errorText = null;

         // Data validations
         if (!int.TryParse(orderNumber, out orderNumber_validated)) {
            errorText = "Invalid order number";
            return false;
         }
         if (!string.IsNullOrEmpty(price)) {
            if (!decimal.TryParse(price, out price_intermediate)) {
               errorText = "Invalid price";
               return false;
            }
            price_validated = price_intermediate;
         }
         if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
            errorText = "Invalid order date";
            return false;
         }

         // Insert the data
         using (System.Data.SqlClient.SqlConnection connection 
         = new System.Data.SqlClient.SqlConnection()) {
            try {
               connection.ConnectionString = connectionString;
               connection.Open();

               sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
               using (System.Data.SqlClient.SqlCommand command 
               = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                  command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
                  command.Parameters.AddWithValue("@orderDate", orderDate_validated);

                  command.ExecuteNonQuery();
               }

               sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
               using (System.Data.SqlClient.SqlCommand command 
               = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                  command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber", 
                                         System.Data.SqlDbType.Int));
                  command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow", 
                                         System.Data.SqlDbType.Int));
                  command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product", 
                                         System.Data.SqlDbType.VarChar, 100));
                  command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price", 
                                         System.Data.SqlDbType.Decimal, 10));
                  command.Parameters["@price"].Precision = 10;
                  command.Parameters["@price"].Scale = 2;

                  command.Prepare();
                  for (int counter = 1; counter <= 3; counter++) {
                     command.Parameters["@orderNumber"].Value = orderNumber_validated;
                     command.Parameters["@orderRow"].Value = counter;
                     command.Parameters["@product"].Value = product;
                     command.Parameters["@price"].Value = price_validated.HasValue 
                                                        ? (object)price_validated 
                                                        : System.DBNull.Value;

                     command.ExecuteNonQuery();
                  }
               }

               if (generateError) {
                  this.GenerateError(connection);
               }

               returnValue = true;
            } catch (System.Data.SqlClient.SqlException sqlException) {
               errorText = string.Format("Error {0} in line {1}:\n{2}", 
                                         sqlException.Number, 
                                         sqlException.LineNumber, 
                                         sqlException.Message);
            } catch (System.Exception exception) {
               errorText = exception.Message;
            }

            if (connection.State == System.Data.ConnectionState.Open) {
               connection.Close();
            }
         }

         return returnValue;
      }
VB.NET
   Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                     , price As String, product As String, orderDate As String _
                                     , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim returnValue As Boolean = False
      Dim orderNumber_validated As Integer
      Dim price_intermediate As Decimal
      Dim price_validated As Decimal? = Nothing
      Dim orderDate_validated As System.DateTime

      errorText = Nothing

      ' Data validations
      If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
         errorText = "Invalid order number"
         Return False
      End If
      If (Not String.IsNullOrEmpty(price)) Then
         If (Not Decimal.TryParse(price, price_intermediate)) Then
            errorText = "Invalid price"
            Return False
         End If
         price_validated = price_intermediate
      End If
      If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
         errorText = "Invalid order date"
         Return False
      End If

      ' Insert the data
      Using connection As System.Data.SqlClient.SqlConnection _
      = New System.Data.SqlClient.SqlConnection()
         Try
            connection.ConnectionString = connectionString
            connection.Open()

            sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
            Using command As System.Data.SqlClient.SqlCommand _
            = New System.Data.SqlClient.SqlCommand(sql, connection)
               command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
               command.Parameters.AddWithValue("@orderDate", orderDate_validated)

               command.ExecuteNonQuery()
            End Using

            sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
            Using command As System.Data.SqlClient.SqlCommand _
            = New System.Data.SqlClient.SqlCommand(sql, connection)
               command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber", 
                                      System.Data.SqlDbType.Int))
               command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow", 
                                      System.Data.SqlDbType.Int))
               command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product", 
                                      System.Data.SqlDbType.VarChar, 100))
               command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price", 
                                      System.Data.SqlDbType.Decimal, 10))
               command.Parameters("@price").Precision = 10
               command.Parameters("@price").Scale = 2

               command.Prepare()
               For counter As Integer = 1 To 3 Step 1
                  command.Parameters("@orderNumber").Value = orderNumber_validated
                  command.Parameters("@orderRow").Value = counter
                  command.Parameters("@product").Value = product
                  command.Parameters("@price").Value = If(price_validated.HasValue, _
                                                          price_validated, System.DBNull.Value)

                  command.ExecuteNonQuery()
               Next counter
            End Using

            If (generateError) Then
               Me.GenerateError(connection)
            End If

            returnValue = True
         Catch sqlException As System.Data.SqlClient.SqlException
            errorText = String.Format("Error {0} in line {1}:\n{2}", _
                                      sqlException.Number, _
                                      sqlException.LineNumber, _
                                      sqlException.Message)
         Catch exception As System.Exception
            errorText = exception.Message
         End Try

         If (connection.State = System.Data.ConnectionState.Open) Then
            connection.Close()
         End If
      End Using

      Return returnValue
   End Function

As you can see, before entering the loop the parameters are all defined and the Prepare method is called. This doesn’t execute anything so no modifications are done in the database but what happens is that the execution plan is created for the statement and internally a statement handle is returned to the provider. Now when the ExecuteNonQuery is called repeatedly the same statement handle is used for all executions. All we need to do is to change the values for the parameters in each iteration.

This seems like a very small change, and from the code point of view it actually is. But from the database point of view this lightens the execution a lot. Each time a new, non-prepared statement is executed the database needs to do the following tasks:

  • Check the syntax of the statement
  • Resolve the objects and columns
  • Check the privileges, does the caller have proper privileges for the operation
  • Run a number of permutations in order to decide the optimal execution plan
  • Compile the execution plan
  • Set the variable values
  • Execute the statement

When a repeating statement is prepared and the same statement handle is used for all executions the first five steps can be omitted for subsequent executions. So no doubt this will save time. For a really complex statement the prepare phase can take even several seconds. To be honest, some of the steps can be avoided even if the statement isn’t prepared beforehand but that’s a whole different story.

There is one thing that needs to be taken care of when Prepare is used. The sizes for parameters need to be explicitly set. For example you can see in the code that I have set the precision and scale for the price. This is mandatory since the database needs to know how much space it needs to allocate in order to successfully run all coming executions. Remember that the database doesn’t yet know all the values we’re going to provide.

Version 5, use transactions (SqlTransaction)

The last two versions cover the usage of transactions. This first one uses SqlTransaction to properly wrap the operations inside a transaction.

If you have played with the application you may have noticed that in cases where the first statement was executed successfully and the second one failed the inserted order row remained in the database. In other words even if the execution failed as a whole, part of the data was saved leaving the database in logically inconsistent state; You have orders but no order items…

Transactions handle these situations. By using a transaction everything is successfully saved or nothing at all. This is a key technology in order to follow ACID principle in modifications.

C#
      public override bool AddOrder(string connectionString, string orderNumber, string price
                                   , string product, string orderDate, bool generateError
                                   , out string errorText) {
         string sql;
         bool returnValue = false;
         int orderNumber_validated;
         decimal price_intermediate;
         decimal? price_validated = null;
         System.DateTime orderDate_validated;

         errorText = null;

         // Data validations
         if (!int.TryParse(orderNumber, out orderNumber_validated)) {
            errorText = "Invalid order number";
            return false;
         }
         if (!string.IsNullOrEmpty(price)) {
            if (!decimal.TryParse(price, out price_intermediate)) {
               errorText = "Invalid price";
               return false;
            }
            price_validated = price_intermediate;
         }
         if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
            errorText = "Invalid order date";
            return false;
         }

         // Insert the data
         using (System.Data.SqlClient.SqlConnection connection 
         = new System.Data.SqlClient.SqlConnection()) {
            try {
               connection.ConnectionString = connectionString;
               connection.Open();

               using (System.Data.SqlClient.SqlTransaction transaction 
               = connection.BeginTransaction("AddOrder")) {

                  sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
                  using (System.Data.SqlClient.SqlCommand command 
                  = new System.Data.SqlClient.SqlCommand(sql, connection, transaction)) {
                     command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
                     command.Parameters.AddWithValue("@orderDate", orderDate_validated);

                     command.ExecuteNonQuery();
                  }

                  sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
                  using (System.Data.SqlClient.SqlCommand command 
                  = new System.Data.SqlClient.SqlCommand(sql, connection, transaction)) {
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber", 
                                            System.Data.SqlDbType.Int));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow", 
                                            System.Data.SqlDbType.Int));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product", 
                                            System.Data.SqlDbType.VarChar, 100));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price", 
                                            System.Data.SqlDbType.Decimal, 10));
                     command.Parameters["@price"].Precision = 10;
                     command.Parameters["@price"].Scale = 2;

                     command.Prepare();
                     for (int counter = 1; counter <= 3; counter++) {
                        command.Parameters["@orderNumber"].Value = orderNumber_validated;
                        command.Parameters["@orderRow"].Value = counter;
                        command.Parameters["@product"].Value = product;
                        command.Parameters["@price"].Value = price_validated.HasValue 
                                                           ? (object)price_validated 
                                                           : System.DBNull.Value;

                        command.ExecuteNonQuery();
                     }
                  }

                  if (generateError) {
                     this.GenerateError(connection, transaction);
                  }

                  transaction.Commit();
               }
               returnValue = true;
            } catch (System.Data.SqlClient.SqlException sqlException) {
               errorText = string.Format("Error {0} in line {1}:\n{2}", 
                                         sqlException.Number, 
                                         sqlException.LineNumber, 
                                         sqlException.Message);
            } catch (System.Exception exception) {
               errorText = exception.Message;
            }

            if (connection.State == System.Data.ConnectionState.Open) {
               connection.Close();
            }
         }

         return returnValue;
      }
VB.NET
   Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                     , price As String, product As String, orderDate As String _
                                     , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim returnValue As Boolean = False
      Dim orderNumber_validated As Integer
      Dim price_intermediate As Decimal
      Dim price_validated As Decimal? = Nothing
      Dim orderDate_validated As System.DateTime

      errorText = Nothing

      ' Data validations
      If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
         errorText = "Invalid order number"
         Return False
      End If
      If (Not String.IsNullOrEmpty(price)) Then
         If (Not Decimal.TryParse(price, price_intermediate)) Then
            errorText = "Invalid price"
            Return False
         End If
         price_validated = price_intermediate
      End If
      If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
         errorText = "Invalid order date"
         Return False
      End If

      ' Insert the data
      Using connection As System.Data.SqlClient.SqlConnection _
      = New System.Data.SqlClient.SqlConnection()
         Try
            connection.ConnectionString = connectionString
            connection.Open()

            Using transaction As System.Data.SqlClient.SqlTransaction _
            = connection.BeginTransaction("AddOrder")

               sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
               Using command As System.Data.SqlClient.SqlCommand _
               = New System.Data.SqlClient.SqlCommand(sql, connection, transaction)
                  command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
                  command.Parameters.AddWithValue("@orderDate", orderDate_validated)

                  command.ExecuteNonQuery()
               End Using

               sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
               Using command As System.Data.SqlClient.SqlCommand _ 
               = New System.Data.SqlClient.SqlCommand(sql, connection, transaction)
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber", 
                                         System.Data.SqlDbType.Int))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow", 
                                         System.Data.SqlDbType.Int))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product", 
                                         System.Data.SqlDbType.VarChar, 100))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price", 
                                         System.Data.SqlDbType.Decimal, 10))
                  command.Parameters("@price").Precision = 10
                  command.Parameters("@price").Scale = 2

                  command.Prepare()
                  For counter As Integer = 1 To 3 Step 1
                     command.Parameters("@orderNumber").Value = orderNumber_validated
                     command.Parameters("@orderRow").Value = counter
                     command.Parameters("@product").Value = product
                     command.Parameters("@price").Value = If(price_validated.HasValue, _
                                                             price_validated, System.DBNull.Value)

                     command.ExecuteNonQuery()
                  Next counter
               End Using

               If (generateError) Then
                  Me.GenerateError(connection, transaction)
               End If

               transaction.Commit()
            End Using

            returnValue = True
         Catch sqlException As System.Data.SqlClient.SqlException
            errorText = String.Format("Error {0} in line {1}:\n{2}", _
                                      sqlException.Number, _
                                      sqlException.LineNumber, _
                                      sqlException.Message)
         Catch exception As System.Exception
            errorText = exception.Message
         End Try

         If (connection.State = System.Data.ConnectionState.Open) Then
            connection.Close()
         End If
      End Using

      Return returnValue
   End Function

The basic principle is quite the same as with commands. I wrapped both of the executions inside a using block which defines the transaction. If all the operations are successfully carried out the transaction is committed in the end by calling Commit method. This makes the changes permanent and visible to other users. If any of the operations fail the transaction block will be left without commit so the transaction is automatically rolled back meaning none of the changes are actually done.

When using SqlTransaction each command need to be bound to the transaction by setting the Transaction property. You can tick the “Cause an SQL execution error in the end“ to test the functionality of the transaction. On the second tab you can see if the data was inserted or not in case of an error.

Version 6: Use transaction (TransactionScope)

This last version is very similar to the previous one and the purpose is the same; to use a transaction. However there are some differences. The SqlTransaction is solely for the database operations. TransactionScope defines a transaction that starts on the calling side and ‘escalates’ to the database. When SQL executions are wrapped inside a transaction scope the transaction manager automatically enlists operations into the transaction. Because of this the transaction scope is extremely easy to use.

C#
      public override bool AddOrder(string connectionString, string orderNumber, string price
                                   , string product, string orderDate, bool generateError
                                   , out string errorText) {
         string sql;
         bool returnValue = false;
         int orderNumber_validated;
         decimal price_intermediate;
         decimal? price_validated = null;
         System.DateTime orderDate_validated;

         errorText = null;

         // Data validations
         if (!int.TryParse(orderNumber, out orderNumber_validated)) {
            errorText = "Invalid order number";
            return false;
         }
         if (!string.IsNullOrEmpty(price)) {
            if (!decimal.TryParse(price, out price_intermediate)) {
               errorText = "Invalid price";
               return false;
            }
            price_validated = price_intermediate;
         }
         if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
            errorText = "Invalid order date";
            return false;
         }

         // Insert the data
         using (System.Transactions.TransactionScope transactionScope 
         = new System.Transactions.TransactionScope()) {
            using (System.Data.SqlClient.SqlConnection connection 
            = new System.Data.SqlClient.SqlConnection()) {
               try {
                  connection.ConnectionString = connectionString;
                  connection.Open();

                  sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
                  using (System.Data.SqlClient.SqlCommand command 
                  = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                     command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
                     command.Parameters.AddWithValue("@orderDate", orderDate_validated);

                     command.ExecuteNonQuery();
                  }

                  sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
                  using (System.Data.SqlClient.SqlCommand command 
                  = new System.Data.SqlClient.SqlCommand(sql, connection)) {
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber", 
                                            System.Data.SqlDbType.Int));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow", 
                                            System.Data.SqlDbType.Int));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product", 
                                            System.Data.SqlDbType.VarChar, 100));
                     command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price", 
                                            System.Data.SqlDbType.Decimal, 10));
                     command.Parameters["@price"].Precision = 10;
                     command.Parameters["@price"].Scale = 2;

                     command.Prepare();
                     for (int counter = 1; counter <= 3; counter++) {
                        command.Parameters["@orderNumber"].Value = orderNumber_validated;
                        command.Parameters["@orderRow"].Value = counter;
                        command.Parameters["@product"].Value = product;
                        command.Parameters["@price"].Value = price_validated.HasValue 
                                                           ? (object)price_validated 
                                                           : System.DBNull.Value;

                        command.ExecuteNonQuery();
                     }
                  }

                  if (generateError) {
                     this.GenerateError(connection);
                  }

                  transactionScope.Complete();
                  returnValue = true;
               } catch (System.Data.SqlClient.SqlException sqlException) {
                  errorText = string.Format("Error {0} in line {1}:\n{2}", 
                                            sqlException.Number, 
                                            sqlException.LineNumber, 
                                            sqlException.Message);
               } catch (System.Exception exception) {
                  errorText = exception.Message;
               }

               if (connection.State == System.Data.ConnectionState.Open) {
                  connection.Close();
               }
            }
         }

         return returnValue;
      }
VB.NET
   Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
                                     , price As String, product As String, orderDate As String _
                                     , generateError As Boolean, ByRef errorText As String) As Boolean _
      Implements IVersion.AddOrder

      Dim sql As String
      Dim returnValue As Boolean = False
      Dim orderNumber_validated As Integer
      Dim price_intermediate As Decimal
      Dim price_validated As Decimal? = Nothing
      Dim orderDate_validated As System.DateTime

      errorText = Nothing

      ' Data validations
      If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
         errorText = "Invalid order number"
         Return False
      End If
      If (Not String.IsNullOrEmpty(price)) Then
         If (Not Decimal.TryParse(price, price_intermediate)) Then
            errorText = "Invalid price"
            Return False
         End If
         price_validated = price_intermediate
      End If
      If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
         errorText = "Invalid order date"
         Return False
      End If

      ' Insert the data
      Using transactionScope As System.Transactions.TransactionScope _
      = New System.Transactions.TransactionScope()
         Using connection As System.Data.SqlClient.SqlConnection _
         = New System.Data.SqlClient.SqlConnection()
            Try
               connection.ConnectionString = connectionString
               connection.Open()

               sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
               Using command As System.Data.SqlClient.SqlCommand _
               = New System.Data.SqlClient.SqlCommand(sql, connection)
                  command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
                  command.Parameters.AddWithValue("@orderDate", orderDate_validated)

                  command.ExecuteNonQuery()
               End Using

               sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
               Using command As System.Data.SqlClient.SqlCommand _
               = New System.Data.SqlClient.SqlCommand(sql, connection)
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber", 
                                         System.Data.SqlDbType.Int))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow", 
                                         System.Data.SqlDbType.Int))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product", 
                                         System.Data.SqlDbType.VarChar, 100))
                  command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price", 
                                         System.Data.SqlDbType.Decimal, 10))
                  command.Parameters("@price").Precision = 10
                  command.Parameters("@price").Scale = 2

                  command.Prepare()
                  For counter As Integer = 1 To 3 Step 1
                     command.Parameters("@orderNumber").Value = orderNumber_validated
                     command.Parameters("@orderRow").Value = counter
                     command.Parameters("@product").Value = product
                     command.Parameters("@price").Value = If(price_validated.HasValue, _
                                                          price_validated, System.DBNull.Value)

                     command.ExecuteNonQuery()
                  Next counter
               End Using

               If (generateError) Then
                  Me.GenerateError(connection)
               End If

               transactionScope.Complete()
               returnValue = True
            Catch sqlException As System.Data.SqlClient.SqlException
               errorText = String.Format("Error {0} in line {1}:\n{2}", _
                                         sqlException.Number, _
                                         sqlException.LineNumber, _
                                         sqlException.Message)
            Catch exception As System.Exception
               errorText = exception.Message
            End Try

            If (connection.State = System.Data.ConnectionState.Open) Then
               connection.Close()
            End If
         End Using
      End Using

      Return returnValue
   End Function

As before the transaction is defined in the using block. This time instead of calling a Commit in the end the Complete method is executed to signal a successful transaction and to make the changes permanent. It’s important to notice that the SqlCommand isn’t explicitly bound to the transaction. The transaction property doesn’t need to be set and the transaction object doesn’t need to be passed to other methods that execute SQL statements inside the single transaction. From the coding point of view this simplifies the situation a lot.

Conclusions

The purpose of this article was to show few key concepts for successful database handling. I hope it clarifies why certain things need to be done and how they actually make your software more robust. 

What I also hope is that you notice that doing the things in a right way doesn't actually require a lot. And as a bonus it makes the code easier to handle and read.

And of course all discussion is more than welcome.

References

The following pages are useful for more information:

History

  • 19th August, 2015: Created
  • 20th August, 2015: VB version added
  • 4th October, 2015: Essential code changes highlighted, minor readability changes in VB code formatting

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
SuggestionNice to know, but now let EntityFramework do its job. Pin
Mr.PoorEnglish4-Oct-15 3:55
Mr.PoorEnglish4-Oct-15 3:55 
AnswerRe: Nice to know, but now let EntityFramework do its job. Pin
Wendelius4-Oct-15 9:20
mentorWendelius4-Oct-15 9:20 
GeneralRe: Nice to know, but now let EntityFramework do its job. Pin
Mr.PoorEnglish4-Oct-15 11:39
Mr.PoorEnglish4-Oct-15 11:39 
GeneralRe: Nice to know, but now let EntityFramework do its job. Pin
Sascha Lefèvre17-Oct-17 5:58
professionalSascha Lefèvre17-Oct-17 5:58 
GeneralRe: Nice to know, but now let EntityFramework do its job. Pin
Herman<T>.Instance25-Jan-18 1:44
Herman<T>.Instance25-Jan-18 1:44 
QuestionGreat Pin
Luc Pattyn28-Sep-15 4:13
sitebuilderLuc Pattyn28-Sep-15 4:13 
AnswerRe: Great Pin
Wendelius29-Sep-15 7:55
mentorWendelius29-Sep-15 7:55 
GeneralMy vote of 5! Pin
enterprisetoday28-Sep-15 0:58
professionalenterprisetoday28-Sep-15 0:58 
Smile | :)

Thanks for your article. Today I learned
  1. sqlcommand etc use unmanaged code
  2. TransactionScope.
Also thanks for providing c# and vb.

Dallas
GeneralRe: My vote of 5! Pin
Wendelius29-Sep-15 7:56
mentorWendelius29-Sep-15 7:56 
GeneralMy vote of 5 Pin
D V L11-Sep-15 23:54
professionalD V L11-Sep-15 23:54 
GeneralRe: My vote of 5 Pin
Wendelius19-Sep-15 21:54
mentorWendelius19-Sep-15 21:54 
SuggestionSuggestion - discuss stored procedure usage Pin
ahagel21-Aug-15 15:32
professionalahagel21-Aug-15 15:32 
GeneralRe: Suggestion - discuss stored procedure usage Pin
Wendelius24-Aug-15 3:41
mentorWendelius24-Aug-15 3:41 
GeneralMy vote of 4 Pin
Abhishek Kumar Goswami21-Aug-15 2:07
professionalAbhishek Kumar Goswami21-Aug-15 2:07 
GeneralRe: My vote of 4 Pin
Wendelius21-Aug-15 7:40
mentorWendelius21-Aug-15 7:40 
GeneralMy vote of 5 Pin
cjb11020-Aug-15 21:18
cjb11020-Aug-15 21:18 
GeneralRe: My vote of 5 Pin
Wendelius21-Aug-15 7:39
mentorWendelius21-Aug-15 7:39 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun20-Aug-15 20:55
Humayun Kabir Mamun20-Aug-15 20:55 
GeneralRe: My vote of 5 Pin
Wendelius21-Aug-15 7:37
mentorWendelius21-Aug-15 7:37 
GeneralMy vote of 5 Pin
MrShadowGames20-Aug-15 11:15
professionalMrShadowGames20-Aug-15 11:15 
GeneralRe: My vote of 5 Pin
Wendelius20-Aug-15 17:44
mentorWendelius20-Aug-15 17:44 
GeneralMy vote of 5 Pin
Jörgen Andersson19-Aug-15 9:39
professionalJörgen Andersson19-Aug-15 9:39 
GeneralRe: My vote of 5 Pin
Wendelius19-Aug-15 18:36
mentorWendelius19-Aug-15 18:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.