In this article I am going to explain how to
insert xml data into SQL using c#.
Description
In this article I take "Data Table" and that "Data Table" convert into xml and that xml insert into SQL table using a Stored Procedure.
Firstly I created a table in SQL
Description
In this article I take "Data Table" and that "Data Table" convert into xml and that xml insert into SQL table using a Stored Procedure.
Firstly I created a table in SQL
|
CREATE
TABLE Employee
(
EmpId
INT identity(1,
1),
NAME
VARCHAR(50),
MobileNo VARCHAR(13)
)
|
Now Code In C# to create a "Data Table" that return 5 rows.
|
//Create a DataTable that retuen 5 rows
private
DataTable GetTable()
{
DataTable dt =
new
DataTable();
dt.Columns.Add("NAME");
dt.Columns.Add("MobileNo");
for (int
i = 0; i < 5; i++)
{
DataRow rr = dt.NewRow();
rr["NAME"]
= "Sharad" + i.ToString();
rr["MobileNo"]
= "99787878788" + i.ToString();
dt.Rows.Add(rr);
}
return dt;
}
|
Now I code to convert "Data Table" into "XML".
|
//Convert DataTable to XML
public string
ConvertDataTableToXMLDataString(DataTable
dataTbl)
{
StringBuilder XMLString =
new
StringBuilder();
if (string.IsNullOrEmpty(dataTbl.TableName))
dataTbl.TableName
= "DataTable";
XMLString.AppendFormat("<{0}>",
dataTbl.TableName);
DataColumnCollection tableColumns =
dataTbl.Columns;
foreach (DataRow
row in dataTbl.Rows)
{
XMLString.AppendFormat("<NewDataSet>");
foreach (DataColumn
column in tableColumns)
{
XMLString.AppendFormat("<{1}>{0}</{1}>",
row[column].ToString(), column.ColumnName);
}
XMLString.AppendFormat("</NewDataSet>");
}
XMLString.AppendFormat("</{0}>",
dataTbl.TableName);
return XMLString.ToString();
}
|
Now I code to Save Xml Data in SQL table, for it I write a stored
procedure.
|
CREATE
PROC InsXmldata
(@xml_data
VARCHAR(MAX)
= '')
AS
BEGIN
DECLARE @XMLDocHandle
INT
IF @xml_data <>
''
BEGIN
EXEC
sp_xml_preparedocument
@XMLDocHandle OUTPUT,
@xml_data
INSERT
INTO Employee
(
NAME,
MobileNo
)
SELECT
NULLIF(NAME,
''),
NULLIF(MobileNo,
'')
FROM
OPENXML(@XMLDocHandle,
N'/Employee/NewDataSet',
2) WITH
(
NAME
VARCHAR(50),
MobileNo
VARCHAR(13)
)
EXEC
sp_xml_removedocument
@XMLDocHandle
END
--- @xml_data END
END
|
Now I code to Save Xml Data into Database (You can write
the given below code on button click).
|
DataTable
dt = GetTable();
dt.TableName
= "Employee";
string XmlDoc =
ConvertDataTableToXMLDataString(dt);
SqlConnection con =
new
SqlConnection("data
source=.;Database=test;uid=sa;pwd=*************;");
con.Open();
SqlCommand cmd =
new
SqlCommand("InsXmldata", con);
cmd.Parameters.AddWithValue("@xml_data",
XmlDoc);
cmd.CommandType = CommandType.StoredProcedure;
int i =cmd.ExecuteNonQuery();
|
Full aspx.cs code
|
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data;
using
System.Data.SqlClient;
using
System.Text;
public
partial class
_Default : System.Web.UI.Page
{
protected void
Page_Load(object sender,
EventArgs e)
{
}
//Create a DataTable that
retuen 5 rows
private
DataTable GetTable()
{
DataTable dt =
new
DataTable();
dt.Columns.Add("NAME");
dt.Columns.Add("MobileNo");
for (int
i = 0; i < 5; i++)
{
DataRow rr = dt.NewRow();
rr["NAME"]
= "Sharad" + i.ToString();
rr["MobileNo"]
= "99787878788" + i.ToString();
dt.Rows.Add(rr);
}
return dt;
}
//Convert DataTable to XML
public string
ConvertDataTableToXMLDataString(DataTable
dataTbl)
{
StringBuilder XMLString =
new
StringBuilder();
if (string.IsNullOrEmpty(dataTbl.TableName))
dataTbl.TableName = "DataTable";
XMLString.AppendFormat("<{0}>",
dataTbl.TableName);
DataColumnCollection tableColumns =
dataTbl.Columns;
foreach (DataRow
row in dataTbl.Rows)
{
XMLString.AppendFormat("<NewDataSet>");
foreach (DataColumn
column in tableColumns)
{
XMLString.AppendFormat("<{1}>{0}</{1}>",
row[column].ToString(), column.ColumnName);
}
XMLString.AppendFormat("</NewDataSet>");
}
XMLString.AppendFormat("</{0}>",
dataTbl.TableName);
return XMLString.ToString();
}
protected void
BtnSaveXmlData_Click(object sender,
EventArgs e)
{
try
{
DataTable dt = GetTable();
dt.TableName = "Employee";
string XmlDoc =
ConvertDataTableToXMLDataString(dt);
SqlConnection con =
new
SqlConnection("data
source=.;Database=test;uid=sa;pwd=*************;");
con.Open();
SqlCommand cmd =
new
SqlCommand("InsXmldata", con);
cmd.Parameters.AddWithValue("@xml_data",
XmlDoc);
cmd.CommandType = CommandType.StoredProcedure;
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
lblmesg.Text = "Save Successfully";
}
else
{
lblmesg.Text = "Some Error Occured";
}
}
catch(Exception
ex) {
lblmesg.Text = ex.Message.ToString();
}
}
}
|
Code of aspx page
|
<%@
Page Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE
html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html
xmlns="http://www.w3.org/1999/xhtml">
<head
runat="server">
<title></title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<asp:Button
ID="BtnSaveXmlData"
runat="server"
Text="Save"
onclick="BtnSaveXmlData_Click"
/>
<asp:Label
ID="lblmesg"
runat="server"></asp:Label>
</div>
</form>
</body>
</html>
|
