where an Stored Procedure takes Xml as input parameter, and C# is calling that Stored Procedure. [Resolved]

Posted by Mahe under C# on 9/24/2012 | Points: 10 | Views : 29765 | Status : [Member] | Replies : 2
Hi All,

Please provide link or code ,
where an Stored Procedure takes Xml as input parameter, and C# is calling that Stored Procedure.

Regards,
Mahe




Responses

Posted by: Tanujad on: 9/24/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
For your requirement please find below the sample code along witht the stored procedure --

private DataTable CreateDataTableSchema()

{
DataTable dt = new DataTable("EmployeeTable"); //given Name to DataTable which will be used in SP
dt.Columns.Add("EmpID", typeof(Int32));
dt.Columns.Add("EmpName", typeof(String));
dt.Columns.Add("EmpDesignation", typeof(String));
dt.Columns.Add("EmpAddress", typeof(String));
return dt;
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable dt = CreateDataTableSchema(); //Function that creates a datatable schema
DataSet ds = new DataSet("Employee"); //given Name to DataSet to be used in Stored procedure
EmployeeEntity objEntityData = new EmployeeEntity(); // Employee Entity Class

objEntityData.EmpID = Convert.ToInt32(txtEmpID.Text);
objEntityData.EmpName = txtEname.Text;
objEntityData.EmpDesignation = txtDesignation.Text;
objEntityData.EmpAddress = txtAddress.Text;

//adding data to Datatable
dt.Rows.Add(objEntityData.EmpID, objEntityData.EmpName, objEntityData.EmpDesignation, objEntityData.EmpAddress);

//adding datatable to dataset and getting the XML out of dataset in the form of string
ds.Tables.Add(dt);
string InputXML = ds.GetXml();

//passing that string form of xml as input to stored procedure
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand command = new SqlCommand("spInsertEmpDetails",con);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@XMLDoc",SqlDbType.VarChar));
command.Parameters[0].Value = InputXML ; //passing the string form of XML generated above

int i=command.ExecuteNonQuery();
}

}


-- Stored Procedure with XML as input
CREATE PROCEDURE [dbo].[spInsertEmpDetails]
(
@XMLDoc XML
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XMLDoc

INSERT INTO tblEmployee (EmpId,EmpName,Designation,Address)
SELECT EmpID, EmpName,EmpDesignation,EmpAddress
FROM OPENXML(@XMLDocPointer,'/Employee/EmployeeTable',2) -- /Employee/EmployeeTable is /DataSetName/Datatable Name that is given in code
WITH
(
EmpID INT
, EmpName VARCHAR(50)
, EmpDesignation VARCHAR(50)
, EmpAddress VARCHAR(50)
)

SET NOCOUNT OFF
END


Hope this gives you a clear understanding.
Please mark as answer if it resolves your query.

Thanks
Tanuja
(If this content helped you, Please mark it as Answer)

Mahe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/25/2012 [Member] Bronze | Points: 25

Up
0
Down
Nice explanation Tanuja

Mahe, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response