使用 C# 将 XML 导入 SQL

问题描述

我知道寻求这种帮助不是体育运动，但是我真的坚持了一段时间 - 现在我正在阅读两本 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 或通过 SelectNode 访问它们时会遇到问题.如果您可以更改输入格式，那会使事情变得更容易.

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
        }
    }