Transactions with Parameters

Introduction

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.

Background

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
	(ClienteContactoId,fecha,campoModificado,valor,modificador)" _
         & "VALUES(@ClienteContactoId,@Fecha,@CampoModificado,@Valor,@Modificador)"

        Dim transCmd As New SqlCommand
        transCmd.Parameters.Add("@Field1", SqlDbType.BigInt)
        transCmd.Parameters.Add("@Field2", SqlDbType.DateTime)
        transCmd.Parameters.Add("@Field3", SqlDbType.VarChar, 50)
        transCmd.Parameters.Add("@Field4", SqlDbType.VarChar, 255)
        transCmd.Parameters.Add("@Field5", SqlDbType.BigInt)

        Dim dtParams As New DataTable
        dtParams.Columns.Add("Field1")
        dtParams.Columns.Add("Field2")
        dtParams.Columns.Add("Field3")
        dtParams.Columns.Add("Field4")
        dtParams.Columns.Add("Field5")

        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"
        dtParams.Rows.Add(row1)

        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"
        dtParams.Rows.Add(row2)

        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 insert/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: row1 and row2).

Now, we just call the function TryTransactionWithParams. This function receives four parameters:

Connection string
SqlCommand object
SQL query
A 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)
        c.Open()

        'Start the transaction    
        Dim myTrans As SqlTransaction = c.BeginTransaction()

        Try
            Dim sql As String = sqlQuerie

            cmd.CommandText = sql
            cmd.Connection = c
            cmd.Transaction = myTrans

            'lets loop the dtParams (which contains the parameters for the sql command, 
            'each row is a sql command) and associate the values of each row 
            'with the parameters.
            'Then lets execute the query

            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)
                Next j
                cmd.ExecuteNonQuery()

                If i = 0 Then i += cmd.Parameters.Count - 1 _
			Else i += cmd.Parameters.Count
            Next i

            'If we reach here, all command succeeded, so commit the transaction
            myTrans.Commit()
            ok = True
        Catch ex As SqlException
            'Something went wrong, so rollback the transaction
            myTrans.Rollback()
            ok = False
        Finally
            If c.State <> ConnectionState.Closed Then c.Close() 'Finally, 
							'close the connection
        End Try

        Return ok
    End Function

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

Now, let's add to the SqlCommand object that we passed as a parameter some properties, such as the SqlQuery, the Transaction object and the Connection object.

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 @ with ?. And when specifying parameter types, replace SqlDbType with MySqlDbType.

Notice

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 SqlCommand object.

Conclusion

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!