問題描述
我知道尋求這種幫助不是體育運動，但是我真的堅持了一段時間 - 現在我正在閱讀兩本 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
}
}
這篇關于使用 C# 將 XML 導入 SQL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！