Insert record in multiple tables with Single query

Question

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 8-Mar-11 23:23pm

Aman4.net

Add a Solution

2 solutions

Solution 2

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[^]

Posted 8-Mar-11 23:36pm

Wild-Programmer

Comments

Albin Abel 9-Mar-11 5:57am

For LinQ method this is correct. My 5

Wild-Programmer 10-Mar-11 5:12am

Thankx Albin ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Albin Abel · Accepted Answer · 2011-03-08T23:36:00

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[^]