将数据集写入SQL表 [英] Write Dataset to SQL Table

问题描述

我已经将一个复杂的XML文件转换为具有7个表和大约70列的数据集我已经在SQL Server中创建了使用XML模式的数据集中的表。如何轻松地将数据集写入SQL表？

解决方案

根据DataSet中有多少行，大概是最好的办法是使用SqlCommandBuilder，如下所示：

  var connection = new SqlConnection（my connection string） ; 
 connection.Open（）; 
 
 //对数据集中的每个表重复
 var adapterForTable1 = new SqlDataAdapter（select * from table1，connection）; 
 var builderForTable1 = new SqlCommandBuilder（adapterForTable1）; 
 adapterForTable1.Update（myDataSet，Table1）; 
  

如果您在DataSet中的表之间定义了复杂的关系，恐怕你不能使用SqlCommandBuilder。相反，您需要做的是为DataSet中的每个表定义一个数据适配器。然后按照依次顺序更新DataSet中的表（即先行不依赖的表，然后依赖表）。

以下是父/子插入的示例（请注意，您将对更新做类似的事情）。 Table1是父级，并具有ParentId（标识列）和一个NVARCHAR字段ParentValue。 Table2是child，有自己的标识列（ChildId），外键字段（ParentId）和它自己的值（ChildValue）。

  var myDataSet = new DataSet（）; 
 
 // **填充数据集的详细信息省略** 
 
 //创建Table1和Table2之间的外键关系。 
 //向Table2的ParentId列添加一个约束，表示它必须是
 //存在于Table1中。 
 var fk = new ForeignKeyConstraint（fk，myDataSet.Tables [Table1]。列[ParentId]，myDataSet.Tables [Table2]。列[ParentId]）
 {
 DeleteRule = Rule.Cascade，
 UpdateRule = Rule.Cascade 
}; 
 myDataSet.Tables [Table2]。Constraints.Add（fk）; 
 myDataSet.EnforceConstraints = true; 
 
 var connection = new SqlConnection（我的连接字符串）; 
 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）; //在父元首插入行
 adapterForTable2.Update（myDataSet，Table2）; // child second 
  

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?

解决方案

Depending on how many rows you have in the DataSet, probably the best thing to do would be to use a SqlCommandBuilder, like so:

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

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

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

这篇关于将数据集写入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！