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 Restrictions: 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: 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
Nomination for article of the month - Jun 2009 Started. Nominate this article for Article of the month - Jun 2009