Hi
Can someone please assist me with changing the code in the DAL to handle output parameters from a SQL stored proc?
I have a stored procedure that returns the results of a basic search module. I have changed the procedure to only return the result based on the page index and number of rows per page that is set for the gridview, hence making the recordset returned small and only relevant to the page the user is viewing. (I won't be able to use the page index eventually, but that is not what I'm worried about at this point)
To do this, I will not be using the gridview's native paging information (as it will always show just one page) but rather the First Next Previous Last scrolling mechanism. I will then also have to tell the user which page he is on and how many total records there are.
The stored procedure returns the total number of records as an output parameter. Now the DAL has to cater for a returned record set as well as an output parameter and I can't seem to figure it out or find any information on it online.
This is my proc:
ALTER procedure [dbo].[PracticalComforts_GetLSTResultsMain]
(
@ComboProvinceData VARCHAR(250),
@ComboCityRegionData VARCHAR(250),
@ComboAreaData VARCHAR(250),
@ComboSuburbData VARCHAR(250),
@SearchWord VARCHAR(100),
@MaximumRows INT,
@CurrentPage INT,
@SortExpression VARCHAR(50) = NULL,
@TotalRecords INT OUTPUT
)
AS
some code here....
SQL Data Provider
Public Overrides Function GetLSTResultsMain(ByVal ResultsProvince As String, ByVal ResultsCityOrRegion As String, ByVal ResultsGreaterArea As String, ByVal ResultsComboSuburb As String, ByVal ResultsSearchCriteria As String, ByVal ResultsListingMaximumRows As Integer, ByVal ResultsListingCurrentPage As Integer, ByVal ResultsListingSortExpression As String) As IDataReader
Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetLSTResultsMain"), ResultsProvince, ResultsCityOrRegion, ResultsGreaterArea, ResultsComboSuburb, ResultsSearchCriteria, ResultsListingMaximumRows, ResultsListingCurrentPage, ResultsListingSortExpression), IDataReader)
End Function
Gridview data source:
<asp:ObjectDataSource ID="odsMainListing"
runat="server"
SelectMethod="GetLSTResultsMain"
SortParameterName="ResultsListingSortExpression"
TypeName="PracticalComforts.Modules.LSTResults.LSTResultsController"
OldValuesParameterformatString="original_{0}">
<SelectParameters>
<asp:ControlParameter ControlID="ddlProvince" Name="ResultsProvince" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddlCityorRegion" Name="ResultsCityOrRegion" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddlGreaterArea" Name="ResultsGreaterArea" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddlSuburb" Name="ResultsComboSuburb" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="TextBox1" Name="ResultsSearchCriteria" PropertyName="Text"
Type="String" />
<asp:Parameter Name="ResultsListingMaximumRows" Type="Int32" />
<asp:Parameter Name="ResultsListingCurrentPage" Type="Int32" />
<asp:Parameter Name="ResultsListingSortExpression" Type="String" />
<asp:Parameter Name="TotalRecords" Direction="Output" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
Code behind file:
Protected Sub odsMainListing_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles odsMainListing.Selecting
Dim iPageIndex As Integer
Dim iPageSize As Integer
Dim sSortExpression As String
iPageIndex = GridviewMainListing.PageIndex
iPageSize = GridviewMainListing.PageSize
sSortExpression = GridviewMainListing.SortExpression
'make sure the parameters are set properly
e.InputParameters("ResultsListingSortExpression") = sSortExpression
e.InputParameters("ResultsListingCurrentPage") = iPageIndex
e.InputParameters("ResultsListingMaximumRows") = iPageSize
End Sub
Please ask if any more information is needed.
Thanks in advance
Michelle