how to use sqltransaction in c#

Question

I am using 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 run properly but when I use "transaction" with them they show error. My code is as follow;

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());
}

and 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.