Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures - Returning result sets (or a set of rows) by executing a stored procedure

(Page 4 of 6 )

Once we know about executing the stored procedures using parameters, we can easily retrieve the entire information from the database. Now, let us bring a set of rows from the database in the form of a dataset, by executing a stored procedure as follows:

Public Function SPgetDataSet(ByVal SPName As String) As DataSet
   Dim ds As New DataSet
   Dim da As DbDataAdapter = GetDBDataAdapter()
   Dim cmd As DbCommand = GetDBCommand()
   AddSPParamFromCache(cmd)
   cmd.CommandText = SPName
   cmd.CommandType = CommandType.StoredProcedure
   da.SelectCommand = cmd
   Try
     da.Fill(ds)
   Catch ex As Exception
     Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
   Finally
     If cmd.Connection.State = ConnectionState.Open Then
       cmd.Connection.Close()
     End If
     cmd.Dispose()
     da.Dispose()
     SPParameterClearCache()
   End Try
   Return ds
End Function

The above method returns a dataset which is filled using a DataAdapter. You can even return a "DataTable" object using a separate method as follows:

Public Function SPgetDataTable(ByVal SPName As String) As DataTable
   Dim dt As New DataTable
   Dim da As DbDataAdapter = GetDBDataAdapter()
   Dim cmd As DbCommand = GetDBCommand()
   AddSPParamFromCache(cmd)
   cmd.CommandText = SPName
   cmd.CommandType = CommandType.StoredProcedure
   da.SelectCommand = cmd
   Try
     da.Fill(dt)
   Catch ex As Exception
     Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
   Finally
     If cmd.Connection.State = ConnectionState.Open Then
       cmd.Connection.Close()
     End If
     cmd.Dispose()
     da.Dispose()
     SPParameterClearCache()
   End Try
   Return dt
End Function

In other words, you can further reduce the above code (which returns the "DataTable" object) by rewriting it as follows:

Public Function SPgetDataTable(ByVal SPName As String) As DataTable
   Dim ds As DataSet = SPgetDataSet(SPName)
   If ds.Tables.Count = 0 Then
     Return Nothing
   Else
     Return ds.Tables(0)
   End If
End Function

In the above code, I am simply reusing the existing method "SPgetDataSet" to decrease several lines of code!

Next: Returning a single row or single value by executing a stored procedure >>
 

More ASP.NET Articles
More By Jagadish Chaterjee

Comments On: Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures

· Hello guys. This is second in the series focusing on "database independent...    · Thanks for the article. Wondering whether can you still have a database independent...    · Jag,Thank you so much for your article. I have been building a web appication...    · Jag,How would you set the SPExecute if you wanted to return a Int (for example)...   >>> Post your comment now!