SQL transaction.Commit() in WinForm

Question

Hi,
Not sure if this is the correct forum. I believe this is more of a C# question, than a MS-SQL question.

Creating a WinForm (C#.NET 4.6).

I'm saving data to a SQL Server database. My C# code is using "transaction.Commit()", so I can perform a "transaction.Rollback()" if there's an issue. My code is looping through rows of a DataTable, to save data to the database. After I call "transaction.Commit()", I clear the DataTable. I'm wondering if the process is waiting for "transaction.Commit()" to fully complete, before the line of code (dataTable.Clear()) is executed? As you can imagine, I don't want to clear the DataTable until the SQL process has fully completed.

thanks
Ron

Answer 1

Hi Ron,

I had started writing a reply to you yesterday and this morning, but my computer crashed and I lost the whole reply. And of course, I hadn't saved anything! Grrr.

Anyway, as @Daniel Zhang-MSFT said, your code will work just fine (the transaction.Commit() will complete before the myDT_Clients.Clear() statement executes).

If you recall, I had mentioned the use of TransactionScope instead of SqlTransaction in my comment. It will make your code a bit cleaner (in my opinion). Here is what your code would look like if you used TransactionScope:

See my blogpost for the code for the static GetTransactionScope() in a Utils class and the reason why I've done it this way instead of just new TransactionScope()
https://geek-goddess-bonnie.blogspot.com/2010/12/transactionscope-and-sqlserver.html

using (TransactionScope scope = Utils.GetTransactionScope())  
using (SqlConnection connection = new SqlConnection(dbConnectionString)) // you can "stack" usings like this  
{  
    try  
    {  
        connection.Open();  
  
        SqlCommand command = connection.CreateCommand();  
        command.Connection = connection;  
        // won't need these now  
        // transaction = connection.BeginTransaction();        
        // command.Transaction = transaction;  
  
        // This can go outside the loop.  
        command.CommandText = "UPDATE Clients SET Name=@Name WHERE ClientID=@ClientID)";                        
          
        foreach (DataRow drow in myDT_Clients.Rows)  
        {  
            command.Parameters.Add("@Name", SqlDbType.VarChar).Value = drow["Name"];  
            command.Parameters.Add("@ClientID", SqlDbType.BigInt).Value = drow["clientID"];  
  
            command.ExecuteNonQuery();                          
            command.Parameters.Clear();  
        }  
  
        //transaction.Commit();  
        scope.Complete();  
        myDT_Clients.Clear();  
    }  
    catch (Exception ex)  
    {  
        MessageBox.Show(ex.Message, "Error!");  
  
        // You won't need the try/catch or the Rollback.  
        // If the TransactionScope using block can't execute with an exception   
        // the transaction is automatically rolled back. In fact there is no  
        // Rollback or similar command in TransactionScope, because it's not needed.  
          
        //try  
        //{  
        //    transaction.Rollback();  
        //}  
        //catch (Exception ex2)  
        //{  
        //    MessageBox.Show(ex2.Message, "Rollback Error!");  
        //}  
    }  
}  

Give it a try if you'd like.

Answer 2

Thanks Daniel and Bonnie!
It sucks that you can't "Accept Answer" from two posts.

Answer 3

Hi RonFunnell-4095,
You can create a new SqlTransaction object on each iteration or move the transaction completely outside the loop if you want all the operations inside the loop to occur in a single transaction.
Base on your code, you put the transaction.commit outside the loop.
So it will commit all operations in the loop and then clear the datatable.
I also made a test to check it and it did as I said.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

The transaction.commit is outside of the loop. The transaction.commit is called at the end of the try/catch (just before the DataTable.Clear:

NOTE: The code same below has been simplified.

SqlTransaction transaction = null;  
            try
            {
                using (SqlConnection connection = new SqlConnection(dbConnectionString))
                {
                    connection.Open();

                    SqlCommand command = connection.CreateCommand();
                    transaction = connection.BeginTransaction();                   
                    command.Connection = connection;
                    command.Transaction = transaction;

                    foreach (DataRow drow in myDT_Clients.Rows)
                    {
                        command.CommandText = "UPDATE Clients SET Name=@Name WHERE ClientID=@ClientID)";                      
                        command.Parameters.Add("@Name", SqlDbType.VarChar).Value = drow["Name"];
                        command.Parameters.Add("@ClientID", SqlDbType.BigInt).Value = drow["clientID"];

                        command.ExecuteNonQuery();                        
                        command.Parameters.Clear();
                    }

                    transaction.Commit();
                    myDT_Clients.Clear();
                }                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error!");

                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    MessageBox.Show(ex2.Message, "Rollback Error!");
                }
            }