Monday, February 6, 2012

Insert data into Database from XML file

Data insertion into sqlserver2005 database from XML file using ASP.Net ,C#

This program is for to insert data into database from XML file. Here I have used sqlserver2005 to insert data. Configure the connection part according to your database name and tablename.
1)Create the Product.xml file by following coding.
2)Put the C# code within any event of an ASP.Net page.
<Product_name>Product 1</Product_name>
<Product_name>Product 2</Product_name>
<Product_name>Product 3</Product_name>

insert the namespaces in your ASP.Net file

using System.Xml;
using System.Data.SqlClient;

Put the below code within button’s onClick event of an ASP.Net page
             string connetionString = null;
            SqlConnection connection;
            SqlCommand command ;
            SqlDataAdapter adpter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            XmlReader xmlFile ;
            string sql = null;

            int product_ID = 0;
            string Product_Name = null;
            double product_Price = 0;

            connetionString = "Data Source=(yourservername);Initial Catalog=(databsename);User ID=(username);Password=(password)";

            connection = new SqlConnection(connetionString);

            xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings());
            int i = 0;
            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                product_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
                Product_Name = ds.Tables[0].Rows[i].ItemArray[1].ToString();
                product_Price = Convert.ToDouble(ds.Tables[0].Rows[i].ItemArray[2]);
                sql = "insert into (tablename) values(" + product_ID + ",'" + Product_Name + "'," + product_Price + ")";
                command = new SqlCommand(sql, connection);
                adpter.InsertCommand = command;
         Response.write ("Data Saved");

It will save the Product.xml content into database.Don't forget to create database table according to data type of the Product.xml file. 

No comments:

Post a Comment