Format columns for ASP.Net GridView based on data type.

Question

I have a very simple ASP.Net page that acts as a front end for a stored procedure. It just runs the procedure and shows the output using a gridview control: less than 40 lines of total code, including aspx markup. The stored procedure itself is very... volatile. It's used for a number of purposes and the output format changes regularly.

The whole thing works great, because the gridview control doesn't really need to care what columns the stored procedure returns: it just shows them on the page, which is exactly what I want.

However, the database this runs against has a number of datetime columns all over the place where the time portion isn't really important- it's always zeroed out. What I would like to be able to do is control the formatting of just the datetime columns in the gridview, without ever knowing precisely which columns those will be. Any time a column in the results has a datetime type, just apply a given format string that will trim off the time component.

I know I could convert to a varchar at the database, but I'd really don't want to have to make developers care about formatting in the query and this belongs at the presentation level anyway. Any other ideas?

---

Finally got this working in an acceptable (or at least improved) way using this code:

Protected Sub OnRowDatabound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim d As DateTime
        For Each cell As TableCell In e.Row.Cells
            If Date.TryParse(cell.Text, d) AndAlso d.TimeOfDay.Ticks = 0 Then
                cell.Text = d.ToShortDateString()
            End If
        Next cell
    End If
End Sub

Answer 1

You can use the isDate() function to see if something is a valid date and then use dateformatting options to make it look like you want.

Some examples for date formating: http://datawebcontrols.com/faqs/CustomizingAppearance/FormatDateTimeData.shtml

Answer 2

If you are auto generating the columns which it sounds like you are. The procedure for using the grids formatting is awful.

You would need to loop through all the columns of the grid, probably in the databound event and apply a formatting expression to any column you find is a date column.

If you are not auto generating and you are hadcoding columns in your grid you will also know alreayd which columns are date columns and you can apply the same format expression to that column. It's something like {0:ddMMyyyy} but you will have to look it up as that's probably not quite right.

so to summarise hook into the databound event. loop through the column collection and ascertain if the column is a date column. I wonder how you might do this :). If you decide a column is a date column set its format expression.

Voila

---------------------- EDIT

Ok how about you write you method that returns the data from the proc to return a datatable. You can bind the datatable to your grid after formatting the data in the datatable. The datatable.Columns collection is a colection of DataColumns and these have a DataType property. You may be looking for System.DateTime or DateTime and it may be one of the properties of the DataType property itself :). I know it's cumbersome but what you are asking is definitly going to be cumbersome. Once you've identified date columns you may be able to do something with it.

If not i'd start looking at the data readers and see if there's anything you can do there or with data adapters. I wish I could give you a proper answer but i think however you manage to do it, it's not going to be pretty. Sorry

Answer 3

if using explicit bound columns is an option, add a DataFormatString to your boundField

<asp:BoundField DataField="Whatever" ... DataFormatString="{0:dd/MM/yyyy}" HtmlEncode="False"/ >

otherwise you could look at doing the formatting the GridView.OnRowDataBound event

Answer 4

THanks a lot for your code.. Just to help others,

select the class and the declarations & then copy the code in the newly created sub