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 +"') ";

You need to use the first to tell sql which transaction the command belongs to as you could have multiple transaction going at the same time. — garethb
– garethb, Commented Nov 11, 2016 at 5:41
Please note that while this will work, you are actually making 5 round trips between your application and the sql server. The same actions can be completed with just one round trip using a stored procedure. — Zohar Peled
– Zohar Peled, Commented Nov 11, 2016 at 7:04
Can I ask for explanation of what do you mean about making 5 round trips? because I don't get it, and why doing this is more inferior than creating Stored Procedure — KokoriNut
– KokoriNut, Commented Nov 11, 2016 at 7:18
So, an exception from db.Close() will cause the code to try to rollback a transaction? This is clearly wrong. Move db.Close() outside the try..catch block. — Anton Shepelev
– Anton Shepelev, Commented Jan 11, 2022 at 13:31

KokoriNut · Accepted Answer · 2018-05-21 08:17:22Z

12

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.

answered May 21, 2018 at 8:17

KokoriNut

1951 gold badge3 silver badges11 bronze badges

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

1 Comment

Sabrina_cs Over a year ago

You can always use a Try Catch overall to manage connection error and in Ado.Net calls I usually intercept all exceptions and return an object with both the result data and the error message and all pertaining so that the calling class manages the errors and we avoid the unhandled exceptions from low level classes as Data Provider classes.

William Xifaras · Accepted Answer · 2016-11-12 17:00:06Z

5

You should use one Command and also wrap your connection in a Using block so its properly disposed. Additionally, you should read from tbl_supplier after the transaction has been committed by executing a SqlDataReader. I'm assuming you just wanted to know how many rows were affected after the transaction committed.

Here is a simplified version of your code.

var conn = 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 connection = new SqlConnection(conn))
{
    connection.Open();
    SqlTransaction sqlTran = connection.BeginTransaction();
    SqlCommand command = connection.CreateCommand();
    command.Transaction = sqlTran;

    try
    {
        command.CommandText = SQL1;
        int rowsAffected = command.ExecuteNonQuery();
        command.CommandText = SQL2;
        rowsAffected += command.ExecuteNonQuery();
        transaction.Commit();
    }
    catch (Exception ex1)
    {
        // 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.
        }
    }
}

edited Nov 12, 2016 at 17:00

answered Nov 12, 2016 at 16:20

William Xifaras

5,3622 gold badges22 silver badges22 bronze badges

5 Comments

usr Over a year ago

Delete the rollback as well.

Scott Chamberlain Over a year ago

The transaction really should be in a using block. Also, with the order of your code, I don't think the command will be enlisted in the transaction.

William Xifaras Over a year ago

@ScottChamberlain I agree putting the transaction in a using block would be more elegant. Not sure about your second point. What's wrong with the order? Not following.

William Xifaras Over a year ago

Take a look at the following MSDN. Feel fre to edit my answer. msdn.microsoft.com/en-us/library/2k2hy99x(v=vs.110).aspx

Scott Chamberlain Over a year ago

The main problem was your old code was missing the command.Transaction = sqlTran, adding that fixed the problem.

Collectives™ on Stack Overflow

Using Transaction in ADO.net SQL

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related