Insert XML file data into the database

1. Get the xml file

XmlDocument xDoc = this. GetUploadData ();

 

Private XmlDocument GetUploadData ()
{
XmlDocument xmlDoc;

HttpPostedFile pf = fileSelect. PostedFile;
If (pf. FileName = "")
{
Lb_Message.Text = "Select Upload File! ";
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. Obtain the value of each node.

XmlNodeList xmlNL = xDoc. GetElementsByTagName ("recordset ");
String ReportType = xmlNL [0]. Attributes ["ModelCode"]. Value. Trim ();
String AgentID = Session ["_ PRCS_USERID"]. ToString (). Trim ();

3. Insert a database

 

XmlNL = xDoc. GetElementsByTagName ("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 = "data is reported successfully! ";

 

Source code:

 

XmlDocument xDoc = this. GetUploadData ();
If (xDoc! = Null)
{
XmlNodeList xmlNL = xDoc. GetElementsByTagName ("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)
{
// Clear the upload for the current day
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 ("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 = "data is reported successfully! ";