Templated Columns in Action

Adapting Columns to Data

Templates are good at implementing special formatting features for columns, so you should take full advantage of their flexibility to adapt column layouts so that they really reflect the nature of your data. In most cases, plain text is good enough when you want to represent information from the data source. But in certain situations, you ll want to use a custom template composed of controls whose structure is more closely aligned with the intent of your data. Want some examples of these controls? Consider Booleans, images, and arrays.

Showing Boolean Values

A Boolean field can take two possible values: true or false. In spite of its simplicity, or maybe because of it, a Boolean value does not have a unique graphical representation. So Booleans can look more user-friendly in applications when they are rendered with a yes/no or 0/1 pair. If you don t want to render Booleans with plain text, check boxes are probably the most effective alternative. Let s examine a concrete example.

The Employees table in the SQL Server 2000 Northwind database has a column named ReportsTo that contains numeric values indicating whether a given employee reports to someone. In particular, the content of the field is the employee ID of the boss and is null if the employee reports to no one. The following ASP.NET code demonstrates a templated column that uses a check box to render the content of the ReportsTo field. (Notice that this code snippet uses an unknown field name, boss, instead of the expected ReportsTo. This name is not a typo, and I ll have more to say about it in a moment.)

<asp:TemplateColumn HeaderText="Reports"> <itemtemplate> <asp:checkbox runat="server" enabled="false" checked='<%# HasBoss((int)DataBinder.Eval(Container.DataItem, "boss")) %>' /> </itemtemplate> </asp:TemplateColumn>

The CheckBox control is disabled to prevent users from clicking it and altering the state. The Checked property takes a data-binding expression that evaluates to a Boolean value returned by the user-defined function HasBoss. Figure 3-11 shows the output of the sample page.

bool HasBoss(int bossID) { if (bossID != 0) return true; return false; }

Figure 3-11

Using check boxes to render Boolean data.

Employing a user-defined function within the data-binding expression for the Checked property is not strictly necessary in our example. What really matters is that Checked is assigned a Boolean value. The ReportsTo field contains integer data, however, so casting is mandatory to satisfy the requirements of the strong typing characteristic of .NET. The following simpler code obtains the same effect:

checked='<%# Convert.ToBoolean(DataBinder.Eval(Container.DataItem, "boss")) %>

A subtle point to consider is that the ReportsTo field can contain nulls, but null values can t be converted to Booleans. To work around this potential problem, I created an expression-based field named boss, which I return instead of the original ReportsTo. The query command that populates the grid of Figure 3-11 looks like this:

SELECT employeeid, titleofcourtesy, firstname, lastname, title, ISNULL(reportsto,0) AS boss FROM Employees

The contents of the ReportsTo field are filtered by the ISNULL T-SQL function. The preceding expression replaces each null entry in the given field with 0, making the conversion to the Boolean type possible and safe. The full source code for the Booleans.aspx application is available on the companion CD.

Showing Images

The template of a column can also contain images that you can use in many ways, not the least of which is to make the user interface more attractive. If you have a field that stores image URLs, you can insert a <asp:image> tag in the template of the column and make the ImageUrl attribute of the element point to the field content.

In the previous example, I disabled the check boxes to prevent users from clicking them. But now they don t appear to represent valid pieces of information, and graying them out makes the user interface look inconsistent. I will fix this using images. Here s how you do it.

To start off, you create two little GIF files, each containing the bitmap that browsers use to represent checked and unchecked controls.

Next, you use these little pictures in lieu of disabled check box controls. The code you need looks like the following:

<itemtemplate> <asp:image runat="server" imageurl='<%# GetProperGifFile( (int) DataBinder.Eval(Container.DataItem, "boss")) %>' /> </itemtemplate>

As shown in Figure 3-12, the final result of the code is decidedly attractive and effective. (The full source code for the Images.aspx application is available on the companion CD.) The look and feel of the user interface is significantly improved because the check boxes are not grayed out. In addition, images cannot be clicked or selected and are not even a valid target for the tab. The correct GIF file is selected by using a rather straightforward function:

String GetProperGifFile(int bossID) { if (bossID != 0) return "checked.gif"; return "unchecked.gif"; }

Figure 3-12

Even though the images look like check boxes, they are elements that cannot be clicked or selected.

Showing Arrays

Master-detail schemas are common in Web development: users select a record and expect to see more details about it. Typically, providing details in this way is implemented using two interrelated DataGrid controls or a single Data Grid control to list the primary records and a child form to display additional information.

In certain situations, however, you will need to display only an array of logically related information for each record in the table for example, all the territories covered by an employee. Because a one-to-many relationship exists between employees and territories, a master-detail schema makes some sense, but setting up a master-detail schema to show only an array of strings (which is what the covered territories actually are from the code s perspective) seems like overkill. Templated columns allow you to elegantly handle this display of related information by using drop-down list controls and ADO.NET data relations.

Let s create a templated column that features a drop-down list control. How can you populate this control? Based on the structure of the Northwind database, the following SQL command returns a result set with two fields containing the ID of the employee and one of the territories she covers:

SELECT et.employeeid, t.territorydescription FROM EmployeeTerritories AS et INNER JOIN Territories AS t ON t.territoryid = et.territoryid

Figure 3-13 shows the structure of the result set.

Figure 3-13

This query displays the sales territories covered by an employee.

The concept behind the code is binding the drop-down list control to the subset of the result set that applies to a given employee. The following code associates the data source of the drop-down list with a user-defined function named GetTerritories:

<asp:TemplateColumn HeaderText="Territories"> <itemtemplate> <asp:dropdownlist runat="server" width="120px" datasource='<%# GetTerritories((DataRowView) Container.DataItem) %>' /> </itemtemplate> </asp:TemplateColumn>

The GetTerritories function takes an argument of type DataRowView. GetTerritories is passed Container.DataItem, which is a view of the data row associated with the current row in the DataGrid control. The DataGrid control, as usual, is associated with a DataTable object that stems from a query on the Employees table.

DataSet ds = new DataSet(); String strCmd = "SELECT * FROM Employees"; SqlDataAdapter da = new SqlDataAdapter(strCmd, conn); da.Fill(ds, "EmployeesTable"); grid.DataSource = ds.Tables["EmployeesTable"];

GetTerritories takes a DataRowView object, but it needs a quick way to access another DataTable object s rows that are logically related to the grid s data source via the employee ID field.

You add a second DataTable object to the same DataSet object used to populate the DataGrid control. This table, named TerritoriesTable, contains the results of the previous query on the Territories table.

StringBuilder sb = new StringBuilder(""); sb.Append("SELECT et.employeeid, t.territorydescription "); sb.Append("FROM EmployeeTerritories AS et "); sb.Append("INNER JOIN Territories AS t "); sb.Append("ON t.territoryid = et.territoryid"); // Append a second table to the same DataSet da = new SqlDataAdapter(sb.ToString(), conn); da.Fill(ds, "TerritoriesTable");

At this point, you create a DataRelation object to relate the two tables. A data relation is a logical, in-memory link that you set between two tables in the same DataSet object. A relation is established similarly to the way it is when using the INNER JOIN SQL command the relation is based on the values of a common field.

	The DataRelation object is a specific part of the ADO.NET object model. It has no direct counterpart in the ADO object model but shares a certain functional resemblance with ADO s data shaping technology.

The following code shows how to create a relation between EmployeesTable and TerritoriesTable according to the values of the employeeid field.

DataColumn dc1 = ds.Tables["EmployeesTable"].Columns["employeeid"]; DataColumn dc2 = ds.Tables["TerritoriesTable"].Columns["employeeid"]; DataRelation drel = new DataRelation("EmployeesAndTerritories", dc1, dc2); ds.Relations.Add(drel);

A DataRelation object must have a name (EmployeesAndTerritories, in this case) and, to be effective, must be added to the Relations collection of the DataSet object. Once the data relation has been successfully set, you call the GetChildRows method of the DataRow object and obtain the array of rows that matches the current value of the key field. GetChildRows returns an array of DataRow objects. GetTerritories just exploits an existing data relation to do its job.

ArrayList GetTerritories(DataRowView drv) { // Extract the underlying row from the DataRowView object DataRow dr = drv.Row; // Retrieve the child rows according to the data relation DataRow[] adr = dr.GetChildRows("EmployeesAndTerritories"); // Create and return an array made of the values in the // TerritoryDescription column ArrayList a = new ArrayList(); foreach(DataRow tmp in adr) a.Add(tmp["territorydescription"]); return a; }

As discussed in Chapter 1, an array object is good at populating a drop-down list control. Figure 3-14 shows the final result. The full source code for the Arrays.aspx application is available on the companion CD.

Figure 3-14

A templated column using a drop-down list control to show an array of related information.