Go4Expert

Go4Expert (http://www.go4expert.com/)
-   ASP.NET (http://www.go4expert.com/articles/asp-net-tutorials/)
-   -   Sending and Reading XML in Stored Procedure via .NET (http://www.go4expert.com/articles/sending-reading-xml-stored-procedure-t18263/)

naimish 30Jun2009 08:49

Sending and Reading XML in Stored Procedure via .NET
 

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 2Jul2009 11:20

Re: Sending and Reading XML in Stored Procedure via .NET
 
Nomination for article of the month - Jun 2009 Started. Nominate this article for Article of the month - Jun 2009

naimish 2Jul2009 11:25

Re: Sending and Reading XML in Stored Procedure via .NET
 
Ohh, Thanks Shabbir :)

shabbir 15Jul2009 23:21

Re: Sending and Reading XML in Stored Procedure via .NET
 
Start voting for this article for Article of the month - June 2009


All times are GMT +5.5. The time now is 18:56.