Using Transaction in ADO.net SQL

Question

I am new to ADO, so I want to ask if I did right using transaction. Here the code snippet

string SQL1 = "INSERT INTO tbl_cust(cust_id,cust_name) values ('000001','YoungMcD') ";
string SQL2 = "UPDATE tbl_cust SET custname='OldMcDonald' WHERE cust_id='000001'";
string SQL3 = "SELECT * FROM tbl_supplier WHERE supplier_code ='000001'";

// write connstring
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
// end of connection string

// setting connection
SqlConnection db = new SqlConnection(conn);
SqlTransaction transaction1;

db.Open();
transaction1 = db.BeginTransaction();

try
{
    // insert to table
    SqlCommand Com1 = new SqlCommand(SQL1, db, transaction1);
    Com1.ExecuteNonQuery();

    SqlCommand Com2 = new SqlCommand(SQL2, db, transaction1);
    Com2.ExecuteNonQuery();

    SqlCommand Com3 = new SqlCommand(SQL3, db, transaction1);
    Com3.ExecuteNonQuery();

    transaction1.Commit();

    db.Close();
}
catch
{
    transaction1.Rollback();
    db.Close();
    msg = "error";
    goto endret;
}

For transaction, should I use

SqlCommand Com1 = new SqlCommand(SQL1, db, transaction1);

instead of

SqlCommand Com1 = new SqlCommand(SQL1, db);

because I already state begin transaction before try{} statement

EDIT:

I get it, First syntax is applicable, but how to use ADO effectively?. I find that this way is too straightforward.

I found myself keep doing this for inserting parameter, ex:

string SQL1 = "INSERT INTO tbl_cust(cust_id,cust_name) values ('" + param1 +"','"+ param2 +"') ";

Answer 1

A lot has been happened since last year.Here I tried to simplified the answer.

string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
string SQL1 = "INSERT INTO tbl_cust(cust_id,cust_name) values ('000001','YoungMcD') ";
string SQL2 = "UPDATE tbl_cust SET custname='OldMcDonald' WHERE cust_id='000001'";

using (SqlConnection conn = new SqlConnection(ConnStr))
{
    SqlTransaction transaction = null;
    try
    {
        conn.Open();
        transaction = conn.BeginTransaction();
        using (SqlCommand cmd = new SqlCommand(SQL1, conn, transaction)) { cmd.ExecuteNonQuery(); }
        using (SqlCommand cmd = new SqlCommand(SQL2, conn, transaction)) { cmd.ExecuteNonQuery(); }
        transaction.Commit();
        savestats = true;
    }
    catch (Exception ex)
    {
         // 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.
        }
    }
}

The reason why transaction is declared outside of try{} so we can rollback it in catch{}.

The downfall of this code is when error happened in conn.Open() for whatever the reason is, then attempt of transaction.Rollback() will cause exception.

That's why another try{} catch{} added to handle it.