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
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.
So what does the code look like?
namespace ProperlyExecutingSqlCommands_CSharp {
internal class Version0 : VersionBase, IVersion {
private static System.Data.SqlClient.SqlConnection connection;
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;
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;
}
public override int VersionNumber {
get {
return 0;
}
}
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.";
}
}
}
}
Public Class Version0
Inherits VersionBase
Implements IVersion
Private Shared connection As System.Data.SqlClient.SqlConnection
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
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
Public Overrides ReadOnly Property VersionNumber() As Integer _
Implements IVersion.VersionNumber
Get
Return 0
End Get
End Property
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. :)
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.
So the first modification is to add proper error handling, in other words use try…catch. The code looks now like this
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 {
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;
}
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
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.
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.
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
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;
}
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
This is a topic I can’t emphasize enough:
- Use parameters.
- Always use parameters.
- 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
The OrderItem table exists no more. The statement executed when the order was added was actually the following:
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
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;
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;
}
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;
}
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
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
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.
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
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;
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;
}
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;
}
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
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
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.
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.
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;
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;
}
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;
}
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
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
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.
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.
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;
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;
}
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;
}
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
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
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
