Check SQL success

Question

I am writing an application in C# (For the first time). I have a user inputting a lot of data in forms and I am inserting this data in to the database. It is a Windows Forms Application using SQL Server.

After a form is submitted by the user multiple tables are updated. (Around 6) I am trying to avoid a situation where 3 tables are updated and the 4th throws an error and I'm not sure how to do this. All of the data will be the correct data type so my question is more on the database connection end.

So, ideally, if all 6 inserts aren't successful I would like none of them to be successful. I know I can do a delete record in the catch clause to roll back. Is this the most efficient way or is there a "best practice" when it comes to this?

Would you care to share some code? It's hard to make suggestions based solely on a lose explanation. — SBI, May 13 '14 at 13:47
Now that I'm thinking about it, I can't do a delete to roll back if there is a connection problem so I would need a solution :) — user3632719, May 13 '14 at 13:47
I'm looking for high level here. Like, are there any functions for this purpose? I wanted to figure this out before I sat down and wrote something but I am starting now so if I don't get an answer before I have something I will surely post it. — user3632719, May 13 '14 at 13:48
start a transaction, do all six inserts within the transaction; if there's no exceptions so far - commit the transaction, otherwise rollback it — Dmitry Bychenko, May 13 '14 at 13:49
John Saunders, this is exactly what I was looking for. Transactions! Thank you!! — user3632719, May 13 '14 at 13:49
It is strange that you need to write a so complex database code and you have never heard about Transactions http://stackoverflow.com/questions/494550/how-does-transactionscope-roll-back-transactions — Steve, May 13 '14 at 13:50
Steve it is very strange but I am still doing it. Thanks for input :) — user3632719, May 13 '14 at 13:50

score 0 · Accepted Answer · edited May 23 '17 at 12:27

Its a little hard without having your current code but in general you can use the TransactionScope class for this.

for example

using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection connection1 = new SqlConnection(connectString1))
        {
            Action1(connection1);
            Action2(connection1);
            Action3(connection1);
            Action4(connection1);
            Action5(connection1);
            Action6(connection1);
        }

        // The Complete method commits the transaction. If an exception has been thrown, 
        // Complete is not  called and the transaction is rolled back.
        scope.Complete();

    }

Now as mentioned in the comments this is not the most performance optimal solution, but will probably work well for most mid sized projects.

you can also use Entity framework for your data access.

also see Transactions in EF on stackoverflow

Exactly what I was looking for. Thanks Patrick, John, and Dmitry! — user3632719, May 13 '14 at 14:00
This will work but needs to be parsed every time by the database. Also, all C# business logic changes will cause a redistribution of code. — CRAFTY DBA, May 13 '14 at 14:04

score 0 · Answer 2 · answered May 13 '14 at 14:03

You can definitely send ad-hoc TSQL statements; However, they have to be parsed each time and compiled if a cache plan is not matched.

Why not use a stored procedure using a table valued parameter (record set), transactions, and error handling?

The advantage here is that it can be tested as a separate unit of code and does not get parsed/recompiled every time.

This small amount of time adds up if you have tons of calls.

Also, if business rules change, the Stored Procedure changes, not the C# program. This is assuming that the input remains the same.

Enclosed are links to pieces of the puzzle. The rest is up to you.

-- Begin Transaction
http://technet.microsoft.com/en-us/library/ms188929.aspx

-- Try/Catch
http://technet.microsoft.com/en-us/library/ms175976.aspx

-- Commit transaction
http://technet.microsoft.com/en-us/library/ms190295.aspx

-- Roll back transaction
http://technet.microsoft.com/en-us/library/ms181299.aspx

-- Table Variable Parameter w/ADO.NET call
http://technet.microsoft.com/en-us/library/bb510489.aspx

score 0 · Answer 3 · answered May 13 '14 at 14:09

The best way is to create a SQL Server stored Procedure that gets all the inputs you wish to save and manage the inserts using commit transaction.

Example (part of the SP)

BEGIN TRY BEGIN TRANSACTION;

        -- Do your inserts here i,e
        --INSERT INTO TABLE1 (COL1,COL2) VALUES ('Val1','Val2')

        COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

--PRINT ERROR_PROCEDURE()
--PRINT ERROR_NUMBER()
--PRINT ERROR_LINE()
--PRINT ERROR_MESSAGE()

--INSERT INTO ERRORLOG (PROGMODULE,ERRORNO,ERRORLINENO,ERROR,ErrorState,ERRORDATE,ERRMAKER)
--VALUES
--(ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_LINE(),ERROR_MESSAGE(),ERROR_STATE(),GETDATE(),'CUSTOM DESCRIPTION');

--or you can return the error to your application

END CATCH

I would argue that the "best way" would not involve stored procedures at all. — Matthew, May 13 '14 at 14:11

Check SQL success

3 Answers3