Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

David Hayden blogged about a cool new ROW_NUMBER() function that SQL 2005 provides that got me excited and inspired to write a little code last night.

 

You can download the sample app I built here. 

 

Features of the Sample

 

This sample is a self-contained ASP.NET application that demonstrates a few things:

 

- How to implement a data-result search page built with ASP.NET 2.0 and Atlas that can present hundreds of thousands of row results using the ASP.NET 2.0 GridView control.  The results are formatted using a “paging” based UI model – where 15 results per page are displayed, and the user can skip from page to page to see their data.  For kicks I also added support for editing and deleting each individual row.

 

- How to optimize the number of rows returned by the SQL Server to the middle-tier web-server.  This sample demonstrates how a large query result (think 1000s, 10s of thousands or 100s of thousands of rows) can be paged efficiently so that only the 15 rows currently on display in a page are ever retrieved by the web-server from the SQL database (this is done using the ROW_NUMBER() function that David describes above, as well as the support for optimized paging provided by the GridView and ObjectDataSource controls).  This avoids your web-server and SQL server grinding to a halt when you execute large queries, and makes for much more scalable performance.

 

- How to easily implement Ajax UI support for paging/editing/deleting on top of hundreds of thousands of rows of data (so no full page refreshes – instead it only updates the portion of the page that changes).  This took me only 60 seconds to-do, and uses the same <atlas:updatepanel> control support I talked about in this earlier blog post. 

 

- How to easily implement Ajax UI support for adding “auto-suggest” behavior to controls like text-boxes.  The December release of the Atlas Project provides a super-easy server control called the <atlas:autocompleteextender> control that you can point at a TextBox, and that will then call a web-service to provide a list of suggestions when a browser user starts typing in the text-box.  This sample demonstrates how to use this to auto-suggest items based on the contents in the database.

 

- How to implement a business class façade/wrapper around a data access layer.  This is in turn used by the new ASP.NET 2.0 ObjectDataSource control for databinding scenarios.  For this simple sample, I could have just used the data access layer built by using File->Add New Item->DataSet from the ObjectDataSource control directly (like I did with my earlier To-do List sample), and basically eliminate the need to write any code in the app.  But I wanted to use this sample to help demonstrate how to build a richer business library layer abstraction that was separate from my data access layer.  The business layer implementation in this sample is pretty trivial (and doesn’t really add much value), but it demonstrates a skeleton of how/where you could easily add business logic rules that were cleanly separated from your data layer.

 

How To Run and Use the Application

 

To setup the sample, follow the below steps:

 

1) Have VS 2005 or Visual Web Developer and SQL Express installed.  Visual Web Developer and SQL Express are both free downloads and provide everything you need to build, debug and run the application.  You can download them from here.

 

2) Download this .zip file containing the sample.  Expand it into any directory you want.

 

3) Using Visual Web Developer or VS 2005, choose File->Open Web-site and point to the root directory of the sample sub-directory (the one with the files in it).

 

4) Select the “PagedData.aspx” file and hit run.

 

This will bring up a page that looks like this:

 

 

This allows you to query for lists by categories in your database.  The default database in the sample doesn’t have any list items, so first we’ll want to create some.  To-do this click the “Bulk Add” link.

 

Create a category called “test” and add 100 items to it.  This will create a bunch of sample data in the database that will help simulate paging.  Then repeat this with a new category called “test2” and add 10,000 items to it.  Then repeat this with a new category called “test3” and add 100,000 items to it.   Note that this last category will probably take a few minutes to create (since the logic in my app is simple and just adds them one row at a time).

 

 

Then click on the “Return to Paged Data Page” link and start typing a search string in the textbox.  Pause slightly after you type “tes” – and then notice how the auto-suggest box will list the three new categories you added:

 

 

These are being populated using the <atlas:autocompleteextender> server control (which is hitting a web-service on the server to lookup the available categories – and so will return the three new ones we just created).

 

Search using “test3” (the one with 100,000 items), and you’ll see this paged view of 100,000 items in the GridView:

 

 

Each page of the screen contains 15 items from the database.  You can switch around to see the different pages.  Click the “…” link to go to pages 11-20.  Click “Last” to jump to the final page of data:

 

 

Note that as you choose the different pages, the only thing that gets updated is the grid of data – the overall page does not refresh.  This is because the GridView control is wrapped using the <atlas:updatepanel> control which allows incremental Ajax refreshes of the page (no code required to accomplish this <g>).

 

Page back a few screens, and then click “edit” on one of the rows.  Note that you can now edit and update its items.  You can also click “delete” on a row to remove one.  Both operations occur in an Ajax way:

 

 

How is this Application Built

 

Here is what the solution directory looks like in Visual Web Developer:

 

 

It contains two pages – PagedData.aspx and AddData.aspx – that are each based on the Site.master master-page.  It also contains one web-service – categories.asmx – which is used by the <atlas:autocompleteextender> control.

 

The database is implemented in PagingSample.mdf and contains 1 simple table called “Lists” (note: you can create new SQL Express databases by select File->Add New Item->Database File):

 

 

ListId is the primary key, and the Category column has been marked to be indexed (note: this is important since we’ll be creating 100,000+ rows).  To set indexes, right click on a column in the table designer and choose “Indexs/Keys” and make sure that “Categories” is being indexed.

 

The database has one stored procedure called “GetPagedLists” that was created using the ROW_NUMBER() approach described in David’s blog:

 

 

Note: you can double-click on the sproc name in the solution explorer to open and edit it.

 

The data access layer was implemented using a DataSet component (choose File->Add New Item->DataSet to create one). It is defined declaratively within the “MyDataLayer.xsd” file and contains two table-adapters:

 

 

GetPagedListData goes against the SPOC above, but all other methods are normal SQL queries (note: the GetListCountByCategory method is defined as: SELECT COUNT(*) FROM Lists where category=@category and returns a scalar integer value).

 

I could have just used the DAL layer directly from the UI tier, but instead chose to wrap the DAL with a business layer façade that looks like this:

 

 

The two significant methods for our paging sample are “GetListCountByCategory” and “GetListsByCategory”.  Here are the simple implementations of them in our business façade layer (note: right now they just thinly wrap the data access layer and expose the row data as custom “ListItem” types – so they don’t provide much value over calling the DAL directly, but do demonstrate how you could add your own custom logic around it):

 

 

Databinding a paged GridView control to this middle-tier object is easy (and automated if you are using the WYSIWYG designer in Visual Web Developer).  Just set the “AllowPaging=true” property on the GridView and set the PageSize value you want (for this sample I am using 15 rows per page).  Then point the GridView at an ObjectDataSource control, which is configured to use the ListManager business façade class.  It has the “AllowPaging” attribute set to true, and has the “SelectMethod” and “SelectCount” properties pointing to the “GetLisByCategory” and “GetListCountByCategory” methods above. 

 

 

Now, when you run the application, the GridView binds against the ObjectDataSource which will invoke the “GetListCountByCategory” method on our “ListManager” business façade class to calculate the total number of rows to page, and then invoke the “GetListByCategory” method passing the appropriate “category” parameter along with the “startRowIndex” and “maximumRows” parameter values that correspond to the page index that the GridView is currently on. 

 

The beauty is that because we only retrieve and return the 15 rows of data we actually need from the database as part of this operation, the result is fast and scalable.  As a browser user pages back and forth on the data within the GridView across the 100,000 rows of data, all UI paging logic and update/edit semantics are handled for us.

 

The last little implementation note to call out is the use of the <atlas:AutoCompleteExtender> control to auto-suggest items within the Category TextBox.  This is a new control provided by the Atlas December drop, and is simple to implement.  Basically, you just add the control to your page and point at the TextBox you want to complete, along with a web-service URL and method to call when the browser user types three letters in the TextBox -- these are then passed to the web-service, which can provide a list of suggestions back:

 

 

The implementation of the web-service method then looks like this within this sample:

 

 

Hope this helps and proves to be a useful sample.  Thanks again to David for the blog post on the new ROW_NUMBER() function in SQL 2005 that makes this much easier to implement.

 

Hope this helps – and happy new year!

 

Scott

 

P.S. Note that with SQL 2000 you can use the same approach as above, although the SPROC ends up being slightly more complicated.

Published Sunday, January 1, 2006 11:05 AM by ScottGu

Comments

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Sunday, January 1, 2006 6:44 PM by Samboy LIms
This is a great article with a great example that just works immediately out of the zip (without further configuration needed). I hope all other tutorials will follow the same style. I am also inspired to replace my ASPNET1.1/SQL2K large dataset "paging" solution in the future. Thanks a lot Scott!!

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Sunday, January 1, 2006 11:45 PM by Rachit
This is a good one! In 1.1, sometimes we couldn't avoid to have a large view state in order to use the default paging of the grid. It should be simple and efficient now. I've not run the example yet but I'll in a day.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, January 2, 2006 12:38 AM by Luke Duff
Cool sample.

It wasn't clear to me how the startRowIndex and maximumRows parameters on the GetListsByCategory method were being filled out. You said it gets those values from the GridView, but I didn't see anywhere in the code where that happens. They're not specified in the ObjectDataSource's SelectParameters.

Then I found the docs on ObjectDataSource's .MaximumRowsParameterName and .StartRowIndexParameterName.

If you named the third parameter just "maxRows" it'd break. You might want to mention that the parameter names in GetListsByCategory rely on being the default values ObjectDataSource expects, or change the sample and explicity fill out those properties on the ObjectDataSource.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, January 2, 2006 3:43 AM by scottgu
Hi Luke,

Yes -- that is a good point. Both of those parameter names are configurable properties on the ObjectDataSource control. People can choose to set those explictly if they want to rename the parameters something else.

Thanks,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, January 2, 2006 9:44 AM by Ramon Durães
Cool!!

How to return a name/value in one textbox with autocomplete?

Tks

Ramon Durães

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, January 2, 2006 2:52 PM by scottgu
Hi Ramon,

Can you provide a little more detail about what you are looking for? I'm not sure what you mean by name/value pair with the textbox?

Thanks!

- Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, January 4, 2006 11:50 AM by Ramon Durães
Scott,

FileUpload don't work ATLAS CTP ?

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Thursday, January 5, 2006 6:25 PM by jmcfetridge@midsolve.com
was wondering if you could elaborate on "declaratively within the “MyDataLayer.xsd” file and contains two table-adapters:" . Do you mean edit the xsd file and add the methods?

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Sunday, January 8, 2006 1:53 PM by scottgu
Hi Ramon,

Apologies for the delay in getting back to you -- I've been on vacation the last 2 weeks and fell behind on email.

The combo-box behavior you describe (drop-down+auto-complete) is the type of control we expect to enable with Atlas. There isn't one yet -- but I expect we'll see one soon.

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Sunday, January 8, 2006 1:57 PM by scottgu
Hi Ramon,

File-Uploads are a somewhat weird element in HTML -- since they are by-design non-scriptable (for security reasons, to avoid someone writing client-side script to maliciously upload a file from a browser).

As such, you'll need to be a little careful with how you use them with Atlas (or other Ajax frameworks).

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Tuesday, January 24, 2006 9:59 AM by Lucian Naie
Wouldn't be nice to have PageCount property of GridView control assignable? Currently it's readonly and it cannot be used when pagination is done on db server and we know the total number of records.
I think it will be usefull to use it in conjuction with ObjectDataSource control. Eg: to easily paginate membership users.

Regards,
Lucian

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, January 25, 2006 12:36 AM by scottgu
Hi Lucian,

You should be able to return the PageCount from the ObjectDataSource when the count is done on the server. That is actually what I'm doing in the sample above.

Thanks,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Thursday, July 27, 2006 4:15 AM by Rod Johnson
I am having a hard time... How come your SelectCountMethod works with one parameter and the SelectMethod takes 3 parameters. Also, how do you get around having to add the parameter names for startRow and maxRows in the objectDataSource? I have been scratching my head for the past 3 hours trying to figure this out, i keep getting the following exception: ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'GetBasicMotorSearchResultsCount' that has parameters: type, Hp, Rpm, Volt, startRowIndex, maximumRows.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, July 31, 2006 3:08 PM by Anthony Grace
I'm have the same problem as Rod when attempting an update: ObjectDataSource 'newAuctionGridObjectDataSource' could not find a non-generic method 'UpdateTaxLots' that has parameters: Land, Acres, SitusAddress1, SitusAddress2, Zip, Comments, Parcel_Num I have declaratively set my primary key "Parcel_Num" as read-only in the ASPX. Any ideas??

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, July 31, 2006 10:58 PM by ScottGu

Hi Anthony,

Can you check the parameters collection within the .aspx file to see if it's parameters match your method?

Thanks,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Tuesday, August 1, 2006 11:30 AM by Anthony Grace
Thanks Scott, I finally got it working. I matched the .aspx update parameters collection members with the corresponding method signature, EXCEPT that I DO add the primary key as a parameter in the update method. I set the DataKeyNames to the primary key and I did NOT add the primary key to the update parameters collection.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, August 4, 2006 1:29 AM by Yasir
Thanks to this post, I just solved a huge bottle neck in our application. We were using Dataadapter.Fill earlier just to get by, but with the SQL 2005 upgrade, this was possible. Thanks for the indepth example.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, September 1, 2006 4:54 PM by Kant
Hi Scott, Nice article. But i have some issues in my application. We are using Factory fasade, which will return class objects to UI.UI doesn’t have access to call BL classes & methods directly (It’s interface based model). My current page has search criteria, where user can search for the required data in sql server db. Since its huge data, i want to go by custom paging. It seems GridView supports custom paging only by . Is it true? If so, "TypeName" property is expecting a class name. Can i use My aspx page class name (Partial class _Default) ? If i say TypeName = "_Default", It's not recognizing. I didn’t create any namespace to specify. If i create a class in APP_CODE folder and pass that class name, then it's accepting. But i can't create classes like this way to my rest of project. 2) The SelectMethod property of this ObjectDataSource is expecting a method having max 3 parameters(intSratPage,intNoOfRecords,Sortoptions.) Where as my requirement is i need to pass multiple parameters to that procedure (for search criteria), and that procedure will call SP in Database. Kindly help me on this issue (custom paging & sorting with search criteria). Regards Kant

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Saturday, September 2, 2006 12:16 PM by ScottGu

Hi Kant,

This series actually goes into more details on how you can use the ObjectDataSource to bind against a business facade class that in turn talks to your data layer: http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

I didn't make out all of your question above (for some reason the "It seems GridView supports custom paging only by " was clipped).  Feel free to send me email directly if the above tutorial series doesn't help solve your question.

Thanks,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, September 4, 2006 11:37 PM by Srikanth
Thanks for you reply scott... May this description is might beclear.. My current page has search criteria, where user can search for the required data in sql server db. Since its huge data, i want to go by custom paging. It seems GridView supports custom paging only by "ObjectDataSource" object. Is it true? If so, "TypeName" property of ObjectDataSource is expecting a class name. Can i use My .aspx class as TypeName property value(Partial class _Default) ? If i say TypeName = "_Default", It's not recognizing. If i create a class in APP_CODE folder and pass that class name to TypeName property, then it's accepting. But i can't create classes like this way to my rest of project. My questions is there anyway to call my code behind class method by ObjectDataSource? I tried to call. but it's giving a "TypeName supplied not found". Kindly help me on this issue (custom paging & sorting with search criteria). Regards Kant

# how to use this feature when fetching records from multiple tables(master and details)

Wednesday, September 6, 2006 8:12 AM by jenny
using the efficient way of handling data described in this article . how to display 20 records at a time when the data has to be fetched from multiple tables. i.e master and detail table .Kindly help

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Tuesday, September 26, 2006 11:31 AM by levine
I have a sqldatareader which read the result set and then i convert it to dataset. After that I bind it to gridview programmatically. I found that I can't do paging on gridview. Does any one have any ideas? Levine

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Sunday, October 22, 2006 9:45 PM by YSW
hi Scott I've really been enjoying your articles as I make the move from VB 6 to .NET. I'd like to try this custom paging but I'm stuck woth SQL 2000. You did say this would work with SQL 2000 but that the proc would be a lot more complicated. By any chance do you have a proc that would do the trick? Thanks a lot.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Saturday, October 28, 2006 1:35 PM by ScottGu

Hi YSW,

Here is a good article that Scott Mitchell has written that covers custom paging with SQL 2000: http://www.4guysfromrolla.com/webtech/042606-1.shtml

Hope this helps,

Scott

# Attempting to modify getPagedLists

Tuesday, November 7, 2006 6:24 AM by Areej Abukar
thanks for the great post. I am trying to create a procedure that is similar to the dbo.getPagedLists described here to be used when paging the gridview (as described above). however, the problem is that the parameter i want to pass to the procedure is actually a where condition that varies dynamically based on user selection of search criteria. how can modify the getPagedLists to use dynamic sql? and does this solution work for sqlserver 2003? thanks alot

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, November 8, 2006 5:56 PM by UserOne
Hi Scott, How do I implement custom paging (in ASP.NET 2.0) with GridView (WITHOUT using objectdatasource) ? (In ASP.NET 1.1, I used datagrid 'custom paging' with SqlDataReader and stored procedure using code-behind file to bind datagrid to SqlDataReader and set datagrid's VirtualItemCount property.) Thank You.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, November 8, 2006 10:17 PM by ScottGu

Hi UserOne,

What you might want to-do is just continue using the DataGrid control and programmatically control paging that way.  I think the GridView unfortunately requires a DataSource control to utilize server-side paging.

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, November 8, 2006 11:57 PM by ScottGu

Hi Areej,

You could in theory use dynamic SQL within the stored procedure to dynamically generate it on the fly.

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Saturday, November 11, 2006 1:13 PM by ScottGu

Hi UserOne,

Here is a pointer to a few articles that cover how to build DataSource controls within ASP.NET: http://weblogs.asp.net/scottgu/archive/2005/12/04/432319.aspx

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, November 17, 2006 2:59 AM by Jeff

Hi Guys,

We have site that is using the GridView Control.  The number of records to be displayed are more than 200 so are usng paging.  However paging by default in Gridview uses PostBack with Javascript to do its things.  Is there anyway to make it so that instead of postback it uses GET (i.e. Querystrings)

Thanks,

Mansoor.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, November 17, 2006 10:45 AM by ScottGu

Hi Jeff,

You should be able to turn off the viewstate of the GridView and have it continue to page.  I think the page index is maintained in control-state and not view-state.

Note for richer UI customization you might also want to check out this blog post I did that shows how to implement paging using the DataList control: http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Saturday, November 18, 2006 8:02 AM by vivek

I got problem here in GridView Page navigation..

i need to press navigate option twice for next or previous Grid page

ie. if i want to go 2nd page i need to press 2 twice insted of once...

code in grdMain_PageIndexChanging is

grdMain.PageIndex = e.NewPageIndex

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Thursday, December 7, 2006 10:42 AM by Michael

I'm using your Example and I'm having problems when the category is null.

With CategoryEntries as )

SELECT ROW_Number() OVER (ORDER By ID ASC) as Row, ID as CategoryID,Name

FROM Categories

Where Root=@Root

)

The example above works, but I can't seem to figure out how to check for null in @root.

if I do this I get an error with the With:

if @root is null

With(...

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Thursday, December 7, 2006 11:56 PM by Kevin McNeish

How does the ObjectDataSource use the result from the SelectCount method to set the number of page links displayed?

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, December 8, 2006 12:44 AM by ScottGu

Hi Kevin,

I believe it uses teh SelectCount to determine the total number of rows.  The GridView can then take this and divide it by its "PageSize" property to calculate the number of pages to display.

Hope this helps,

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, December 18, 2006 3:03 AM by Pure Krome

Hi Scott,

  even though this article might be turning one years old, I still rate it up in one of the most effective and important articles you've posted in the last 12 - 18 months :)

that said, i was trying to make this even MORE efficent! Notice how the ObjectDataSource (when EnablePaging="true") requires a delegate method for the attribute 'SelectCountMethod' (eg. SelectCountMethod="LoadAllCount"). When i set break points, this always gets called AFTER the delegate method to retrieve all paged results is called. Well, i was returning both the paged results AND the totalCount (using an OUTPUT direction argument). Funny thing is, that works. Now my problem is that it only works when the delegate methods are INSTANCE methods, not static methods? why? before the instance delegate 'SelectMethod' returns an IEnumerable (i actually return a ReadOnlyCollection<CustomObject>) I set a private int field to the totalCount amount. Then, when the instance delegate method 'LoadAllCount' is called, it doesn't really hit the DB, it retrieves the result from the private field. To the consumer it looks like two calls to the DB, but in effect it's just one call.

Now for the corker (if you're still reading). My methods are STATIC methods becuase I follow the mindset that u don't need to create an (empty) instance of an object, then call Load on it to populate it's data, but instead return an instance of the object.

eg. how to load an instance of myObject via static helper methods:

MyObject myObject;

myObject = MyObject.Load(1);

ReadOnlyCollection<MyObject> myObjectCollection;

int totalRecords;

myObjectCollection = MyObject.LoadAll(1, 25, out totalRecords); // First row number, rows per page, total records in table/query.

So .. how can i _my_ results (not the ObjectDataSource converted dictionary results) from the first SelectMethod call before the Count is called?

whoa, that was long :( I can take this into email if that's easier and you are interested :)

-Pure Krome-

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, January 17, 2007 7:21 PM by Robert Clancy

I would like to use a PagedDataSource in the code behind rather than a declaritive ObjectDataSource. Setting the VirtualCount value (equivalent to SelectCountMethod) returns the following error and I don't know why: "Cannot compute Count for a data source that does not implement ICollection."

The code I'm trying is:

           Dim pagedData As New PagedDataSource()

           pagedData.DataSource = myTable

           pagedData.AllowPaging = True

           pagedData.PageSize = 20

           pagedData.VirtualCount = recordCount

           GridView1.DataSource = pagedData

           GridView1.DataBind()

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Thursday, January 25, 2007 10:50 AM by Izy

Hi scott,

I'm trying to run you app. when i try to add records i get this err:

Server Error in '/asp2' Application.

--------------------------------------------------------------------------------

Could not open new database 'C:\WEBSITES\ASPNET2PAGING\APP_DATA\PAGINGSAMPLE.MDF'. CREATE DATABASE is aborted.

An attempt to attach an auto-named database for file C:\websites\aspnet2paging\App_Data\PagingSample.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

File activation failure. The physical file name "C:\websites\aspnet2paging\App_Data\PagingSample_log.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Could not open new database 'C:\WEBSITES\ASPNET2PAGING\APP_DATA\PAGINGSAMPLE.MDF'. CREATE DATABASE is aborted.

An attempt to attach an auto-named database for file C:\websites\aspnet2paging\App_Data\PagingSample.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

File activation failure. The physical file name "C:\websites\aspnet2paging\App_Data\PagingSample_log.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

Source Error:

Line 1057:            if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)

Line 1058:                        != System.Data.ConnectionState.Open)) {

Line 1059:                this.Adapter.InsertCommand.Connection.Open();

Line 1060:            }

Line 1061:            try {

Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\asp2\d0b2f110\221cd5a3\App_Code.sxsi5glm.2.cs    Line: 1059

Stack Trace:

[SqlException (0x80131904): Could not open new database 'C:\WEBSITES\ASPNET2PAGING\APP_DATA\PAGINGSAMPLE.MDF'. CREATE DATABASE is aborted.

An attempt to attach an auto-named database for file C:\websites\aspnet2paging\App_Data\PagingSample.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

File activation failure. The physical file name "C:\websites\aspnet2paging\App_Data\PagingSample_log.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.]

  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995

  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

  System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838

  System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33

  System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628

  System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170

  System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359

  System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28

  System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424

  System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66

  System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496

  System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82

  System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105

  System.Data.SqlClient.SqlConnection.Open() +111

  MyDataLayerTableAdapters.ListsTableAdapter.Insert(String Name, String Category) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\asp2\d0b2f110\221cd5a3\App_Code.sxsi5glm.2.cs:1059

  ListManager.AddList(String listName, String category) in c:\websites\aspnet2paging\App_Code\BusinessLayer\ListManager.cs:64

  AddData.Button1_Click(Object sender, EventArgs e) in c:\websites\aspnet2paging\AddData.aspx.cs:22

  System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105

  System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107

  System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7

  System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11

  System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

--------------------------------------------------------------------------------

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, February 2, 2007 12:12 PM by Vanan

Awasome.Thrilled to see this example i got to do the same for my client.

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, February 19, 2007 10:44 AM by jun

download link doesn't work...

# Attach DB did not work

Wednesday, February 21, 2007 8:17 PM by tony

The zip file seems missing pagingsample_log.ldf file, and I can't attach the db to my sql 2005 server, could you double check and add the log.ldf into the zip fil, thanks

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, February 23, 2007 1:36 AM by ScottGu

Hi Tony,

Unfortunately I no longer have the database I'm afraid.

Sorry!

Scott

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Monday, March 5, 2007 2:41 AM by Dave

Hi Scott,

Thanks for the insightful posts!  I'm implementing a Gridview with custom paging and sorting; having the stored proc do all the work; if set the DataSource in code to a function that returns a dataset I get results; if I wire up the GridView with an ObjectDataSource pointing to the same function I don't get results in the GridView even though I see the correct SQL commands coming through on profiler.  Do I need to  so more setup on the data layer?  

Thanks

Dave

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Wednesday, March 7, 2007 12:21 AM by ScottGu

Hi Dave,

That is odd - is your GridView pointing at the DataSource control?  It would be good to isolate whether that scenairo works with a simple query, and then add the paging logic back in.

Thanks,

Scott

# Thanks!

Wednesday, March 7, 2007 5:00 AM by Maniero

informative and helpfull. Thanks! Heiko

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Friday, March 9, 2007 1:40 PM by Gary Noter

OK. I'm after the SQL sproc.

Why is not the full code shown?

I download the file and put it on 4 separate systems and no go.

Can't "attach" the database file to SQL Server 2005 because internal hard coded stuff.

I even try to excerpt your examples from 4guysfromrolla.com.

All examples fail because "RowNum" is "invalid column name".

# re: Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Tuesday, March 13, 2007 7:25 PM by Dave

Thanks Scott.  Turns out the signature of the SelectCountMethod didn't match what I was passing.   Odd that you need 2 passes to the DB to render a gridview, one to get the results, one to get the count.  My stored proc uses output params to return the count at the same time it is getting back the page of data so I have to finagle things to keep it to a single DB call.

Dave