使用c#(3.5)在sql数据库中插入复杂的xml信息
本文关键字：插入 复杂 信息 xml 数据库 sql 使用 | 更新日期: 2023-09-27 17:51:11
我一直在网上搜索，找不到一个直接的答案。我开始这个项目在工作中使用c#(Visual Studio 2005)和xml。我想在sql数据库中插入这个复杂xml文件中的信息。下面是我的代码:
这是我从
读取的XML文件<?xml version="1.0" encoding="UTF-8"?>
<queryConfirmation>
<submitter>
<entityDBID>800000000000001</entityDBID>
<vendorID>1111514</vendorID>
</submitter>
<submissionFilename>test.xml</submissionFilename>
<certification>
<name>JOE SMITH</name>
<title>SUPERVISOR</title>
<phone>
<number>1234567890</number>
<extension>11</extension>
</phone>
<date>2008-02-26</date>
</certification>
<batchStatus>
<dcn>2000000000387584</dcn>
<processDate>2014-03-13</processDate>
<successfullyProcessed>false</successfullyProcessed>
<error>
<code>09</code>
<message>09: This entity does not have the privilege to perform this transaction.</message>
</error>
</batchStatus>
</queryConfirmation>
这是我目前的代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using System.Data.SqlClient;
namespace QRXSprocessPrototype2
{
public partial class Form1 : Form
{
SqlConnection DRDB = new SqlConnection("Data Source=MA_DEV1;Initial Catalog=QRXS;Integrated Security=True");
SqlDataAdapter DA = new SqlDataAdapter();
DataSet DS = new DataSet("response");
public string entityDBID;
public string vendorID;
public string submissionFilename;
public string name;
public string title;
public string phoneNumber;
public string phoneExtension;
DateTime certificationDate;
public string dcn;
DateTime processDate;
public string successfullyProcessed;
public string code;
public string message;
public Form1()
{
InitializeComponent();
}
private void button3_Click(object sender, EventArgs e)
{
XmlDocument doc = new XmlDocument();
doc.Load(@"C:'QRXS'download'14000000000000251943.xml");
XmlElement xelRoot = doc.DocumentElement;
XmlNodeList submitterNodes = xelRoot.SelectNodes("/queryConfirmation/submitter");
XmlNodeList subFileNameNode = xelRoot.SelectNodes("/queryConfirmation");
XmlNodeList certNode = xelRoot.SelectNodes("/queryConfirmation/certification");
XmlNodeList certPhoneNode = xelRoot.SelectNodes("/queryConfirmation/certification/phone");
XmlNodeList batchNodeList = xelRoot.SelectNodes("/queryConfirmation/batchStatus");
XmlNodeList bsErrorList = xelRoot.SelectNodes("/queryConfirmation/error");
DRDB.Open();
foreach (XmlNode xndNode in submitterNodes)
{
entityDBID = xndNode["entityDBID"].InnerText;
vendorID = xndNode["vendorID"].InnerText;
foreach (XmlNode submisFilenameNode in rootNodes)
{
submissionFilename = submisFilenameNode["submissionFilename"].InnerText;
//I get a nullreferenceexception on this line
}
foreach (XmlNode cfn in certNode)
{
name = cfn["name"].InnerText;
title = cfn["title"].InnerText;
certificationDate = DateTime.Parse(cfn["date"].Value.ToString());
}
foreach (XmlNode cfnp in certPhoneNode)
{
phoneNumber = cfnp["number"].Value;
phoneExtension = cfnp["extension"].Value;
}
foreach (XmlNode bsNode in batchNodeList)
{
dcn = xndNode["dcn"].InnerText;
processDate = DateTime.Parse(bsNode["processDate"].Value.ToString());
successfullyProcessed = bsNode["successfullyProcessed"].InnerText;
}
foreach (XmlNode bsError in bsErrorList)
{
code = bsError["code"].Value;
message = bsError["message"].InnerText;
}
DA.InsertCommand = new SqlCommand("INSERT INTO response VALUES (" + entityDBID + ",'" + vendorID + "'," + submissionFilename + "'," + name + "'," + title + "'," + phoneNumber + "'," + phoneExtension + "'." + certificationDate + "'." + dcn + "'." + processDate + "'." + successfullyProcessed + "'." + code + "'." + message + ")", DRDB);
}
DRDB.Close();
}
}
}
这不是代码的最终形式。我只是想知道我在哪里做错了循环插入信息到数据库中。谢谢你的帮助。
天哪，我觉得自己像个白痴，没有把我的代码检查一遍。我离这个项目太近了。
private void button3_Click(object sender, EventArgs e)
{
XmlDocument doc = new XmlDocument();
doc.Load(@"C:'QRXS'download'14000000000000251943.xml");
XmlElement xelRoot = doc.DocumentElement;
XmlNodeList rootNodes = xelRoot.SelectNodes("/queryConfirmation");
XmlNodeList submitterNodes = xelRoot.SelectNodes("/queryConfirmation/submitter");
XmlNodeList certNode = xelRoot.SelectNodes("/queryConfirmation/certification");
XmlNodeList certPhoneNode = xelRoot.SelectNodes("/queryConfirmation/certification/phone");
XmlNodeList batchNodeList = xelRoot.SelectNodes("/queryConfirmation/batchStatus");
XmlNodeList bsErrorList = xelRoot.SelectNodes("/queryConfirmation/batchStatus/error");
foreach (XmlNode xndNode in submitterNodes)
{
entityDBID = xndNode["entityDBID"].InnerText;
vendorID = xndNode["vendorID"].InnerText;
foreach (XmlNode submisFilenameNode in rootNodes)
{
submissionFilename = submisFilenameNode["submissionFilename"].InnerText;
}
foreach (XmlNode cfn in certNode)
{
name = cfn["name"].InnerText;
title = cfn["title"].InnerText;
certificationDate = cfn["date"].InnerText;
}
foreach (XmlNode cfnp in certPhoneNode)
{
phoneNumber = cfnp["number"].InnerText;
phoneExtension = cfnp["extension"].InnerText;
}
foreach (XmlNode bsNode in batchNodeList)
{
dcn = bsNode["dcn"].InnerText;
processDate = bsNode["processDate"].InnerText;
successfullyProcessed = bsNode["successfullyProcessed"].InnerText;
}
foreach (XmlNode bsError in bsErrorList)
{
code = bsError["code"].InnerText;
message = bsError["message"].InnerText;
}
try
{
DA.InsertCommand = new SqlCommand("INSERT INTO response VALUES (@entityDBID, @vendorID, @submissionFilename, @fullName, @title, @number, @extension, @certificationDate, @dcn, @processDate, @successfullyProcessed, @code, @message);", DRDB);
DA.InsertCommand.Parameters.AddWithValue("@entityDBID", SqlDbType.NVarChar).Value = entityDBID;
DA.InsertCommand.Parameters.AddWithValue("@vendorID", SqlDbType.NVarChar).Value = vendorID;
DA.InsertCommand.Parameters.AddWithValue("@submissionFilename", SqlDbType.NVarChar).Value = submissionFilename;
DA.InsertCommand.Parameters.AddWithValue("@fullName", SqlDbType.NVarChar).Value = name;
DA.InsertCommand.Parameters.AddWithValue("@title", SqlDbType.NVarChar).Value = title;
DA.InsertCommand.Parameters.AddWithValue("@number", SqlDbType.NVarChar).Value = phoneNumber;
DA.InsertCommand.Parameters.AddWithValue("@extension", SqlDbType.NVarChar).Value = phoneExtension;
DA.InsertCommand.Parameters.AddWithValue("@certificationDate", SqlDbType.NVarChar).Value = certificationDate;
DA.InsertCommand.Parameters.AddWithValue("@dcn", SqlDbType.NVarChar).Value = dcn;
DA.InsertCommand.Parameters.AddWithValue("@processDate", SqlDbType.NVarChar).Value = processDate;
DA.InsertCommand.Parameters.AddWithValue("@successfullyProcessed", SqlDbType.NVarChar).Value = successfullyProcessed;
DA.InsertCommand.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = code;
DA.InsertCommand.Parameters.AddWithValue("@message", SqlDbType.NVarChar).Value = message;
DRDB.Open();
DA.InsertCommand.ExecuteNonQuery();
DRDB.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}