How I do Update in SQL with c#

Question

I have this function in c#. When the FOR is call, a error appears in ExecuteNonQuery. The error is "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

SqlConnection cnn = new SqlConnection(WebConfigurationManager.ConnectionStrings["strCnn"].ToString());
                cnn.Open();
                SqlTransaction trx = cnn.BeginTransaction();

                try
                {

                    SqlCommand cmd= new SqlCommand();

                    for (int j = 0; j < arr.Length; j++) {
                        cmd.CommandText = "UPDATE rc SET nc= " +  arr[j].Col3 + " WHERE cr = " + arr[j].Col1;
                        cmd.Connection = cnn;
                        cmd.ExecuteNonQuery();
                    }

                    trx.Commit();
                    return 1;
                }
                catch (SqlException ex)
                {
                    try
                    {
                        trx.Rollback();
                        return 0;
                    }
                    catch (Exception exRollback)
                    {
                        return 0;
                    }  
                }

Answer 1

As has been pointed out, you never assign your transaction to the command. However there are a few other points I have picked up on.

First and foremost USE PARAMETRISED QUERIES, they will improve performace, type safety and most importantly save you from SQL Injection Attacks.

So instead of:

comando.CommandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = " +  arr[j].Col3 + " WHERE CD_RECURSO = " + arr[j].Col1;

You would use:

comando.CommandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = @Col3 WHERE CD_RECURSO = @Col1";
comando.Parameters.AddWithValue("@Col3", arr[j].Col3);
comando.Parameters.AddWithValue("@Col1", arr[j].Col1);

Secondly, wrap your sql command object with a using wrapper to ensure it is properly dispose of, there is no benefit from reusing the same object over and over again (and this can cause problems):

for (int j = 0; j < arr.Length; j++) 
{
    using (var comando = new SqlCommand("UPDATE RECURSO_CLIENTE SET NM_CLIENTE = @Col3 WHERE CD_RECURSO = @Col1", conexao))
    {
        comando.Transaction = trx;
        comando.Parameters.AddWithValue("@Col3", arr[j].Col3);
        comando.Parameters.AddWithValue("@Col1", arr[j].Col1);
        comando.ExecuteNonQuery();
    }
}

Finally, if you are using SQL-Server 2008+ you can use Table valued Parameters to do this update in a single query:

You first need a type

CREATE TABLE YourTypeName AS TABLE (Col1 INT, Col3 INT);

Then your update statement would be something like:

DECLARE @UpdateValues AS YourTypeName;

MERGE RECURSO_CLIENTE rc
USING @UpdateValues u
    ON rc.CD_RECURSO = u.Col1
WHEN MATCHED UPDATE
    SET NM_CLIENTE = u.Col3;

This means a single statement and you don't need to use explicit transactions. (You might wonder why I have used merge instead of UPDATE, here is why). So putting it all together you would get:

var dataTable = new DataTable();
dataTable.Columns.Add("Col1", typeof(int));
dataTable.Columns.Add("Col3", typeof(int));

for (int j = 0; j < arr.Length; j++) 
{
    var newRow = dataTable.NewRow();
    newRow[0] = arr[j].Col1;
    newRow[1] = arr[j].Col3;
    dataTable.Rows.Add(newRow);
}

string sql = @" MERGE RECURSO_CLIENTE rc
                USING @UpdateValues u
                    ON rc.CD_RECURSO = u.Col1
                WHEN MATCHED UPDATE
                    SET NM_CLIENTE = u.Col3;";

using (var conexao = new SqlConnection(WebConfigurationManager.ConnectionStrings["strConexao"].ToString()))
using (var comando = new SqlCommand(sql, conexao))
{
    conexao.Open(); 
    var tableParam = new SqlParameter("@UpdateValues", SqlDbType.Structured);
    tableParam.TypeName = "@YourTypeName";
    tableParam.Value = dataTable;
    comando.Parameters.Add(tableParam);
    comando.ExecuteNonQuery();
}

Answer 2

This error message shows that you have opened a transaction and it is still open at the point of executing ExecuteNonQuery,

You are executing ExecuteNonQuery before commitment of transaction.

Define

comando.Transaction = trx;

So that ExecuteNonQuery will be executed on the same transaction.

Answer 3

Your sqlCommand doesn't know about your transaction.

Here's a copy-paste fix:

SqlConnection conexao = new SqlConnection(WebConfigurationManager.ConnectionStrings["strConexao"].ToString());
conexao.Open();
SqlTransaction trx = conexao.BeginTransaction();

try
{
    for (int j = 0; j < arr.Length; j++) {
        var commandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = " +  arr[j].Col3 + " WHERE CD_RECURSO = " + arr[j].Col1;
        SqlCommand comando = new SqlCommand(commandText, conexao, trx);
        comando.ExecuteNonQuery();
    }

    trx.Commit();
    return 1;
}
catch (SqlException ex)
{
    try
    {
        trx.Rollback();
        return 0;
    }
    catch (Exception exRollback)
    {
        return 0;
    }  
}

Answer 4

You need to set SqlCommand's transaction property.

 SqlCommand comando = new SqlCommand();
 comando.Transaction = trx;

Answer 5

You need to assign the transaction to the command, like so:

SqlCommand comando = new SqlCommand();
comando.Transaction = trx;

---

I would also recommend setting the Connection property outside of the for loop too, so you code would look like this:

SqlCommand comando = new SqlCommand();
comando.Transaction = trx;
comando.Connection = conexao;

for (int j = 0; j < arr.Length; j++) {
    comando.CommandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = " +  arr[j].Col3 + " WHERE CD_RECURSO = " + arr[j].Col1;
    comando.ExecuteNonQuery();
}

trx.Commit();

Answer 6

you forgot to set transaction

 comando.Transaction = trx;

Answer 7

Use

// Create command on transaction and automatically assign open transaction
var comando = conexao.CreateCommand()

or assign the transaction to the command.

// Create command
var comando = new SqlCommand();
// and assign transaction manually
comando.Transaction = trx;