This time, I want to share something about committing multiple queries to the database with the ability of rolling back if one of them fails (we all know transactions) by taking advantage of the goodness of SQL parameters. I've been searching for something like this, and I couldn't find it, so I made my own attempt.
I had a function that received a connection string and an array containing SQL queries to execute, and that function handled the Transaction. Everything was happiness with MySQL and this function working together. No errors, no problems, until I started using Microsoft SQL instead.
My first error came when trying to execute a SQL Query with a date. Something like "cannot convert string into date type" appeared when debugging.
As queries are being passed in an array, would be a mess to try to find values in the query, see if it's a date, cast it, and reconstruct our query. So the reasonable solution for me seemed to be using SQL parameters. This solution not only solves the error I was facing, but also gives us security from SQL injection, validation between data and types, etc.
Using the Code
The calling code to the TryTransactionWithParams Function
Dim sql As String = "INSERT INTO clientes_contactos_modificaciones
Dim transCmd As New SqlCommand
transCmd.Parameters.Add("@Field3", SqlDbType.VarChar, 50)
transCmd.Parameters.Add("@Field4", SqlDbType.VarChar, 255)
Dim dtParams As New DataTable
Dim row1 As DataRow = dtParams.NewRow
row1(0) = "value 1"
row1(1) = CType(Now, DateTime)
row1(2) = "value 2"
row1(3) = "value 3"
row1(4) = "value 4"
Dim row2 As DataRow = dtParams.NewRow
row2(0) = "value 5"
row2(1) = CType(Now, DateTime)
row2(2) = "value 6"
row2(3) = "value 7"
row2(4) = "value 8"
Return oTransactions.tryTransactionWithParams(cadcon, transCmd, sql, dtParams)
This is just a sample code. I just hard-coded values and number of queries that we are going to have for demonstration, but you can modify this for a real scenario where you don't know how many queries you are going to have.
We are just creating a
SqlCommand object and giving it parameters for the fields we are going to
update. Now we create a
DataTable where we are going to store the values for the parameters for each query (as said, this is just a sample and I hard-coded two transactions:
Now, we just call the function
TryTransactionWithParams. This function receives four parameters:
- Connection string
- SQL query
DataTable with the values for each query
The TryTransactionWithParams Function
Friend Function tryTransactionWithParams(ByVal connString As String, _
ByVal cmd As SqlCommand, ByVal sqlQuerie As String, _
ByVal dtParams As DataTable) As Boolean
Dim ok As Boolean
Dim c As New SqlConnection(connString)
Dim myTrans As SqlTransaction = c.BeginTransaction()
Dim sql As String = sqlQuerie
cmd.CommandText = sql
cmd.Connection = c
cmd.Transaction = myTrans
For i = 0 To dtParams.Rows.Count - 1
For j = 0 To cmd.Parameters.Count - 1
cmd.Parameters(j).Value = dtParams.Rows(i).Item(j)
If i = 0 Then i += cmd.Parameters.Count - 1 _
Else i += cmd.Parameters.Count
ok = True
Catch ex As SqlException
ok = False
If c.State <> ConnectionState.Closed Then c.Close() End Try
This function receives the parameters mentioned above. Let's take a look at it. First, we create the
SqlConnection object and we open the connection. After that, we start the
Now, let's add to the
SqlCommand object that we passed as a parameter some properties, such as the
Transaction object and the
After that, we just create a nested loop, in which we give the corresponding values to the command parameters. Once all values for a query are given, we execute it and loop to the next query (or row of the
DataTable where values are stored)
After the nested loop is done, we just commit the transaction. If it fails, we'll just rollback so no changes are made in our database. All or nothing!
Points of Interest
Notice that this sample works with Microsoft SQL, but making small changes would work with MySQL. Where parameters appear, replace
?. And when specifying parameter types, replace
Please notice the following. You may say that this sample works only for cases where we have to commit the same SQL query, but with different values (as my scenario). It is true, but you can easily extend the functionality of this function. All that is needed is to pass a collection (in an
array, in a
DataTable or whatever you prefer) to the function and loop through it appropriately, as we did with the values we passed or as we did with the
Well, that's it. Now we have a function, that receiving the appropriate parameters, will execute a bunch of queries taking advantage of the goodness of SQL parameters. If there are any questions, concerns, suggestions, etc., please feel free to leave a comment. Hope it helps.
Thanks for reading!
- 25th February, 2011: Initial post