Making sure insert happens to multiple tables

Question

I have two tables "EnrolledSubjects" and "StudentAccounts" in SqlServer. Obviously the enrolled subjects should go to the former while their fees should go to the latter. I use the simple Insert Statement for both tables but the problem is, how can I be sure that when one of the tables fail on insert, the other table must not insert anything too. I can use try-catch like so:

try
{
  insert statement to EnrolledSubjects table;
  insert statement to StudentAccounts table;
} catch(Exception ex) {
  get the error here
}

but what if there is no error in inserting to the EnrolledSubjects but error occurs in the insertion to the StudentAccounts? Then that means I would need to delete whatever was inserted to the EnrolledSubjects table.
Can you please tell me how to implement this right according to my specification. Thnnk you.

I'm not a .NET person, but what you are looking for is something called a _transaction_. If an error occurs in either of your inserts, SQL Server would roll back the whole to where it was before it started. So, you should wrap those inserts in a transaction. — Tim Biegeleisen, Jul 05 '16 at 04:32
Thank you Tim. If you have time, can you give me an example on how to do it in sql? — Ibanez1408, Jul 05 '16 at 04:36
Have a look [here](http://stackoverflow.com/questions/10153648/correct-use-of-transactions-in-sql-server-2008). How you would go about doing this from your C# code is another story. — Tim Biegeleisen, Jul 05 '16 at 04:37
`System.Data.SqlClient.SqlCommand` support transactions. `cmd.Transaction = connection.BeginTransaction()` just after opening `try` and `Commit()` at the end and `Rollback()` in `catch` — Alex Kudryashev, Jul 05 '16 at 04:38

Will Ray · Accepted Answer · 2016-07-05T04:42:36.937

4

As @TimBiegeleisen mentioned in the comments, you're going to want a TransactionScope:

try
{
    using (var scope = new TransactionScope())
    {
        // insert statement to EnrolledSubjects table;
        // insert statement to StudentAccounts table;
        scope.Complete(); // don't forget this!
    }
}
catch(Exception ex)
{
    // get error here
}

The transaction will rollback if scope.Complete() has not been called. You will need to add a reference to System.Transactions if your project doesn't have it already.

edited Jul 05 '16 at 04:42

answered Jul 05 '16 at 04:37

Will Ray

10,621
3
46
61

Thank you. I think this will do the job. – Ibanez1408 Jul 05 '16 at 04:39

score 0 · Answer 2 · answered Jul 05 '16 at 05:07

first you check the first insert statement is successfully insert or not if it is successfully than you go for another insertions if second one is also successfully inserted than you make a commit for both.

if they give a errors than roll back the transaction.

Making sure insert happens to multiple tables

2 Answers2