SqlBulkCopy - 将 XML 数据导入 SQL 表 [英] SqlBulkCopy - Importing XML data into SQL table

问题描述

我在使用 SqlBulkCopy 导入 XML 文件时遇到问题.XML 文件如下所示:

I'm having proplems with importing an XML file using SqlBulkCopy. The XML file looks like this:

   <root>
    <Automobiles>
        <Car Id="1" Name="SomeModel1"/>
        <Car Id="2" Name="SomeModel2"/>
        <Car Id="3" Name="SomeModel2"/>
    </Automobiles>
   </root>

我的桌子是这样的:

Int         Id
varchar    Name

这是我的代码:

DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("autolist.xml"));

SqlConnection connection = new SqlConnection(
    ConfigurationManager.ConnectionStrings["Connection"].ToString());
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = "Automobiles";

foreach (DataColumn dc in ds.Tables[0].Columns)
{
    sbc.ColumnMappings.Add(dc.Caption, dc.Caption);
}

connection.Open();

sbc.WriteToServer(ds.Tables[0]);

connection.Close();

我只能导入 Id 而不能导入 Name，因为它的属性和 DataSet 只包含一列.有没有办法将属性映射到列?

I could only import the Id but not Name because its attribute and the DataSet contains only one column. Is there any way to map the attributes to columns?

也许使用 XmlDocument 而不是 DataSet 更好?

Maybe its better to use XmlDocument instead of DataSet?

我认为我有什么问题.当我在汽车元素周围有一些根元素并且数据集表中缺少列时，数据集完全不同.它在没有根元素的情况下运行良好.

I think i what is wrong. Dataset is quite different when i have some root element around Automobiles element and columns are missing in Dataset tables. It works well without root element.

推荐答案

我一般不喜欢使用 DataSet 将 XML 数据导入我的数据库；DataSet 类有一种非常特殊的方式来将其数据结构化为 XML，反之，读取其他 XML.

I generally don't like using a DataSet to import XML data into my database; the DataSet class has a very specific way of structuring its data as XML and conversely, reading other XML.

也就是说，我更喜欢调用 WriteToServer 重载 采用 IDataReader 实施.

That said, I prefer call the WriteToServer overload that takes an IDataReader implementation.

您可以轻松创建一个扩展方法，该方法采用 IEnumerable<T> 实现并将其映射到 IDataReader;基本上，您通过名称和索引将类的属性映射到表上的字段，其中索引也是 Expression 您为 T 创建的实例(编译为 lambda)T 的实例并返回属性值.

You can easily create an extension method that takes an IEnumerable<T> implementation and maps it to an IDataReader; basically, you map the properties of the class to the fields on the table by name and index where the index is also an index into an array of Expression instances (compiled to lambdas) that you create for T which takes the instance of T and returns the property value.

您通常会映射 IDataReader.Read 实现到 IEnumerable.MoveNext 方法.

You'd generally map the IDataReader.Read implementation to IEnumerable<T>.MoveNext method.

一旦你有了这样的东西，你可以将你的 XML 映射到一个镜像表的轻量级数据对象，然后将它们的序列传递给一个扩展方法，该方法将序列映射到 IDataReader 实施.

Once you have something like this in place, you can map your XML to a lightwieght data object which mirrors the table and then pass the sequence of them into an extension method which maps the sequence to an IDataReader implementation.

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