1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Sending and Reading XML in Stored Procedure via .NET

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

  1. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth

    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:
    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 
     
    SaswatPadhi likes this.
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
  3. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    Ohh, Thanks Shabbir :)
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83

Share This Page