Master-detail外键插入

Question

有两张桌子。

Customer              Contact
_______________________________________
CustomerId    *-->    CustomerId
CustomerName          ContactId
...                   ContactFirstName
                      ...

One customer can have many contacts

存储过程

CREATE PROCEDURE dbo.InsertCustomers
(
    @CustomerId int,
    @CustomerName nvarchar(50)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerName]) VALUES (@CustomerName);

SELECT CustomerId, CustomerName FROM Customers WHERE (CustomerId = @CustomerId)

第二个：

CREATE PROCEDURE dbo.InsertContacts
(
    @CustomerId int,
    @ContactFirstName nvarchar(20)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Contacts] ([CustomerId], [ContactFirstName]) VALUES (@CustomerId, @ContactFirstName);

SELECT ContactId, CustomerId, ContactFirstName FROM Contacts WHERE (CustomerId = @CustomerId)

使用Linq- to -sql，我尝试将数据从winforms插入到数据库中。在designer中，我为类Customer和类Contact都将插入行为设置为存储过程，但在

_context.SubmitChanges();获取错误

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CustomerContact". The conflict occurred in database "test", table "dbo.Customers", column 'CustomerId'.The statement has been terminated.

我应该编辑我的存储过程还是在代码中处理它？

如何在插入子行时设置Contacts.CustomerId。

Answer 1

您可以使用简单的Linq方法来插入数据，而不是使用存储过程插入数据。

DataClasseseMyDataContext db = new DataClasseseMyDataContext(conString);

db.Customers.InsertOnSubmit(Customers);
tblSupplierPO.tblInvSupplierPOPIDetails.Add(Contacts);

db.SubmitChanges();

您必须已在表之间创建关系并将其添加到DBML