Format data in dynamically generated columns from crosstab query

[joshblair]

Hello,

I have an OleDbDataReader that contains data from a crosstab query.
The column count, and their names of many of the columns are not known
at design time. The cross tab in question is returning currency data
in these columns but in the datareader using OleDB, the columns are of
type decimal. I'd like to able to add formatting to these columns on
the fly at runtime but have been unable to figure this out.

Is there a way to manipulate the data and the formatting of that data
dynamically even if I don't know the names of the columns in question
at designtime?

One idea I had was to obtain the data in a DataTable rather than a
DataReader and then manipulate the data before binding it to the
datagrid.

What about overriding the ItemDataBound event or something similar?

Any ideas or recommendations are very much appreciated.

Thanks,

Josh Blair

 

[Elton Wang]

Hi Josh,

You can use
string fieldType =
OleDbDataReaderObj.GetSchemaTable().Rows[FIELD_INDEX]["DataType"].GetType().Name;

to have information about the field type.

Or similarly, if you use DataTable as datagrid's datasource, you can get
DataRowView in datagrid_ItemDataBound event. Then use
DataRowViewObj[FIELD_INDEX].GetType().Name to get the field type.

Depending on the type info, you can cast data to specified type, then format
it's string:

switch (fieldType)
{
case "Decimal":
decimal decObj = (decimal)obj;
return decObj.ToString("C");
case "DateTime":
DateTime dateObj = (DateTime)obj;
return dateObj.ToString("MMM dd yyyy");
// other case
default:
return obj.ToString();
}

However, as you mentioned, money type in DB returns Decimal type. If in your
query, there are also some other decimal (not money type) type fields. That
will cause confusion.


HTH

 

[joshblair]

Elton, thanks for the tips. I had never noticed the GetSchemaTable
member before. If I were to keep the datasource as an OleDBDataReader,
should I still use the datagrid_ItemDataBound event to convert the
decimal to a string formatted as currency?

Thanks,

Josh Blair

 

[Elton Wang]

I think it's better to use the datagrid_ItemDataBound event to convert the
decimal to a string formatted as currency?

If you use OleDBDataReader as data source, you can't cast e.Item.DataItem to
DataRowView. You should cast it to
System.Data.Common.DbDataRecord. Anyway, from this object, you can also use
DbDataRecordobj[col_index].GetType().Name to get its type.

HTH

 

[joshblair]

Thanks again for your time and information.

Josh Blair