September 21, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Advanced Data Reporting in ASP.NET

  • October 25, 2002
  • By Dino Esposito
  • Send Email »
  • More Articles »

Sample Chapter from Building Web Solutions with ASP.NET and ADO.NET by Dino Esposito for MS Press (ISBN: 0-7356-15780)

6 - Advanced Data Reporting

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 0based 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.

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.

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:

<asp:DataGrid id="grid" runat="server" 

OnSelectedIndexChanged="SelectionIndexChanged">

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.

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 0based 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.)



Click here for larger image

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

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.



Click here for larger image

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

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.





Page 1 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel