Thread: [Rainbowportal-devel] record paging, advise wanted.
Brought to you by:
danijel_kecman,
manudea
From: Chris F. <ch...@cf...> - 2004-04-20 04:09:31
|
Has anyone implemented paging for records? I have found several methods, most are inadequate. 1: It is a datalist not a datagrid. The datagrid has built in paging, data list does not. I would prefer to stick with a datalist as it is faster and means less re-writing of code. Worst case senario I'll use this. 2: Some methods that work at the sproc level assume a contigious identity field. Something like SELECT * FROM table WHERE IdentityField > start AND IdentityField < end What if I delete records 1-5, then page 1 only returns 5 records. 3: Creating an entire temp table with its own Identity field, copying ALL records and using that table for the query shown in #2 so that you are guaranteed to get the right number of records even if there are holes in the original table's identity field from deleted records. I highly doubt that there is a worse solution than this. There has got to be a way to do this. It is just too common of a problem to not have an available solution. I would be surprised if this is a feature not addressed in SQL Server but I cannot find it. Help! Chris Farrell |
From: Jason H. <ja...@so...> - 2004-04-20 04:56:57
|
Paging is a feature that has to occur on a programmatic level. As you noted with point #1, the datagrid has good paging support built into the control, but the datalist (or repeater) controls does not. Point #2 shouldn't be much of a concern because you are running a SELECT statement each time, so you'll have a fresh copy of the dataset each time. Even in the case of the datagrid, if you delete records, you'll still have to rebind the data which will give you a fresh dataset. If you want to stick with the datalist, you are going to have to implement some sort of custom paging. With SQL, you can use the TOP statement as part of your SELECT statement to return a certain number of records. For example, the following will select the first 10 records from a table: SELECT TOP 10 * FROM TABLE ORDER BY ID ASC; To implement paging programmatically, you can add a WHERE clause that will select the records on a page by page basis. As part of your PREVIOUS and NEXT links, you'll have to provide the ID of the last record viewed. This ID can be used to select which records are displayed after link is clicked. For example: Samepage.aspx.vb (pseudo code): intNextID = 0 intPrevID = 0 If Not(IsNothing(Request.QueryString("nextid")) Then intNextID = CType(Request.QueryString("nextid"), Integer) If Not(IsNothing(Request.QueryString("previd")) Then intPrevID = CType(Request.QueryString("previd"), Integer) If intNextID > 0 Then strSQL = "SELECT TOP 10 * FROM TABLE ORDER BY ID ASC WHERE ID > " & intNextID.ToString() ElseIf intPrevID > 0 Then strSQL = "SELECT TOP 10 * FROM TABLE ORDER BY ID ASC WHERE ID > " & intPrevID.ToString() Else strSQL = "SEELCT TOP 10 * FROM TABLE ORDER BY ID ASC" End If End If You'll have to populate both links with the ID of the first for PREV and last for NEXT of your current recordset. PREV link = samepage.aspx?previd=5 NEXT link = samepage.aspx?nextid=15 Jason -----Original Message----- From: rai...@li... [mailto:rai...@li...] On Behalf Of Chris Farrell Sent: Monday, April 19, 2004 11:04 PM To: rai...@li... Subject: [Rainbowportal-devel] record paging, advise wanted. Has anyone implemented paging for records? I have found several methods, most are inadequate. 1: It is a datalist not a datagrid. The datagrid has built in paging, data list does not. I would prefer to stick with a datalist as it is faster and means less re-writing of code. Worst case senario I'll use this. 2: Some methods that work at the sproc level assume a contigious identity field. Something like SELECT * FROM table WHERE IdentityField > start AND IdentityField < end What if I delete records 1-5, then page 1 only returns 5 records. 3: Creating an entire temp table with its own Identity field, copying ALL records and using that table for the query shown in #2 so that you are guaranteed to get the right number of records even if there are holes in the original table's identity field from deleted records. I highly doubt that there is a worse solution than this. There has got to be a way to do this. It is just too common of a problem to not have an available solution. I would be surprised if this is a feature not addressed in SQL Server but I cannot find it. Help! Chris Farrell ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel |
From: Thierry \(Tiptopweb\) <th...@ti...> - 2004-04-20 05:15:37
|
There is a paging interface in the UI directory of Rainbow project It is working on datalist I think and you just have to inherit from it and add an event handler from what I can remember. It is used for the Picture module. Would that be good enough? Thierry -----Original Message----- From: rai...@li... [mailto:rai...@li...]On Behalf Of Chris Farrell Sent: Tuesday, 20 April 2004 6:04 AM To: rai...@li... Subject: [Rainbowportal-devel] record paging, advise wanted. Has anyone implemented paging for records? I have found several methods, most are inadequate. 1: It is a datalist not a datagrid. The datagrid has built in paging, data list does not. I would prefer to stick with a datalist as it is faster and means less re-writing of code. Worst case senario I'll use this. 2: Some methods that work at the sproc level assume a contigious identity field. Something like SELECT * FROM table WHERE IdentityField > start AND IdentityField < end What if I delete records 1-5, then page 1 only returns 5 records. 3: Creating an entire temp table with its own Identity field, copying ALL records and using that table for the query shown in #2 so that you are guaranteed to get the right number of records even if there are holes in the original table's identity field from deleted records. I highly doubt that there is a worse solution than this. There has got to be a way to do this. It is just too common of a problem to not have an available solution. I would be surprised if this is a feature not addressed in SQL Server but I cannot find it. Help! Chris Farrell ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel |
From: Bill A. <wja...@re...> - 2004-04-20 13:05:12
|
Go take a look at AspNetForums code. there is a pager and alphalist module and examples (sql-code). The sql-code is a bit outdated as they use a temp table (CREATE TABLE #PageIndexForProp) as oppose to a temp table variable (DECLARE @PageIndexForProp TABLE) which is faster ( assuming your version supports it). hth, -bja -----Original Message----- From: rai...@li... [mailto:rai...@li...]On Behalf Of Thierry (Tiptopweb) Sent: Tuesday, April 20, 2004 1:15 AM To: Chris Farrell; rai...@li... Subject: RE: [Rainbowportal-devel] record paging, advise wanted. There is a paging interface in the UI directory of Rainbow project It is working on datalist I think and you just have to inherit from it and add an event handler from what I can remember. It is used for the Picture module. Would that be good enough? Thierry -----Original Message----- From: rai...@li... [mailto:rai...@li...]On Behalf Of Chris Farrell Sent: Tuesday, 20 April 2004 6:04 AM To: rai...@li... Subject: [Rainbowportal-devel] record paging, advise wanted. Has anyone implemented paging for records? I have found several methods, most are inadequate. 1: It is a datalist not a datagrid. The datagrid has built in paging, data list does not. I would prefer to stick with a datalist as it is faster and means less re-writing of code. Worst case senario I'll use this. 2: Some methods that work at the sproc level assume a contigious identity field. Something like SELECT * FROM table WHERE IdentityField > start AND IdentityField < end What if I delete records 1-5, then page 1 only returns 5 records. 3: Creating an entire temp table with its own Identity field, copying ALL records and using that table for the query shown in #2 so that you are guaranteed to get the right number of records even if there are holes in the original table's identity field from deleted records. I highly doubt that there is a worse solution than this. There has got to be a way to do this. It is just too common of a problem to not have an available solution. I would be surprised if this is a feature not addressed in SQL Server but I cannot find it. Help! Chris Farrell ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel |
From: Dave R. <dw...@da...> - 2004-04-23 04:21:16
|
Here are 2 methods for paging. Both assume SQL Server as the DB. Not shown is the simplest solution, but it would depend on contiguous ids. No paging solution is perfect and which solution to implement depends on factors such as how large the data set is. The sproc below could be altered to only use a subset of records in the temp table to enhance performance on very large data sets. The first example is without a temp table. In practice, you would replace the hardcoded values for page size (10) and current page (30 - divide by page size to get the page number). This solution has the "advantage" that the earlier pages will return with less overhead than later pages. As the current page value rises (in this case 30) the overhead rises with it. This works well for search solutions where the user normally looks at the first few pages, but not as well for something like customer records, where it is just as likely the user wants to see a name beginning with "Z" as "A". -- This would select the items from page 3 of a -- datasource. 10 and 30 would be replaced -- programmatically to create the pages SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 30 * FROM products) AS t1 ORDER BY productid DESC) AS t2 ORDER BY productid And here is a sproc using a temp table. This solution has the "advantage" of having the same overhead no matter where in the result set your page falls. Very large result sets *might* be able to be broken up with a bit more logic in the INSERT INTO #TempTable SQL. CREATE PROCEDURE [Get_Customers_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, CompanyName nvarchar(40), ContactName nvarchar (30), ContactTitle nvarchar (30), Phone nvarchar (24), Fax nvarchar (24) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( CompanyName, ContactName, ContactTitle, Phone, Fax ) SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter SELECT @TotalRecords = COUNT(*) FROM Customers Dave Ranck Whatever your hand finds to do, do it with all your might www.daveranck.com blog.daveranck.com -----Original Message----- From: rai...@li... [mailto:rai...@li...] On Behalf Of Bill Anderson Sent: Tuesday, April 20, 2004 9:02 AM To: th...@ti...; Chris Farrell; rai...@li... Subject: RE: [Rainbowportal-devel] record paging, advise wanted. Go take a look at AspNetForums code. there is a pager and alphalist module and examples (sql-code). The sql-code is a bit outdated as they use a temp table (CREATE TABLE #PageIndexForProp) as oppose to a temp table variable (DECLARE @PageIndexForProp TABLE) which is faster ( assuming your version supports it). hth, -bja -----Original Message----- From: rai...@li... [mailto:rai...@li...]On Behalf Of Thierry (Tiptopweb) Sent: Tuesday, April 20, 2004 1:15 AM To: Chris Farrell; rai...@li... Subject: RE: [Rainbowportal-devel] record paging, advise wanted. There is a paging interface in the UI directory of Rainbow project It is working on datalist I think and you just have to inherit from it and add an event handler from what I can remember. It is used for the Picture module. Would that be good enough? Thierry -----Original Message----- From: rai...@li... [mailto:rai...@li...]On Behalf Of Chris Farrell Sent: Tuesday, 20 April 2004 6:04 AM To: rai...@li... Subject: [Rainbowportal-devel] record paging, advise wanted. Has anyone implemented paging for records? I have found several methods, most are inadequate. 1: It is a datalist not a datagrid. The datagrid has built in paging, data list does not. I would prefer to stick with a datalist as it is faster and means less re-writing of code. Worst case senario I'll use this. 2: Some methods that work at the sproc level assume a contigious identity field. Something like SELECT * FROM table WHERE IdentityField > start AND IdentityField < end What if I delete records 1-5, then page 1 only returns 5 records. 3: Creating an entire temp table with its own Identity field, copying ALL records and using that table for the query shown in #2 so that you are guaranteed to get the right number of records even if there are holes in the original table's identity field from deleted records. I highly doubt that there is a worse solution than this. There has got to be a way to do this. It is just too common of a problem to not have an available solution. I would be surprised if this is a feature not addressed in SQL Server but I cannot find it. Help! Chris Farrell ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ Rainbowportal-devel mailing list Rai...@li... https://lists.sourceforge.net/lists/listinfo/rainbowportal-devel |