ASP.NET Cookbook

1.15. Formatting Columnar Data in a DataGrid

Problem

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

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

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 0) 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

Tip

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}")

Warning

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.

Warning

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
}