Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 26 Productivity Tips for Managing Data (Part 1 of 2) : Page 3

Regardless of your .NET language of choice, managing data is a vital skill for most applications.

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
Figure 2: Basic 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; }

Comment and Contribute






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