Sending and Reading XML in Stored Procedure via .NET

naimish's Avatar author of Sending and Reading XML in Stored Procedure via .NET
This is an article on Sending and Reading XML in Stored Procedure via .NET in ASP.NET.
Rated 4.50 By 2 users

Problem



We encounter a situation where we need to execute the stored procedure passing XML string as parameter. The SP executor is used to execute the stored procedure with dataset as input from the application. It converts the dataset into XML string and passes it to the Store procedure

Solution



This code snippet takes the dataset and stored procedure name as input. The same code can also be used for MS Access database by replacing the SqlConnection, SqlDataAdapter, and SqlCommand to OleDbConnection, OleDbDataAdapter, and OleDbCommand respectively.

Suggestions:
  1. Import SqlClient namespace
  2. Connection string for the database needs to be changed accordingly
Restrictions:
  1. Forms only standard XML string with dataset name as root element, datatable as child element and rows as inner child element.
  2. MappingType needs to be set for the dataset if XML string needs to be formed in element-attribute format (refer the sample project)

The code



The sample project demonstrates how to form the dataset, the way of calling the SP executor and how to handle these in the stored procedure.
Code: VB.NET
Public Function GetDataThruSP(ByVal dssampleDataset As DataSet, ByVal strSpName As String) As DataSet 
    Dim objCon As SqlConnection
    Dim objAdapter As SqlDataAdapter
    Dim cmdCommand As New SqlCommand
    Dim objDataSet As DataSet
    Dim strParam As String
    
    'set the connection string of the database
    Dim connString As String = "Data
    
    Source=.\SQLEXPRESS;AttachDbFilename="
"bin\EmployeeDatabase.mdf"";Integrated Security=True;User Instance=True"
    Try    
        objAdapter = New SqlDataAdapter
        objDataSet = New DataSet
        
        objCon = New SqlConnection(connString)
        objCon.Open()
        
        objAdapter.SelectCommand = cmdCommand
        objAdapter.SelectCommand.Connection = objCon
        
        objAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure   
        objAdapter.SelectCommand.CommandText = strSpName
        strParam = dssampleDataset.GetXml()

        objAdapter.SelectCommand.Parameters.AddWithValue("@parameter1", strParam)
        
        objDataSet = New DataSet()
        objAdapter.Fill(objDataSet)
        objCon.Close()
        
        Return objDataSet
    Catch ex As Exception
        objCon.Close()
    Throw New Exception(ex.ToString)
    End Try
End Function
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Nomination for article of the month - Jun 2009 Started. Nominate this article for Article of the month - Jun 2009
naimish's Avatar
Banned
Ohh, Thanks Shabbir
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Start voting for this article for Article of the month - June 2009
naimish like this