One of the most frequently asked questions about .NET web programming is, how do I get a subfile-type display with paging up and running? As I'll show you in this article, the GridView ASP.NET control provided in Visual Studio 2005 supplies paging functionality similar to what is available with System i subfile support.

An example of a paging GridView is shown in Figure 1. The data that is displayed is from the SYSTABLES database file on my System i that contains approximately 9,500 records. Rather than display all 9,500 records in the gird, I configured the GridView to display 10 rows at a time. The GridView automatically added the paging navigation shown at the bottom of the grid. By clicking on a number or the "three dot" option, I navigate to another set of rows.

This type of paging is in contrast to the default behavior of the GridView, which is to simply load all of the records into the grid. Although the GridView has no problem with loading that many rows, it becomes cumbersome to work with the data; the only navigation control is a vertical scroll bar that is added to the right side of the grid when paging is not used. For a smaller set of data, it might be preferable to use the default behavior instead of paging. For example, if I anticipate that I will only display 100 rows or fewer, I would probably not use paging.

Regardless of whether you use the paging features of the GridView, it is important that you understand when the data is loaded. The GridView is similar to a "load-all" subfile on the System i. Adding the paging features does not cause the GridView to act like a "page-at-a-time" subfile. When you use the GridView, you retrieve all the data into a DataSet object that is cached in memory.

In this example, I run an SQL SELECT statement to select all the rows in the SYSTABLES table. The rows are placed into a DataSet object, which is linked to the GridView. When the GridView is displayed, it works with the data in the DataSet, which is a copy of the data in the database. The navigation features of the GridView simply navigate over the DataSet, not directly over the database. That means that you are working with a snapshot of the data that was current when the SELECT was run. It is exactly the same as if you had created a "load-all" subfile; the data in the subfile is a copy of the data, current when it was loaded. If you need to always see live data, you can do that, but the technique is more involved than what I am showing in this article, just as it is more involved to create a "page-at-a-time" subfile on the System i.

Create an Example

If you have Visual Studio 2005 installed and you have the iSeries Access OLE DB Provider on your PC, you can get an example up and running in minutes. You don't have to write any code for this example, so it does not matter if you prefer Visual Basic or C#.

To get started, open an existing website project in Visual Studio or create a new website project. Add a new web form and go to design view for the form. At this point, the form will be blank.

Go to the Visual Studio Toolbox and expand the Data section, as shown in Figure 2. Locate the SqlDataSource control and drag-and-drop it onto the blank web form; it does not matter where you drop it. You may see a visual representation of the SqlDataSource tool on the web form, as shown in Figure 2. For reasons I don't entirely understand, certain versions of Visual Studio might omit the visual representation and simply provide the Properties panel as shown in Figure 3. Regardless of what your version of Visual Studio does or does not display, click the Configure Data Source link. If you get the visual representation shown in Figure 2 but you don't see the SqlDataSource Tasks menu, click the "smart tag" icon (the little boxed arrow in the upper right of the SqlDataSource control) to display the menu. If you are working with the Properties panel shown in Figure 3, just click the link at the bottom of the panel.

The Configure Data Source wizard shown in Figure 4 is displayed. If you have an existing connection to your System i, you can select it; if not, click the New Connection button and create a new connection to your System i. You will need to select either the IBM OLE DB IBMDA400 or IBM OLE DB IBMDASQL provider for this example.

I've omitted some of the panels in the wizard that you'll see after you create the connection. Figure 5 shows where you can enter an SQL SELECT statement to fill the DataSet. For your first test, it might be wise to select a table that does not have over 10,000 rows in it. A while back I read some Microsoft documentation that indicated that their GridView was tested with data sets of 100,000 rows, and it easily handled that number of rows. However, it would take some time to run a query for that many rows, and since the point is to simply see how the paging features work, you can select any table that has more than 10 rows.

My opinion is that if you ever find that you're trying to display more than a few hundred rows, you should examine the application requirements. It is probably a much better idea to include prompts for selection criteria so that your users can narrow down the data that will be presented to them. Users are not dumb, but they often don't express their requirements in terms that we understand. For example, when a user says that he or she "needs to see all of the data," I would favor an interpretation that means they want to be able to easily access all of the data, not that all of the data must necessarily be presented at once. If users insist that they must have access to all of the data in one presentation, I would make it a point to observe their use of the application to see if it might be possible to provide some selection criteria that works for them. If what they really want is an Excel spreadsheet instead of an interactive application, then arrange to get the data downloaded into Excel.

After you've entered your reasonable SELECT statement, you can test it, as shown in Figure 6. The data shown on the Test Query panel is what you'll see in the GridView.

Add the GridView

Now that you have the data source configured, go back to the Visual Studio Toolbox. From the Data section, drag-and-drop a GridView control onto the web form. Again, it does not matter where you drop the control. Visual Studio responds by displaying a prototype GridView. In Figure 7, I selected the SqlDataSource1 data source from the GridView Tasks menu. When you select the data source, the column headers are extracted from the data set and show up in the GridView.

Figure 8 shows is a close-in look at the GridView Tasks menu. For this test, check the Enable Paging option. You can also check the Enable Sorting option if you want; that allows you to sort the data in the GridView by clicking the column headings. The sorting is done against the data set; it does not require another trip to your System i to rerun the SQL statement.

After you check the Enable Paging option, you can go to the Properties panel for the GridView, shown in Figure 9. Locate the Paging section of the properties. The default settings shown in Figure 9 provide a paging display like that shown in Figure 1.

At this point, you can simply run the web application. Depending on how much data is queried in your SELECT statement, you should soon see the first page of your GridView application. You can click the navigation links (the numbers) at the bottom of the page to go to other pages of data.

After you see the paging feature in operation, you may want to go back to the Properties panel and adjust some of the Paging options. For now, you don't have to be concerned with any of the ImageUrl properties. You may want to try other Mode options and set the PageButtonCount and PageSize to different values. You can also change the Position option to control where the navigation links are displayed on the page.

In Search of Grid Controls

If you really want to provide ASP.NET web applications that are extraordinary, you have to realize that the Microsoft GridView control is a mere shadow of what is available. For whatever reason, all the way from the old Visual Basic days until now, Microsoft has provided grid-type controls that provide merely adequate functionality.

There is a vibrant and active community of third-party vendors who specialize in providing substantially better grid controls for .NET applications. Most of these vendors have been active since the days of VB3, so there is little danger of getting stuck with an orphan product. The controls are typically licensed to each developer, with the runtime code being freely distributable. Some of the well-known vendors include Infragistics, Component One , Devexpress, and Telerik. This list is not necessarily complete and does not imply either an endorsement or intentional omission on my part. This is simply a list I found from thumbing through the first few pages of a .NET-oriented publication. I have experience with one of the vendor's products in this list, and the difference between its grid and the Microsoft GridView is substantial.

So, learn about the capabilities of the Microsoft GridView. If it is suitable for your needs, by all means, use it. However, if you are planning to move to ASP.NET for web applications, take a few minutes to visit each of the websites listed, and others that are available. Once you understand the basics of creating a DataSet, you'll be able to work with any of the third-party tools.