none
SqlTransaction not working as I expect RRS feed

  • Question

  • Hi All,

    we would like having a suggestion on how to use properly SqlTransaction. Here below the code we are trying to use:

     static void TestSqlServer(string connectionString)
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
    
                    SqlCommand command = connection.CreateCommand();
                    string statement = string.Empty;
                    command.Connection = connection;
    
    
                    using (SqlTransaction trans = connection.BeginTransaction(System.Data.IsolationLevel.Serializable))
                    {
                        try
                        {
                            statement = "INSERT INTO ATable(id, Name, Number) VALUES (100,'Luigi2',0)";
                            command.CommandText = statement;
                            command.Transaction = trans;
                            q = command.ExecuteNonQuery();
                        }
                        catch { }
                        try
                        {
                            //This update fails because of Number is declared as numeric in the table and here is a string
                            statement = "UPDATE ATable SET Name = 'Luigi+Filippo', Number = 'AA' WHERE ID = 1";
                            command.CommandText = statement;
                            command.Transaction = trans;
                            command.ExecuteNonQuery();
    
                            Console.WriteLine(String.Format("EXECUTED: '{0}'", statement));
                        }
                        catch { }
    
                        try
                        {
                            statement = "UPDATE ATable SET Name = 'Filippo', Number = '2272' WHERE ID = 1";
                            command.CommandText = statement;
                            command.Transaction = trans;
                            command.ExecuteNonQuery();
                            Console.WriteLine(String.Format("EXECUTED: '{0}'", statement));
                        }
                        catch { }
                        try
                        {
                            statement = "UPDATE ATable SET Name = 'David+Francesca', Number = '0123456789' WHERE ID = 2";
                            command.CommandText = statement;
                            command.Transaction = trans;
                            command.ExecuteNonQuery();
                            Console.WriteLine(String.Format("EXECUTED: '{0}'", statement));
                        }
                        catch { }
                        try
                        {
                            statement = "INSERT INTO ATable(id, Name, Number) VALUES (101,'Luigi2',0)";
                            command.CommandText = statement;
                            command.ExecuteNonQuery();
                        }
                        catch { }
                        trans.Commit();
                    }
                }
            }

    What we are experiencing is that the Commit() fails because the SqlTransaction is no longer usable due to the failure of one of the statement.

    What we want to do is to commit all statements not giving back any error\exception and also we want to decide if committing or rollback all the statemtents after having reached a certain number of errors.

    Thanks in advance

    David and Luigi

    Thursday, February 23, 2012 5:34 PM

Answers

All replies

  • Just create a savepoint so that you can rollback only a portion of the transaction:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.save(v=vs.71).aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, February 23, 2012 7:47 PM
  • Hi Paul,

    here find the code that we are want to implement.

    After the first statement (correctly inserted), the second one fails and the "tran" object is no more usable due to "This SqlTransaction has completed; it is no longer usable." error as you ca see below.

    On Oracle and Sybase the methodology works fine with respective classes.

    static void TestSqlServer(string connectionString) { const int CommitThreshold = 2; using (SqlConnection connection = new SqlConnection(connectionString)) { int successCommandscounter = 0; connection.Open(); SqlCommand command = connection.CreateCommand(); string statement = string.Empty; command.Connection = connection; using (SqlTransaction tran = connection.BeginTransaction(System.Data.IsolationLevel.Serializable)) {

    \\this is execute fine successCommandscounter += Execute("INSERT INTO ATable(id, Name, Number) VALUES (100,'Luigi2',0)", command, tran);

    \\this fails

    successCommandscounter += Execute("UPDATE ATable SET Name = 'Luigi+Filippo', Number = 'AA' WHERE ID = 1", command, tran);

    successCommandscounter += Execute("UPDATE ATable SET Name = 'Filippo', Number = '2272' WHERE ID = 1", command, tran); if (successCommandscounter < CommitThreshold) tran.Rollback(); else tran.Commit(); } } } public static int Execute(string statement, SqlCommand command, SqlTransaction tran) { command.Transaction = tran; command.CommandText = statement; tran.Save("savepoint"); try { command.ExecuteNonQuery(); return 1; } catch (Exception) { tran.Rollback("savepoint"); return 0; } }

    Thank you in advance

    we are desperate programmers :-)

    Friday, February 24, 2012 10:26 AM
  • The SQLTransaction Class does not support an error threshold. Upon the first failure the current transaction cannot continue.

    If you want to implement an error threshold then a SQL Server stored procedure will be required. You can check the @@Error value after execution of each statement to keep a running total of failures. When the threshold has been exceeded you can do a rollback.

    http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 24, 2012 2:31 PM