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

[myNameRon]

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]

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.

 

[jmcilhinney]

Commit will either complete successfully or throw an exception. That's all you have to worry about.

 

[myNameRon]

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]

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]

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]

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]

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.