Using Custom Functions and Data-Binding Syntax to Modify the Display of Data Based on Its Value

Recall that the makeup of the DataGrid can be implicitly specified by setting the AutoGenerateColumns property to True (the default), which has the effect of creating a DataGrid column for each field in the DataSource; alternatively, the makeup of the DataGrid can be explicitly specified by setting the AutoGenerateColumns property to False and specifying what DataSource columns should be included through the use of the Columns tag. In the Columns tag, you can specify the type of each DataGrid column. If you create a DataGrid column of type TemplateColumn, recall that you need to provide, at minimum, an ItemTemplate tag in the TemplateColumn. This ItemTemplate tag contains HTML and data-binding syntax used when displaying each item.

With the DataList and Repeater Web controls, there is no notion of predefined column types. Rather, the makeup of these two controls is determined by their various templates. To display a set of fields from the DataSource with the DataList or Repeater, you must use data-binding syntax.

NOTE

Recall that data-binding syntax takes the form

 <%# ... %> 

where typically a call to the DataBinder.Eval method is found within the tags. For example, to display the value of a field named title_id using data-binding syntax, you would use:

 <%# DataBinder.Eval(Container.DataItem, "title_id") %> 


Listing 11.1 contains a DataList that displays information about the titles table found in the pubs database. Specifically, it lists each book, its price, publish date, and year-to-date sales. (The astute reader will recall that in Chapter 3's Listing 3.4, a similar example was given. The main difference between Listing 11.1 and Listing 3.4 is that Listing 3.4 used a DataGrid as opposed to a DataList, and it did not display the pubdate field.)

Listing 11.1 When Using a DataList, You Must Use Data-Binding Syntax to Emit the Contents of a DataSource Field
  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4: Sub Page_Load(sender as Object, e as EventArgs)   5:   If Not Page.IsPostBack then   6:     BindData()   7:   End If   8: End Sub   9:  10:  11: Sub BindData()  12:   '1. Create a connection  13:   Const strConnString as String = "server=localhost;uid=sa;pwd=;database=pubs"  14:   Dim objConn as New SqlConnection(strConnString)  15:  16:   '2. Create a command object for the query  17:   Const strSQL as String = "SELECT * FROM titles"  18:   Dim objCmd as New SqlCommand(strSQL, objConn)  19:  20:   objConn.Open() 'Open the connection  21:  22:   'Finally, specify the DataSource and call DataBind()  23:   dlTitles.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)  24:   dlTitles.DataBind()  25:  26:   objConn.Close() 'Close the connection  27: End Sub  28: </script>  29:  30: <asp:DataList  runat="server"  31:        Font-Name="Verdana" Font-Size="10pt" RepeatColumns="3"  32:        AlternatingItemStyle-BackColor="#eeeeee">  33:   <ItemTemplate>  34:     <b><%# DataBinder.Eval(Container.DataItem, "title") %></b><br />  35:     Retailing for <%# DataBinder.Eval(Container.DataItem, "price", "{0:c}") %><br />  36:     Published on <%# DataBinder.Eval(Container.DataItem, "pubdate", "{0:d}") %><br />   37:     <%# DataBinder.Eval(Container.DataItem, "ytd_sales", "{0:#,###}") %> copies sold  38:   </ItemTemplate>  39: </asp:DataList> 

Note that the DataBinder.Eval statement uses an optional third parameter in the binding syntax. This parameter specifies the formatting for the data returned. Specifically, on line 35 the value of the price field is formatted as a currency; on line 36, the value of the pubdate field is formatted as a date; and on line 37, the value of the ytd_sales is formatted so that there will be a comma every three digits.

Figure 11.1 shows a screenshot of Listing 11.1 when viewed through a browser. Note that the ytd_sales field allows NULL values, so for books whose ytd_sales field is NULL (such as Net Etiquette), no amount is displayed for the number of books sold.

Figure 11.1. A three-column HTML table is used to display the books in the titles database table.

graphics/11fig01.gif

Displaying a Message for Books with a NULL ytd_sales Field

Often you will find that you want to display data differently in a data Web control, depending on what the value of the data is. In Listing 11.1, it would be nice to display a helpful message if no books have been sold. To accomplish this, we can create a custom function that takes as input the potential data type from the DataBinder.Eval syntax and returns a string. The return value of this function corresponds to the HTML that will be emitted. The custom function should appear in your ASP.NET Web page's server-side script block or in the code-behind page.

For our example, we'll create a custom function named DisplaySales, which will return a string and take an object as input. The reason this function's input parameter will be of type object is because the value of the ytd_sales might be one of two types: integer, when there is an integer value for the field; or DBNull, when the value of the field is NULL. If the ytd_sales field did not accept NULL values, or if we alter our SQL statement in the BindData() subroutine to only retrieve records that don't have NULL ytd_sales field values, our custom function can accept an integer input parameter. (See Listing 3.8 in Chapter 3 for an example of retrieving only those rows whose ytd_sales field is not NULL.)

NOTE

The price field of the titles table can also accept NULL values. The logic used in Listing 11.2 to display a custom message if the value of the ytd_sales field is NULL can be used to provide similar functionality for books with a NULL price.


Listing 11.2 contains an updated version of the code first presented in Listing 11.1, which displays "No copies sold" if the book's ytd_sales field is NULL.

Listing 11.2 A Custom Function Is Used to Alter the Output Based Upon the Value of the Data

[View full width]

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   5: '... The Page_Load event handler and BindData() subroutine have been omitted   6: '... for brevity. Refer back to Listing 11.1 for details ...   7:   8:   Function DisplaySales(ytdSales as Object) as String   9:     If ytdSales.Equals(DBNull.Value) then  10:        'ytdSales is NULL  11:        Return "No copies sold"  12:     Else  13:       'ytdSales is some integer value  14:       Return String.Format("{0:#,###}", Convert.ToInt32(ytdSales)) & " copies sold"  15:     End If   16:   End Function  17: </script>  18:  19: <asp:DataList  runat="server"  20:         Font-Name="verdana" Font-Size="10pt" RepeatColumns="3"  21:         AlternatingItemStyle-BackColor="#eeeeee">  22:   <ItemTemplate>  23:     <b><%# DataBinder.Eval(Container.DataItem, "title") %></b><br />  24:       Retailing for <%# DataBinder.Eval(Container.DataItem, "price", "{0:c}") %><br />  25:       Published on <%# DataBinder.Eval(Container.DataItem, "pubdate",  "{0:d}") %><br  graphics/ccc.gif/>  26:       <%# DisplaySales(DataBinder.Eval(Container.DataItem, "ytd_sales")) %>  27:   </ItemTemplate>  28: </asp:DataList> 

Listing 11.2 differs from Listing 11.1 in two ways. First, it contains the DisplaySales function that is responsible for displaying a helpful message if the ytd_sales field for a particular DataSource row is NULL (see lines 8 through 16). Second, the data-binding syntax in the DataList's ItemTemplate has been updated so that the data-binding syntax that displays the ytd_sales calls the DisplaySales function (line 26). Let's look at these two additions individually.

Let's turn our attention to the DisplaySales function. Note that it accepts an input parameter of type object (line 8), because we don't know whether the value of the ytd_sales column is an integer or NULL. In the DisplaySales function, we immediately check to determine whether the ytdSales input parameter is NULL (line 9). If it is, we return "No copies sold" (line 11); otherwise, ytdSales is converted to an integer (the Convert.ToInt32 call on line 14), and passed into String.Format so that it will be properly formatted with commas every three digits.

In our DataList declaration, a small change was made to the data-binding syntax on line 26. Specifically, we pass the value of the DataBinder.Eval method to the DisplaySales function. The string value returned by the DisplaySales function is then emitted as HTML.

CAUTION

Note that in Listing 11.1, the call to the DataBinder.Eval method to display the ytd_sales field had the optional third parameter, which formatted the value of ytd_sales, inserting a comma every three decimal places. In Listing 11.2, however, this was omitted from the DataBinder.Eval method call used to pass in a value to the DisplaySales function. This is because if the formatting parameter of the DataBinder.Eval method is present, the formatting is applied to the specified DataSource value and a string is returned. In our example, had we left in this formatting call, (that is, had line 26 appeared as

 <%# DisplaySales(DataBinder.Eval(Container.DataItem, "ytd_sales",  "{0:#,###}")) %> 

then the value passed into the DisplaySales function would be of type string. That is, if ytd_sales was NULL, then a blank string would be passed into the DisplaySales function. In this event, it checked on line 9 in Listing 11.2 to see whether the input parameter was of type DBNull would always be False, because the type would always be of type string. Furthermore, a runtime error would occur on line 14 when trying to convert a blank string or a string with commas in it into an Int32.


Figure 11.2 shows a screenshot of Listing 11.2 when viewed through a browser. Note that the books in Figure 11.1 that did not display a number of books sold now display a more sensible message: "No copies sold."

Figure 11.2. A helpful message is displayed for books that have a NULL ytd_sales field.

graphics/11fig02.gif

Using Custom Data-Binding Functions to Compute New Values

There are times when your data might not contain the precise information you want to display, but the information you do want to display can be computed from the data at hand. An everyday example would be an e-commerce Web site calculating the total cost for an order. The e-commerce database might contain information like the sales tax for each state, and the price for each item in its inventory. When a user makes a purchase, the amount to charge the user is the sum of the price of the items purchased multiplied by the applicable sales tax.

Displaying information that is computed from the values of other fields can be accomplished in two ways. One method is to perform the calculations in your SQL statement. For example, imagine for a moment that there is a book tax at 5%. If we want to display a list of books with a price that reflects the book tax, we could use the following SQL statement:

 SELECT title, price, price * 1.05 AS PriceWithBookTax  FROM titles 

To display the price of the book with the book tax included, we'd simply emit the value of the PriceWithBookTax field.

Another way to display computed values is to perform the computation in the ASP.NET Web page. This computed value can be performed in a function that is called via the data-binding syntax, passing in the values of the DataSource fields that are the operands in the computation. That is, we could write a function called ComputeBookPriceWithBookTax(bookPrice ) that simply returns a value equal to the bookPrice input parameter times 1.05. This function could then be called in the following manner:

 <%# ComputeBookPriceWithBookTax(DataBinder.Eval(Container.DataItem, "price")) %> 

WHICH METHOD IS BEST? COMPUTING A VALUE IN THE SQL QUERY OR IN THE ASP.NET WEB PAGE?

Two approaches for computing a value from the values in the fields of a database table have been presented. One approach moves the work to be done to the database, while the other leaves the work to be done in the ASP.NET Web page. Which of these two options is the best choice? If the computation is performed at the database level, the ASP.NET Web page does not have to be littered by such computations. By placing this computation in a stored procedure, the computation is encapsulated from the ASP.NET Web page, meaning that if the computation ever needs to be changed (perhaps the book tax rate increases to 6.5%), the logic only needs to be altered in one place the stored procedure. Compare that to placing the computation in the ASP.NET Web page, which would require a plethora of Web pages to be changed in the event that the logic used to perform the computation changed.

However, there are times when you, as an ASP.NET developer, cannot alter the SQL query. Perhaps you call some existing stored procedure that returns the specified data. You might want to change this stored procedure to include the computation, but the database administrator might not permit such a change. Or perhaps you are bringing in your data from an XML file, where such complex computations cannot be performed when querying.

Although the computational logic can be placed in either the SQL query (or stored procedure) or in an ASP.NET Web page, the best place for such logic, actually, is in neither the SQL query nor the ASP.NET Web page. Rather, such computations belong in a .NET component that implements the business logic for your Web site. In the article "Displaying Custom Classes in a DataGrid" (referenced in the "On the Web" section at the end of this chapter), I discuss the benefits of custom middleware objects and their benefits in designing modular Web applications. If you are unfamiliar with business objects and n-tier architecture, or are just interested in Web application architecture, I would encourage you to read this article as well as "Application Architecture: An N-Tier Approach" (also referenced in the "On the Web" section).

Let's look at an example of using the data-binding syntax and a custom function to generate new data that we'll display in a data Web control. Specifically, let's examine how we can display information on each book's gross income and how much money the author has made on royalties. The titles database table contains advance and royalty fields. advance is of SQL type money, and indicates how much money the author received as an advance. The royalty field is an integer type and corresponds to the percentage of each sale that the author is paid.

NOTE

Typically, authors only make money based on a percentage of sales of their book. The money earned from this percentage of sales is known as royalties. When the author is writing the book, the publisher commonly provides advance royalty payments to help motivate the author to continue and complete the work. These advances are borrowed against the author's future royalty earnings. Therefore, if a book has an advance value higher than the current royalty value, the publisher has paid the author more money than the author has yet earned.


In displaying the titles table in a data Web control, we might want to display a couple of pieces of information that aren't contained in the database table specifically, the amount of money the book has grossed and the amount of money the author has earned from book sales. The book's gross can be computed by multiplying the price of the book times the sales (we'll assume that the price field indicates the price at which the publisher sells the books to the book stores, not the price at which the book stores sell the books to consumers). The amount the author has earned from royalties can be computed by multiplying the gross by the author's royalty percentage. Listing 11.3 contains the code to display these two computed values.

Listing 11.3 The DataGrid Includes Columns for Each Book's Gross and the Royalties Earned by the Author

[View full width]

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4: '... The Page_Load event handler and BindData() subroutine have been omitted     5: '... for brevity. Refer back to Listing 11.1 for details ...    6:    7:   Function DisplaySales(ytdSales as Object) as String    8:     If ytdSales.Equals(DBNull.Value) then    9:        'ytdSales is NULL   10:       Return "No copies sold"   11:     Else   12:       'ytdSales is some integer value   13:       Return String.Format("{0:#,###}", Convert.ToInt32(ytdSales))   14:     End If   15:   End Function   16:   17:   Function DisplayRoyalty(royalty as Object) as String   18:     If royalty.Equals(DBNull.Value) then   19:       'royalty is NULL   20:       Return "Royalty not<br>yet determined"   21:     Else   22:       'royalty is some integer value   23:       Return royalty.ToString() & "%"   24:     End If   25:   End Function   26:   27:   Function DisplayAdvance(advance as Object) as String   28:     If advance.Equals(DBNull.Value) then   29:       'advance is NULL   30:       Return "Advance not<br>yet determined"   31:     Else   32:       'advance is some integer value   33:       Return String.Format("{0:c}", Convert.ToDouble(advance))   34:     End If   35:   End Function   36:   37:   38:   Function DisplayGross(ytdSales as Object, price as Object) as String   39:     If ytdSales.Equals(DBNull.Value) or price.Equals(DBNull.Value) then   40:       Return "$0.00"   41:     Else   42:       Dim gross as Double   43:       gross = Convert.ToDouble(price) * Convert.ToInt32(ytdSales)   44:   45:       Return String.Format("{0:c}", gross)    46:     End If   47:   End Function   48:   49:   Function DisplayAuthorsRoyaltyTake(ytdSales as Object, price as object, _   50:                                      royalty as Object) as String   51:     If ytdSales.Equals(DBNull.Value) or price.Equals(DBNull.Value) _   52:                      or royalty.Equals(DBNull.value) then   53:       Return "$0.00"   54:     Else   55:       Dim gross as Double, authorsRoyalty as Double   56:       gross = Convert.ToDouble(price) * Convert.ToInt32(ytdSales)   57:       authorsRoyalty = gross * Convert.ToInt32(royalty) / 100   58:   59:       Return String.Format("{0:c}", authorsRoyalty)   60:     End If   61:   End Function   62: </script>   63:   64: <asp:DataGrid  runat="server"   65:         Font-Name="Verdana" Font-Size="10pt"   66:         AlternatingItemStyle-BackColor="#eeeeee"   67:         AutoGenerateColumns="False" CellPadding="3"   68:   69:         HeaderStyle-HorizontalAlign="Center"   70:         HeaderStyle-Font-Bold="True"   71:         HeaderStyle-Font-Size="13pt">   72:   <Columns>   73:     <asp:BoundColumn DataField="title" HeaderText="Title" />   74:     <asp:BoundColumn DataField="price" HeaderText="Price"   75:         ItemStyle-HorizontalAlign="Right" DataFormatString="{0:c}" />   76:     <asp:TemplateColumn HeaderText="Copies Sold"   77:              ItemStyle-HorizontalAlign="Right">   78:       <ItemTemplate>   79:         <%# DisplaySales(DataBinder.Eval(Container.DataItem, "ytd_sales")) %>   80:       </ItemTemplate>   81:     </asp:TemplateColumn>   82:     <asp:TemplateColumn HeaderText="Advance"   83:              ItemStyle-HorizontalAlign="Right">   84:       <ItemTemplate>    85:         <%# DisplayAdvance(DataBinder.Eval(Container.DataItem, "advance")) %>   86:       </ItemTemplate>   87:     </asp:TemplateColumn>   88:     <asp:TemplateColumn HeaderText="Royalty"   89:             ItemStyle-HorizontalAlign="Right">   90:       <ItemTemplate>   91:         <%# DisplayRoyalty(DataBinder.Eval(Container.DataItem, "royalty")) %>   92:       </ItemTemplate>   93:     </asp:TemplateColumn>   94:     <asp:TemplateColumn HeaderText="Gross"   95:             ItemStyle-HorizontalAlign="Right">   96:       <ItemTemplate>   97:          <%# DisplayGross(DataBinder.Eval(Container.DataItem, "ytd_sales"), _   98:          DataBinder.Eval(Container.DataItem, "price")) %>   99:       </ItemTemplate>  100:     </asp:TemplateColumn>  101:     <asp:TemplateColumn HeaderText="Royalty Payout"  102:             ItemStyle-HorizontalAlign="Right">  103:       <ItemTemplate>  104:             <%# DisplayAuthorsRoyaltyTake(DataBinder.Eval( Container.DataItem,  graphics/ccc.gif"ytd_sales"), _  105:                 DataBinder.Eval(Container.DataItem, "price"), _  106:                 DataBinder.Eval(Container.DataItem, "royalty")) %>  107:       </ItemTemplate>  108:     </asp:TemplateColumn>  109:   </Columns>  110: </asp:DataGrid> 

The code for Listing 11.3 is lengthy, but the content is fairly simple to digest. Before tackling the code and DataGrid declaration in Listing 11.3, it might help to first take a look at the screenshot in Figure 11.3.

Figure 11.3. The Gross and Royalty Payout columns of the DataGrid are computed on the fly in the ASP.NET Web page.

graphics/11fig03.gif

The DataGrid, which is declared starting on line 64, explicitly lists each column displayed via the Columns tag (starting on line 72). The title and price fields are displayed using BoundColumns (lines 73 through 75). Recall that with the DataList in Listing 11.2, to display a DataSource field we needed to use data-binding syntax. With the DataGrid, we can use BoundColumn controls if we only need to display the data "as is," or only need simple formatting applied. For example, the price field in Listing 11.3 is displayed via a BoundColumn, and is formatted as currency by specifying the DataFormatString property (line 75).

The remaining five DataGrid columns are displayed using TemplateColumns. The reason TemplateColumns are used here is because we want to customize the output of the data based upon its value; hence, we need to employ data-binding syntax. To accomplish this, we have to use a TemplateColumn and specify the data-binding syntax in the TemplateColumn's ItemTemplate. For example, on lines 76 through 81 a TemplateColumn is used to display the ytd_sales field. Because this field can contain NULL values, we want to replace the values where zero copies have been sold with the text: "No copies sold." To accomplish this, we use the data-binding syntax, passing the value of the DataBinder.Eval method to the custom function DisplaySales (line 79). The DisplaySales function (lines 7 15) checks to see whether the passed-in value is NULL. If it is, the string "No copies sold" is returned (line 10); otherwise, a formatted string is returned (line 13).

NOTE

The price field might also contain NULLs. One possible enhancement to Listing 11.3 would be to have the Price column display a textual message such as "No price decided yet," as opposed to a blank string when the price field is NULL. This enhancement is left as an exercise to the reader.


Because the advance and royalty fields might also contain NULL values, the same approach is used in displaying these two fields in the DataGrid. The Advance column is specified from lines 82 through 87, and its associated display function, DisplayAdvance, can be found spanning lines 27 to 35. The Royalty column is specified from lines 88 to 93, and its associated display function, DisplayRoyalty, is defined on lines 17 25.

The next two columns in the DataGrid do not correspond to data found directly in the DataSource. Rather, the data in the Gross and Royalty Payout columns are computed from various fields in the DataSource. This computation for both columns is performed in a custom function; the Gross column value is computed in DisplayGross, whereas the Royalty Payout column value is computed in the DisplayAuthorsRoyaltyTake function.

The DisplayGross function, which is called in the data-binding syntax on lines 97 and 98, accepts two input parameters: the sales and price of the book. Because both of these database fields can contain NULL values, the DisplayGross function accepts objects as its inputs. The DisplayGross function, which starts on line 38, begins by checking whether either the sales or price for the book is a NULL value. If either are NULL, it returns the string "$0.00," because the book has failed to make any money at this point (line 40). If, however, neither are NULL, a local variable of type Double named gross is created, and assigned the value of the book's price times its sales (line 43). The function then returns the gross variable, formatted as a currency (line 45).

To compute the Royalty Payout column value, the DisplayAuthorsRoyaltyTake function is called (lines 104 106). Recall that the author's royalty earnings are computed by multiplying the book's gross by the author's royalty percentage. Because the gross is computed by multiplying the sales by the price, the DisplayAuthorsRoyaltyTake function needs to accept three input parameters: the sale and price, to compute the gross; and the author's royalty.

Again, all three of these fields can be NULL, so the DisplayAuthorsRoyaltyTake function must accept three inputs of type object. As with the DisplayGross function, the DisplayAuthorsRoyaltyTake function starts by checking whether any of its input parameters are NULL; if they are, the string "$0.00" is returned (line 53). Otherwise, first the gross is computed (line 56), and then the author's royalty is multiplied by the gross (line 57). Because the author's royalty is stored in the database as an integer, we need to divide the final result by 100 (line 57). Finally, on line 59, this computed value is formatted as a currency and returned.

Using Custom Functions to Display Non-NULL Fields

In the previous two code listings, the DataSource fields being passed to the custom display function could possibly contain NULL values. In light of this, we needed to have our custom display functions accept inputs of type object, and then check whether the incoming parameter was of type DBNull. If you want to potentially customize the display of a DataSource field based on its value, and the DataSource field cannot be a NULL value, then the custom function can specify its input parameter to be of the appropriate type.

For example, in the titles table, there is a field called pubdate, which specifies the date the book was published. Imagine that we want to display this date in a red-colored font if the pubdate value is more than one year ago from today. Because the pubdate field cannot contain NULL values, we could write our custom function to accept an input of type DateTime like so:

 Function DisplayPubDate(pubdate as DateTime) as String    If TimeSpan.Compare(DateTime.Now, pubdate.AddYears(1))     'pubdate is more than one year ago from today     Return "<font color=red>" & pubdate.ToString() & "</font>"    Else     Return pubdate.ToString()    End If  End Function 

To call this function from the data Web control in our ASP.NET Web page, we could use the following data-binding syntax:

 <%# DisplayPubDate(Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "pub date"))) %> 

Note that a call to Convert.ToDateTime is made. This is needed because the return type of the DataBinder.Eval method is object, and the DisplayPubDate function expects an input of type DateTime.

NOTE

If you are using Visual Basic .NET and you do not set the OptionStrict property in your ASP.NET Web page or code-behind file, you can omit the explicit cast to a DateTime, because Visual Basic .NET will handle this for you automatically. However, if you are using C# or a program with Visual Basic .NET where the OptionStrict property is set, you will get a compile-time error if you fail to include this explicit cast.




ASP. NET Data Web Controls Kick Start
ASP.NET Data Web Controls Kick Start
ISBN: 0672325012
EAN: 2147483647
Year: 2002
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net