Store
Procedure is nothing but we can pass query in Sql through Store Procedure in
Code behind in Asp.Net when we want data from Database. Here we will create xml
store procedure and pass data from Xml file through Store Procedure in dot net.
Here we will See When we Upload Xml File to FileUpload Control and Store
Procedure through Xml File Pass Data from Sql and Store in Database and for See
Bind Gridview Control so user can see easily. So let’s see how to achieve this
functionality.
Step 1: Create table in
Database
CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
[ZipCode] bigint NOT NULL,
[Country] [varchar](50) NOT NULL,
[State] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
CONSTRAINT
[PK_CustomerDetails] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Step 2: Design your
Webpage like below.
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" Text="Upload XML" runat="server" OnClick="UploadXML" />
</div>
<br />
<div>
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal" Width="703px">
<FooterStyle BackColor="#CCCC99" ForeColor="Black" />
<HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#4B4B4B" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#242121" />
</asp:GridView>
</div>
Step 3: For Connection we need Connection
String here we will Create in Web.Config file and call in Webpage.
Web.Config
<connectionStrings>
<add name="DBCS" connectionString="Data
Source=UMESH-PC\SQLEXPRESS;Initial Catalog=UH;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Now call Connection String in your Webpage.
string CS
= ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
Step 4: Create XML
File.
<?xml version="1.0" standalone="yes"?>
<Customers>
<Customer Id ="1">
<Name>Umesh Patel</Name>
<Email>Umesh@gmail.com</Email>
<ZipCode>365420</ZipCode>
<Country>India</Country>
<State>Gujrat</State>
<City>Ahmedabad</City>
</Customer>
<Customer Id = "2">
<Name>Chirag Patel</Name>
<Email>Chirag@gmail.com</Email>
<ZipCode>235689</ZipCode>
<Country>India</Country>
<State>Gujrat</State>
<City>Rajkot</City>
</Customer>
<Customer Id ="3">
<Name>Mathews</Name>
<Email>Mathews@gmail.com</Email>
<ZipCode>235432</ZipCode>
<Country>France</Country>
<State>Peris</State>
<City>Peris</City>
</Customer>
<Customer Id ="4">
<Name>Robert</Name>
<Email>Robert@gmail.com</Email>
<ZipCode>545686</ZipCode>
<Country>Russia</Country>
<State>Jerushlem</State>
<City>Jerushlem</City>
</Customer>
</Customers>
Step 5: Now Generate Upload button click
event and Create Upload File Code and Store in Server Folder.
protected void UploadXML(object
sender, EventArgs e)
{
string
fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string
filePath = Server.MapPath("~/Uploads/")
+ fileName;
FileUpload1.SaveAs(filePath);
string
xml = File.ReadAllText(filePath);
string
constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using
(SqlConnection con = new SqlConnection(constr))
{
using
(SqlCommand cmd = new
SqlCommand("InsertXML"))
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xml",
xml);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
bind();
}
}
}
Step 6: Here you can see I have passed xml
Parameter in Store Procedure. Create Store Procedure in Sql so when we pass
data through file upload control then we have to store in database so we need
Store Procedure.
CREATE PROCEDURE [dbo].[InsertXML]
@xml XML
AS
BEGIN
SET NOCOUNT
ON;
INSERT INTO
CustomerDetails
SELECT
Customer.value('@Id','INT') AS Id, --ATTRIBUTE
Customer.value('(Name/text())[1]','VARCHAR(100)') AS Name, --TAG
Customer.value('(Email/text())[1]','NVARCHAR(100)') AS Email, --TAG
Customer.value('(ZipCode/text())[1]','bigint') AS ZipCode, --TAG
Customer.value('(Country/text())[1]','VARCHAR(100)') AS Country, --TAG
Customer.value('(State/text())[1]','VARCHAR(100)') AS State, --TAG
Customer.value('(City/text())[1]','VARCHAR(100)') AS City --TAG
FROM
@xml.nodes('/Customers/Customer')AS TEMPTABLE(Customer)
END
Step 7: Now bind Gridview from database so
when we upload xml file and store data in database then we can see data in
Gridview directly.
public void bind()
{
using
(SqlConnection con = new SqlConnection(CS))
{
con.Open();
SqlCommand
cmd = new SqlCommand("Select * from CustomerDetails", con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataTable
dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
Step 8: Call this bind () method in
Page_load () event so when we call Page first time then only call this method
we don’t have to call Postback Page.
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
bind();
}
}
Step 9: Now check in
your browser and upload Xml File and see output.
Thanks for comments.....