将具有多个相关表的DataSet批量插入SQL Server C＃ [英] Bulk Insert DataSet with multiple related tables into SQL Server C#

问题描述

我有一个包含多个表的数据集，源文档是XML文件。

I have a DataSet with multiple tables the source document is an XML file.

mydataset.ReadXML（filename）;

有多个具有多个关系的表。我有通过代码在SQL中创建的数据库-表，列和关系。

There are multiple tables with multiple relations. I have the database in the SQL created via code - tables, columns and relations.

现在我想插入数据。 （是的，我想要一切）。
编辑： SQL表使用身份列自动生成-因为我不确定传入数据将不会重复我想假设的唯一参数之一。

Now I would like to insert the data . (Yes I want everything). The SQL tables use Identity Columns autogenerate - because I am not sure the incoming data will never duplicate one of the parameters that I would like to assume is unique.

那么考虑到我有外键约束，我应该采取什么方法来确保输入数据，应该如何迭代数据集中的表以确保没有尝试插入需要现有ID的表中。我应该创建一个硬编码的地图（我还是不太喜欢），还是在表中查找外键并验证父表等。

So what methods should I do to ensure data is input considering I have foreign key constraints , how should I iterate the the tables in the dataset to make sure I don't try to insert in tables requiring an existing id. Do I create a hard coded map (I prefer not too) , or do I walk the tables looking for a Foreign key and verify the parent table etc..

任何想法？我确信有人以前做过，并且对我来说很简单。

Any Ideas ? I am sure someone has done this before and has a simple answer for me.

推荐答案

您有两种选择。假设数据库未生成键值，这非常简单。

You have a couple of options. Assuming the database is not generating the key values, this is pretty simple. Either

1）您发现了加载表的顺序，以便每个带有外键的表在其引用的表之后 加载。 。

1) You discover the order to load the tables so that each table with a Foreign Key is loaded after the table to which it refers.

2）您可以在 SqlBulkCopy ，然后在加载后有选择地检查约束。

2) You turn off constraint checking in SqlBulkCopy, and then optionally check the constraints after loading.

要在加载后检查约束，请运行以下命令

To check the constraints after load, run a command like

alter table SomeTable with check check constraint fk_SomeTable_OtherTable

如果您确实有数据库生成的密钥，这将更加困难。但是解决此问题的最佳方法是使用IDENTITY列中的SEQUENCE对象，然后运行 sp_sequence_get_range 即可将新的密钥范围提取到客户端，然后首先在客户端应用密钥。

If you do have database-generated keys, this is all harder. But the best way to tackle that is to use SEQUENCE objects isntead of IDENTITY columns and run sp_sequence_get_range to fetch the new key ranges to the client and apply the keys there first.

这篇关于将具有多个相关表的DataSet批量插入SQL Server C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！