将数据集写入SQL表

我已经将一个复杂的XML文件转换为一个数据集,该数据集有7个表和大约70个列,我在SQL Server中创建了表,以使用XML Schema匹配数据集中的表.如何轻松地将我的数据集写入SQL表？

根据DataSet中有多少行,可能最好的方法是使用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");

如果您在DataSet中的表之间定义了复杂的关系,我恐怕您无法使用SqlCommandBuilder.相反,您需要做的是为DataSet中的每个表定义数据适配器.然后,按依赖顺序更新DataSet中的表(即,首先执行没有依赖关系的表,然后是依赖表).

这是一个父/子插入的示例(请注意,您将为更新执行类似的操作).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