Click here to Skip to main content
14,929,225 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi All,

I am developing web application in asp.net by using LINQ. I am looking for solution to insert records in multiple tables with single query.

E.g. I have three tables which has relations with each other.

TableA
ColAA (Primary Key)
ColAB
ColAC Foreign Key with C);

TableB
ColBA (Primary Key)
ColBB
ColBC(Foreign Key with A);

TableC
ColCA (Primary Key)
ColCB
ColCC(Foreign Key with B);

While, I will insert record in TableA in the mean time I have to insert records in TableB and TableC. I do not want to make three trip of database server with code or execute three queries in single stored procedure

What is the Best policy to do it efficiently?

Regards!
Aman
Posted

I am curious how the database know which table has to inserted first if it is a single query, in order to maintain the constraint rules.

What you need to do is pack your queries in a Transaction. it allows you to roll back if any query fails.

Stored procedure is a good way to avoid trips

Have a look at here how to roll back in a stored procedure, in case of errors.

http://www.eggheadcafe.com/tutorials/aspnet/6a8ef7d5-840e-4629-b53a-1a40e7db601f/using-try--catch-to-roll.aspx[^]
   
Do all inserts but call the SubmitChanges() statement at the end.

Refer below link:

http://stackoverflow.com/questions/287594/insert-into-multiple-database-tables-using-linq-asp-net-mvc[^]
   
Comments
Albin Abel 9-Mar-11 5:57am
   
For LinQ method this is correct. My 5
Amit Kumar Tiwari 10-Mar-11 5:12am
   
Thankx Albin ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900