1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Sending and Reading XML in Stored Procedure via .NET

Discussion in 'ASP.NET' started by naimish, Jun 30, 2009.

  1. naimish

    naimish New Member


    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


    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.

    1. Import SqlClient namespace
    2. Connection string for the database needs to be changed accordingly
    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.
    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" 
    		objAdapter = New SqlDataAdapter 
    		objDataSet = New DataSet 
    		objCon = New SqlConnection(connString) 
    		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() 
    		Return objDataSet 
    	Catch ex As Exception 
    	Throw New Exception(ex.ToString) 
    	End Try 
    End Function 
    SaswatPadhi likes this.
  2. shabbir

    shabbir Administrator Staff Member

  3. naimish

    naimish New Member

    Ohh, Thanks Shabbir :)
  4. shabbir

    shabbir Administrator Staff Member

Share This Page