C# - How to implement begin trans, commit trans, and rollback in SQL Server

Question

I would like to insert a function in my code which is begin,commit, and rollback trans because the data is prone to errors. my code is this:

clsDBConnect dbConn = new clsDBConnect();
SqlCommand CMD;
SqlDataReader Reader;

private void Save()
{
     CMD = new SqlCommand("SELECT * FROM tblEmployees", dbConn.connection);

     Reader = CMD.ExecuteReader();

     while (Reader.Reader())
     {
          while(variable1 != variable2)
          {
               //Lots of CMD.ExecuteNonQuery();
               //Lots of Reader
               //Lots of computation
               variable1 += 1;
          }
     }
}

How can I rollback inserts and updates when error occurs during the loop?

Thanks!

please check this http://stackoverflow.com/questions/21465040/commit-and-rollback-in-c-sharp — imsome1, Feb 15 '17 at 06:02
Have you done any research???? https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction(v=vs.110).aspx — monstertjie_za, Feb 15 '17 at 06:02
I suspect you might want to reconsider your design. Without knowing the specific details, inserts and updates inside a loop automatically seems like a code smell. — Zohar Peled, Feb 15 '17 at 06:16

score 0 · Accepted Answer · answered Feb 15 '17 at 06:23

Transaction handling is done in a way like this:

DbTransaction trans = dbConn.BeginTransaction();
try
{
    // ... do your stuff...

    // Last line of the try-block... if you are still alive here, commit
    trans.Commit();
}
catch (Exception e)
{
     // Something evil happened... Rollback
     trans.Rollback();
}

C# - How to implement begin trans, commit trans, and rollback in SQL Server

1 Answers1