Save XML File Datas To Database

Save XML File Datas To Database

XmlToDatabase. aspx

<asp:Content ID="Content1" ContentPlaceHolderID="cphheader" Runat="Server">

asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="cphcontent" Runat="Server">

<asp:Button ID="btnReader" runat="server" Text="Read" OnClick="btnReader_Click"/>

<asp:GridView ID="GridView1" runat="server">asp:GridView>

<asp:Xml ID="Xml1" runat="server">asp:Xml>

<asp:Label id="Label1" runat="server">asp:Label>

asp:Content>

XmlToDatabase. aspx.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.Data;

using System.IO;

using System.Configuration;

using System.Xml;

using System.Xml.XPath;

using System.Xml.Serialization;

using System.Drawing;

public partial class ADMIN_XmlToDatabase : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnReader_Click(object sender, EventArgs e)

{

try

{

string myXMLfile = Server.MapPath("~/web.xml");

DataSet ds = new DataSet();

System.IO.FileStream fsReadXml = new System.IO.FileStream(myXMLfile, System.IO.FileMode.Open);

ds.ReadXml(fsReadXml);

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

SqlConnection conn = new SqlConnection("Data Source=info;Initial Catalog=info;User ID=info;Password=info");

int RowsCount = ds.Tables[0].Rows.Count;

for (int i = 0; i <= RowsCount -1 ; i++)

{

conn.Open();

SqlCommand cmd = new SqlCommand("SP_Name", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@CODE_ID", ds.Tables[0].Rows[i][0].ToString());

cmd.Parameters.AddWithValue("@NAME_NM", ds.Tables[0].Rows[i][1].ToString());

// cmd.Parameters.AddWithValue("@SKU", ds.Tables[0].Rows[0][3].ToString());

cmd.Parameters.AddWithValue("@CATEGORY", ds.Tables[0].Rows[0][2].ToString());

cmd.Parameters.AddWithValue("@ADDRESS", ds.Tables[0].Rows[i][3].ToString());

cmd.Parameters.AddWithValue("@CITYID", "14");

cmd.Parameters.AddWithValue("@PINCODE", ds.Tables[0].Rows[i][5].ToString()); //@Item_GenericShortCode_cd

cmd.Parameters.AddWithValue("@STATEID", ds.Tables[0].Rows[i][6].ToString());

cmd.Parameters.AddWithValue("@CONTACTPERSON", ds.Tables[0].Rows[i][7].ToString());

cmd.Parameters.AddWithValue("@TELEPHONENUMBER", ds.Tables[0].Rows[i][8].ToString());

cmd.Parameters.AddWithValue("@SITECREATED", DateTime.Now);

cmd.ExecuteNonQuery();

conn.Close();

}

fsReadXml.Close();

}

catch (Exception ex)

{

Response.Write(ex.ToString());

}

finally

{

}

}

Stored Procedure

Alter procedure Site

(

@SITEID int,

@CODE varchar(300),

@NAME varchar(300),

@SITE_SITECATEGORY_TYPE varchar(300),

@ADDRESS varchar(300),

@ CITYID int,

@SITE_SITEPINCODE_CD varchar(300),

@STATEID int,

@CONTACTPERSON varchar(300),

@TELEPHONENUMBERvarchar(300),

@SITE_SITECREATED_DTTM DateTime

)

As

IF EXISTS(SELECT * FROM

Site WHERE

CODE_ID=@CODE_ID)

BEGIN

UPDATE Site

CODE_ID =CODE_ID, NAME = @NAME,

CATEGORY_TYPE = @CATEGORY_TYPE,ADDRESS = @ADDRESS,

CITYID = @CITYID, PINCODE = @SITEPINCODE,

STATEID = @STATEID,CONTACTPERSON = @CONTACTPERSON,

TELEPHONENUMBER = @TELEPHONENUMBER,

CREATED_DTTM = @CREATED_DTTM

WHERE SITEID_ID = @SITEID_ID

END

ELSE

BEGIN

Insert Into Site

(

CODE, NAME, CATEGORY, ADDRESS,

CITYID,PINCODE,STATEID,CONTACTPERSON,

TELEPHONENUMBER, CREATED_DTTM

)

Values

(

@CODE, @NAME_NM, @CATEGORY, @ADDRESS,

@CITYID, @PINCODE, @STATEID,@CONTACTPERSON,

@TELEPHONENUMBER, @CREATED_DTTM

)

END