Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures - Returning a single row or single value by executing a stored procedure
(Page 5 of 6 )
Now that we know about returning a set of rows, it is time to retrieve a single row. Let us go through the following method:
Public Function SPgetDataRow(ByVal SPName As String) As DataRow
Dim dt As DataTable = SPgetDataTable(SPName)
If dt.Rows.Count > 0 Then
Return dt.Rows(0)
Else
Return Nothing
End If
End Function
You can observe from the above method that I am simply reusing the previous method "SPgetDataTable" (which returns a set of rows) and returning only the first row. Returning a single value is a bit different from above. Let us consider the following method:
Public Function SPgetGetValue(ByVal SPName As String) As String
Dim cmd As DbCommand = GetDBCommand()
AddSPParamFromCache(cmd)
cmd.CommandText = SPName
cmd.CommandType = CommandType.StoredProcedure
Try
cmd.Connection.Open()
Return cmd.ExecuteScalar & ""
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()
SPParameterClearCache()
End Try
End Function
In the above code, you can understand that I am using the "ExecuteScalar" method of the Command object to improve the performance while retrieving a single value from the database.
Next: Testing the execution of stored procedures (in ASP.NET) using DAL methods >>
More ASP.NET Articles
More By Jagadish Chaterjee
