Using SQL transactions with C# in ASP website

Question

I am trying to code a method where a user can pass multiple sql statements in string array format. and create a transaction. Rollback if nessesary. At the moment I get an error saying that the transaction needs a command to execute. Any advice would be appreciated, Maybe another(or right) way of doing what I need done. The following is the existing code.

public bool Scalar(params string[] sqlTexts)
{
    SqlTransaction tran = null;

    try
    {
        using (SqlConnection connection = new SqlConnection(strConnectionString)) // Auto dispose of connection
        {
            connection.Open(); // Open the connection
            using (tran = connection.BeginTransaction())
            {
                using (SqlCommand cmdCommand = connection.CreateCommand()) // Auto dispose of command
                {
                    foreach (string currentSQL in sqlTexts)
                    {
                        cmdCommand.CommandText = currentSQL;
                        cmdCommand.ExecuteScalar();
                    }
                }

                tran.Commit();
            }
        }
        return true;
    }
    catch (Exception)
    {
        if (tran != null)
            tran.Rollback();
        return false;
    }
}

When you debug does currentSQL hold a valid SQL statement with each iteration of the for loop? Might be a good idea to set the CommandType depending on the command to be executed. — Fred
– Fred, Commented Jul 22, 2013 at 9:07

lc. · Accepted Answer · 2013-07-22 09:02:28Z

4

You need to pass the transaction to the command before executing it:

cmdCommand.Transaction = tran;

Furthermore, you might want to be careful. You're using the transaction tran in the try block, but referencing it afterwards in the catch block. I would suggest moving the try/catch to inside the using(tran=...) block.

answered Jul 22, 2013 at 9:02

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dean Martin Over a year ago

Hi, Yes but its declared outside the try catch block :) Thanks so much for the advice though. It worked.

lc. Over a year ago

Yes but it will be disposed before you hit the catch block, so I believe tran.Rollback() will throw an exception and not work

Dean Martin Over a year ago

Any ideal why it would end up executing all the sql statements twice?

lc. Over a year ago

@ReidGarwin Not from the code presented. Trace through the program and see exactly what's getting called when

Dean Martin Over a year ago

Turns out that the ExecuteScalar was the cause. ExecuteNonQuery worked perfectly.

Collectives™ on Stack Overflow

Using SQL transactions with C# in ASP website

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related