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:
- Import SqlClient namespace
- Connection string for the database needs to be changed accordingly
- Forms only standard XML string with dataset name as root element, datatable as child element and rows as inner child element.
- 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
