Jieha Lee

Jieha Lee

  • 1.6k
  • 186
  • 9.1k

How to insert the XML file in DB

Jan 19 2018 3:48 AM
How to insert this XML file in SQL Server? I have done one but cannot output. The error said "String was not recognized as a valid DateTime"
 
Here the code that I done:
  1. SqlDataAdapter adpter = new SqlDataAdapter();  
  2. DataSet ds = new DataSet();  
  3. int appointment_ID = 0;  
  4. string nric = null;  
  5. string client_Name = null;  
  6. DateTime? appointment_Date = null;  
  7. DateTime? start_Time = null;  
  8. SqlConnection conn = new SqlConnection(@"Data Source=USER1-PC\SQLEXPRESS;Integrated Security=true;Database=ProductDB");  
  9. XmlReader xmlFile = XmlReader.Create("G://appointment.xml"new XmlReaderSettings());  
  10. ds.ReadXml(xmlFile);  
  11. int i = 0;  
  12. conn.Open();  
  13. for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)  
  14. {  
  15. appointment_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);  
  16. appointment_Date = DateTime.Parse(ds.Tables[0].Rows[i].ItemArray[1].ToString());  
  17. start_Time = DateTime.Parse(ds.Tables[0].Rows[i].ItemArray[2].ToString());  
  18. nric = ds.Tables[0].Rows[i].ItemArray[3].ToString();  
  19. client_Name = ds.Tables[0].Rows[i].ItemArray[4].ToString();  
  20. SqlCommand command = new SqlCommand("insert into Appointment values(" + appointment_ID + ",'" + appointment_Date + "'," + start_Time + "'," + nric + "'," + client_Name + ")", conn);  
  21. adpter.InsertCommand = command;  
  22. adpter.InsertCommand.ExecuteNonQuery();  
  23. }  
  24. conn.Close();  
  25. MessageBox.Show("Done .. ");  
Here is appointment.xml:
  1. <Interface>  
  2. <Appointment>  
  3. <Appointment_ID> 000000087508</Appointment_ID>  
  4. <Appointment_Date>2018-01-25</Appointment_Date>  
  5. <Appointment_Start_Time>13:15:00</Appointment_Start_Time>  
  6. <Client>  
  7. <NRIC>456987123</NRIC>  
  8. <Client_Name>JAMES</Client_Name>  
  9. </Client>  
  10. </Appointment>  
  11. <Appointment>  
  12. <Appointment_ID>000000087501</Appointment_ID>  
  13. <Appointment_Date>2018-01-25</Appointment_Date>  
  14. <Appointment_Start_Time>14:30:00</Appointment_Start_Time>  
  15. <Client>  
  16. <NRIC>963258741</NRIC>  
  17. <Client_Name>Bill Tan</Client_Name>  
  18. </Client>  
  19. <Client>  
  20. <NRIC>123456789</NRIC>  
  21. <Client_Name>David Ang</Client_Name>  
  22. </Client>  
  23. </Appointment>  
  24. <Checksum>2</Checksum>  
  25. </Interface>

Answers (1)

1
Manav Pandya

Manav Pandya

  • 0
  • 19.9k
  • 2.3m
Jan 19 2018 5:18 AM
Hello
 
I have solved your issue , please find following snippet :
 
  1. public ActionResult Index()  
  2.         {  
  3.             SqlDataAdapter adpter = new SqlDataAdapter();  
  4.             DataSet ds = new DataSet();  
  5.             int appointment_ID = 0;  
  6.             string nric = null;  
  7.             string client_Name = null;  
  8.             DateTime appointment_Date ;  
  9.             DateTime start_Time ;  
  10.             SqlConnection conn = new SqlConnection(@"Data Source=kdpl36\sqlexpress16,2433;Initial Catalog=iWebSquare_DB_TC;User ID=ecmusr;Password=Sql#2703");  
  11.             XmlReader xmlFile = XmlReader.Create("c:/users/manav/documents/visual studio 2013/Projects/XMLInsertToDB/XMLInsertToDB/demo.xml"new XmlReaderSettings());  
  12.             ds.ReadXml(xmlFile);  
  13.             int i = 0;  
  14.             conn.Open();  
  15.             try  
  16.             {  
  17.                 for (i = 0; i <= ds.Tables[1].Rows.Count - 1; i++)  
  18.                 {  
  19.                     appointment_ID = Convert.ToInt32(ds.Tables[1].Rows[i].ItemArray[0]);  
  20.                     appointment_Date = Convert.ToDateTime(ds.Tables[1].Rows[i].ItemArray[1]);  
  21.                     start_Time = Convert.ToDateTime(ds.Tables[1].Rows[i].ItemArray[2]);  
  22.                     nric = ds.Tables[2].Rows[i].ItemArray[0].ToString();  
  23.                     client_Name = ds.Tables[2].Rows[i].ItemArray[1].ToString();  
  24.                     SqlCommand command = new SqlCommand("insert into Appointment(appid,date,starttime,nric,name) values(" + appointment_ID + ",'" + appointment_Date + "','" + start_Time + "','" + nric + "','" + client_Name + "')", conn);  
  25.                     adpter.InsertCommand = command;  
  26.                     adpter.InsertCommand.ExecuteNonQuery();  
  27.                 }  
  28.             }  
  29.             catch(Exception e)  
  30.             {  
  31.                   
  32.             }  
  33.               
  34.             conn.Close();  
  35.             return View();  
  36.         }  
Thanks 
Accepted Answer