Hi guys.

I'm building a photo gallery module for some college project. Im trying to retrieve the number of rows afected on the query for some paging purposes. Here's the Code

1. The SqlDataProvider:

public override IDataReader GetPhotosByUser(int userId, long pageIndex, int pageSize)
        {
            SqlParameter[] collection = new SqlParameter[3];
            collection[0] = new SqlParameter("@PageIndex", pageIndex);
            collection[1] = new SqlParameter("@PageSize", pageSize);
            collection[2] = new SqlParameter("@UserID", userId);

            IDataReader photoReader = SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, GETPHOTOSBYUSER
                , collection);
            return photoReader;
        }

2. The Controller:

 public List<Photo> GetPhotosByUser(int userId, long pageIndex, int pageSize, ref int totalRecords)
        {
            return CBO.FillCollection<Photo>(DataProvider.Instance().GetPhotosByUser(userId, pageIndex
                , pageSize), ref totalRecords);
        }

3. The Stored Procedure:
-- =============================================
-- Author:        Jaime A. Jaramillo
-- Create date: Jul 13th, 2009
-- Description:    Gets a Set of Photos from a given user
-- =============================================
CREATE PROCEDURE [dbo].[SN_PHOTOALBUM_USP_GET_PHOTOS_BY_USER]
    @UserID AS INT
    ,@PageIndex AS BIGINT
    ,@PageSize AS INT
   
AS
BEGIN
    --First Calculate the page bounds
    DECLARE @PageLowerBound AS BIGINT
    DECLARE @PageUpperBound AS BIGINT
    SET @PageLowerBound = @PageIndex * @PageSize
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    --Create a temp table for indexing
    CREATE TABLE #QueryIndex
    (
        Id int NOT NULL IDENTITY (1, 1),
        PhotoID bigint NOT NULL
    )
   
    --Then do a insert of only what you need
    INSERT INTO #QueryIndex
    (
        PhotoID
    )
    SELECT
        P.PhotoID
    FROM
        Photos AS P INNER JOIN Albums AS A
    ON
        P.AlbumID = A.AlbumID
    WHERE
        A.UserID = @UserID
    ORDER BY
        P.LastUpdate DESC
        ,P.CreationDate DESC   


    --Finally Select the page your currently querying

    SELECT
        P.PhotoID
        ,P.AlbumID
        ,P.Guid
        ,P.Width
        ,P.Height
        ,P.Url
        ,P.Title
        ,P.Comment
        ,P.CreationDate
        ,P.LastUpdate
    FROM
        Photos AS P INNER JOIN #QueryIndex AS I
    ON
        P.PhotoID = I.PhotoID
    WHERE
        I.Id >= @PageLowerBound
        AND I.Id <= @PageUpperBound
   
END

4. The Object Data Source calling the controller:

<asp:ObjectDataSource ID="odsPhoto" runat="server" OldValuesParameterformatString="original_{0}"
            SelectMethod="GetPhotosByUser" TypeName="SocialNetwork.Modules.PhotoAlbum.PhotoAlbumController">
            <SelectParameters>
                <asp:Parameter DefaultValue="1" Name="userId" Type="Int32" />
                <asp:Parameter DefaultValue="1" Name="pageIndex" Type="Int64" />
                <asp:Parameter DefaultValue="8" Name="pageSize" Type="Int32" />
                <asp:Parameter DefaultValue="0" Direction="InputOutput" Name="totalRecords" Type="Int32" />
            </SelectParameters>
        </asp:ObjectDataSource>

The totalRecords variable is supposed to be filled with the total records affected by the query... but it returns 0 always

Anybody can tell me what is wrong with my code? Thanks

Hi Joker,

do you select the number of rows as a second resultset? You have to do that and give the result the alias TotalRecords after that it should be possible for you to get the ref value of the function. In the CBO class following happens:

Public

Function GetTotalRecords(ByRef dr As IDataReader) As Integer

.I discovered, that using an approach with () OVERinstead of temp tables speeds the application.

ROW_NUMBER

greats matze