How to use SqlTransaction in C#

Question

I am using the following code to execute two commands at once. I used SqlTransaction to assure either all command get executed or rolled back. When I run my program without "transaction", it runs properly; but when I use "transaction" with them, they show error.

My code:

SqlTransaction transaction = connectionsql.BeginTransaction();
            
try
{
    SqlCommand cmd1 = new SqlCommand("select account_name from master_account where NOT account_name = 'BANK' AND NOT account_name = 'LOAN'", connectionsql);
    SqlDataReader dr1 = cmd1.ExecuteReader();
    while (dr1.Read())
    {
        comboBox1.Items.Add(dr1[0].ToString().Trim());
    }
    cmd1.Dispose();
    dr1.Dispose();

    SqlCommand cmd2 = new SqlCommand("select items from rate",connectionsql);
    SqlDataReader dr2 = cmd2.ExecuteReader();
    while (dr2.Read())
    {
        comboBox2.Items.Add(dr2[0].ToString().Trim());
    }
    cmd2.Dispose();
    dr2.Dispose();
    transaction.Commit();

    dateTimePicker4.Value = dateTimePicker3.Value;
}
catch(Exception ex)
{
    transaction.Rollback();
    MessageBox.Show(ex.ToString());
}

Error:

Answer 1

You have to tell your SQLCommand objects to use the transaction:

cmd1.Transaction = transaction;

or in the constructor:

SqlCommand cmd1 = new SqlCommand("select...", connectionsql, transaction);

Make sure to have the connectionsql object open, too.

But all you are doing are SELECT statements. Transactions would benefit more when you use INSERT, UPDATE, etc type actions.

Answer 2

The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods. The transaction is rolled back on any error, or if it is disposed without first being committed. Try/Catch error handling is used to handle any errors when attempting to commit or roll back the transaction.

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection 
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction. 
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred 
                // on the server that would cause the rollback to fail, such as 
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

See SqlTransaction Class

Answer 3

You can create a SqlTransaction from a SqlConnection.

And use it to create any number of SqlCommands

SqlTransaction transaction = connection.BeginTransaction();
var cmd1 = new SqlCommand(command1Text, connection, transaction);
var cmd2 = new SqlCommand(command2Text, connection, transaction);

Or

var cmd1 = new SqlCommand(command1Text, connection, connection.BeginTransaction());
var cmd2 = new SqlCommand(command2Text, connection, cmd1.Transaction);

---

If the failure of commands never cause unexpected changes don't use transaction.

if the failure of commands might cause unexpected changes put them in a Try/Catch block and rollback the operation in another Try/Catch block.

Why another try/catch? According to MSDN:

Try/Catch exception handling should always be used when rolling back a transaction. A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

Here is a sample code:

string connStr = "[connection string]";
string cmdTxt = "[t-sql command text]";

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
    var cmd = new SqlCommand(cmdTxt, conn, conn.BeginTransaction());
    

    try
    {
        cmd.ExecuteNonQuery();
        //before this line, nothing has happened yet
        cmd.Transaction.Commit();
    }
    catch(System.Exception ex)
    {
        //You should always use a Try/Catch for transaction's rollback
        try
        {
            cmd.Transaction.Rollback();
        }
        catch(System.Exception ex2)
        {
            throw ex2;
        }
        throw ex;
    }

    conn.Close();
}

The transaction is rolled back in the event it is disposed before Commit or Rollback is called.

So you don't need to worry about app being closed.

Answer 4

Well, I don't understand why are you used transaction in case when you make a select.

Transaction is useful when you make changes (add, edit or delete) data from database.

Remove transaction unless you use insert, update or delete statements

Answer 5

Update or Delete with sql transaction

 private void SQLTransaction() {
   try {
     string sConnectionString = "My Connection String";
     string query = "UPDATE [dbo].[MyTable] SET ColumnName = '{0}' WHERE ID = {1}";

     SqlConnection connection = new SqlConnection(sConnectionString);
     SqlCommand command = connection.CreateCommand();
     connection.Open();
     SqlTransaction transaction = connection.BeginTransaction("");
     command.Transaction = transaction;
     try {
       foreach(DataRow row in dt_MyData.Rows) {
         command.CommandText = string.Format(query, row["ColumnName"].ToString(), row["ID"].ToString());
         command.ExecuteNonQuery();
       }
       transaction.Commit();
     } catch (Exception ex) {
       transaction.Rollback();
       MessageBox.Show(ex.Message, "Error");
     }
   } catch (Exception ex) {
     MessageBox.Show("Problem connect to database.", "Error");
   }
 }

Answer 6

First you don't need a transaction since you are just querying select statements and since they are both select statement you can just combine them into one query separated by space and use Dataset to get the all the tables retrieved. Its better this way since you made only one transaction to the database because database transactions are expensive hence your code is faster. Second of you really have to use a transaction, just assign the transaction to the SqlCommand like

sqlCommand.Transaction = transaction;

And also just use one SqlCommand don't declare more than one, since variables consume space and we are also on the topic of making your code more efficient, do that by assigning commandText to different query string and executing them like

sqlCommand.CommandText = "select * from table1";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "select * from table2";
sqlCommand.ExecuteNonQuery();