将XML插入到数据库中实例

将XML插入到数据库中实例

1 获取xml文件

XmlDocument xDoc = this.GetUploadData();

private XmlDocument GetUploadData()
{
XmlDocument xmlDoc;

HttpPostedFile pf = fileSelect.PostedFile;
if(pf.FileName == "")
{
lb_Message.Text = "请选择上传文件！";
return null;
}
byte[] buffer= new byte[pf.ContentLength];
Stream st = pf.InputStream;
st.Read(buffer,0,pf.ContentLength);

string xmlContent = Encoding.Default.GetString(buffer);

try
{
xmlDoc = new XmlDocument();
xmlDoc.LoadXml(xmlContent);
return xmlDoc;
}
catch
{
return null;
}
}

2 得到各个结点的值

XmlNodeList xmlNL = xDoc.getElementsByTagName_r("recordset");
string ReportType = xmlNL[0].Attributes["ModelCode"].Value.Trim();
string AgentID = Session["_PRCS_USERID"].ToString().Trim();

3 插入数据库

xmlNL = xDoc.getElementsByTagName_r("row");
if (xmlNL.Count > 0)
{
strSql = "";
foreach (XmlNode xn in xmlNL)
{
string ProductLine = xn.Attributes["ProductLine"].Value;
string ProductSeries = xn.Attributes["ProductSeries"].Value;
string ProductModel = xn.Attributes["ProductModel"].Value;
string SInNum = xn.Attributes["SInNum"].Value;
string HalfwayNum = xn.Attributes["HalfwayNum"].Value;
string InvNum = xn.Attributes["InvNum"].Value;
string SOutNum = xn.Attributes["SOutNum"].Value;

strSql += "INSERT INTO [dbo].[RSKPI_UploadDetail]([AgentID], [ReportType], [UploadTime], [ProductLine], [ProductSeries], [ProductModel], [SInNum], [HalfwayNum], [InvNum], [SOutNum]) "+
"VALUES('"+AgentID+"', '"+ReportType+"', getdate(), '"+ProductLine+"', '"+ProductSeries+"', '"+ProductModel+"', "+SInNum+", "+HalfwayNum+", "+InvNum+", "+SOutNum+"); ";
}
SqlHelper.ExecuteNonQuery(dbConn, CommandType.Text, strSql);
this.lb_Message.Text = "数据上报成功！";

源代码：

XmlDocument xDoc = this.GetUploadData();
if (xDoc != null)
{
XmlNodeList xmlNL = xDoc.getElementsByTagName_r("recordset");
string ReportType = xmlNL[0].Attributes["ModelCode"].Value.Trim();
string AgentID = Session["_PRCS_USERID"].ToString().Trim();
string strSql = "select count(*) from dbo.RSKPI_AgentPermission where AgentID='"+AgentID+"' and ReportType='"+ReportType+"'";
if (SqlHelper.ExecuteScalar(dbConn, CommandType.Text, strSql).ToString() != "0")
{
strSql = "select parValue from dbo.RSKPI_Parameter where ParKey='"+ReportType+"'";
string version1 = SqlHelper.ExecuteScalar(dbConn, CommandType.Text, strSql).ToString();
string version2 = xmlNL[0].Attributes["EditionNum"].Value.Trim();
if (version1 == version2)
{
//清除当天的上传
strSql = "delete from RSKPI_UploadDetail where AgentID='"+AgentID+"' and ReportType = '"+ReportType+"' and UploadTime > convert(datetime,convert(varchar(10),getdate(),120)) and UploadTime < convert(datetime,convert(varchar(10),getdate()+1,120))";
SqlHelper.ExecuteNonQuery(dbConn, CommandType.Text, strSql);

xmlNL = xDoc.getElementsByTagName_r("row");
if (xmlNL.Count > 0)
{
strSql = "";
foreach (XmlNode xn in xmlNL)
{
string ProductLine = xn.Attributes["ProductLine"].Value;
string ProductSeries = xn.Attributes["ProductSeries"].Value;
string ProductModel = xn.Attributes["ProductModel"].Value;
string SInNum = xn.Attributes["SInNum"].Value;
string HalfwayNum = xn.Attributes["HalfwayNum"].Value;
string InvNum = xn.Attributes["InvNum"].Value;
string SOutNum = xn.Attributes["SOutNum"].Value;

strSql += "INSERT INTO [dbo].[RSKPI_UploadDetail]([AgentID], [ReportType], [UploadTime], [ProductLine], [ProductSeries], [ProductModel], [SInNum], [HalfwayNum], [InvNum], [SOutNum]) "+
"VALUES('"+AgentID+"', '"+ReportType+"', getdate(), '"+ProductLine+"', '"+ProductSeries+"', '"+ProductModel+"', "+SInNum+", "+HalfwayNum+", "+InvNum+", "+SOutNum+"); ";
}
SqlHelper.ExecuteNonQuery(dbConn, CommandType.Text, strSql);
this.lb_Message.Text = "数据上报成功！";