Introduction

In my last article I shared code to be able to use parameters with transactions. I received some comments saying that transactions should be handled in database and not in code. And they are completely right under my understanding. My explanation to them about why I made this if I think the same as them was the following:

"Sometimes we are not able to manage or alter databases but we still should do our best, and in these cases, we need to do it from code. So having a standard function to use parameters with transactions from our code would help us to still prevent data errors and securing our solution." 

Background 

If you read my last article you probably remember that it was a first approach and that we could use it only in a scenario: when we need to commit the same query multiple times but with different values.

This time I want to share something that is not limited to a single scenario. This is just a sample, so if you want to make it more efficient, or faster, you can modify it. 

Why is it helpful? 

When we are not able to manipulate the database and we need to integrate with a closed solution (for example) (so we are not able to set up transactions from database) we often have classes to manage our database and retrieve and commit data from and towards it.

When we have to handle only a Query it is easy. We have a SqlCommand object, we add SqlParameters to it, we assign them values and we execute the query. But when we need to handle multiple queries at once we usually have a function that uses SqlTransactions to handle it, but still using SqlParameters is a pain. So now we'll have the ability to manage multiple queries using parameters, no matter how many queries are we going to commit.

Even more, we don't even need to know what the queries are going to be, we could handle it dynamically and it still would work! 

Using the code 

The Transaction Function 

Dim ok As Boolean

Dim c As New SqlConnection(connString)
c.Open()

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

Try
    Dim posValue As Integer = 0

    For i = 0 To dtQueries.Rows.Count - 1
        Dim cmd As New SqlCommand
        cmd = arrCommand(i)

        cmd.CommandText = dtQueries.Rows(i).Item(0).ToString
        cmd.Connection = c
        cmd.Transaction = myTrans

        For j = 0 To cmd.Parameters.Count - 1

            cmd.Parameters(j).Value = dtValues.Rows(posValue).Item(0)
            posValue += 1

        Next j

        cmd.ExecuteNonQuery()

    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

This function receives four things that we need to commit the transactions:

1. ConnectionString
2. An array with SqlCommand objects
3. A DataTable with the SqlQueries
4. A DataTable with the values

We create the SqlConnection object, we pass to its constructor the ConnectionString and we open the connection. Right after that we create the SqlTransaction object, and we start the transaction.

Now, we just simply loop through our Queries Datatable. For each row of our Queries Datatable we passed, a SqlCommand object in our Command Array with the needed  parameters was passed,  and the corresponding values in the same order in our Values DataTable too. We'll just have a nested loop through the parameters of the associated SqlCommand object to the current query to assign it its value.

Notice that Values DataTable has only a column, that's why we use the variable posValue, to keep track of the following row we need to be positioned at.

After all the parameters for this query were assigned we just execute the query. Once all the queries were configured we commit the transaction to the database.

All this code is inside a Try-Catch block to be able to rollback if something went wrong.

Calling Code  

'Creation of Queries Datatable
Dim dtQueries As New DataTable
dtQueries.Columns.Add("Query")
'Creation and addition of the queries to the datatable
dtQueries.Rows.Add("UPDATE clientes SET Baja=@Baja WHERE Id=@Id")
dtQueries.Rows.Add("INSERT INTO clientes_movimientos(ClienteId,Fecha,Accion,Motivo,Modificador)_
    VALUES(@ClienteId,@Fecha,@Accion,@Motivo,@Modificador)")

Dim  As SqlCommand
'Creation of SqlCommand Datatable
Dim dtCommand As New DataTable
dtCommand.Columns.Add("SqlCommand")
'Creation and addition of the sql command objects with their parameters
Dim cmd1 As New SqlCommand
cmd1.Parameters.Add("@Baja", SqlDbType.Bit)
cmd1.Parameters.Add("@Id", SqlDbType.BigInt)

dtCommand.Rows.Add(cmd1)
arrCommand(0) = cmd1
''''''''''''''''''''''''''
Dim cmd2 As New SqlCommand
cmd2.Parameters.Add("@ClienteId", SqlDbType.BigInt)
cmd2.Parameters.Add("@Fecha", SqlDbType.DateTime)
cmd2.Parameters.Add("@Accion", SqlDbType.Char, 1)
cmd2.Parameters.Add("@Motivo", SqlDbType.VarChar, 255)
cmd2.Parameters.Add("@Modificador", SqlDbType.BigInt)

dtCommand.Rows.Add(cmd2)
arrCommand(1) = cmd2
'Creation of Values Datatable
Dim dtValues As New DataTable
dtValues.Columns.Add("Value")
dtValues.Rows.Add(baja)
dtValues.Rows.Add(id)
dtValues.Rows.Add(id)
dtValues.Rows.Add(CType(Now, DateTime))
Dim accion As String
If baja = True Then accion = "B" Else accion = "A"
dtValues.Rows.Add(accion)
dtValues.Rows.Add(motivo)
dtValues.Rows.Add(userId)

Return oTransactions.tryTransactionWithParaReturn oTransactions.tryTransactionWithParamsAndQueries(
    cadcon, arrCommand, dtQueries, dtValues)

In the calling code we just need to prepare the objects that we are going to pass as parameters to the Transaction Function.

We first create our Queries DataTable with a column called "Query". We'll add as many rows as needed with the SQL Queries we want to execute.

After that we'll create our Command Array. Its length is going to be the number of rows our Queries DataTable has. We'll create a SqlCommand object with its corresponding parameters added for each query, and we'll add them to the Command Array in the same order as their corresponding queries were added to our Queries DataTable.

As we are going to have (in most cases) different number of parameters for each query, our Values DataTable will have only a column called "Value" and we'll add a row for each value of each query that we have. It is IMPORTANT that the values are added in the same order as the parameters.

Now we are ready to call our Transaction function. 

Final Words   

Well, we now have a standard function to use in different scenarios and we are able now to use SqlParameters within SqlTransactions having different queries and parameters. You can read the original article from my blog If there's any question, concern, suggestion or something, please feel free to leave a comment. Hope it helps.

Thanks for reading!