CODEWITHSUNDEEP
c#sqlsql-server
Jeebwise
Jeebwise

Reputation: 177

SQL INSERT for multiple tables

I can't figure out the wording to do a proper Google search. I am writing a program in C# that writes to several tables at once(in sequence) in SQL Server. The way I have it setup right now is I have different methods created that work on inserting for each table. They all use the sql command/parameter structure. And then when submit is clicked all of these methods are called and do there thing. Should I be using a different way to insert into all the tables like store procedures? My real main question - how do I fail all of the inserts if one fails. Right now it has the unfortuate issue of continuing on and no way of undoing.

Upvotes: 0

Views: 746

Answers (3)

rudi bruchez
rudi bruchez

Reputation: 642

You should encapsulate your INSERTs into a transaction. The bad way to do it is to use a TransactionScope in ADO.NET, the good way is to write a stored procedure and BEGIN and COMMIT/ROLLBACK your transaction inside you proc. You don't want to go back and forth form client to server while maintaing a transaction, because you will hurt concurreny and performance (exclusive locks are hold on the resources inserted until the transaction ends).

Here is a pseudo code example of T-SQL transaction management:

BEGIN TRAN
BEGIN TRY
    INSERT
    INSERT
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE() -- you can use THROW in SQL Server 2012 to retrhrow the error
    ROLLBACK
END CATCH

Upvotes: 3

Dr.Elch
Dr.Elch

Reputation: 2225

You are looking for database transactions. They can be either done in SQL or in C#.

Have a closer look at this question!

Upvotes: 1

GBoehm
GBoehm

Reputation: 183

You can use Transactions look for IDbTransaction or TransactionScope for example. in Stored Procs it would be easier to let the server decide if a rollback is needed or not.(FailureCode) Yeah go with storedprocs it will be faster because of lesser overhead. Transactions are possible either way

Upvotes: 0

Related Questions