SqlBulkCopy的 - 导入XML数据转换成SQL表 [英] SqlBulkCopy - Importing XML data into SQL table

问题描述

我在使用 SqlBulkCopy的导入XML文件proplems。该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>

我的表是这样的：

My table looks like this:

Int         Id
varchar    Name

下面是我的code：

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();

我只能导入编号而不是名字，因为它的属性和数据集只包含一列。有没有什么办法来映射属性列？

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 而不是数据集？

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.

推荐答案

我一般不喜欢使用的 数据集 以XML数据导入到我的数据库;在数据集类有构建其作为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.

这是说，我preFER致电 在WriteToServer 重载接受一个的的IDataReader > <$ C $ 实施。

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

您可以轻松地创建一个扩展方法，该方法的 IEnumerable的＆LT; T＆GT; 实现，它映射到一个的IDataReader ;基本上，映射的类的名字和索引在表中的字段的属性，其中指数也是一个指数到的 防爆pression 实例（编译到lambda表达式）创建的 T 这需要 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＆LT; T＆GT;。.MoveNext 方法

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

一旦你有这样的事情之后，就可以将XML映射到这反映了表，然后将它们传递的顺序为它映射序列到 IDataReader的扩展方法lightwieght数据对象的实施。

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屋！