ASPxGridView Screencast: Massive Dataset + XPO = Fast Grid

ASP.NET Team Blog
20 November 2007

One of the biggest problems with ASP.NET scalability is loading large datasets in a paged grid.

What typically happens when displaying large datasets in a grid is that all the data is fetched instead of just the data needed for the current grid size and settings. This can be a performance problem with any dataset and it's a major problem with large datasets.

The Developer Express Data Controller (XPODataSource) solves this problem by automatically creating smart queries to fetch just the right amount of data.  Whether sorting, grouping or filtering, it will optimize queries to quickly pull only the necessary data. It also does intelligent caching so it doesn't have to run the same query if it was recently run. The XPODataSource and everything needed to run it comes with the ASPxGridView & Editors Library, so if you have the ASPxGridView component, then you have eXpressPersistent Objects (XPO) and the XPODataSource.

You can use the Devexpress Data Controller to make the ASPxGridView extremely fast and responsive with massive datasets. Everything you need is packaged with the ASPxGridView to work with large datasets. The steps aren't complicated, basically all you have to do is initialize XPO, create an XPODataSource and then hook it up to the ASPxGridView.

I've prepared a screencast and a set of written steps to show you how to hook it up. They both present the same information so you can use either or both to learn how to achieve grid speed nirvana.

Here's the screencast - I move pretty quick so it's only about 5 minutes long:

image

The written steps and the sample files are below.

Let me know which tutorial style works best, or if you found both useful. Also, please try this out with your own database and tell me how it feels to quickly navigate a huge dataset with a web grid. Nice, isn't it? ;)


How to use the XPODataSource with the ASPxGridView:

Use the steps below to enable the XPODataSource to work with an ASPxGridView:

Use Wizard for Class Generation

This is a one time step and doesn't need to be maintained. Add a new item to your project (App_Code directory). Select Persistent Classes v7.3 and give it a name. After pressing enter, the wizard appears letting you define your database connection to generate the classes from:

image

You can select the tables or just keep the default for all tables and the wizard will generate a file containing the classes.

image

The wizard generated file contains object representations of the database tables for use by the XPODataSource as you'll soon see. This file doesn't require any modification and can be closed:

image

Add Init Code

There are only two places that will require initialization code. The first is in the Global Application Class and the second is in the Page containing the XPODataSource. Start by adding a new item to your project (Global Application Class). In the Application_Start event, add the following code:

   void Application_Start(object sender, EventArgs e)
   {
        string conn = DevExpress.Xpo.DB.MSSqlConnectionProvider.GetConnectionString("servername", "username", "password", "databasename");
        DevExpress.Xpo.Metadata.XPDictionary dict = new DevExpress.Xpo.Metadata.ReflectionDictionary();
 
        dict.GetDataStoreSchema(typeof(Northwind.ServerSideGridTest).Assembly);
        DevExpress.Xpo.DB.IDataStore store = DevExpress.Xpo.XpoDefault.GetConnectionProvider(conn, DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists);
        object layer = new DevExpress.Xpo.ThreadSafeDataLayer(dict, store);
        Application.Add("XpoLayer", layer);
   }

Oliver has a deeper explanation of this code here but in general there are two helper methods to note. The first is the GetConnectionString helper method in which you define your connection string. If you're using a trusted connection, remove the username and password parameters. The second method is the GetDataStoreSchema where you define the large table that will be referenced/loaded.

The other place that requires the initialization code is in the Page_Init event of the Page you have the ASPxGridView and XPODataSource. This is explained in the next section.

Create your web page

The final step is very simple since you've done the 2 steps which are probably the hardest. Create a new web page and add an ASPxGridView and XPODataSource control (DX: Web v7.3 tab). For the XPODataSource, set the ServerMode to true and TypeName to the large table that will be displayed by the ASPxGridView:

image

Set the ASPxGridView's DataSource property to the XPODataSource:

image

Add the following Page_Init method to your page:

   using DevExpress.XPO; 
 
   UnitOfWork unitOfWork;
   private void Page_Init(object sender, EventArgs e)
   {
        unitOfWork = new UnitOfWork(Application["XpoLayer"] as IDataLayer);
        XpoDataSource1.Session = unitOfWork;
   }

 

That's it, you're done! You can now run the project and experience the fast and amazing performance of using the Developer Express Data Controller.

I've created a sample for download. The sample uses the Adventure Works LT database. If you don't have this database then you can download it from CodePlex. I recommend creating a project that uses your own database and large tables. You need to experience this change with data that makes you want to tear your hair out. Hook it up to the XPODataSource, view it with the ASPxGridView and then tell me how much better life is in the fast lane.

28 comment(s)
Linton
Linton

Nice article, thanks. I'm wondering if you plan to extend this sample to demonstrate the code needed to implement CRUD with XPODataSource

20 November, 2007
Michael Chean
Michael Chean

How about extending it for business rules?  Where do they go?

20 November, 2007
Anonymous
John Jackovin

Any further thought on doing some screencasts specifically designed for smart clients?  We have a smart client that uses your grid and retrieves a massive amount of data.  I would love to see some casts that show the unique situations encountered using web services between the client app and the data store.

20 November, 2007
Anonymous
EP

What if you want to add records or edit records? How would that work with XPO? I noticed that there are no select querys, so I'm wondering what would be the equivilant of update/delete queries. What if I want to pass certain variables to the select strings.. Or is XPO used for strictly represntation purposes.

20 November, 2007
Matthew MacSuga
Matthew MacSuga

Awesome screencast !!  I think it would be fantastic if you showed an additional one, based on the same premis but with WinForms, instead of ASP.NET.

20 November, 2007
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hi Robert,

Thanks, Insert/Update/Delete is easyily done on the grid events. Using XPODataSource, get the XPO object and then change the properties (Insert/Update/Delete) or create a new object. I'll see if we can get a sample but I recommend taking a look at the following articles:

www.devexpress.com/Help

21 November, 2007
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hi Michael,

You'd have to choose. If you have an XPO object that has a property which is calculated based on other properties/method/classes, then it's not possible to sort/group/filter by it (using the server mode).

For further information look in the XPO help, XPO forums and XPO blog.

XPO is a pretty mature product that has been released for many years and used a lot.

Thanks.

21 November, 2007
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hi Matthew,

Thanks. There is a screencast for the XtraGrid you can watch here: www.devexpress.com/.../dataManagementTest.html

21 November, 2007
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hello EP,

Please see my response to Robert as it contains a link to helpful articles on XPO.

Thanks.

21 November, 2007
Ray Navasarkian (DevExpress)
Ray Navasarkian (DevExpress)

Hi John

If you have specific suggestions for screencasts, please send me or Julian an Email and we'll do our best to organize things.

21 November, 2007
Anonymous
  ASPxGridView Screencast: Massive Dataset + XPO = Fast Grid by rssdreams

Pingback from    ASPxGridView Screencast: Massive Dataset + XPO = Fast Grid by rssdreams

21 November, 2007
Anonymous
Shankar

Hi Mehul/Robert

Nice demo.

The CRUD needs to obey laws of security at a site and be offered as part of a grid and detail with row/column access rights

Can it be specified to grid as an XML and executed at run time

24 November, 2007
Anonymous
Richard Hill

I am following this in my own project, and when I get to the database connection, if I try and access one database it only lists 4 tables ( out of around 100).   If I link to another on the same server, it shows them all.  

Coincidently (possibly) the 4 tables it shows are aspnet_paths,

aspnet_applications,aspnet_users,aspnet_roles

Any ideas how to see the rest of the tables ?

5 December, 2007
RAUL TORTIMA
RAUL TORTIMA

I currently have EntitySpaces as my main datalayer, and I like it a lot.

XPO came for free when I bought AspxGridView, so I wonder what would be faster, using ES or XPO...

17 December, 2007
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Richard,

Sounds like your querying your local SqlExpress tables. Double check the sql server name. Also check your rights. If your still able to figure it out then please contact our support team.

Thanks.

19 December, 2007
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hello Raul,

XPO would be faster because of the interface the ASPxGridView supports to use the XPODataSource. No other datasources currently support this technology yet.

Thanks.

19 December, 2007
Anonymous
anant tiwari

Hi,

i have the problem in using xpodatasource as its not giving me the wizard for setting the datasource ,userid,password, etc

i do have the component installed on my machine can you plz suggest any idea.....

Thankx for the same

19 February, 2008
Anonymous
Anant

Hi

I m unable to get the wizard when i added the xpodatasource as displayed in the clip

Thankx in advance

19 February, 2008
Anonymous
.Net World

In a previous post , I showed you how to use the powerful Server mode feature in ASPxGridView and XtraGrid

29 March, 2008
Anonymous
The ASPx Blog

Check out this 4 minute video on a very useful feature in the 2008 vol 2 release. The video/screencast

10 July, 2008
Anonymous
The ASPx Blog

In a previous post , I showed you how to use the powerful Server mode feature in ASPxGridView and XtraGrid

22 August, 2008
Anonymous
Jonh

Hi!

Can you show a XPO example code to replace this SQL command: "

Update Customer Set CustomerName="ABC" where CustomerID=123"

Thanks

26 August, 2008
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hi John,

Try this:

Customer c = session.GetObjectByKey<Customer>(123);

if(c != null) {

 c.CustomerName="ABC";

 c.Save();

}

26 August, 2008
Anonymous
Bruno Buzzi

Hi!

Great Screencast!

Isn't there a way to do the same using a table with composite (multi-column) keys?

I'm having some trouble to load the data because my table has more than 200000.

Thanks a lot!

16 September, 2008
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hi Chan, Couple of ideas I've noticed that helped me:

1. Really good desktop machine with fast hard drive and loads of memory.

2. Properly installing the VS2008 SP1 (ie, remove previous using tool and then install VS2008 SP1). Please see my blog post here:

http://tinyurl.com/6pqulq

16 September, 2008
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Hi Bruno,

Thank you, but unfortunately, if your data table has a multi-column (composite) key, the ASPxGridView won't be able to fetch the table's data in Server mode: www.devexpress.com/issue=Q105660

16 September, 2008
Anonymous
Sergio E.

Hi, interesting video and so much possibilities but, I have a quiestion...

What I need to do to use a composite query as datasource?

Some like "Select t1.c1,t1.c2,t2.c5 from table1 as t1, table2 as t2 where t1.clv=t2.clv and t2.c5=1 order by c1"

Hoy do I generate de XPObject? As far I can see, the wizzard only let yoy to generate a single table wrapper..

thanks

4 June, 2009
Mehul Harry (DevExpress)
Mehul Harry (DevExpress)

Sergio,

In XPO composite queries like this are built using Linq to XPO, there is a good example of a join query here...

www.devexpress.com/Help

19 June, 2009

Please login or register to post comments.