使用 C# 将 XML 导入 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/772946/
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
Import XML to SQL using C#
提问by adopilot
I know it's not sporting asking for this kind of help, But I've been really stuck on this for a while - right now I am reading two C# books and working everyday over 9 hours.
我知道寻求这种帮助并不是出于体育目的，但我真的坚持了一段时间 - 现在我正在阅读两本 C# 书籍并且每天工作超过 9 个小时。
Okay here is my problem: I have a WinForms C# application that is almost complete. In SQL I have three tables that look like this:
好的，这是我的问题：我有一个几乎完整的 WinForms C# 应用程序。在 SQL 中，我有三个表，如下所示：
CREATE TABLE [dbo].[Racuni](
[BROJ] [varchar](12) NULL,
[DATUM] [datetime] NULL,
[TS] [datetime] NULL,
[USER_ID] [int] NULL,
[KASA_ID] [varchar](3) NULL,
[TOTAL] [float] NULL,
[STATUS] [varchar](1) NULL,
[ARH] [varchar](max) NULL
) ON [PRIMARY]
Create Table "Rac_Npl"
( br_rac Char( 12 )
, kasa_id Char( 3 )
, npl_id Integer
, iznos Money);
CREATE TABLE [dbo].[Stavke](
[br_rac] [varchar](12) NULL,
[kasa_id] [char](3) NULL,
[art_id] [int] NULL,
[kol] [float] NULL,
[mpc] [money] NULL,
[ompc] [money] NULL)
And I have XML file(s) on local disk for importing these three tables - the XML looks like this:
我在本地磁盘上有用于导入这三个表的 XML 文件 - XML 如下所示：
<?xml version="1.0" encoding="windows-1250"?>
<transaction>
<table name="qryRacuniSmjene">
<fields>
<field name="BROJ" type="1" size="12"/>
<field name="DATUM" type="9" size="0"/>
<field name="TS" type="11" size="0"/>
<field name="USER_ID" type="3" size="0"/>
<field name="KASA_ID" type="1" size="3"/>
<field name="TOTAL" type="8" size="4"/>
<field name="STATUS" type="1" size="1"/>
<field name="ARH" type="16" size="1"/>
</fields>
<data>
<row>
<![CDATA[09-0002-0001]]>
<![CDATA[16.04.2009]]>
<![CDATA[16.04.2009 13:23:27]]>
<![CDATA[1]]>
<![CDATA[001]]>
<![CDATA[2,60]]>
<![CDATA[D]]>
<![CDATA[
porezni broj: 000000000000
Zaobilaznica bb
]]>
</row>
<row>
<![CDATA[09-0002-0002]]>
<![CDATA[16.04.2009]]>
<![CDATA[16.04.2009 13:23:27]]>
<![CDATA[1]]>
<![CDATA[001]]>
<![CDATA[2,60]]>
<![CDATA[D]]>
<![CDATA[
porezni broj: 000000000001
Zaobilaznica bb
]]>
</row>
</data>
</table>
<table name="qryRac_nplSmjene">
<fields>
<field name="br_rac" type="1" size="12"/>
<field name="kasa_id" type="1" size="3"/>
<field name="npl_id" type="3" size="0"/>
<field name="iznos" type="8" size="4"/>
</fields>
<data>
<row>
<![CDATA[09-0002-0001]]>
<![CDATA[001]]>
<![CDATA[1]]>
<![CDATA[2,60]]>
</row>
<row>
<![CDATA[09-0002-0002]]>
<![CDATA[001]]>
<![CDATA[1]]>
<![CDATA[2,60]]>
</row>
</data>
</table>
<table name="qryStavkeSmjene">
<fields>
<field name="br_rac" type="1" size="12"/>
<field name="kasa_id" type="1" size="3"/>
<field name="art_id" type="3" size="0"/>
<field name="kol" type="6" size="0"/>
<field name="mpc" type="8" size="4"/>
<field name="ompc" type="8" size="4"/>
</fields>
<data>
<row>
<![CDATA[09-0002-0001]]>
<![CDATA[001]]>
<![CDATA[152414]]>
<![CDATA[1,000]]>
<![CDATA[2,60]]>
<![CDATA[2,60]]>
</row>
<row>
<![CDATA[09-0002-0001]]>
<![CDATA[001]]>
<![CDATA[152414]]>
<![CDATA[1,000]]>
<![CDATA[2,60]]>
<![CDATA[2,60]]>
</row>
</data>
</table>
</transaction>
Once again I am embarassed to request assistance in this way, but I'll try to suport StackOverflow in any way I can.
我再次不好意思以这种方式请求帮助，但我会尽我所能支持 StackOverflow。
采纳答案by Richard Szalay
Multiple CDATA elements are not consistantly supported across implementations. For example, you will have problems accessing them an XDocument or via SelectNodes. If you can change the input format that would make things easier.
跨实现并不一致地支持多个 CDATA 元素。例如，您在通过 XDocument 或通过 SelectNode 访问它们时会遇到问题。如果您可以更改输入格式，那将使事情变得更容易。
This code hasn't been tested and there's no error handling or bad data checking, but it should get you started. Investigate using XPathDocument / XPathNavigator for performance and read my inline comments.
此代码尚未经过测试，也没有错误处理或错误数据检查，但它应该可以帮助您入门。使用 XPathDocument / XPathNavigator 调查性能并阅读我的内嵌注释。
class XmlCsvImport
{
public void ImportData(string xmlData, ConnectionStringSettings connectionSettings)
{
DbProviderFactory providerFactory = DbProviderFactories.GetFactory(connectionSettings.ProviderName);
IDbConnection connection = providerFactory.CreateConnection();
connection.ConnectionString = connectionSettings.ConnectionString;
// TODO: Begin transaction
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlData);
foreach (XmlNode tableNode in doc.SelectNodes("/transaction/table"))
{
IDbCommand command = CreatCommand(connection, tableNode);
foreach (XmlNode rowNode in tableNode.SelectNodes("data/row"))
{
string[] values = GetRowValues(rowNode);
if (values.Length != command.Parameters.Count)
{
// TODO: Log bad row
continue;
}
this.FillCommand(command, values);
command.ExecuteNonQuery();
}
}
// TODO: Commit transaction
}
private IDbCommand CreatCommand(IDbConnection connection, XmlNode tableNode)
{
string tableName = tableNode.Attributes["name"].Value;
IDbCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
XmlNodeList fieldNodes = tableNode.SelectNodes("fields/field");
List<string> fieldNameList = new List<string>(fieldNodes.Count);
foreach (XmlNode fieldNode in tableNode.SelectNodes("fields/field"))
{
string fieldName = fieldNode.Attributes["name"].Value;
int fieldType = Int32.Parse(fieldNode.Attributes["type"].Value);
int fieldSize = Int32.Parse(fieldNode.Attributes["size"].Value);
IDbDataParameter param = command.CreateParameter();
param.ParameterName = String.Concat("@", fieldNode.Attributes["name"]);
param.Size = fieldSize;
param.DbType = (DbType)fieldType; // NOTE: this may not be so easy
command.Parameters.Add(param);
fieldNameList.Add(fieldName);
}
string[] fieldNames = fieldNameList.ToArray();
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.AppendFormat("INSERT INTO [{0}] (", tableName);
string columnNames = String.Join("], [", fieldNames);
string paramNames = String.Join(", @", fieldNames);
command.CommandText = String.Concat(
"INSERT INTO [", tableName, "] ([",
columnNames,
"]) VALUES (@",
paramNames,
")"
);
return command;
}
private string[] GetRowValues(XmlNode row)
{
List<string> values = new List<string>();
foreach (XmlNode child in row.ChildNodes)
{
if (child.NodeType == XmlNodeType.Text ||
child.NodeType == XmlNodeType.CDATA)
{
values.Add(child.Value);
}
}
return values.ToArray();
}
private void FillCommand(IDbCommand command, string[] values)
{
for (int i = 0; i < values.Length; i++)
{
IDbDataParameter param = (IDbDataParameter)command.Parameters[i];
param.Value = values[i]; // TODO: Convert to correct data type
}
}
回答by John M Gant
You can use an XPathNavigator object to parse the XML, and then use an SqlCommand object to insert the data into the tables. There are lots of code samples for both objects on the Internet.
您可以使用 XPathNavigator 对象来解析 XML，然后使用 SqlCommand 对象将数据插入表中。互联网上有很多这两个对象的代码示例。
