Recipe 2.19. Formatting Columnar Data in a GridView

Problem

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

Solution

Use the DataFormatString attribute of the asp:BoundField element:

1. Within the .aspx file that contains the GridView control, add a BoundField element 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 2-20 shows the appearance of an example GridView with the Publish Date and List Price columns formatted for dates and currency, respectively. Examples 2-48, 2-49 through 2-50 show the .aspx and code-behind files for an application that produces this result.

Figure 2-20. Formatting columnar data in a GridView output

Discussion

The formatting of dates and numbers in a GridView is performed with the DataFormatString attribute of the asp:BoundField 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.

Table 2-2.

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:

Table 2-3.

Format character	Associated property/description	Example format pattern (en-US)
d	ShortDatePattern	MM/dd/yyyy
D	LongDatePattern	dddd, dd MMMM yyyy
f	Full date and time (long date and short time)	dddd, dd MMMM yyyy HH:mm
F	FullDateTimePattern (long date and long time)	dddd, dd MMMM yyyy HH:mm:ss
g	General (short date and short time)	MM/dd/yyyy HH:mm
G	General (short date and long time)	MM/dd/yyyy HH:mm:ss
M, M	MonthDayPattern	MMMM dd
r, R	RFC1123Pattern	ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
S	SortableDateTimePattern (based on ISO 8601) using local time	yyyy'-'MM'-'dd'T'HH':'mm':'ss
t	ShortTimePattern	HH:mm
T	LongTimePattern	HH:mm:ss
u	UniversalSortableDateTimePattern using universal time	yyyy'-'MM'-'dd HH':'mm':'ss'Z'
U	Full date and time (long date and long time) using universal time	dddd, dd MMMM yyyy HH:mm:ss
y, Y	YearMonthPattern	yyyy MMMM

Formatting can be applied when using data binding to any other controlincluding text boxes, repeaters, and the likeby passing the same format string described in this recipe as the second parameter of the Eval method. For example:

 Eval(" PublishDate ", "{0:MMM dd, yyyy}") Eval(" 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 RowDataBound event can be used to provide total flexibility in the data presented. As with most events in ASP.NET, the RowDataBound event is passed two parameters. The first argument is the sender of the event. In this case, it will be the GridView. The second argument is the event arguments. This parameter (by default named e) provides a reference to the item that has been data bound. By using this argument, each column in the row that has been data bound can be accessed and the data formatted as required. There are almost no limits to the reformatting that can be done using the RowDataBound event. The following code provides an example of using the RowDataBound event to format the Publish Date and List Price columns in our example:

 
 Protected Sub gvBooks_RowDataBound(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) 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 a data row since this event is also 'called for the header, footer, pager, etc. and no formatting is 'required for these items If (e.Row.RowType = DataControlRowType.DataRow) Then 'get the date published that was placed in the GridView during data 'binding and reformat it as required cell = CType(e.Row.Controls(DATE_PUBLISHED_COL), _ DataControlFieldCell) datePublished = CType(cell.Text, _ Date) cell.Text = datePublished.ToString("MMM dd, yyyy") 'get the list price that was placed in the GridView during data 'binding and reformat it as required cell = CType(e.Row.Controls(LIST_PRICE_COL), _ DataControlFieldCell) listPrice = CType(cell.Text, _ Single) cell.Text = listPrice.ToString("C2") End If End Sub 'gvBooks_RowDataBound 
 protected void gvBooks_RowDataBound(Object sender, System.Web.UI.WebControls.GridViewRowEventArgs e) { const int DATE_PUBLISHED_COL = 1; const int LIST_PRICE_COL = 2; TableCell cell; DateTime datePublished; Single listPrice; // make sure the item data bound is adata row since this event is also // called for the header, footer, pager, etc. and no formatting is // required for these items if (e.Row.RowType == DataControlRowType.DataRow) { // get the date published that was placed in the GridView during data // binding and reformat it as required cell = (DataControlFieldCell)(e.Row.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 GridView during data // binding and reformat it as required cell = (DataControlFieldCell)(e.Row.Controls[LIST_PRICE_COL]); listPrice = Convert.ToSingle(cell.Text); cell.Text = listPrice.ToString("C2"); } } //gvBooks_RowDataBound 

Remove any DataFormatString properties from the BoundField 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.

See Also

Search Standard Numeric Format Strings in the MSDN Library.

Example 2-48. Formatting columnar data in a GridView (.aspx)

 <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02GridViewWithFormattedColumnsVB1.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02GridViewWithFormattedColumnsVB1" Title="GridView With Formatted Columns - ASPX" %> <asp:Content  Runat="server" ContentPlaceHolder> <div align="center" > GridView With Formatted Columns In ASPX (VB) </div> <asp:GridView  Runat="Server" AllowPaging="false" AllowSorting="false" AutoGenerateColumns="false" BorderColor="#000080" BorderStyle="Solid" BorderWidth="2px" Caption="" HorizontalAlign="Center" Width="90%" > <HeaderStyle HorizontalAlign="Center" Css /> <RowStyle css /> <AlternatingRowStyle css /> <Columns> <asp:BoundField DataField="Title" HeaderText="Title" /> <asp:BoundField HeaderText="Publish Date" DataField="PublishDate" ItemStyle-HorizontalAlign="Center" DataFormatString="{0:MMM dd, yyyy}" /> <asp:BoundField HeaderText="List Price" DataField="ListPrice" ItemStyle-HorizontalAlign="Center" DataFormatString="{0:C2}" /> </Columns> </asp:GridView> </asp:Content> 

Example 2-49. Formatting columnar data in a GridView code-behind (.vb)

 Option Explicit On Option Strict On Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code behind for ''' CH02GridViewWithFormattedColumnsVB1.aspx ''' </summary> Partial Class CH02GridViewWithFormattedColumnsVB1 Inherits System.Web.UI.Page '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Private Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load Dim dSource As SqlDataSource = Nothing If (Not Page.IsPostBack) Then 'configure the data source to get the data from the database dSource = New SqlDataSource() dSource.ConnectionString = ConfigurationManager. _ ConnectionStrings("dbConnectionString").ConnectionString dSource.DataSourceMode = SqlDataSourceMode.DataSet dSource.ProviderName = "System.Data.OleDb" dSource.SelectCommand = "SELECT Title, PublishDate, ListPrice " & _ "FROM Book " & _ "ORDER BY Title" 'set the source of the data for the gridview control and bind it gvBooks.DataSource = dSource gvBooks.DataBind() End If End Sub 'Page_Load End Class 'CH02GridViewWithFormattedColumnsVB1 End Namespace 

Example 2-50. Formatting columnar data in a GridView code-behind (.cs)

 using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { /// <summary> /// This class provides the code behind for /// CH02GridViewWithFormattedColumnsCS1.aspx /// </summary> public partial class CH02GridViewWithFormattedColumnsCS1 : System.Web.UI.Page { ///*********************************************************************** /// <summary> /// This routine provides the event handler for the page load event. /// It is responsible for initializing the controls on the page. /// </summary> /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void Page_Load(object sender, EventArgs e) { SqlDataSource dSource = null; if (!Page.IsPostBack) { // configure the data source to get the data from the database dSource = new SqlDataSource(); dSource.ConnectionString = ConfigurationManager. ConnectionStrings["dbConnectionString"].ConnectionString; dSource.DataSourceMode = SqlDataSourceMode.DataReader; dSource.ProviderName = "System.Data.OleDb"; dSource.SelectCommand = "SELECT Title, PublishDate, ListPrice " + "FROM Book " + "ORDER BY Title"; // set the source of the data for the gridview control and bind it gvBooks.DataSource = dSource; gvBooks.DataBind(); } } // Page_Load } // CH02GridViewWithFormattedColumnsCS1 }