使用C#将XML导入SQL [英] Import XML to SQL using C#
问题描述
我知道寻求这种帮助并不是运动， 但是我已经坚持了一段时间了-现在我正在阅读两本C#书，每天工作超过9个小时.
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.
好的，这是我的问题: 我有一个几乎完整的WinForms C#应用程序. 在SQL中，我有三个看起来像这样的表:
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:
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)
我在本地磁盘上有XML文件，可用于导入这三个表- XML看起来像这样:
And I have XML file(s) on local disk for importing these three tables - the XML looks like this:
<?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>
再一次让我感到尴尬，以这种方式请求帮助，但是我将尽一切可能尝试支持StackOverflow.
Once again I am embarassed to request assistance in this way, but I'll try to suport StackOverflow in any way I can.
推荐答案
在各个实现中未始终支持多个CDATA元素.例如，您将无法通过XDocument或通过SelectNodes访问它们.如果您可以更改输入格式，那将使事情变得更容易.
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.
此代码尚未经过测试，没有错误处理或错误的数据检查，但它可以帮助您入门.使用XPathDocument/XPathNavigator进行性能研究，并阅读我的嵌入式注释.
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.
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
}
}
这篇关于使用C#将XML导入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
