V24s

"Get Updates : Subscribe to our e-mail newsletter to receive updates........" "Good Luck frnds" Admission 2018 Application Form 2018 Colleges Entrance Exam 2018 Results 2018 Notification 2018 University Educational Jobs 2018 Government Jobs 2018

Search This Blog v24s guys

Follow by Email

04 April, 2013

Pass XML as Parameter from C# to SQL Stored Procedure in VB.NET

Introduction:

Here I will explain how to pass or send xml file as a parameter to SQL stored procedure in asp.net usingC# and VB.NET.

Description:

In previous articles I explained read xml node values and bind data to gridview, how to insert and read data from xml in asp.net, Create online poll system with percentage graphs, Bind xml data to dropdownlist/gridview in asp.net . Now I will explain how to pass or send xml file as a parameter to SQLstored procedure in asp.net using C# and VB.NET.

In situation I got requirement like read data from xml file and send that xml file as parameter to store procedure. My XML File Name as “Sample.xml” and that would contains data like this

<?xml version="1.0" encoding="utf-8" ?>
<users>
          <user>
                   <FirstName>Suresh</FirstName>
                   <LastName>Dasari</LastName>
                   <UserName>SureshDasari</UserName>
                   <Job>Team Leader</Job>
          </user>
          <user>
                   <FirstName>Mahesh</FirstName>
                   <LastName>Dasari</LastName>
                   <UserName>MaheshDasari</UserName>
                   <Job>Software Developer</Job>
          </user>
          <user>
                   <FirstName>Madhav</FirstName>
                   <LastName>Yemineni</LastName>
                   <UserName>MadhavYemineni</UserName>
                   <Job>Business Analyst</Job>
          </user>
</users>
Now I need to send this xml file as parameter to stored procedure for that first create xml file in your application (Right Click your application and select Add New Item >> Select XML file) and give name as “Sample.xml” and write following code in your aspx page like this 


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Send xml file as a parameter to SQL stored procedure in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSend" Text="Send XML to Database" runat="server" onclick="btnSend_Click" /><br /><br />
<b>Inserted Records Details</b> :
<asp:GridView ID="gvDetails" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
After that add following namespaces in codebehind

C# Code


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

Now add following code in code behind


protected void btnSend_Click(object sender, EventArgs e)
{
XmlTextReader xmlreader = new XmlTextReader(Server.MapPath("Sample.xml"));
DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
if (ds.Tables.Count != 0)
{
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("prc_readxmldata", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@XMLdata", SqlDbType.Xml).Value = ds.GetXml();
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds1=new DataSet();
da.Fill(ds1);
gvDetails.DataSource = ds1;
gvDetails.DataBind();
con.Close();
}
}
}
VB.NET Code


Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

End Sub
Protected Sub btnSend_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim xmlreader As New XmlTextReader(Server.MapPath("Sample.xml"))
Dim ds As New DataSet()
ds.ReadXml(xmlreader)
xmlreader.Close()
If ds.Tables.Count <> 0 Then
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("prc_readxmldata", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@XMLdata", SqlDbType.Xml).Value = ds.GetXml()
Dim da As New SqlDataAdapter(cmd)
Dim ds1 As New DataSet()
da.Fill(ds1)
gvDetails.DataSource = ds1
gvDetails.DataBind()
con.Close()
End Using
End If
End Sub
End Class



In above code I mentioned stored procedure “prc_readxmldata” now we need to create that stored procedure in database like as shown below


CREATE PROCEDURE prc_readxmldata
(
@XMLdata XML
)
AS
BEGIN
SELECT
t.value('(FirstName/text())[1]','nvarchar(120)')AS FirstName ,
t.value('(LastName/text())[1]','nvarchar(120)')AS LastName,
t.value('(UserName/text())[1]','nvarchar(120)')AS UserName,
t.value('(Job/text())[1]','nvarchar(120)')AS Job
FROM
@XMLdata.nodes('/users/user')AS TempTable(t)
END
Here @XMLdata.nodes('/users/user') this will select sub nodes inside of nodes.

Once procedure created in your database run your application and check your output

Demo



Post a Comment

Popular Posts

Recent Posts

Google Analytics