CODEWITHSUNDEEP
c#asp.netdataset
Wayne Molina
Wayne Molina

Reputation: 19596

Handling paging and "lazy-loading" with DataSets?

My company uses raw, untyped DataSets filled via Stored Procedures exclusively. I have been tasked with finding a way to retrieve very large result sets (paging) and ways to get Lazy Loading functionality (at least I think this is lazy loading; I'm still learning that stuff to be honest) so we aren't pulling back tens of thousands of rows in one batch and hogging server resources.

I personally am not that familiar with DataSets as I avoid them whenever possible, and I would rather get rid of them entirely here, but saying "Change everything to use LINQ/EF" isn't going to be a valid answer since there's no business value to management (and it would take too long to redo things, so the idea would be shot down immediately).

Are there some resources I can look into to get this same kind of functionality but using standard untyped DataSets?

EDIT: Also, I need a solution that can work with dynamically created SQL that does not use a stored procedure.

Upvotes: 2

Views: 4218

Answers (4)

Stefan P.
Stefan P.

Reputation: 9519

I had the same problem with asp.net 2.0 website, there is no "lazy-loading" solution to this. In order to paginate the data-sets I am using 2 sprocs that will help me wrap the paging functionality on every select I am doing.

CREATE PROCEDURE [dbo].[Generic_Counting]
    @tables VARCHAR(MAX),
    @filter VARCHAR(MAX) = '1=1'    
AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @strQuery VARCHAR(8000)

    SET @strQuery = ' SELECT COUNT(*) FROM '+ @tables +'
             WHERE '+ @filter 

    execute     (@strQuery)
    IF @@ERROR<>0 
    BEGIN 
        --error on generic count
        SET NOCOUNT OFF
        RETURN 10067
    END

    SET NOCOUNT OFF
    RETURN 0
END
GO

CREATE PROCEDURE [dbo].[Generic_Paging]
    @tables VARCHAR(1000),
    @pk VARCHAR(100), 
    @pageNumber INT = 1,
    @pageSize INT = 10, 
    @fields VARCHAR(MAX) = '*',
    @filter VARCHAR(MAX) = '1=1',
    @orderBy VARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @strQuery VARCHAR(8000) 
    DECLARE @strMinRecord VARCHAR(12);
    DECLARE @strMaxRecord VARCHAR(12);

    SET @strMinRecord = CONVERT(VARCHAR(12),((@pageNumber -1)*@pageSize + 1))  
    SET @strMaxRecord = CONVERT(VARCHAR(12), (@pageNumber * @pageSize)) 

        -- Use ROW_NUMBER function

    SET @strQuery ='
    WITH Generic_CTE As
    (
        SELECT ''RowNumber'' = ROW_NUMBER() OVER(ORDER BY ' +
        ISNULL(@orderBy,@pk) +'),' + 
        @fields + 
        ' FROM ' + @tables +
        ' WHERE ('+ @filter +')
    )
    SELECT ' + @fields + '
    FROM Generic_CTE
    WHERE RowNumber BETWEEN ' + @strMinRecord +' AND '+ @strMaxRecord 



    --print @strQuery
    execute (@strQuery)

    IF @@ERROR<>0 
    BEGIN 
        --error on generic paging
        SET NOCOUNT OFF
        RETURN 10066
    END
    SET NOCOUNT OFF 
    RETURN 0
END
GO

Upvotes: 1

richaux
richaux

Reputation: 2672

You could take a look at the Value List Handler pattern, designed to be used where "the client requires a list of items ... for presentation. The number of items in the list is unknown and can be quite large in many instances."

The examples (in the link above and here) are for Java but should translate to asp.net fairly readily.

Upvotes: -1

MusiGenesis
MusiGenesis

Reputation: 75336

You'll need to implement paging inside your stored procedures. I assume you're using Sql Server, so here's a link:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Note that this has nothing to do with DataSets per se. Presumably, your code generates a DataSet from a stored procedure call. If you rewrite your procs to do paging, your code will then generate a DataSet that contains only the requested page's records.

You could use the DataSet returned by your original proc to implement paging, by caching the DataSet and returning only selected rows to the client (or more accurately, using only selected rows of the DataSet to generate the client HTML), but this is a super-duper, really bad idea.

Upvotes: 2

e36M3
e36M3

Reputation: 6022

All you need to do is to modify your stored procedure to page the result set. This of course will also mean that you'll have to pass as parameters certain criteria such as page number etc. Assuming you're using SQL Server 05 or newer, take a look at the following:

http://www.codeproject.com/KB/database/PagingResults.aspx

Upvotes: 2

Related Questions