User Name:


User Email:




This information will only be saved for the purposes of communicating with those who have provided this information voluntarily regarding our services.We will never sell your name or email address to anyone.
© 2018 - First Crazy Developer (Abhishek Kumar)
  

crazydeveloper Insert data from XML to database

Today someone asked me, how to insert data from XML to Database?

Now we are going to insert the values of an XML file to a Database Table using SQL Insert Command . Here the Dataset using an XmlReader for read the content of the XML file - SampleProduct.XML . Locate the XML file using XmlReader and pass the XmlReader as argument of Dataset. Also establish a connection to the Database using a connectionstring . After getting the data from XML file to the Dataset , we can loop through the dataset values and use insert command to add the values to the Product table in the Databse.

Look the following code:


Through ADO.Net with DataSet :

 1: using System;
 2: using System.Collections.Generic;
 3: using System.Data;
 4: using System.Data.SqlClient;
 5: using System.Linq;
 6: using System.Web;
 7: using System.Web.UI;
 8: using System.Web.UI.WebControls;
 9: using System.Xml;
 10:  
 11: public void createXMLFile()
 12:         {
 13:             System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 14:             System.Xml.XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
 15:             doc.AppendChild(docNode);
 16:             System.Xml.XmlNode productsNode = doc.CreateElement("guest");
 17:             doc.AppendChild(productsNode);
 18:  
 19:             System.Xml.XmlNode productNode = doc.CreateElement("product");
 20:             System.Xml.XmlAttribute productAttribute = doc.CreateAttribute("product_ID");
 21:             productAttribute.Value = "1001";
 22:             productNode.Attributes.Append(productAttribute);
 23:             productsNode.AppendChild(productNode);
 24:  
 25:             System.Xml.XmlNode nameNode = doc.CreateElement("Product_Name");
 26:             nameNode.AppendChild(doc.CreateTextNode("Sample Products"));
 27:             productNode.AppendChild(nameNode);
 28:  
 29:             System.Xml.XmlNode priceNode = doc.CreateElement("product_Price");
 30:             priceNode.AppendChild(doc.CreateTextNode("500"));
 31:             productNode.AppendChild(priceNode);
 32:             doc.Save(Server.MapPath("guest.xml"));
 33:             
 34:             //Response.Write("Guest details have saved into " + Server.MapPath("guest.xml"));
 35:         }
 36:         public void insertDataIntoDatabasefromXML()
 37:         {
 38:             string connetionString = null;
 39:             SqlConnection connection;
 40:             SqlCommand command;
 41:             SqlDataAdapter adpter = new SqlDataAdapter();
 42:             DataSet ds = new DataSet();
 43:             XmlReader xmlFile;
 44:             string cmd = null;
 45:  
 46:             string product_ID =null;
 47:             string Product_Name = null;
 48:             double product_Price = 0;
 49:  
 50:             connetionString = "Data Source=LENOVO-PC\\SQLEXPRESS;Initial Catalog=SampleDatabase;Integrated Security=true";
 51:  
 52:             connection = new SqlConnection(connetionString);
 53:  
 54:             xmlFile = XmlReader.Create(Server.MapPath("guest.xml"), new XmlReaderSettings());
 55:             ds.ReadXml(xmlFile);
 56:             int i = 0;
 57:             connection.Open();
 58:             for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
 59:             {
 60:                 product_ID = ds.Tables[0].Rows[i]["product_ID"].ToString();
 61:                 Product_Name = ds.Tables[0].Rows[i]["Product_Name"].ToString();
 62:                 product_Price = Convert.ToDouble(ds.Tables[0].Rows[i]["product_Price"]);
 63:                 cmd = "insert into product values(@product_ID,@Product_Name,@product_Price)";
 64:                 command = new SqlCommand();
 65:                 command.Parameters.AddWithValue("@product_ID", Server.HtmlEncode(product_ID.ToString()));
 66:                 command.Parameters.AddWithValue("@Product_Name", Server.HtmlEncode(Product_Name.ToString()));
 67:                 command.Parameters.AddWithValue("@product_Price", float.Parse(product_Price.ToString()));
 68:                 command.CommandText = cmd;
 69:                 command.Connection = connection;
 70:                 command.CommandType = CommandType.Text;
 71:                 command.ExecuteNonQuery();
 72:             }
 73:             connection.Close();
 74:         }
 75:  
 76:         public void updateDataIntoDatabasefromXML()
 77:         {
 78:             string connetionString = null;
 79:             SqlConnection connection;
 80:             SqlCommand command;
 81:             SqlDataAdapter adpter = new SqlDataAdapter();
 82:             DataSet ds = new DataSet();
 83:             XmlReader xmlFile;
 84:             string sql = null;
 85:  
 86:             string product_ID = null;
 87:             string Product_Name = null;
 88:             double product_Price = 0;
 89:  
 90:             connetionString = "Data Source=LENOVO-PC\\SQLEXPRESS;Initial Catalog=SampleDatabase;Integrated Security=true";
 91:  
 92:             connection = new SqlConnection(connetionString);
 93:  
 94:             xmlFile = XmlReader.Create(Server.MapPath("guest.xml"), new XmlReaderSettings());
 95:             ds.ReadXml(xmlFile);
 96:             int i = 0;
 97:             connection.Open();
 98:             for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
 99:             {
 100:                 product_ID = ds.Tables[0].Rows[i]["product_ID"].ToString();
 101:                 Product_Name = ds.Tables[0].Rows[i]["Product_Name"].ToString();
 102:                 product_Price = Convert.ToDouble(ds.Tables[0].Rows[i]["product_Price"]);
 103:                 string cmd = @"update product set [email protected]_Name,[email protected]_Price";
 104:                 cmd += " where [email protected]_ID";
 105:                 command = new SqlCommand();
 106:                 command.Parameters.AddWithValue("@product_ID", Server.HtmlEncode(product_ID.ToString()));
 107:                 command.Parameters.AddWithValue("@Product_Name", Server.HtmlEncode(Product_Name.ToString()));
 108:                 command.Parameters.AddWithValue("@product_Price", float.Parse(product_Price.ToString()));
 109:                 command.CommandText = cmd;
 110:                 command.Connection = connection;
 111:                 command.CommandType = CommandType.Text;
 112:                 command.ExecuteNonQuery();
 113:                 
 114:             }
 115:             connection.Close();
 116:         }

Through XmlSerialization


 1: public class Guest
 2: {
 3: [XmlArray(ElementName="Sample Products")]
 4: public virtual List Sample_Products {get; set;}
 5: }
 6: public class Product
 7: {
 8: [Key]
 9: [XmlAttribute]
 10: public int product_ID { get; set; }
 11: [XmlElement]
 12: public String product_Name { get;set; }
 13: [XmlElement]
 14: public int product_Price { get;set; }
 15: }
 16:  
 17: public class Test
 18: {
 19: public void createXMLFile()
 20: {
 21: Guest g = new Guest()
 22: {
 23: Sample_Products = new List()
 24: {
 25: new Product() { product_ID = 1001, product_Name = "Sample Product",product_Price= 500 }
 26: }
 27: };
 28:  
 29: XmlSerializer s = new XmlSerializer(typeof(Guest));
 30: using (System.IO.Stream fs = System.IO.File.OpenWrite(Server.MapPath("guest.xml")))
 31: {
 32: s.Serialize(fs,g);
 33: }
 34:  
 35: }
 36:  
 37: public void insertDataIntoDatabasefromXML()
 38: {
 39: Guest g;
 40: XmlSerializer s = new XmlSerializer(typeof(Guest));
 41: using (System.IO.Stream fs = System.IO.File.OpenRead(Server.MapPath("guest.xml")))
 42: {
 43: g = (Guest)s.Deserialize(fs);
 44: }
 45:  
 46: using(Context db = new Context())
 47: {
 48: g.Sample_Products.ForEach(x=>db.Products.Add(x))
 49: db.SaveChanges();
 50: }
 51: }

crazydeveloper Home Page 21 June 2015

Become a Fan