Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Source Code - The core database routines to work with stored procedures: “SPgetDataTable” method
(Page 2 of 4 )
This is one of the core methods I used to retrieve information from a stored procedure using parameters.
All the parameters will be available in the parameter cache “dtParameterList”. This method goes through all the parameters available in the parameter cache and converts them into the parameters of the command type. We shall execute the stored procedure along with all the parameters.
After successful execution of the stored procedure, it gives the information in the form of rows (or a data table), which is what we finally return.
The following is the complete source code for “SPgetDataTable”:
Public Function SPgetDataTable(ByVal ProcName As String) As DataTable
Dim cmd As SqlCommand
Try
cmd = New SqlCommand(ProcName, New SqlConnection(getConnectionString))
Dim Parm As SqlParameter
Dim privateDataTable As New DataTable
With cmd
.CommandType = CommandType.StoredProcedure
Dim UsedParameter As MSSQLProcParameter Dim ConvertedParameter As SqlParameter
Dim drParam As DataRow
If Not viewstate("dtParameterList") Is Nothing Then
For Each drParam In viewstate("dtParameterList").Rows
UsedParameter = Nothing
Dim ser As New XmlSerializer(GetType(MSSQLProcParameter))
UsedParameter = CType(ser.Deserialize(New StringReader(drParam(0))), MSSQLProcParameter)
ConvertedParameter = UsedParameter.getSQLParameter
.Parameters.Add(ConvertedParameter)
Next
End If
Dim privateSQLDataAdapter As New SqlDataAdapter(cmd)
privateSQLDataAdapter.Fill(privateDataTable)
.Connection.Close()
.Dispose()
privateSQLDataAdapter.Dispose()
End With
Return privateDataTable
Catch ex As Exception
Try
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
cmd.Dispose()
End If
Catch e As Exception
End Try
Throw New Exception(ex.Message & ". Stored Procedure: " & ProcName & " -->Error Logged")
End Try
End Function
Next: Where does the processing start? >>
More ASP.NET Articles
More By Jagadish Chaterjee
