使用C＃将XML导入到SQL中＃

问题描述：

我知道这不是体育需要这种帮助，但我一直坚持这一段时间 - 现在我正在阅读两本C＃书籍，每天工作超过9小时。使用C＃将XML导入到SQL中＃

好的，这是我的问题：我有一个几乎完整的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)

而且我在本地磁盘上的XML文件（S）导入这三个表 - 的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>

我再次尴尬地以这种方式请求帮助，但我会尽力以任何方式支持StackOverflow。

答

多个CDATA元素在实现中并未得到持续支持。例如，您在访问XDocument或通过SelectNodes时会遇到问题。如果你可以改变输入格式，这会让事情变得更容易。

此代码尚未经过测试，没有错误处理或错误的数据检查，但它应该让您开始。调查使用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 
     } 
    }

答

您可以使用XPathNavigator对象来解析XML，然后使用SqlCommand对象将数据插入表中。互联网上的两个对象都有很多代码示例。

使用C＃将XML导入到SQL中＃

相关推荐