The ASPx Blog

In the DotNetRocks show #246 on ASP.NET scalability, Stephen Forte mentions one of the biggest problems with 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:

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:

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

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:

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:

Set the ASPxGridView's DataSource property to the XPODataSource:

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.