使用单个查询在多个表中插入记录 [英] Insert record in multiple tables with Single query

问题描述

大家好，

我正在使用LINQ在asp.net中开发Web应用程序.我正在寻找一种解决方案，以单个查询在多个表中插入记录.

例如.我有三个相互关联的表.

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);

同时，我将在同时必须在TableB和TableC中插入记录的同时在TableA中插入记录.我不想使用代码进行数据库服务器的三趟旅行，也不想在单个存储过程中执行三个查询

有效执行此操作的最佳策略是什么?

问候！
Aman

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

推荐答案

我很好奇数据库如何知道如果是单个查询必须首先插入哪个表才能维护约束规则.

您需要做的是将查询打包在Transaction中.如果有任何查询失败，它可以使您回滚.

存储过程是避免绊倒的好方法

看看这里如何在发生错误的情况下回滚存储过程.

http://www.eggheadcafe .com/tutorials/aspnet/6a8ef7d5-840e-4629-b53a-1a40e7db601f/using-try--catch-to-roll.aspx [

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

执行所有插入操作，但最后调用SubmitChanges()语句.

请参考以下链接:

http://stackoverflow.com/questions/287594/insert-into-multiple-database-tables-using-linq-asp-net-mvc [

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

这篇关于使用单个查询在多个表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！