Devart Forums

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