Column Types

Data Pagination

Unlike the DataList control, the DataGrid control supports data pagination, that is, the ability to divide the displayed data source rows into pages. In real-world scenarios, the size of a data source easily exceeds the page real estate. So to preserve scalability on the server and to provide a more accessible page to the user, you display only a few rows at a time.

Pagination, however, requires a certain amount of work behind the scenes. The control must know how many items per page you want to display and then set up a pagination toolbar to let the user move between pages. In addition, the control must be able to download and process only the records that belong in the current page. Finally, the control must track and expose the index of the current page.

Setting up pagination for a DataGrid control is complicated because the control instance is on the Web server, not the client browser. The ASP.NET Data Grid control detects user events on the client, such as clicking a button to move to a new page. These events are automatically posted back to the Web server and processed. As a result, the browser receives a new version of the same page in which a new set of rows is shown.

What does this mean for your code? The Web server needs to know about the data source every time a postback event associated with the grid is generated. This is a key point that I ll deal with later in the chapter. For now, suffice to say that you have to figure out the best way to track the data source on the server any time processing is required, and you must provide the logic to carry out the desired tasks paging, sorting, filtering, and so on. Although ASP.NET and ADO.NET classes help you implement a lot of these tasks, they don t automatically handle them all. For this reason, I like to refer to the pagination feature of DataGrid controls as semi-automaticbuilt-in features.

Semi-Automatic Pagination

Before I discuss any further the programming details of pagination, let me spend some time explaining the overall programming interface of the DataGrid control so that you can understand the rationale behind some apparently odd design choices.

Remember that the DataGrid control just like any other ASP.NET control was designed to function on the Web as a bridge between the browser and Web server via postback events. To be effective, postback events need to coordinate state management with the client-side environment. The ASP.NET run time manages to restore the state of the page before actually invoking your initialization code (by using the Page_Load event and setting the IsPostBack property), and then it processes the event. To preserve any significant state information, the run time needs to pack the information, send it to the client, and then retrieve it when the page is posted back for further processing. The control s view state (set in the ViewState property) is the architectural element responsible for preserving and retrieving state information.

For performance reasons, a DataGrid control does not store the data source within the view state. As a result, you are responsible for retrieving and making available on the server the DataGrid control s data source whenever it is needed. Possible options for doing this include reloading the data source from the database server each time the data source is needed or storing the data source in global objects such as Session, Cache, Application, or (and why not?) ViewState or in files. What you really need to know at this point in our discussion is that the code of the DataGrid control cannot make assumptions about how the data source is stored. When a postback event occurs, the DataGrid control s DataSource property is undefined, so you are responsible for filling (or refilling) it properly and then restoring data from the storage medium appropriate for the scalability of the application.

caution

Do not access the DataGrid control s DataSource property in ASP.NET code without first taking precautionary measures, because the DataGrid control will not necessarily evaluate to a non-null object. Unless you take the appropriate counter-measures discussed later in this chapter, the DataSource property will be undefined when postback events are raised even though you correctly set the property when the page was first accessed. The DataSource property is not persisted.

So as you can see, because the DataGrid control by design cannot make assumptions about how its data source has been persisted on the server (the data source does not travel with the rest of the DataGrid control s programming interface on the Web), the control cannot implement a totally automatic engine for pagination or sorting.

To enable pagination of the DataGrid control, you need to tell the control about it. You do this through the AllowPaging property. When pagination is enabled, the rendering algorithm of the DataGrid control changes slightly. By default (the default is pagination turned off), the grid renders all the items in the data source in the order of the first item to the last. When pagination is on, the DataGrid control renders only the rows that fall in the range of the current page. The control keeps track internally of the requested page size, the subsequent page count, and the current page index. Basically, it has the built-in capability to page through records for a given page size and data source, but it requires you to correctly set up the data source each time a new page is requested.

Properties for Pagination

Table 2-6 shows the DataGrid control properties that support pagination.

Table 2-6 Pagination Properties for the DataGrid Control

Property

Description

AllowPaging

Enables or disables pagination. This property is false by default.

CurrentPageIndex

Gets or sets the current page index.

PageSize

Gets or sets the current page size.

PageCount

Gets the number of pages according to the current page size.

AllowCustomPaging

Enables or disables custom pagination. This property is false by default.

VirtualItemCount

Gets or sets the total number of items you plan to display when using custom pagination. By contrast, the item count for default pagination is obtained from the data source.

The DataGrid control supports two flavors of pagination: default and custom. With default pagination, you simply provide the whole data source; the grid handles pagination internally. With custom pagination, you are expected to store in the DataSource property only those rows to be displayed on the current page; the DataGrid control renders all the rows it finds in the Items collection and sorts them from first item to last. So you must ensure that the collection corresponds to the current page. Furthermore, the index of the current page must be explicitly set, and the DataGrid control determines the value of the PageCount property by using VirtualItemCount. I ll cover this in more detail later on.

The Pager Bar

The pager bar is an interesting and complimentary feature offered by the Data Grid control to let users easily move from page to page. The pager bar is a row displayed at the bottom of the DataGrid control that contains links to available pages. When you click any of these links, the control automatically fires the PageIndexChanged event and updates the page index accordingly.

The pager bar offers two display options: it can show a generic pair of Next and Previous buttons or a detailed series of numeric buttons, each of which points to a particular page. (See Figure 2-6.) The next code fragment illustrates these options. You can also thoroughly customize the pager bar determining its font, size, background and foreground colors, and alignment settings or even hide the standard bar and roll your own. You control the pager bar by using the PagerStyle property s Mode attribute. Values for the Mode attribute come from the PagerMode enumeration.

if (UseNumericPages.Checked) grid.PagerStyle.Mode = PagerMode.NumericPages; else grid.PagerStyle.Mode = PagerMode.NextPrev;

Figure 2-6

The pager bar s two default button options.

Page Button Count

When the pager mode is set to NumericPages, you can control the number of buttons displayed. You normally do this to avoid an endless (and thus unhelpful) list of numbers in the case of a very lengthy data source. By setting the PageButtonCount property, you define the maximum number of buttons you want the pager bar to host. The default value for PageButtonCount is 10.

grid.PagerStyle.PageButtonCount = 3;

If the DataGrid control has more pages than are specified in PageButtonCount, ellipsis buttons are displayed in the pager bar. When clicked, the ellipsis button displays the next or previous set of numeric buttons.

Page Button Text

Next and Previous are only the default labels for the pager bar. When the pager bar mode is set to NextPrev, you can set the text you want displayed for both buttons. To do so, you use any valid HTML expressions, including images.

grid.PagerStyle.PrevPageText = "<img src=prev.gif>"; grid.PagerStyle.NextPageText = "<b>Next...</b>";

Changing the labels for numeric buttons is a bit more complicated because you don t have predefined properties to set, but you can still change them. I ll discuss how later in this chapter.

Pagination in Action

To enable pagination in DataGrid controls, you must perform several tasks. First you have to enable paging at the DataGrid control level by setting the AllowPaging property to true. You must assign the desired value to PageSize to let the control know how many rows you want displayed for each page. (The default for PageSize is 10.)

Second you must make sure that the data source is properly refreshed whenever the user scrolls back and forth between pages. The DataGrid control raises the PageIndexChanged event each time a user clicks the pager bar to jump to a certain page. You must write a handler for PageIndexChanged and make it accomplish a couple of tasks.

<asp:datagrid runat="server"  AllowPaging="true" PageSize = "4" OnPageIndexChanged="PageIndexChanged"> <PagerStyle PageButtonCount="3" Mode="NumericPages" />  </asp:datagrid>

The PageIndexChanged event handler has the following prototype:

void PageIndexChanged(Object sender, DataGridPageChangedEventArgs e) { grid.CurrentPageIndex = e.NewPageIndex; // TO DO: Refresh the data source and re-bind }

In the body of the handler, you must set CurrentPageIndex to the page the user clicked. You get the index of the newly requested page by using the NewPageIndex property of the event. If needed, you can also retrieve a current instance of the button that caused the event by using the CommandSource property of the DataGridPageChangedEventArgs class.

caution

The DataGrid control does not automatically set the new page index. It only notifies the event handler of the index requested by the page. You are responsible for assigning the value to the CurrentPageIndex property. If you omit this step, you will not experience a run-time error but the DataGrid control won t page.

In addition to updating the page index, the PageIndexChanged event handler must refresh the DataGrid control. The way in which you accomplish this depends completely on your application and how you obtain the data source. Typically, you must reassign the DataGrid control s DataSource property and invoke the DataBind method to initiate HTML rendering.

grid.DataSource = CreateDataSource(); grid.DataBind();

Figure 2-7 shows a pageable DataGrid control in action.

Figure 2-7

A pageable DataGrid control

tip

Before being assigned the new page index in the body of the PageIndexChanged event handler, the CurrentPageIndex property contains the value of the previously displayed page. You can use this information for implementing special tracking features in your DataGrid control.

You use the CurrentPageIndex property to determine the currently displayed page in the DataGrid control when paging is enabled. You can also use this property to programmatically control which page is displayed. CurrentPageIndex, in fact, is a read-write property, so if you need to display a given page in the DataGrid control without any user intervention, set CurrentPageIndex to the page index you need and then rebind the data to the control. Bear in mind that the CurrentPageIndex property is zero-based.

Customizing the Pager Bar

You can shape the appearance of the pager bar by using the PagerStyle property, which evaluates to a DataGridPagerStyle object. DataGridPagerStyle exposes properties such as BackColor, Font, and BorderStyle, as well as a property for setting CSS styles. Many of these attributes apply to the pager bar as a whole, so what do you do when you want to change the style of the pager bar s individual buttons? You need to hook into the ItemCreated event.

<asp:datagrid runat="server"  AllowPaging="true" OnItemCreated="ItemCreated" OnPageIndexChanged="PageIndexChanged"> <PagerStyle PageButtonCount="3" Mode="NumericPages" />  </asp:datagrid>

The ItemCreated event occurs on the server whenever a constituent element of the DataGrid control is created. By hooking into this event, you can modify on the fly both the HTML structure and the style of the element. Since this event fires for all the elements, the first task you must perform in the handler is to determine whether the item being created is exactly what you were waiting for. You check the type of the element by using the ItemType property. A reference to the item being created is returned by e.Item. The ListItemType enumeration lists all the feasible elements supported by the DataGrid control.

void ItemCreated(Object sender, DataGridItemEventArgs e) { // Get the type of item being created ListItemType elemType = e.Item.ItemType; // Make sure it is the pager bar if (elemType == ListItemType.Pager) { // The pager bar as a whole has the following layout: // // <TR><TD colspan=X> ... links ... </TD></TR> // // Item points to <TR>. The code below moves to <TD>. TableCell pager = (TableCell) e.Item.Controls[0]; // More code goes here } }

In the overall layout of the DataGrid control, the pager bar is rendered by using a TableRow object. Within this TableRow object, you find exactly one TableCell control that spans all columns. The link buttons the user sees and perceives as the full pager bar are the contents of the TableCell control. Let s review the HTML code that is generated for the first pager bar of Figure 2-8. You can check this code at any time by snooping into the HTML source code of a page that uses a DataGrid control.

<tr style="background-color:PaleGreen;font-weight:bold;"> <td colspan="2"> <span>1</span>&nbsp; <a href="javascript:__doPostBack(...)">2</a>&nbsp; <a href="javascript:__doPostBack(...)">3</a> </td> </tr>

The settings you enter by using PagerStyle apply only to the TableRow control the ASP.NET counterpart of a <tr> tag. If you need to modify controls placed inside TableRow, you can use only ItemCreated.

TableCell pager = (TableCell) e.Item.Controls[0];

The preceding expression retrieves the TableCell control that represents the <td> tag, inside which are the pager buttons. To access the pager buttons, you need to loop through the Controls collection of the TableCell control. Note that anything generated through ASP.NET is a control, including literal content. The blanks that separate the button captions are controls as well. To skip over them, you can use, for example, a for statement with a step value of 2.

for (int i=0; i<pager.Controls.Count; i+=2) { // i.th control is pager.Controls[i] }

Now let s see how to associate a CSS stylesheet with each pager bar button and how to modify the text displayed for each element. To make the pager bar more readable, let s enclose the previous and next page numbers in square brackets and prefix the text Page to the label of the current page. Figure 2-8 shows the output. The full source code for the CustomPager.aspx application is available on the companion CD.

Figure 2-8

A customized pager bar enhances accessibility.

The content of the pager bar cell is heterogeneous. It contains n elements of which n-1 are clickable link buttons and one is a static label. Of course, this label refers to the current page. You need to distinguish among the control types to apply different settings to each. To check whether a given object is of a particular type, you can use the is operator and then cast the generic object as the needed type.

for (int i = 0; i < pager.Controls.Count; i += 2) { Object o = pager.Controls[i]; if (o is LinkButton) { LinkButton h = (LinkButton) o; h.Text = "[ " + h.Text + " ]"; } else // Can only be a Label { Label l = (Label) o; l.Text = "Page " + l.Text; } }

note

The C# is type operator checks whether an object expression is of a particular type. In Microsoft Visual Basic .NET, the Is operator has a different goal. You use it to determine whether two object variables refer to the same instance of an object. In Visual Basic .NET, to obtain the same behavior as the C# is operator, you have to resort to the TypeOf...Is operator.

Once you hold a valid instance of the link button or label, you can set any other property each provides. For example, you can assign each a new CSS style by using the CssClass property.

tip

When defining a new CSS style for a control, don t be afraid to use DHTML behaviors. Although they are supported only by Internet Explorer version 5 and later versions, all other browsers ignore them, so you ll experience no ill effects.

Replacing the Standard Pager Bar with Your Own

If for any reason you don t want your pages to display the standard pager bar, you can replace it with your own. You hide the pager bar by turning off its Visible attribute.

<PagerStyle Visible="false" />

The DataGrid control does not provide you with a built-in mechanism for plugging in a custom pager bar. But changing the structure of the page slightly allows you to add your own. In this example, the DataGrid control and your personal pager bar are two rows of the same table.

<table > <tr><td> <asp:datagrid ...> ... </asp:datagrid> </td></tr> <tr><td> personal pager here </td></tr> </table>

Depending on the look you desire for the grid, some of the settings you applied at the DataGrid control level have to be moved to the outer table level, for example, background color and shadow settings. The outside table also needs to have a border.

The following code shows how to transform the pager bar into a numeric text box with a button enabling the user to jump to a specified page. The user enters the page number in the text box and then clicks the Go button to jump to that page. The full source code for the PersonalPager.aspx application is available on the companion CD.

<asp:panel runat="server"> <asp:label runat="server" css text="Page" /> <asp:textbox runat="server" css width="30px" text="1" /> <asp:button runat="server" css Text="Go" OnClick="OnGotoPage" /> </asp:panel>

note

If you wanted to, you could display both the ordinary pager bar and a personal pager bar. Really, the personal pager bar is any custom toolbar appended to the bottom of the DataGrid control.

Figure 2-9 shows what the personal pager bar looks like on a Web page. Notice in the figure which graphical element has so far been neglected in our discussion.

Figure 2-9

The footer and personal pager bar in a DataGrid control.

Although the DataGrid control and the personal pager bar together form a unique element, each are distinct HTML elements and can be shown individually. To avoid showing the pager bar when the DataGrid control is not shown, you might want to consider displaying both elements at the outer table level. For example, when the DataGrid control is shown only as the result of a postback event, the personal pager bar might be not be hidden and displayed. You can make sure the elements are displayed properly by handling the Visible attribute on the outer table.

<table runat="server" visible="false" >

Note, though, that Visible is an ASP.NET attribute, so a runat="server" attribute is also needed.

Using the Footer

In a DataGrid control, the footer is hidden by default. To turn it on, you must set the ShowFooter property to true. Of course, you can also do that declaratively by using the ShowFooter attribute.

<asp:datagrid runat="server"  ShowFooter="True">

The footer style is subject to full modification via the FooterStyle property, so you can change its font, colors, and borders. One aspect you cannot change declaratively and indeed often need to change is the structure of the footer. The footer is rendered via an empty row, so it maintains the same structure as other rows. If this footer structure suits you, use the column s FooterText property to assign it HTML contents. If you want the footer to have a different number of columns or columns of different widths, you must intervene at the time the footer is created, namely in the ItemCreated event handler.

void ItemCreated(Object sender, DataGridItemEventArgs e) { ListItemType elemType = e.Item.ItemType; // Handle other types of items here if (elemType == ListItemType.Footer) { // Footer is just an empty row. Remove all // the cells but one and span over all. TableCellCollection tcc = e.Item.Cells; int nTotalCols = tcc.Count; // Use nTotalCols as tcc.Count changes // dynamically as you remove cells for (int i=0; i<nTotalCols-1; i++) e.Item.Cells.RemoveAt(1); // Only 1 cell at this time ... TableCell c = e.Item.Cells[0]; c.ColumnSpan = nTotalCols; c.Text = grid.PageCount.ToString() + " pages found."; } }

In most cases, you need a footer made up of a single cell that works as a summary row. To obtain this, you must remove at run time all cells but one, as shown in the preceding code example. Next, you set the ColumnSpan property of the remaining cell to the total number of the columns and display the summary text.

Custom Pagination

Every time the DataGrid control moves to a different page, all contents in the associated data source have to be reloaded. No matter how you retrieve the data whether from a database, an XML file, or the Session object as long as you use default pagination, you must have all that data stored in memory to successfully refresh the grid. When the data source is very large, storing it can consume a lot of resources on the Web server.

Custom pagination enables you to load only the segment of data needed to display a single page. To enable custom pagination, you set both the AllowPaging and AllowCustomPaging properties to true. Next, you provide code that handles the PageIndexChanged event.

So how is coding actually different when you enable custom pagination? The only significant difference between default paging and custom paging is the way in which the DataGrid control processes what s stored in the Items property. In the case of default paging, the DataGrid control assumes to have in memory all possible items and, based on the page size and current index, extracts a subset of items that will fit in the selected page. With custom paging, the DataGrid control always displays all contents of the Items property, so you are responsible for loading only the records that fit in the selected page. This approach clearly requires less Web server memory and looks inherently more scalable at least for large blocks of data. Let s see custom pagination in action.

Setting the Virtual Item Count

When you use custom paging, you don t need to handle the pager bar yourself. As long as you inform the DataGrid control about the virtual number of items you plan to display through all pages, you have pager bar functionality served for free, as usual. You let the DataGrid control know about the virtual item count by setting the VirtualItemCount property. You should set the property before the DataGrid control is displayed. A good place to set it is in Page_Load when IsPostBack is False.

if (!Page.IsPostBack) SetVirtualItemCount();

If your DataGrid will show the entire contents of a table say, the Products table of the SQL Server 2000 Northwind database the virtual item count is simply the total number of records found in the table.

public void SetVirtualItemCount() { // Set up the connection String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; SqlConnection conn = new SqlConnection(strConn); // Set up the command String strCmd = "SELECT COUNT(*) FROM products"; SqlCommand cmd = new SqlCommand(strCmd, conn); // Execute the command conn.Open(); int nItemCount = (int) cmd.ExecuteScalar(); conn.Close(); // Set the grid's virtual item count grid.VirtualItemCount = nItemCount; return; }

note

ExecuteScalar is a method of the ADO.NET command classes, including in particular, SqlCommand. It executes a SQL command or a stored procedure, and it returns only the scalar value on column 1, row 1 in the result set. ExecuteScalar returns the value boxed in an Object class, so casting is necessary to obtain, for example, a true integer value.

Obtaining the Page Content

With custom paging, the amount of memory allocated is limited to the number of items that fit in a single grid page. The rub is, how can you get all and only the records that fit in a particular page? Once again, the way you handle this issue is strictly application-specific. The SQL language provides no support for pagination. The only way to get records from a SQL-based data management system is by using the SELECT statement. The only way to restrict the set of rows returned is by using the WHERE clause. Therefore, you must figure out a way to retrieve the contents of a particular page based on some sort of field condition. Because no official SQL-based solution exists, any assumption you can make in your own project given the structure of your own databases is valid. In the remainder of this section, I ll examine a SQL case scenario. Consider the following SQL statement:

SELECT TOP n Fields FROM Table WHERE Key > page_related_info

caution

Very few databases support the TOP clause in the SELECT statement. The TOP clause was introduced with SQL Server version 7 and has no counterpart in SQL Server version 6.5 and Oracle. Informix SQL does provide a similar behavior through the FIRST clause. Although less flexible, the SET ROWCOUNT statement can be used as a rougher counterpart for TOP when this clause is not supported.

With SQL Server 7 and later versions, SELECT with a TOP clause returns from the specified table the first n rows that match the Boolean comparison in the WHERE clause. It goes without saying that, for our purposes, n is the page size of the DataGrid control.

The role of page_related_info is less defined. Its function has to be such that, based on the page number, you can directly access the first record entered in that page. If the table has a unique key column set with a regular series of numbers, you can calculate the first value for the key field on a given page with a simple arithmetic expression, based on the first record to display.

For example, the Northwind Products table s ProductID field contains incrementing numbers: 1, 2, 3, and so on. You can then use this field as the key column for your query. The statement that loads the records for page 4, then, looks like this:

SELECT TOP n * FROM Products WHERE productid > (n-1)*4

Page 4 contains records from 31 through 40. Even if your table does not have a column like this, you should consider adding a new one ad hoc. Doing so is not manna from heaven, though, as you must regenerate the column every time a row is deleted. Putting this all together, you can retrieve the rows needed for display as follows:

SqlDataReader dr; SqlDataReader CreateDataSource(int nPageIndex) { // Page index is assumed to be 0-based int nPageSize = grid.PageSize; int nBaseProductID = nPageSize * nPageIndex; // Set up the connection String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; SqlConnection conn = new SqlConnection(strConn); // Set up the command String strCmd = "SELECT TOP " + nPageSize + " " + "productid, productname, quantityperunit, unitsinstock " + "FROM products " + "WHERE productid >" + nBaseProductID; SqlCommand cmd = new SqlCommand(strCmd, conn); // Execute the command conn.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return dr; }

When you retrieve data frequently, the SqlDataReader class is a better option than SqlDataAdapter because the former results in a structure the data reader that is faster to load than the data set. While the data reader is in use, the associated connection is busy. The argument passed to ExecuteReader specifies that the connection must be closed when the SqlDataReader object is closed. CreateDataSource passes the SqlDataReader object to the DataGrid control, which stores the object in its DataSource property. At this point in the code, the data reader and the connection are still open. Setting DataSource does not automatically transfer the content of the data source to the DataGrid control s memory (the Items property). Until the content is bound, you cannot close the data reader and release the database connection. The following code performs this binding and closes the data reader:

grid.DataSource = CreateDataSource(0); grid.DataBind(); dr.Close();

Figure 2-10 shows a snapshot from the sample application illustrating custom paging. The full source code for the CustomPaging.aspx application is available on the companion CD.

Figure 2-10

Custom pagination was used to create the scrolling mechanism in this database.

Using Generic SQL

Let s explore two more general approaches to implementing custom pagination that are not dependent on any application-specific assumption or precondition. One is based on T-SQL cursors and the FETCH statement. The other is based on a generic and parametric SQL command made of three nested SELECT statements.

The first approach, which is based on server-side cursors, has two major drawbacks. First and foremost, server-side cursors cause a loss in performance, which is why they are a last resort solution. The second drawback is that each row you fetch via cursors is stored in a separate result set. To bind these rows to the DataGrid control, you need to merge the result sets into a single Data Table object.

A much better (but less generic) approach can be inferred from the following SQL statement, which retrieves the rows displayed on page 3, where pages have 2 records each and are sorted by the lastname field:

SELECT TOP 2 * FROM (SELECT TOP 2 employeeid, lastname FROM Employees WHERE lastname IN (SELECT TOP 6 lastname FROM Employees ORDER BY lastname) ORDER BY lastname DESC) AS tmp ORDER BY tmp.lastname

As you can see, the statement comprises three nested SELECT commands. Despite the apparent complexity, the statement takes significant advantage of the query optimization engine that comes with SQL Server 2000. The key operations that the query performs can be more easily described in the temporary tables shown below:

SELECT TOP 6 employeeid, lastname INTO t1 FROM Employees ORDER BY lastname SELECT TOP 2 employeeid, lastname INTO t2 FROM t1 ORDER BY lastname DESC SELECT * FROM t2 ORDER BY lastname

The first statement, which corresponds to the innermost nested SELECT command, copies into the temporary table t1 a subset of rows that, once sorted, will contain the rows on the page to be displayed. For example, if 2 is the page size, and you want to access page 3, then the last 2 rows of the first 2*3 rows are what you need. These 2 rows are moved to another table t2 in the reverse order and then returned.

Using temporary tables is not really effective and, more important, can create additional conflicts in a Web scenario where many concurrent requests must be processed. Nested queries provide the same outcome with much better performance, though at the price of a less readable SQL syntax.

To create a more generic SQL statement, you can parameterize three elements: the number of rows to return, the number of rows to fetch, and the field to sort by. The number of rows to return is the grid s page size (or less if the total number of rows doesn t exactly match a multiple of the page size). The number of rows to fetch is calculated by multiplying the page size by the 1-based index of the page to show. The TOP and the ORDER BY clauses of the T-SQL language do not accept parameters, so a DataGrid that wants custom pagination should prepare its query command through string formatting rather than regular SQL parameters:

// Prepare the SQL command StringBuilder sb = new StringBuilder(""); sb.Append("SELECT TOP {0} * FROM "); sb.Append("(SELECT TOP {0} employeeid, firstname, lastname "); sb.Append("FROM Employees WHERE {1} IN "); sb.Append("(SELECT TOP {2} {1} FROM Employees ORDER BY {1}) "); sb.Append("ORDER BY {1} DESC) AS tmp "); sb.Append("ORDER BY {1}"); String strCmd = sb.ToString();

The {n} placeholders are replaced with actual data through the String.Format method.

int nRowsToDisplay = grid.PageSize; int nMod = grid.VirtualItemCount % grid.PageSize; if (nPageIndex == grid.PageCount && nMod > 0) nRowsToDisplay = nMod; strCmd = String.Format(strCmd, nRowsToDisplay, strCurrentSortExpr, grid.PageSize * nPageIndex);

If the page to show is the last one, then the number of rows to display is the page size or the modulus of the total number of items and the page size. Finally, the sort expression is the current sort expression of the DataGrid, or the primary key if you don t support sorting. The full source code for the UsingGenericSql.aspx application is available on the companion CD.



Building Web Solutions with ASP. NET and ADO. NET
Building Web Solutions with ASP.Net and ADO.NET
ISBN: 0735615780
EAN: 2147483647
Year: 2002
Pages: 75
Authors: Dino Esposito

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net