In this post we’ll see How To Insert Data From XML File To Database.
XML is a general purpose tag based language and very easy to transfer and store data across applications. The .Net technology is widely supported XML File format. The .Net Framework provides the classes for read, write and other operations in XML formatted files. Moreover the Dataset in ADO.Net uses XML format as internal storage format.
Here we are going to insert the values of XML files to a Database Table using SQL insert command. Here the Dataset is using an XMLReader to read the content of the XML file – Product.xml.
Locate the XML file using XMLReader and pass the XMLReader as argument of Dataset. Also establish a connection to the Database using a connection string. 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 Database.
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Insert Data From XML File to DataBase</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="insert_btn" Text="Insert Data" runat="server" OnClick="insert_btn_Click" /> </div> </form> </body> </html>
Default.axps.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
string str = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;
DataSet ds = new DataSet();
XmlReader xmlfile;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void insert_btn_Click(object sender, EventArgs e)
{
string pid = null;
string pname = null;
string pprice = null;
SqlDataAdapter adpter = new SqlDataAdapter();
con = new SqlConnection(str);
cmd = new SqlCommand();
string query = null;
cmd.Connection = con;
xmlfile = XmlReader.Create("http://localhost:6931/product.xml", new XmlReaderSettings());
ds.ReadXml(xmlfile);
int i = 0;
con.Open();
for (i = 0; i < ds.Tables[0].Rows.Count; i++)
{
pid = ds.Tables[0].Rows[i].ItemArray[0].ToString();
pname = ds.Tables[0].Rows[i].ItemArray[1].ToString();
pprice = ds.Tables[0].Rows[i].ItemArray[2].ToString();
query = "insert into Product values('" + pid + "','" + pname + "','" + pprice + "')";
cmd = new SqlCommand(query, con);
adpter.InsertCommand = cmd;
adpter.InsertCommand.ExecuteNonQuery();
}
con.Close();
Response.Write("<script>alert('Done');</script>");
}
}
Product.xml
<?xml version="1.0" encoding="utf-8" ?> <Table> <Product> <Product_id>1</Product_id> <Product_name>Sample Product 1</Product_name> <Product_price>1000</Product_price> </Product> <Product> <Product_id>2</Product_id> <Product_name>Sample Product 2</Product_name> <Product_price>2000</Product_price> </Product> <Product> <Product_id>3</Product_id> <Product_name>Sample Product 3</Product_name> <Product_price>3000</Product_price> </Product> <Product> <Product_id>4</Product_id> <Product_name>Sample Product 4</Product_name> <Product_price>4000</Product_price> </Product> </Table>
Below are the screen shot related to this project
