Tip 5: DataSet Calculations
Developers can implement dataset calculations at two levels. On a row-by-row basis you can define a calculated DataColumn. For an entire DataTable (or a collection of rows) you can compute a
SUM or
COUNT.
You can create a calculated column as follows:
string cExpr = "HourlyRate * Hours";
Dt.Columns.Add("Pay", typeof(Decimal), cExpr);
If you're using typed datasets, you can define a calculated column by setting the
Expression property in the Dataset Designer.
You can perform a calculation on all rows of a DataTable by using the
DataTable.Compute() method. Note that
Compute returns an object that you can cast to the appropriate type.
string cExpr = " SUM(Hours)";
string cCond = " OTFlag = true";
decimal nTotHrs =
(decimal)Dt.Compute(cExpr,cCond);
Finally, you can automatically calculate aggregate amounts in a parent-child relationship. Suppose you have a DataTable of clients, a DataTable of orders, and a parent-child relationship based on the client ID. You could store a calculated column in
dtClients to reflect the sum of orders for each client by using the expression
SUM(Child.OrderAmount) in the parent table. If you are utilizing typed datasets, you can specify that expression directly in the Visual Studio 2005 DataSet Designer.
Tip 6: An Overview of the ASP.NET 2.0 GridView
Most browser-based database applications will need to make use of the functionality that the new GridView control (formerly the DataGrid control) offers in ASP.NET 2.0. Developers who come from the Windows Forms environment to Web Forms will discover that the GridView control differs greatly across the two platforms. The next four tips will cover some of the more common functionality.
Figure 2 shows a basic ASP.NET GridView. The example retrieves data from the Northwind database, binds the GridView to the dataset results, defines columns and alignments and formats, and displays any row with Freight greater than $100.00 in yellow. You can implement this in five steps:
You first recreate this by dropping the GridView control from the Web Forms toolbox onto a Web Forms page. After the Web Forms Designer creates an instance of the GridView control on the page, right-click on the GridView to load the property page. Change the name (
ID) to
grdOrders, set
AllowPaging to
true,
AllowSorting to
true, and
AutoGenerateColumns to
false. If you want the alternating background color effect, expand the
AlternatingRowStyle property and set the
BackColor to the alternating color you wish.
 | |
| Figure 3: Defining columns for the ASP.NET 2.0 GridView. |
Second, because you set the
AutoGenerateColumns property to
false, you need to define the columns manually. In the property sheet, if you select the
Columns collection, Visual Studio 2005 will display a dialog that allows you to define columns (see
Figure 3). This dialog allows you to add bound fields by highlighting
BoundField, clicking Add, and setting the
DataField property on the right. Also, for any columns the user may wish to sort, you can also define the
SortExpression property.
Note that in the Gridview display in
Figure 2, some columns are right-aligned. Also notice that the two date columns (
Order Date and
Shipped Date) are formatted differently: one shows the date only, and one shows the date as well as the time, in
HH:MM format.
Once you add individual bound columns in
Figure 3, you can set the
HorizontalAlign property under the
HeaderStyle and
ItemStyle group properties. You can set the date formats in the
DataFormatString property: the values are
{0:d} for date only, and
{0:g} for date/time that displays everything but seconds. Remember that you must also set the
HtmlEncode property to
false for any columns that use
DataFormatString, because of new security functionality in ASP.NET 2.0 to prevent cross-site posting.
Remember that you must also set the HtmlEncode property to false for any GridView columns that utilize DataFormatString, due to new security functionality added to ASP.NET 2.0 to prevent cross-site posting.
|
|
Now that you've designed the GridView, you can write the code to bind the Gridview.
DataSet dsData = this.GetData();
// Run whatever process you want, for the query
// SELECT OrderID, CustomerID, OrderDate,
// ShippedDate, Freight FROM Orders ORDER BY
// OrderID
this.grdOrders.DataSource = dsData.Tables[0];
this.grdOrders.DataBind();
Finally, you can implement the function to highlight rows with Freight greater than $100. You do so by tapping into the
GridView's RowCreated event, which allows you to cast the current GridView row as a DataRowView object. You can evaluate the value of freight, and set the background color.
protected void grdOrders_RowCreated(object sender,
GridViewRowEventArgs e)
{
if (e.Row.DataItem != null) {
DataRowView drv = (DataRowView)e.Row.DataItem;
decimal nFreight = Convert.ToDecimal(drv["Freight"]);
if (nFreight> 100)
e.Row.BackColor = System.Drawing.Color.Yellow;
}
}
Note that in a production environment you'd want to abstract out the logic for freight in your business layer. You'd also want to use a CSS definition instead of hard-coding the alternate color.
Tip 7: Making Selections from the GridView
Beginners sometimes ask how to define a link for a particular row in a GridView, perhaps to launch another Web page with more details on the row (see
Figure 4).
 | |
| Figure 4: An ASP.NET 2.0 GridView, with a link to open each order. |
You can accomplish this in multiple ways. One way involves three simple steps. First, you need to open the column designer for the GridView and add a ButtonField (as opposed to a BoundField) from the list of available fields. Keep the
ButtonType property as
Link, set the
Text to
Open (or whatever text you wish), and set the
CommandName to Select.
In the third step (yes, the third, you did not miss the second step) you'll tap into the
GridView's SelectedIndexChanged event to determine the row associated with the Open link that the user clicks. You might wonder "how" specifically to determine the row. The GridView contains a design-time property called
SelectedDataKey that you can set to a unique identifier (or identifiers). In this situation, the second step will set this to
OrderID. Finally, the third step will read the value of
SelectedDataKey in the
SelectedIndexChanged event.
protected void grdOrders_SelectedIndexChanged
(object sender, EventArgs e)
{
int nOrderID = (int) this.grdOrders.SelectedDataKey.Values
["OrderID"];
// Now we can load another page with the ID
}
A final note on this: if you want to display an icon instead of a text link (maybe an "open" or "edit" icon), repeat all the above steps, but change the
ButtonType from
Link to
Image, and set the
ImageURL to the URL of the image you want to display.
In Tip 6 I showed you how to color a row based on a condition. You may also face a situation where you want to display an icon in the GridView based on a condition.
To accomplish this, you need to define a special TemplateField column in the GridView. To do so, open the actual HTML source for the Web page and add the following code as the first (or last) column in the GridView.
<asp:TemplateField >
<ItemTemplate>
<img src= '<%# GetPic(Container.DataItem) %>'/>
</ItemTemplate>
<ItemStyle Width="3px" />
<HeaderStyle Width="3px" />
</asp:TemplateField>
Your custom code will be the function
GetPic, which will receive the current GridView row as a parameter and return the name of the icon image to use: either the desired icon if the row matches the condition or a blank
.gif if the row does not match the condition. Note that you cannot return an empty string: the GridView will simply place the nasty little red 'X' in the cell.
protected string GetPic(object dataitem)
{
string cIcon = "";
// evaluate the row from the GridView
bool lFlag = Convert.ToBoolean(DataBinder.Eval
(dataitem, "ShowPictureFlag"));
if (lFlag == true)
cIcon = "SpecialPic.gif";
else
cIcon = "Blank.gif";
return cIcon;
}
Previous Page
