Recipe 1.15 Formatting Columnar Data in a DataGrid

     

Recipe 1.15 Formatting Columnar Data in a DataGrid

1.15.1 Problem

You need to format dates and numbers in your DataGrid controls.

1.15.2 Solution

Use the DataFormatString attribute of the asp:BoundColumn tag.

1. Within the .aspx file that contains the DataGrid control, add a BoundColumn tag with the appropriate DataFormatString attribute for each column you want to format.

2. If the DataFormatString does not provide the flexibility you need to format your data, use the ItemDataBound event instead to gain greater flexibility.

Figure 1-19 shows the appearance of an example DataGrid with the Publish Date and List Price columns formatted for dates and currency, respectively. Examples Example 1-42 through Example 1-44 show the .aspx and code-behind files for an application that produces this result.

Figure 1-19. Formatting columnar data in a DataGrid output

1.15.3 Discussion

The formatting of dates and numbers in a DataGrid is performed with the DataFormatString attribute of the asp:BoundColumn element. The general format of the formatting string is { A : B } , where A is the zero-based index number of the property the format applies to (this is generally ) and B specifies the format.

Numeric formats can be any of the following. Most numeric formats can be followed by an integer defining the number of decimal places displayed.

Format character	Description
C	Displays numeric values in currency format
D	Displays numeric values in decimal format
E	Displays numeric values in scientific (exponential) format
F	Displays numeric values in fixed format
G	Displays numeric values in general format
N	Displays numeric values in number format
X	Displays numeric values in hexadecimal format

Time/date formats can be any combination of the following:

Format character

Formatting can be applied when using data binding to any other control ”including text boxes, repeaters, and the like ”by passing the same format string described in this recipe as the third parameter of the DataBinder.Eval method. For example:

 DataBinder.Eval(Container.DataItem, " PublishDate ", "{0:MMM dd, yyyy}") DataBinder.Eval(Container.DataItem, " ListPrice", "{0:C2}") 

Formatting data in this manner can be costly in terms of performance. A less costly approach is shown next .

If the DataFormatString does not provide the flexibility you need to format your data, the ItemDataBound event can be used to provide total flexibility in the data presented. As with most events in ASP.NET, the ItemDataBound event is passed two parameters. The first argument is the sender of the event. In this case it will simply be the DataGrid . The second argument is the event arguments. This parameter (by default named e ) provides a reference to the item that has just been data bound. By using this argument, each column in the row that has just been data bound can be accessed and the data formatted as required. There are virtually no limits to the reformatting that can be done using the ItemDataBound event. The following code provides an example of using the ItemDataBound event to format the Publish Date and List Price columns in our example.

Be sure to remove any DataFormatString properties from the BoundColumn elements when using this method of formatting. The additional data conversions and formatting will result in a performance hit as well as potential confusion if the formatting is coded differently.

 Private Sub dgBooks_ItemDataBound(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _ Handles dgBooks.ItemDataBound Const DATE_PUBLISHED_COL As Integer = 1 Const LIST_PRICE_COL As Integer = 2 Dim cell As TableCell Dim datePublished As Date Dim listPrice As Single  'make sure the item data bound is an item or alternating item since   'this event is also called for the header, footer, pager, etc. and   'no formatting is required for these items   If ((e.Item.ItemType = ListItemType.Item) Or _   (e.Item.ItemType = ListItemType.AlternatingItem)) Then   'get the date published that was placed in the datagrid during data   'binding and reformat it as required   cell = CType(e.Item.Controls(DATE_PUBLISHED_COL), TableCell)   datePublished = CType(cell.Text, Date)   cell.Text = datePublished.ToString("MMM dd, yyyy")   'get the list price that was placed in the datagrid during data   'binding and reformat it as required   cell = CType(e.Item.Controls(LIST_PRICE_COL), TableCell)   listPrice = CType(cell.Text, Single)   cell.Text = listPrice.ToString("C2")   End If  End Sub 'dgBooks_ItemDataBound 

 private void dgBooks_ItemDataBound(Object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { const int DATE_PUBLISHED_COL = 1; const int LIST_PRICE_COL = 2; TableCell cell = null; DateTime datePublished; Single listPrice;  // make sure the item data bound is an item or alternating item since   // this event is also called for the header, footer, pager, etc. and   // no formatting is required for these items   if ((e.Item.ItemType == ListItemType.Item)   (e.Item.ItemType == ListItemType.AlternatingItem))   { // get the date published that was placed in the datagrid during data   // binding and reformat it as required   cell = (TableCell)(e.Item.Controls[DATE_PUBLISHED_COL]);   datePublished = Convert.ToDateTime(cell.Text);   cell.Text = datePublished.ToString("MMM dd, yyyy");   // get the list price that was placed in the datagrid during data   // binding and reformat it as required   cell = (TableCell)(e.Item.Controls[LIST_PRICE_COL]);   listPrice = Convert.ToSingle(cell.Text);   cell.Text = listPrice.ToString("C2");   }  } // dgBooks_ItemDataBound 

Example 1-42. Formatting columnar data in a DataGrid (.aspx)

 <%@ Page Language="vb" AutoEventWireup="false" Codebehind="CH01DatagridWithFormattedColumnsVB1.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH01DatagridWithFormattedColumnsVB1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>DataGrid With Formatted Columns - ASPX</title> <link rel="stylesheet" href="css/ASPNetCookbook.css"> </head> <body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0"> <form id="frmDatagrid" method="post" runat="server"> <table width="100%" cellpadding ="0" cellspacing="0" border="0"> <tr> <td align="center"> <img src="images/ASPNETCookbookHeading_blue.gif"> </td> </tr> <tr> <td class="dividerLine"> <img src="images/spacer.gif" height="6" border="0"></td> </tr> </table> <table width="90%" align="center" border="0"> <tr> <td><img src="images/spacer.gif" height="10" border="0"></td> </tr> <tr> <td align="center" class="PageHeading"> DataGrid With Formatted Columns In ASPX (VB) </td> </tr> <tr> <td><img src="images/spacer.gif" height="10" border="0"></td> </tr> <tr> <td align="center"> <asp:DataGrid id="dgBooks" runat ="server" BorderColor="000080" BorderWidth="2px" AutoGenerateColumns="False" width="100%"> <HeaderStyle HorizontalAlign="Center" ForeColor="#FFFFFF" BackColor="#000080" Font-Bold=true CssClass="TableHeader" /> <ItemStyle BackColor="#FFFFE0" cssClass="TableCellNormal" /> <AlternatingItemStyle BackColor="#FFFFFF" cssClass="TableCellAlternating" /> <Columns> <asp:BoundColumn HeaderText="Title" DataField="Title" /> <asp:BoundColumn HeaderText="Publish Date" DataField="PublishDate" ItemStyle-HorizontalAlign="Center"  DataFormatString="{0:MMM dd, yyyy}" />  <asp:BoundColumn HeaderText="List Price" DataField="ListPrice" ItemStyle-HorizontalAlign="Center"  DataFormatString="{0:C2}" />  </Columns> </asp:DataGrid> </td> </tr> </table> </form> </body> </html> 

Example 1-43. Formatting columnar data in a DataGrid code-behind (.vb)

 Option Explicit On Option Strict On '----------------------------------------------------------------------------- ' ' Module Name : CH01DatagridWithFormattedColumnsVB1.aspx.vb ' ' Description: This class provides the code behind for ' CH01DatagridWithFormattedColumnsVB1.aspx ' '***************************************************************************** Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples Public Class CH01DatagridWithFormattedColumnsVB1 Inherits System.Web.UI.Page 'controls on form Protected dgBooks As System.Web.UI.WebControls.DataGrid '************************************************************************* ' ' ROUTINE: Page_Load ' ' DESCRIPTION: This routine provides the event handler for the page load ' event. It is responsible for initializing the controls ' on the page. '------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Dim dbConn As OleDbConnection Dim da As OleDbDataAdapter Dim ds As DataSet Dim strConnection As String Dim strSQL As String If (Not Page.IsPostBack) Then Try 'get the connection string from web.config and open a connection 'to the database strConnection = _ ConfigurationSettings.AppSettings("dbConnectionString") dbConn = New OleDb.OleDbConnection(strConnection) dbConn. Open ( ) 'build the query string and get the data from the database strSQL = "SELECT Title, PublishDate, ListPrice " & _ "FROM Book " & _ "ORDER BY Title" da = New OleDbDataAdapter(strSQL, dbConn) ds = New DataSet da.Fill(ds) 'set the source of the data for the datagrid control and bind it dgBooks.DataSource = ds dgBooks.DataBind( ) Finally If (Not IsNothing(dbConn)) Then dbConn.Close( ) End If End Try End If End Sub 'Page_Load End Class 'CH01DatagridWithFormattedColumnsVB1 End Namespace 

Example 1-44. Formatting columnar data in a DataGrid code-behind (.cs)

 //---------------------------------------------------------------------------- // // Module Name: CH01DatagridWithFormattedColumnsCS1.aspx.cs // // Description: This class provides the code behind for // CH01DatagridWithFormattedColumnsCS1.aspx // //**************************************************************************** using System; using System.Configuration; using System.Data; using System.Data.OleDb; namespace ASPNetCookbook.CSExamples { public class CH01DatagridWithFormattedColumnsCS1 : System.Web.UI.Page { // controls on form protected System.Web.UI.WebControls.DataGrid dgBooks; //************************************************************************ // // ROUTINE: Page_Load // // DESCRIPTION: This routine provides the event handler for the page // load event. It is responsible for initializing the // controls on the page. //------------------------------------------------------------------------ private void Page_Load(object sender, System.EventArgs e) { OleDbConnection dbConn = null; OleDbDataAdapter da = null; DataSet ds = null; String strConnection = null; String strSQL = null; if (!Page.IsPostBack) { try { // get the connection string from web.config and open a connection // to the database strConnection = ConfigurationSettings.AppSettings["dbConnectionString"]; dbConn = new OleDbConnection(strConnection); dbConn.Open( ); // build the query string and get the data from the database strSQL = "SELECT Title, PublishDate, ListPrice " + "FROM Book " + "ORDER BY Title"; da = new OleDbDataAdapter(strSQL, dbConn); ds = new DataSet( ); da.Fill(ds); // set the source of the data for the datagrid control and bind it dgBooks.DataSource = ds; dgBooks.DataBind( ); } // try finally { //clean up if (dbConn != null) { dbConn.Close( ); } } // finally } } // Page_Load } // CH01DatagridWithFormattedColumnsCS1 }