How Begin and Commit transaction in each loop

[malinsky]

Hello,

please help me. How implement begin and commit transaction i each loop for one prepared SqlCommand. See code with comment.

Code: Select all

#region Test Devart commit transaction in each loop
SqlConnection connection = new SqlConnection(<connection string to MSSQL>);
SqlTransaction trans = null;
SqlCommand cmdIns = new SqlCommand("SP_Insert_Record", connection);
cmdIns.CommandType = CommandType.StoredProcedure;
cmdIns.ParameterCheck = true;

try
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    // first start transaction and Prepare() command
    trans = connection.BeginTransaction(); 
    cmdIns.Prepare();

    // commit transaction in each loop
    for (int i = 0; i < 10; i++)
    {
        // start transaction for each next loop
        if (trans == null)
            trans = connection.BeginTransaction(); 

        cmdIns.Transaction = trans;

        cmdIns.Parameters["@KomuEmail"].Value = i.ToString();

        // only first ExecuteNonQuery() accepted, second loop raised exception:
        //
        // ExecuteNonQuery requires the command to have a transaction when the connection
        // assigned to the command in a local transaction waiting
        // to complete. Transaction property of the command has not been initialized. [-2146233079]
                    
        cmdIns.ExecuteNonQuery(); 

        trans.Commit();
        trans = null;

        Console.WriteLine("i = {0}", i);
    }
}
catch (Exception exception)
{
    if (trans != null)
        trans.Rollback();
    Console.WriteLine(String.Format("Exception[HResult]: {0}[{1}]", exception.Message, exception.HResult));
}
finally
{
    if (connection.State == ConnectionState.Open)
    {
        connection.Close();
    }
}
#endregion

What am I doing wrong ?

Thanks Pavel

[Pinturiccio]

This is a designed behaviour. When you prepare a command, you prepare it with a certain transaction, and under the second run of cmdIns.ExecuteNonQuery(), you have another transaction object that is different from the one specified during preparing the command. Microsoft SqlClient provider has the same behavior.

If you want to use one command with several transactions, you should avoid preparing a command, or use one common transaction.

[malinsky]

Hello,

OK. Thanks for the explanation.

PMal