将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 = "数据上报成功!";