Resolved WinForm - SQL transaction.Commit() waiting to complete?

myNameRon

New member
Joined
Jan 9, 2021
Messages
3
Programming Experience
Beginner
Creating a WinForm (C#.NET 4.6). I believe this is more of a C# coding question than a SQL question?

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
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,451
Location
Sydney, Australia
Programming Experience
10+
My code is looping through rows of a DataTable, to save data to the database.
Firstly, why would you do that when you can save the lot with a single call to Update on a data adapter? The RowState of each DataRow must be set appropriately for them to be saved but that may already be the case and, if it's not, it won't be hard to remedy.
 

myNameRon

New member
Joined
Jan 9, 2021
Messages
3
Programming Experience
Beginner
Thanks jmcilhinney, I realize the Commit will complete successfully or throw an exception, but was just wondering if the "next line of code" was going to execute before Commit had a chance to fully complete.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,416
Location
Chesapeake, VA
Programming Experience
10+
No it will not. Commit() will block until completion.

Out of curiosity, why would you think that the next line would start executing? What have you learned or have been reading that makes you think code is executed in a non linear manner?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,451
Location
Sydney, Australia
Programming Experience
10+
You seem to be asking whether Commit is an asynchronous method, i.e. whether it will return before the database operation has completed. It is not and I've never seen anything to suggest that it is. As I said, Commit will either return successfully or throw an exception and that is all you have to worry about. If the transaction fails to commit then an exception will be thrown and, obviously, that has to happen before the method returns.
 

myNameRon

New member
Joined
Jan 9, 2021
Messages
3
Programming Experience
Beginner
I didn't know for sure (I assumed it would wait until completion), but wanted to make sure. Didn't want to clear/delete data objects (i.e. DataTable, or a DataRow) that were needed for the "database operation" to complete fully.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,451
Location
Sydney, Australia
Programming Experience
10+
Didn't want to clear/delete data objects (i.e. DataTable, or a DataRow) that were needed for the "database operation" to complete fully.
That couldn't possibly be the case. Even if the transaction was committed asynchronously, all the data would already have been sent to the database before you even called Commit. The database doesn't work directly on the data in your app. That data is sent to the database first and it works on it locally.
 
Top Bottom