Building Web Solutions with ASP.NET and ADO.NET by Dino Esposito, Esposito (Wintellect) Dino

From Barnes & Noble

The Barnes & Noble Review
To build better web applications (and services), use better tools. Among the many new web development tools Microsoft has introduced, two stand out: ASP.NET and ADO.NET. Each, alone, offers extraordinary power (and a not-insignificant learning curve). Used together, they really show their stuff -- and they radically change the way you build web solutions. Some developers will be left behind. Those who forge ahead are likely to earn huge payoffs -- in efficiency, in application performance, and in the breadth of problems they can solve.

Dino Esposito, who writes both MSDN Magazine's "Cutting Edge" column and the "Diving into Data Access" column for MSDN Voices, has authored an exceptionally useful guide to ASP.NET and ADO.NET for experienced developers. Esposito focuses heavily on data access and data reporting, the heart of contemporary web development. He illuminates .NET's new data-bound controls and data management options as never before.

This isn't a basic tutorial, nor is it a programmer's reference. Rather, Esposito identifies many of the key issues developers will encounter, and presents solutions -- complete with plenty of code. Along the way, he identifies many of the "big wins" ASP.NET and ADO.NET can offer experienced programmers. He also answers dozens of the questions developers have been asking about these technologies ("What's the difference between custom controls and user controls, and when do I use each?" "What's the best technique for caching data on my server?" "Can I save any of my existing ADO code?" "I really liked OLE DB providers; why'd Microsoft go ahead and change them?")

Esposito begins with an in-depth look at .NET's immensely versatile data-bound web controls. These respond to the development community's desperate need for ways to automate (or partially automate) the association of rows of data with graphical HTML elements such as dropdown lists or tables). Next, he introduces pageable data grids, and the DataGrid control -- ASP.NET's control of choice for high-functionality data reporting.

DataGrid controls give you powerful customization capabilities; HTML templates take customization even further. You'll learn how to design a column's contents to closely reflect the meaning and structure of your data; then combine HTML and server controls to design custom layouts.

Esposito starts Part II by focusing on code reuse, and showing how to go from yesterday's ASP "spaghetti code" to ASP.NET's "lasagna code" -- carefully organized, layered, and far more appetizing. For instance, he introduces ASP.NET's new code-behind technique, which simplify the way you manage complex web projects by letting you store directives and page layout in one ASPX file while you store the code that drives the page in a separate file. (Web designers work on the files they're comfortable with; programmers work on the files they're comfortable with. What a concept.)

Next, he takes a closer look at data access and reporting -- returning to the DataGrid control to show you how to squeeze even more functionality out of it than you imagined was there. There's a full chapter on ADO.NET techniques for disconnected web application development (the concept isn't new, but ADO.NET was designed from the outset for n-tier environments, and ADO wasn't.)

You'll find detailed chapters on interoperability, on ASP.NET techniques for web services development, and finally, on expositing data to .NET applications. Here, Esposito covers four approaches, from building .NET classes around made-to-measure XML schema, to writing .NET data providers. As you'd expect, each option is presented with detailed code examples.

Speaking of code, it's all in Microsoft's new C# language. But if you're sticking with VB, Visual Basic.NET equivalents for many of the book's code samples can be found on the CD-ROM (along with a complete electronic copy of the book). FYI, the code is written for ".NET Release Candidate 3." Until now, most .NET books have been written for Beta 2 (or, worse, Beta 1). This one's so close to the final 1.0 .NET release, you can taste it. If you want to get serious results with ASP.NET and ADO.NET, this book delivers. (Bill Camarda)

Bill Camarda is a consultant, writer, and web/multimedia content developer with nearly 20 years' experience in helping technology companies deploy and market advanced software, computing, and networking products and services. He served for nearly ten years as vice president of a New Jersey–based marketing company, where he supervised a wide range of graphics and web design projects. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks For Dummies®, Second Edition.

From the Publisher

Most Web applications follow a simple "3F" pattern: fetch, format, and forward to the browser. With this in-depth guide, you'll take your ASP.NET and ADO.NET skills to the next level and learn key techniques to develop more complex Web applications. Discover how to build applications for ad hoc and effective Web reporting, applications that work disconnected from the data source and use XML to communicate with non-Microsoft.NET systems, and general purpose applications that take advantage of the data abstraction of ADO.NET. Along the way, you'll learn how to take advantage of code reusability, user controls, code-behind, custom Web controls, and other time-saving techniques employed by ASP.NET experts.

Product Details

• Table of Contents
• Read a Sample Chapter

Table of Contents

Read a Sample Chapter

• Item Selection
• Enabling Item Selection
• Using the SelectedIndexChanged Event
• Selecting Rows Programmatically
• Selection and Drill-Down
• Filtered Views
• Allowing the Selection of Multiple Items
• Properties of the SuperGrid Control
• Layout of the SuperGrid Control
• Retrieving the Selected Items
• Selecting by Condition
• Changing the Background Color
• Evaluating the Condition
• Adding an Extra Column
• Aggregates and Summary Rows
• The Right Way to Query
• Creating Relations Between Tables

The DataGrid control not only is the most versatile reporting tool you have in ASP.NET, but also is likely the only tool you will want to use when building professional reports. In the previous chapters, I reviewed the basic set of features and functionalities supplied by the control. Although the DataGrid control is useful in many circumstances, you still need to write a lot of code (mostly smart and tricky code) to build complex solutions.

The DataGrid control deliberately mimics the interface and behavior of a Microsoft Excel worksheet, so programmers like you expect it to provide the same level of functionality. Microsoft was just whetting your appetite when it developed the control's object model, however, because you can actually do so much more with it than is immediately apparent from scanning its supported methods and events. In this chapter, you will learn a bunch of practical solutions and tips that take advantage of the object model. So far, we've only just touched on the concepts that we will explore in the following pages: item selection and information drill-down.

Item Selection

If you need a grid of data, you probably also need a way to let your users select a particular row of that grid and see related information. I already touched on this topic in Chapter 1 when I discussed the selection mechanism for the DataList control. Let's see how it works for the DataGrid control.

The internal mechanism for selection when using the DataGrid control is nearly identical to the one you saw in action for the DataList control. Typically, users trigger the selection by clicking a column button with the CommandName attribute set to the keyword select. The same event can also occur programmatically when the SelectedItemIndex property of the DataGrid control is set to a value greater than -1.

A selected row can have a custom style that you specify by using the SelectedItemStyle property; however, columns (including templated columns) do not support a particular template for a selected item, such as the DataList control's SelectedItemTemplate template. Only one row in the DataGrid control can be selected at any time. Multiple selection is not supported. Later in this chapter I'll show you how to work around this limitation.

Enabling Item Selection

To enable declarative (as opposed to programmatic) selection, you need to have in the DataGrid control a button column with the CommandName property set to select.

<asp:ButtonColumn CommandName="select" HeaderText="Employee"
   DataTextField="EmployeeName" />

The content of the column can be bound to one data source field as well as show static text or even a small picture. When you don't want data bound text, use the Text property instead of DataTextField. (These properties are part of the programming interface of the ButtonColumn class and have nothing to do with the actual selection.) You can place the select column anywhere in the grid and bind it to any column in the data source, just as you can for the EditCommandColumn discussed in Chapter 4.

The DataGrid control treats select command columns in a special way. When the column is clicked, the SelectedIndexChanged event fires to enable you to handle the event. The DataGrid control also applies to the selected row the graphical settings you set by using the SelectedItemStyle property, as the following example shows:

<SelectedItemStyle BackColor="blue" />

The SelectedIndex property gets and sets the index of the currently selected item. The index is 0–based and refers to the current page, not to the whole data source. The SelectedItem property returns the object that represents the currently selected item. This object is an instance of the class DataGridItem.

Styling the Selected Row

The selection style has a lower priority than any style settings you explicitly set for the column by using ItemStyle. For example, the following code draws the Position column with a reddish color, and the style is not overridden when the user selects the column:

<asp:BoundColumn runat="server" DataField="title" Heade rText="Position" > 
   <itemstyle backcolor="#ffddff" />
</asp:BoundColumn>

Note that any style attribute set by using ItemStyle or AlternatingItemStyle at the grid level (as opposed to the column level, shown in the preceding code) is overridden during selection. Figure 6-1 shows what a selected column looks like.

Figure 6-1 A DataGrid control with a selected row. (Image unavailable)

Using Formatted Text

You can use only button columns to enable selection. Button columns can contain only plain data bound text or static text. You cannot apply special templates that mix database fields with special graphical settings, and as I mentioned earlier in this chapter, templated columns do not support an ad-hoc template for selection. You can work around this limitation, however, as Figure 6-1 shows. The trick is using in- memory columns based on expressions. An expression-based column doesn't take up too much memory because it stores only the metadata of the column plus the expression.

After you retrieve the bindable data, run the following code, which adds a new, customized column. This column embeds in the text any simple HTML formatting you want.

// ds is the DataSet that has just been filled up
DataTable dt = ds.Tables["MyTable"];
DataColumn dc = new DataColumn("EmployeeName", typeof(S tring), 
   "titleofcourtesy + ‘ <b>‘ + lastname + ‘</ b>, ‘ + firstname");
dt.Columns.Add(dc);

This dynamic column is then bound to the select command column.

<asp:ButtonColumn CommandName="select" HeaderText="Employee"
   DataTextField="EmployeeName" />

Using Images

You can use small images to identify which column the user can click to select a row. Just set the Text property of the ButtonColumn class with the HTML text that points to the URL of the image.

<asp:ButtonColumn CommandName="select" 
   Text="<img alt=‘Select' src=unselected.gif>" />

For a better graphical result, you might want to explicitly set the border attribute of the <img> tag to 0 and the align attribute to absmiddle. To make the grid even more user friendly, you can make a final refinement and change the bitmap when the row is drawn in selected mode. See Figure 6-2.

Figure 6-2 You can change a bitmap when a row is redrawn to really draw attention to the selected item. (Image unavailable)

Using the SelectedIndexChanged Event

When a row is selected, the grid fires the SelectedIndexChanged event. You hook into this event in the usual ASP.NET fashion:

(Code Unavailable)

You write a handler for SelectedIndexChanged only when you need to accomplish tasks in response to the row selection—for example, changing the bitmap to reflect selection. You don't need the handler to draw the row in selected mode.

public void SelectionIndexChanged(Object sender, EventA rgs e) 
{
   SelectRecord();
}

The SelectedIndex and SelectedItem properties let you know about the selected item. To retrieve the DataRow object that produced the current DataGrid item, you can leverage the combined effect of the DataKeyField and DataKeys properties. As discussed in previous chapters, you set DataKeyField with the name of a field that accepts unique values, and DataKeys gathers those key values. You select an entry within the collection by using the index returned by SelectedIndex. The following code shows how to retrieve the key value for the selected row:

public void SelectRecord() 
{
   int nEmpID = (int) grid.DataKeys[grid.SelectedIndex ];
   DataRow dr = RetrieveRowByID(nEmpID);
}

When the SelectedIndexChanged event fires, the SelectedIndex property is guaranteed to be up-to-date.

The select command column is a button column, so you would expect it to fire the ItemCommand event when clicked. This is exactly what happens. With tracing enabled, notice that, as shown in Figure 6-3, the ItemCommand event arrives first, before the SelectedIndex property is updated.

Figure 6-3 Enabling tracing reveals the order of event firing when the user clicks the select command button. (Image unavailable)

When ItemCommand fires, SelectedIndex contains the index of the previously selected row.

Selecting Rows Programmatically

In principle, to select a row, you don't need a select command column. Although clicking a cell is the most intuitive way for a user to make a selection, you could select rows programmatically, regardless of whether a specific command column is present. To select a row programmatically, you set the SelectedIndex property to the 0–based index that corresponds to the position of the row in the current page. You cannot select a row that belongs to another page, but if you assign to SelectedIndex a value higher than the number of items in the page, no exception is thrown.

Deselecting the Selected Row

The DataGrid control does not provide an interactive way to deselect the currently selected row. You can do that only programmatically by setting the SelectedIndex property to -1. This operation will not fire any event to the page.

Can you remove the selection automatically and interactively? The intuitive approach would be to enable the user to deselect the row by clicking the row a second time—one click selects, and two consecutive clicks restore the original state. But this functionality is impossible for a simple reason: when the DataGrid control is rendered with a selected row, the cell of the select column is not clickable and cannot post back for a selection change event. The workable approach is to provide the page with a link button.

<asp:linkbutton runat="server" id="btnUnselect" enabled ="false" 
   text="Unselect" onclick="OnUnselectRecord" />

The link button is disabled or invisible until a row is selected. The state of the link button can be easily managed by using the SelectedIndexChanged event or any other code that runs after a selection is made. The link's onclick event will then deselect the row programmatically.

Selecting Rows by Using Field Values

Another interesting feature you might want to implement in your pages is the ability to select a row based on the value of a key field. In Figure 6-4, you see that no row is selected and the ID text box contains 3. (In Figure 6-4 there are also more controls that I'll talk about later on.)

Figure 6-4 Enter the employee ID and click Go, and the row with a matching primary key will be selected. (Image unavailable)

When the user clicks the Go button, the row whose primary key value matches 3 is selected and displayed in the panel beside the row. (See Figure 6-5.) The SelectedIndex property interprets any number you pass on as a page index, so when you want to select a row based on the value of a given field, you have to figure out how to turn that field value into a page index. The easiest way to accomplish this is by using the DataKeys collection. Set the DataGrid control's DataKeyField property to the name of the field, typically a key field. Next, walk your way through the DataKeys collection and compare any value found with the specified key. If a match is found, the current index of the loop is the value for SelectedIndex. The following code shows how the DataKeys approach works with a numeric employee ID field:

private int GetPageIndexFromID(int nEmpID)
{
   int nRetValue = -1;
   for (int i=0; i<grid.DataKeys.Count; i++)
      if (nEmpID == (int) grid.DataKeys[i])
      {
         nRetValue = i;
         break;
      }
   return nRetValue;
}

This approach is not perfect. It does not work when you need to evaluate a more complex expression that involves more fields, and the search is limited to the items currently displayed.

Figure 6-5 Enter the employee ID and click Go to select the row with a matching primary key. (Image unavailable)

Selection and Drill-Down

When you have a lot of information to display and decide to split the presentation into two (or more) steps, you will greatly appreciate the grid's ability to select rows. For example, the user first reviews a quick list of records in which only summary information is shown. Then he selects one particular row and asks for more information about it.

To handle this information drill-down, use the SelectedIndexChanged event, which is ideal for catching the user's request for additional information about a record. Then turn the clicked grid item into a more manageable DataRow object. To retrieve the ADO.NET object that lies behind a grid row, you can use one of two approaches. In the first approach, you obtain a DataView object from the data source and use the Find method on the primary key. If the data source is not already a DataView object, you can easily build one from a DataTable object. The Find method, however, does not return a DataRow object directly; it returns the index of the row in the DataView object that contains the primary key value specified. It returns a null value if the primary key value does not exist.

If this approach does not work well for you—for example, you find it too restrictive because of the exclusive use of primary keys—a good alternative is to use the Select method of the DataTable object. The Select method evaluates an expression and returns an array with the matching DataRow objects.

// ds is the DataSet just filled up
DataTable dt = ds.Tables["MyTable"];
DataRow[] a = dt.Select("EmployeeID=" + nEmpID.ToString ());

The cardinality of the resulting array depends on the nature of the expression. If the expression has to match only against the values of the primary key, the array will be empty or contain at most one row. For this reason, using the following code makes some sense. The try block assumes that the array has exactly one row. The code falls into the catch section only if no row is found.

try {
   view.EmployeeID = a[0]["EmployeeID"].ToString();
   view.TitleOfCourtesy = a[0]["TitleOfCourtesy"].ToSt ring();
   view.FirstName = a[0]["firstname"].ToString();
   view.LastName = a[0]["lastname"].ToString();
   view.Title = a[0]["title"].ToString();
   statusbar.Text = "Ready";
}
catch (Exception exc) {
   view.ClearAll(); 
   statusbar.Text = "<b>Error occurred: </ b>" + exc.Message;
}

The preceding code snippet uses the contents of the selected DataRow object to populate the fields of a user control. All the text boxes you see grouped in Figure 6-5 are the constituent controls of a user control named ViewPanel.

<expo:ViewPanel runat="server" id="view" />

The ViewPanel user control exposes the Text property of each text box as an individual property named for the represented field. To clear the whole panel (for example, when an item is deselected), you don't have to clear all the fields one at a time. All you have to do is call the ClearAll method exposed by the control. This example is a practical application of what you learned about pagelets and reusability in Chapter 5.

Filtered Views

Let me point out a tricky issue that you might face when working with grids that show filtered data views. Suppose that you use a DataView object as the data source of a DataGrid control and that you apply a filter on the view so that the set of records you see by using the grid does not match the actual number of rows in the original table. In the application shown in Figure 6-5, you can set a filter string to restrict the rows displayed in the grid. A filter string is an expression that may contain column names combined with operators and constants. For example, the filter string shown below denotes all the rows where the value of the employeeid field is greater than 3:

employeeid > 3

The filter string is assigned to the DataView object built on top of the table and used to display the content of the DataGrid control. You assign the filter string to the DataView object's RowFilter property and then link the DataView object with the grid, as shown in the following code:

DataView dv = new DataView(ds.Tables["MyTable"]);
dv.RowFilter = txtFilterString.Text;
grid.DataSource = dv;
grid.DataBind();

The same results could also be achieved using the DataViewManager object, which is a sort of centralized handler of table settings. The DataViewManager object works on all the tables in a DataSet object and returns the default view for the given table. The code below shows how to set a filter using the DataViewManager object:

DataViewManager dvm = ds.DefaultViewManager;
dvm.DataViewSettings["MyTable"].RowFilter = txtFilterSt ring.Text;

Let's review the behavior of the DataTable object's Select method when a filter is set. The rub lies in the fact that Select always works on the underlying table and is unaffected by any filter you might build atop the table. So the sample application could paradoxically locate and display information about records that aren't in the grid's view. This subtle problem could easily become a serious security issue if the filter has been applied to limit the activity of the current user.

To work around this problem (which is by design), you can concatenate the filter string and the Select object's expression by using the Boolean AND operator. This is exactly what the "Restrict Search To The Current Filtered View" check box does in the sample application.

String strFilter = "";
if (bSearchOnFilter.Checked)
   strFilter = (txtFilterString.Text != "" 
      ? txtFilterString.Text + " AND " : "");
DataRow[] a = dt.Select(strFilter + "EmployeeID=" + nEm pID.ToString());

This code is fully demonstrated in the EmployeeManager.aspx and EmployeeManagerWithImages.aspx sample applications which are available on the companion CD. The results are shown in Figure 6-5.

Allowing the Selection of Multiple Items

The DataGrid control does not support the selection of multiple items in the current page, much less the whole data source. Nevertheless, a lot of Web sites out there provide this functionality. For example, Web sites that let the user create a mailbox show messages in a table of rows. Each row contains a check box for selection, and a link at the bottom of the page allows the user to execute actions on the selected rows. In ASP.NET, the table can easily be obtained using the DataGrid control. After you add an extra column with a check box and figure out how to expose the information behind a row, you are really close to creating a multiselection grid.

A multiselection grid is relatively easy to build as a constituent part of the page. You insert a DataGrid control with a templated column (to provide the check box) and then write all the necessary event handlers. In this section, I'll be doing something slightly different and more complex but a lot more reusable: I'll build a new DataGrid control that automatically provides the check box column, a custom footer with predefined functions, and a collection that returns all the items currently selected in the current page. I have indeed chosen a very fancy name for this new control: the SuperGrid control.

Properties of the SuperGrid Control

Just like the PowerGrid control of Chapter 5, the SuperGrid control is a grid that automatically provides advanced sorting and pagination. In addition, it places at your disposal the extra properties shown in Table 6-1.

Table 6-1 Multiselection Properties of the SuperGrid Control

Property	Description
AllowMultiSelect	A Boolean value that enables and disables the multiselection feature. False by default.
AllowMultiSelectFooter	A Boolean value that enables and disables a custom footer with grid-specific functions. It overrides the user-defined footer, if any. False by default.
SelectedItems	Returns a collection of DataGridItem objects, each of which corresponds to a selected item in the current page. Null by default.

The following directive is needed to enable the use of the SuperGrid control in ASP.NET pages. You can change the TagPrefix attribute when you want to.

<%@ Register TagPrefix="expo" Namespace="BWSLib" Assemb ly="SuperGrid" %>

The following code snippet, instead, demonstrates how to use the control. The output of the code is shown in Figure 6-6.

<expo:SuperGrid id="grid" runat="server" 
   AutoGenerateColumns="false"
   AllowMultiSelect="true"
   AllowMultiSelectFooter="true"
   DataKeyField="employeeid"
   OnUpdateView="UpdateView">
   <Columns>
      <asp:TemplateColumn HeaderText="Name" SortExpre ssion="lastname">
      <itemtemplate> <%# 
         DataBinder.Eval(Container.DataItem, "titleo fcourtesy") + " <b>"
         + DataBinder.Eval(Container.DataItem, "last name") + "</b>, "
         + DataBinder.Eval(Container.DataItem, "firs tname") %>
      </itemtemplate>
      </asp:TemplateColumn>
      <asp:BoundColumn DataField="title" HeaderText=" Position" 
         SortExpression="title, employeeid" />
      <asp:BoundColumn DataField="hiredate" HeaderTex t="Hired" 
         SortExpression="hiredate, employeeid" 
         DataFormatString="{0:d}" />
      <asp:BoundColumn DataField="country" HeaderText ="Country" 
         SortExpression="country" />
   </Columns>
</expo:SuperGrid>

Figure 6-6 The SuperGrid control in action. The first column enables selection. The footer bar lets you clear all selections. (Image unavailable)

Layout of the SuperGrid Control

As you see in Figure 6-6, the DataGrid control has an extra column not mentioned in the code we just examined. It is a templated column that displays a check box. The SuperGrid control adds this column dynamically when the AllowMultiSelect property is set to true. In Figure 6-6, note the customized footer with an Unselect link button. You enable this control-specific footer by using the AllowMultiSelectFooter property. It overrides any footer you might have specified in the grid's declaration. The grid you create in the ASP.NET page is automatically and programmatically completed with a templated column and a footer when multiselection is enabled.

Adding the Select Column

To show a check box in the column's cells, you have two options. You can write a new custom column class or, more simply, you can create a dynamic templated column. (I discussed dynamic columns in Chapter 3.) The .NET Framework provides the Page object's LoadTemplate method, which takes a virtual (not physical) path to a user control (an ASCX file) and uses the control's contents to populate the column. You can also create column templates from in-memory strings by saving them to a temporary file. Finally, you can even create a dynamic template using a new class that inherits from ITemplate. This is how the select column is created in the sample.

The creation of the extra column takes place during the grid's initialization phase and is fired from within the Init event handler.

(Code Unavailable)

When the Init event is raised, the control has already been associated with the page, making it possible for you to access properties and methods on the Page object.

public void OnInit(Object sender, EventArgs e)
{
   if (AllowMultiSelect)
      AddSelectColumn();
}

AddSelectColumn is an internal member function that creates a column with two templates: ItemTemplate and FooterTemplate. The former provides the check box for selecting the row. The latter provides a custom button bar with selection commands such as Unselect. The following code listing demonstrates the creation of the select column:

private void AddSelectColumn()
{
   TemplateColumn tc = new TemplateColumn();
   tc.ItemStyle.BackColor = Color.SkyBlue;
   tc.ItemTemplate = new SuperGridColumnTemplate();
   Columns.AddAt(0, tc);
}

Adding the Footer Template

In our example, a footer template is needed to host the link buttons that will execute grid-specific actions—for example, deselecting all the selected items in the current page. The following code shows the steps for adding a custom footer to the select column. It creates an instance of an ITemplate-based class for the footer template and assigns the resulting object to the column's FooterTemplate property.

tc.FooterTemplate = new SuperGridFooterTemplate();

At this point in our example, the Unselect link button would appear at the bottom of the select column and look pretty dull, as shown in Figure 6-7.

Figure 6-7 A straightforward but not very visually stimulating link button. (Image unavailable)

You might want to manipulate the footer quite a bit to turn it into a more specific status bar with links, labels, and—why not—drop-down lists. I designed the SuperGrid control to provide a made-to-measure footer with an Unselect link button. This footer clears all other footer cells you might have. You enable the grid-specific footer by using the Boolean property AllowMultiSelectFooter. The next code listing shows how the SuperGrid control sets up the grid's footer to make it look like the footer in Figure 6-6.

(Code Unavailable)

The Unselect button in the built-in footer invokes the SuperGrid control's public method named ClearSelection. So to easily integrate your existing footer with the deselect feature of the SuperGrid control, add a new link button that explicitly invokes the ClearSelection method.

Binding the OnClick Event Handler

So far in our example, the link button defined in the footer template isn't bound to code. The link button declaration is incomplete and lacks an event handler for the OnClick attribute:

<asp:linkbutton runat=server text=Unselect id=lnkUnselect />

Unfortunately, you cannot bind the OnClick attribute by using a method within the template code. If you add an attribute such as OnClick=OnUnselect to the previous declaration, the control compiles successfully but a run-time error occurs as soon as you open the ASP.NET page. The rub is that the ASP.NET run time expects to find the definition of the method within the template. Having the method defined as a public member of the SuperGrid class is not enough. You can try inserting into the template string a block such as the following:

<script runat=server>
public void OnUnselect(object sender, EventArgs e)
{
   // Do something
} 
</script>

The problem, however, is just shifted. Now the ASP.NET run time locates the click handler but still fails with any object or method code you invoke that is not part of the template. What is really needed here is a binding between the link button in the footer template and code defined within the SuperGrid control. This code can't take the form of a declaration.

Any template is seen as a container control separated from the hosting page, so you cannot see methods and objects within the context of the DataGrid control, and you are denied access to the link button via its ID. The ID lnkSelect means nothing to the ASP.NET run time if it is called from within the grid's context. To get hold of the living instance of the link button, the SuperGrid code needs to search for it within the footer item container. This can be easily done in the ItemCreated handler when the item being created is the footer.

if (itemType==ListItemType.Footer && 
      AllowMultiSelectFooter && AllowMultiSelect)
{
   //Look for a link button called "lnkSelect" in the conte xt 
   // of the grid item that represents the footer
   LinkButton lb = (LinkButton) e.Item.FindControl("ln kSelect");
 
   // Now you hold the living instance of the link 
   //button in the footer and can bind it to any code in the 
   // context of the SuperGrid control
   lb.Click += new EventHandler(OnUnselect);
 
   // Other code here...
}

Calling FindControl on the DataGridItem object that represents the footer template returns a valid LinkButton object with the specified ID. Utilizing the DataGridItem object is the key to bringing a reference to the link button in the context of the SuperGrid control. Now its Click event can be easily bound with any method of the SuperGrid control. The OnUnselect handler ends up calling the public method ClearSelection.

public void OnUnselect(Object sender, EventArgs e) {
   ClearSelection();
}
 
public void ClearSelection()
{
   foreach(DataGridItem dgi in Items)
   {
      // The check box is the first control in the first cell
      CheckBox cb = (CheckBox) dgi.Cells[0].Controls[ 0];
      cb.Checked = false;
   }
}

Clearing the selection is as easy as locating the check box control in the controls hierarchy and unchecking it.

Retrieving the Selected Items

The SuperGrid control exposes the items selected in the current page by using a custom property named SelectedItems, which returns an array of DataGridItem objects. SelectedItems is implemented as a read-only data member. The get accessor walks through the grid's Items collection and adds to the ArrayList object only those items whose CheckBox control is checked.

public ArrayList SelectedItems {
   get {
      if (!AllowMultiSelect) return null;
 
      ArrayList a = new ArrayList();
      foreach(DataGridItem dgi in Items)
      {
         CheckBox cb = (CheckBox) dgi.Cells[0].Controls[0];
         if (cb.Checked)
            a.Add(dgi);
      }
      return a;
   }
}

A client page uses the SelectedItems property as follows:

(Code Unavailable)

Figure 6-8 (on page 195 in the next section) shows a sample page that adds the selected items to a cart. The cart is represented by a list box control that has some extra code for removing items. When the user adds items to the cart, the code also checks to see whether the item is already present in the cart.

Using the DataGridItem Object

Each displayed item in a DataGrid control is programmatically exposed using the DataGridItem class. This exposure applies to client rows as well as to nonclient items such as the header, footer, and pager bar. The client items are stored in the Items collection. The SelectedItems property of the SuperGrid control returns a subset of the Items collection. A DataGridItem object, though, is not a DataRow object and does not owe its content to a data source. How can you retrieve the actual row of data behind a DataGrid control item?

The DataGridItem has a number of interesting properties. One of the first that is likely to get your attention is ItemIndex, but it merely returns a 0–based index indicating the position of the row in the page. Another cool property is DataSetIndex, which returns the absolute index of a row in the data source. You can then use this index on the DataTable object to selectively access the DataRow used to populate the grid item.

// ds is the DataSet that has just been filled up
DataTable dt = ds.Tables["MyTable"];
DataRow row = dt.Rows[dgi.DataSetIndex];

Alternatively, as you learned in previous chapters, you can use the ItemIndex property to access the key value for a row if you use both the DataKeyField and DataKeys properties.

You expect the DataGridItem property to represent only the row object you are searching. However, handle this property with extreme care because in a few event handlers, it is not yet initialized and points to null objects.

Maintaining Selections Across Pages

The SuperGrid control does not support selection across pages. The structure of the control and its way of using postback events makes effective coding difficult. Although you can implement selection across pages in a pageable control, I recommend that you code cross-page selection according to the design of your application.

The sample application shown in Figure 6-8 handles cross-page selection. It saves references to the items selected in a given page to an external control, for instance, a list box. This control is then used as an intermediate cart that serves as the real application repository for selections. By controlling the insert process (to prevent the addition of the same item twice) and adding the ability to remove items from the cart, you can obtain an effective selection solution without too much effort—and without spending too much time changing the state management policies of the DataGrid control. The full source for the supergrid.cs, multiselect.aspx, and multiselect.cs applications is available on the companion CD.

Figure 6-8 A multiple selection application that uses the SuperGrid control. (Image unavailable)

Selecting by Condition

In all the techniques discussed so far, the selection of a control is manual and interactive, occurring only when the user clicks it. What about enhancing the control a little bit to make it support selection by condition?

When the selection feature is enabled in a DataGrid control, your ultimate goal is to retrieve one or more DataRow objects. In the normal process of selection, you use the grid to provide a friendly user interface. When you want to pick up all the rows that match a certain condition, you don't need the grid to help with the selection process, although it is helpful in providing visual feedback about the matching rows.

When you need to process all the rows that meet certain criteria, you can use the Select method on the DataTable object. Select returns an array of DataRow objects without the involvement of the DataGrid control. I'm going to show you how to force a DataGrid control to draw all the rows that match a condition with a unique background color to indicate selection. The grid will not expose these rows through a collection because you can retrieve them by using the DataTable object.

The ability to highlight rows by condition is governed by two more custom properties on the SuperGrid control that we haven't discussed yet. They are RowSelectFilter and SelectBackColor. I'll add these properties to the programming interface of the SuperGrid control.

public String RowSelectFilter {
   get { return (String) ViewState["RowSelectFilter"]; }
   set { ViewState["RowSelectFilter"] = value; }
}
private Color m_SelectBackColor = Color.SkyBlue;
public Color SelectBackColor {
   get { return m_SelectBackColor; }
   set { m_SelectBackColor = value;}
}

I made the RowSelectFilter property persistent across page requests by using the control's ViewState collection. RowSelectFilter holds the string that represents the condition. The role of SelectBackColor is quite self-explanatory—it represents the color to use for the background of the row.

Changing the Background Color

Implementing the select-by-condition feature requires two important steps. First, you evaluate the condition for the row being drawn. Second, you change the background color for the row. The second step is the easiest, so I'll explain it first.

You can override the background color of a DataGrid control item by hooking into the ItemCreated event. You make sure that the item type is either Item or AlternatingItem, and then you set the BackColor property of the item. (This code snippet must then be expanded to include the code that actually evaluates the condition.)

if (itemType == ListItemType.Item || 
   itemType == ListItemType.AlternatingItem) 
{
   DataRowView drv = (DataRowView) e.Item.DataItem;
   if (drv != null && RowSelectFilter != "")
   {
      if (bMeetSomeCriteria)
         e.Item.BackColor = SelectBackColor;
         // Can set other style properties here...
   }
}

Evaluating the Condition

If the condition is not subject to dynamic changes, evaluating it is straightforward. A generic and variable condition is a bit more complicated to evaluate. What will the expression look like? The easiest approach you can take is to support any expression that is acceptable for data columns and for the DataTable object's Select method. This is a great approach for two reasons: you already have the tools you need, and a rather powerful language, in data-binding expressions. The following are typical expressions you can evaluate:

country = ‘USA'
hiredate < #1/1/1994#

These expressions can be enriched with Boolean and arithmetic operators, some functions (Len, Substring, Convert, Iif), and ad-hoc operators such as LIKE and IN. (See the MSDN documentation for more information about data-binding expressions.) How do you evaluate these expressions?

A very promising tool seems to be the DataBinder.Eval method. Unfortunately, Eval understands only those expressions that evaluate to properties or column fields. Therefore, to use Eval, you must add a new expression-based column to the data source. After you add a new column, however, you have no further reason to stick to Eval. Also, Eval is not a particularly lightweight method.

An alternative approach that does not require the creation of a column is based on the DataTable object's Select method. As the code following demonstrates, you select all the rows that meet the criteria, then compare each of them to the current data item until a match is found:

if (itemType == ListItemType.Item || 
   itemType == ListItemType.AlternatingItem) 
{
   DataRowView drv = (DataRowView) e.Item.DataItem;
   if (drv != null && RowSelectFilter != "")
   {
      DataTable dt = drv.Row.Table;
      DataRow [] a = dt.Select(RowSelectFilter);
      foreach(DataRow dr in a)
         if (dr == drv.Row)
         {
            e.Item.BackColor = SelectBackColor;
            // Can set other style properties here...
            break;
         }
   }
}

This code works fine, but consider that Select can take a while to complete and might return a bunch of rows. In addition, this code calls Select for each item and alternating item. There has to be a better way.

Adding an Extra Column

As discussed in previous chapters, an expression-based column is not very expensive and does not result in the storage of new data. Its only cost is evaluating the expression when you attempt to read the value, so it is ideal for our purposes. The approach we'll take is to pad the data source with a newly added, sneaky column, defined as follows:

DataColumn dc;
dc = new DataColumn("RowSelectFilter", 
   typeof(bool), 
   RowSelectFilter);

The column will be a Boolean column with a hard-coded name—say, RowSelectFilter. Its contents are dynamically determined by evaluating the expression set through the RowSelectFilter property. Whenever you access a row of the RowSelectFilter column, the expression is evaluated and results in a Boolean value. Because the column is added internally to the SuperGrid control code, it is invisible to the user. Assuming that you have such a column, the code that highlights a grid item changes as follows:

if (itemType == ListItemType.Item || 
   itemType == ListItemType.AlternatingItem) 
{
   DataRowView drv = (DataRowView) e.Item.DataItem;
   if (drv != null && RowSelectFilter != "")
   {
      if ((bool) drv["RowSelectFilter"])
         e.Item.BackColor = SelectBackColor;
   }
}

One problem left to solve, but it is the trickiest. How and when do you create the RowSelectFilter column? The RowSelectFilter column must be up and running when the DataGrid control is rendered and, of course, cannot be created prior to setting up the data source. To create the column, you need to access the contents of the DataSource property, extract the collection of columns, and add the new one. If you perform this task before the grid is rendered, you end up executing the task too many times. On the other hand, creating the column when the RowSelectFilter property is set is risky because nothing can guarantee that at that time the DataSource property points to a valid and non-null object. A better time to create the RowSelectFilter column is when the DataSource property is set. But this solution also isn't free of a little drawback: you must always set the RowSelectFilter property before you bind the grid to the data source and order the refresh.

Detecting when a given property is assigned—DataSource in this case—is not that difficult from within a control. You simply override the property, as shown in the following code snippet:

public override object DataSource {
   get {return base.DataSource;}
   set {base.DataSource = value;}
}

This code defines an override for the DataSource property that is identical to the original property. Now customizing the set accessor is as easy as adding some lines of code:

set { base.DataSource = value;
 
   // Custom code
   DataTable dt = null;
   if (DataSource is DataTable)
      dt = (DataTable) DataSource;
   else if (DataSource is DataView)
      dt = ((DataView)DataSource).Table;
 
   DataColumn dc;
   try {
      if (dt.Columns.Contains("RowSelectFilter"))
         dt.Columns["RowSelectFilter"].Expression = RowSelectFilter;
      else {
         dc = new DataColumn("RowSelectFilter", 
            typeof(bool), RowSelectFilter);
         dt.Columns.Add(dc);
      }
   }
   catch {RowSelectFilter="";}
}

The code first ascertains the type of data source. (As mentioned earlier in this chapter, the sample code supports only data sources that are DataView or DataTable objects. You can easily enhance the code to make it support the DataViewManager class. Supporting DataReader and collection classes is much trickier.)

After you hold the living instance of the DataTable object that populates the DataGrid control, you add a new column named RowSelectFilter. If the column already exists—for example, because you already added it—you only change the value of its Expression property.

Figure 6-9 shows the SuperGrid control in the context of our sample application. You can declaratively set all the properties pertinent to the select-by-condition feature. The full source code for the SuperGrid.cs, ByCondition.aspx, and ByCondition.cs applications is available on the companion CD.

(Code Unavailable)

Figure 6-9 A version of the SuperGrid control that selects rows by condition. (Image unavailable)

Aggregates and Summary Rows

To round out our discussion of advanced DataGrid controls, let's examine complex reports in which you have summary rows with partial totals. The sample application extracts from the Northwind database information about the customers and orders. The grid displays all the orders issued in a given year, sorted by customer. Each row contains the total price of the order, which is already an aggregate value because the order is stored as a collection of items. As shown in Figure 6-10, the summary row is inserted between customers and contains the total number of orders and the total price of all orders.

Figure 6-10 A more complex DataGrid control showing partial totals and summary rows. (Image unavailable)

The Right Way to Query

Don't let the tremendous number of methods and events in a DataGrid control fool you. A DataGrid control is really best suited for displaying tabular data. Although it provides many tools for combining the code you use to build the grid, these tools rarely affect the structure and content of the data. Despite this limitation, the DataGrid control does let you significantly change the layout of the cells displayed. What implications does this functionality have for your code?

The DataGrid control cannot add summary rows on the page because it has not been designed to—period. But if you have extra rows in the data source, you can modify the cell layout so that the whole row looks like a summary row. An important guideline to follow when you work with the DataGrid control is to provide the control with pre-processed data that closely resembles the final expected output.

Creating Relations Between Tables

Let's see how to query for all the orders in the Northwind database, grouped by year and customer. Given a year, the following SQL command selects all the orders issued by customers. Only the sum of all item prices for each order is displayed. Figure 6-11 shows the typical result set when the query generates.

SELECT o.customerid, od.orderid, SUM(od.quantity*od.uni tprice) AS price 
   FROM Orders o, [Order Details] od
   WHERE Year(o.orderdate) = @nYear AND od.orderid=o.o rderid
   GROUP BY o.customerid, od.orderid
   ORDER BY o.customerid

Figure 6-11 The results of a query displaying the total price of each order. (Image unavailable)

Notice in Figure 6-11 that the price column contains the total amount for each order. The summary row would add all the orders for a customers and also display the name of the customer. There are two basic ways of doing this, one of which uses the SQL language.

The GROUP BY clause of the SELECT statement in the T-SQL language provides the WITH ROLLUP extension that adds predefined summary rows to the result set. Of course, such a summary row has the layout of all other columns, but the content of each column can be customized to some extent. The following statement illustrates how to modify the previous command to allow for summary rows. Figure 6-12 shows the result set.

SELECT 
   CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE ‘(Total)' END 
      AS AllCustomersSummary,
   CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END 
      AS IndividualCustomerSummary,
   SUM(od.quantity*od.unitprice) AS price 
   FROM Orders o, [Order Details] od
   WHERE Year(o.orderdate) = 1998 AND od.orderid=o.ord erid
   GROUP BY o.customerid, od.orderid WITH ROLLUP
   ORDER BY AllCustomersSummary

GROUPING is the T-SQL aggregate function that works in conjunction with ROLLUP in the body of a GROUP BY clause. The use of GROUPING causes a new column to be added to the result set. This column contains a value of 1 if the row has been added by the ROLLUP operator—therefore, it is a summary row. Otherwise, the column has a value of 0. By using a CASE..WHEN..END statement, you can merge this new column with the grouping column. For example, the T-SQL statement below creates a new column called AllCustomersSummary which normally contains the value of the CustomerID column and the string "(Total)" in all rows created by grouping on that column.

CASE GROUPING(o.customerid) WHEN 0 
   THEN o.customerid 
   ELSE ‘(Total)' 
END AS AllCustomersSummary

Figure 6-12 The results of a query displaying the total price of each order. (Image unavailable)

By using the WITH ROLLUP feature, you get data from the source already in a format suitable for display. In the rest of the chapter, though, I'm going to illustrate an alternate, more flexible, but less lightweight, approach. It exploits some new features of ADO.NET (such as data relations) and lets you use summary rows with any number of columns. Key to this approach is the fact that summary rows are stored in a separate result set.

Based on the SQL Server 2000 Northwind database, the query in the preceding code, run for the year 1998, results in 270 records and 81 customers. The next query shows how to get this second result set, with the item totals for each order listed in the summary row. The results are shown in Figure 6-13.

SELECT c.customerid, 
      c.CompanyName, 
      Total=SUM(od.quantity*od.unitprice), 
      TotalOrders=COUNT(DISTINCT od.OrderID)
   FROM Customers c, [Order Details] od, Orders o
   WHERE Year(orderdate) = @nYear 
      AND c.CustomerID = o.CustomerID 
      AND o.OrderID = od.OrderID 
   GROUP BY c.CompanyName, c.CustomerID
   ORDER BY c.customerid

Figure 6-13 The results of the query that provides the data for the summary row. (Image unavailable)

With the ADO.NET classes, you don't have to merge these two result sets because they can be more effectively processed in memory during the grid rendering. I grouped the queries in a new stored procedure that takes one input argument—the year.

CREATE PROCEDURE Orders_Summary_By_Customers_And_Year
   (@nYear int)

The data adapter runs the stored procedure and returns two result sets. The former result set is used to populate the data grid; the latter serves to fill up summary rows.

da.Fill(ds, "Orders");
ds.Tables["Orders1"].TableName = "OrdersSummary";

As an alternative, you can use table mappings to predefine the names for multiple result sets. For example:

da.TableMappings.Add("Orders1", "OrdersSummary");
da.Fill(ds, "Orders");

Adding Summary Rows

The DataGrid control does not allow you to add table rows at run time. Even if you could figure out a way to do so, often you would be adding rows to pages that already have the maximum number of rows. These new rows wouldn't affect the way in which the grid extracts the items for a given page. To prevent any problem with rows and pages, you add extra rows directly to the data source. Ideally, you add an extra blank row between the blocks of records with different customer IDs, but making an insertion requires you to scroll the whole result set. You could more effectively append rows, set the customerid field as appropriate, and then sort. After sorting, each semi-blank row—the summary row—is magically in place.

foreach(DataRow row in dtOrdersSummary.Rows)
{
   DataRow blank = dtOrders.NewRow();
   blank["CustomerID"] = row["CustomerID"];
 
   // Application- specific trick. Figure out a reliable
   // way to identify the summary row later while drawing it ems
   blank["OrderID"] = -1;
   dtOrders.Rows.Add(blank);
}

The key strategy in the preceding code is inserting information in the summary row that simplifies your detection of it later when you need to display the summary row. During the grid rendering, you hook into the ItemCreated event and check the contents of the row being drawn. If the row is the summary row, the graphical layout changes to display information from the OrdersSummary table. In our example, I decided to mark the summary row with a -1 value in the OrderID field. (This decision is application-specific.) Figure 6-14 shows how the grid looks at this point.

Figure 6-14 The grid with summary totals. An OrderID field of -1 identifies a summary row. (Image unavailable)

Populating the Summary Row

The layout (font, background color, and number of cells) and contents of the summary row must be modified. Any graphical update can be done in the ItemCreated event. For example, you can group the first two cells and render the whole row with a white background and bold fonts.

DataRowView drv = (DataRowView) e.Item.DataItem;
if ((int) drv["OrderID"] == -1)
{
   e.Item.BackColor = Color.White; 
   e.Item.Cells[2].Font.Bold = true;
   e.Item.Cells[2].HorizontalAlign = HorizontalAlign.R ight;
   e.Item.Cells.RemoveAt(1); // the order# column
   e.Item.Cells[0].ColumnSpan = 2;
}

The way you bind the cells with summary text depends on the type of the grid columns involved. If they are templated columns, you simply set the Text property of the desired cell with the desired text. For other column types, including bound columns, the binding approach is a little more complicated. In the case of a bound column, the association between the cell text and the data is handled in the ItemDataBound event. The ItemDataBound event fires after ItemCommand. As a result, any text you set in ItemCreated is soon overwritten by ItemDataBound. To work around this, hook into the ItemDataBound event.

Retrieving Summary Data

When the ItemDataBound event is processed, the grid is working on a row taken from the Orders in-memory table. When the row appears as a summary row, you need to replace this information with data coming from the OrdersSummary table. How do you get the corresponding summary row? Create a data relation between the two tables, which makes this retrieval quite straightforward.

DataColumn dc1 = dtOrders.Columns["CustomerID"];
DataColumn dc2 = dtOrdersSummary.Columns["CustomerID"];
DataRelation dr = new DataRelation("OrdersAndSummary", dc1, dc2);
ds.EnforceConstraints = false;
ds.Relations.Add(dr);

A DataRelation object creates a relationship between two tables that have a common column. Both tables must be part of the same DataSet object. After the relation is set, you get from each row of the parent table the array of child rows by calling the GetChildRows method.

DataRelation dr = new DataRelation("OrdersAndSummary", 
dc1, dc2, false);

You set the relation between the two tables upon loading. During the ItemDataBound event, you obtain the corresponding summary row with the following, surprisingly simple, code:

DataRowView drv = (DataRowView) e.Item.DataItem;
DataRow[] a = drv.Row.GetChildRows("OrdersAndSummary");
DataRow drSummaryRow = a[0]; // Only one row selected by design
// Build the string to display
e.Item.Cells[1].Text = strTextToDisplay;

The final result of this code is shown in Figure 6-15. The full source code for the SuperGrid.cs, Summary.aspx, and Summary.cs applications is available on the companion CD. Note that the sample application also combines the summary rows with the select-by-condition feature I examined earlier in the chapter.

Figure 6-15 The SuperGrid control used to display summary rows and partial totals. (Image unavailable)

Conclusion

In this chapter, I examined techniques and tools for building sophisticated, professional reports using the DataGrid control. I also suggested the importance of having effective SQL code. The effectiveness of the SQL code has to be measured not only in terms of performance and query execution plans but also in terms of the capabilities supplied by the objects you will use on the Web server. ADO.NET classes offer a lot, and balancing the workload between SQL Server and the .NET Web server has never been so attainable.

Reporting does not exhaust the range of functionality provided by Web applications. In Chapter 7, I'll focus on disconnected applications, caching, and batch update.