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) but 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 MS 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"

       .....

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 are you 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 (wich 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()

Read more: Codeproject

Posted via email from Jasper-net