C# 将数据集写入 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/549200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write Dataset to SQL Table
提问by MartGriff
I have converted a complex XML File to a Dataset that has 7 Tables and around 70 Columns I have created the tables in SQL Server to match the ones in the Dataset using the XML Schema. How can I easily write my Dataset to the SQL tables?
我已将复杂的 XML 文件转换为具有 7 个表和大约 70 个列的数据集我在 SQL Server 中创建了表以使用 XML 架构匹配数据集中的表。如何轻松地将数据集写入 SQL 表？
回答by Ian P
If you're using .NET 3/3.5, you could look into LINQ.
如果您使用 .NET 3/3.5，您可以查看 LINQ。
回答by leppie
You probably want to look at DataAdapters and TypedDatasets.
您可能想查看 DataAdapters 和 TypedDatasets。
These are all available via the VS designer, simply create a dataset, and drop the SQL tables in from the server explorer view. :) (or something like that, it's been a while)
这些都可以通过 VS 设计器获得，只需创建一个数据集，然后从服务器资源管理器视图中放入 SQL 表。:)（或类似的东西，已经有一段时间了）
回答by Bryan Slatner
Depending on how many rows you have in the DataSet, probably the best thing to do would be to use a SqlCommandBuilder, like so:
根据您在数据集中有多少行，可能最好的做法是使用 SqlCommandBuilder，如下所示：
var connection = new SqlConnection("my connection string");
connection.Open();
// repeat for each table in data set
var adapterForTable1 = new SqlDataAdapter("select * from table1", connection);
var builderForTable1 = new SqlCommandBuilder(adapterForTable1);
adapterForTable1.Update(myDataSet, "Table1");
If you have complex relationships defined between the tables in the DataSet, I'm afraid you can't use the SqlCommandBuilder. What you'll need to do, instead, is define a data adapter for each table in your DataSet. Then, update the tables in the DataSet in dependency order (i.e., do the tables with no dependencies first, then the dependent tables).
如果你在DataSet中的表之间定义了复杂的关系，恐怕你不能使用SqlCommandBuilder。相反，您需要做的是为 DataSet 中的每个表定义一个数据适配器。然后，按照依赖顺序更新DataSet中的表（即先做没有依赖的表，然后是依赖表）。
Here's an example of a parent/child insert (note that you would do similar things for updates). Table1 is the parent, and has ParentId (the identity column), and an NVARCHAR field ParentValue. Table2 is the child, has its own identity column (ChildId), the foreign key field (ParentId), and its own value (ChildValue).
这是父/子插入的示例（请注意，您将对更新执行类似的操作）。Table1 是父级，具有 ParentId（标识列）和一个 NVARCHAR 字段 ParentValue。Table2 是子表，有自己的标识列 (ChildId)、外键字段 (ParentId) 和自己的值 (ChildValue)。
var myDataSet = new DataSet();
// ** details of populating the dataset omitted **
// create a foreign key relationship between Table1 and Table2.
// add a constraint to Table2's ParentId column, indicating it must
// existing in Table1.
var fk = new ForeignKeyConstraint("fk", myDataSet.Tables["Table1"].Columns["ParentId"], myDataSet.Tables["Table2"].Columns["ParentId"])
{
DeleteRule = Rule.Cascade,
UpdateRule = Rule.Cascade
};
myDataSet.Tables["Table2"].Constraints.Add(fk);
myDataSet.EnforceConstraints = true;
var connection = new SqlConnection("my connection string");
var adapterForTable1 = new SqlDataAdapter();
adapterForTable1.InsertCommand =
new SqlCommand("INSERT INTO MasterTable (ParentValue) VALUES (@ParentValue); SELECT SCOPE_IDENTITY() AS ParentId", connection);
adapterForTable1.InsertCommand.Parameters.Add("@ParentValue", SqlDbType.NVarChar).SourceColumn = "ParentValue";
var adapterForTable2 = new SqlDataAdapter();
adapterForTable2.InsertCommand =
new SqlCommand("INSERT INTO ChildTable (ParentId, ChildValue) VALUES (@ParentId, @ChildValue); SELECT SCOPE_IDENTITY() AS ChildId", connection);
adapterForTable2.InsertCommand.Parameters.Add("@ParentId", SqlDbType.Int).SourceColumn = "ParentId";
adapterForTable2.InsertCommand.Parameters.Add("@ChildValue", SqlDbType.NVarChar).SourceColumn = "ChildValue";
connection.Open();
adapterForTable1.Update(myDataSet, "Table1"); // insert rows in parent first
adapterForTable2.Update(myDataSet, "Table2"); // child second
回答by MichaelGG
If the DataTables and SQL Tables line up, then a fast way is SqlBulkCopy.
如果 DataTables 和 SQL Tables对齐，那么一个快速的方法是SqlBulkCopy。
