.NET: How to change the value in a DataTable

Question

In ADO.NET i'm using GetSchemaTable to return the schema table for a results set.

DataTable schema = rdr.GetSchemaTable();
gridSchema.DataSource = schema;
gridSchema.DataBind();

Unfortunatly the "ProviderType" value is displaying as an integer, rather than the OleDbType enumeration value that it is:

ProviderType     Desired Display Value
============     =====================
129              Char
3                Integer
129              Char
129              Char
3                Integer
3                Integer
129              Char
135              DBTimeStamp
129              Char
129              Char
...

All these integers are the the enumeration values for the OleDbType enumeration:

public enum OleDbType
{
    Empty = 0,
    SmallInt = 2,
    Integer = 3,
    Single = 4,
    Double = 5,
    Currency = 6,
    Date = 7,
    BSTR = 8,
    IDispatch = 9,
    Error = 10,
    Boolean = 11,
    Variant = 12,
    IUnknown = 13,
    Decimal = 14,
    TinyInt = 16,
    UnsignedTinyInt = 17,
    UnsignedSmallInt = 18,
    UnsignedInt = 19,
    BigInt = 20,
    UnsignedBigInt = 21,
    Filetime = 64,
    Guid = 72,
    Binary = 128,
    Char = 129,
    WChar = 130,
    Numeric = 131,
    DBDate = 133,
    DBTime = 134,
    DBTimeStamp = 135,
    PropVariant = 138,
    VarNumeric = 139,
    VarChar = 200,
    LongVarChar = 201,
    VarWChar = 202,
    LongVarWChar = 203,
    VarBinary = 204,
    LongVarBinary = 205,
}

i want to display the data type as something human readable, rather than an integer.

---

i've tried looping through the schema DataTable and modify the values inside the DataTable:

DataTable schema = rdr.GetSchemaTable();

//Change providerType column to be readable
foreach (DataRow row in schema.Rows)
{
   OleDbType t = (OleDbType)row["ProviderType"];
   row["ProviderType"] = t.ToString();
}

gridSchema.DataSource = schema;
gridSchema.DataBind();

But that throws an exception:

Column 'ProviderType' is read only.

i even looked at the GridView's RowDataBound event, thinking i could change the value as it is rendered:

protected void gridSchema_RowDataBound(object sender, GridViewRowEventArgs e)
{
   //todo: magic
   //e.Row["ProviderType"]
}

But it doesn't look like you can play with rendered values.

Can anyone suggest a nice way to much with the value of the ProviderType column so that it is human readable when i display it to humans?

---

Update

The workaround i'm using right now is tack an extra column on the end:

DataTable schema = rdr.GetSchemaTable();

schema.Columns.Add("OleDbDataType", typeof(String));
schema.Columns.Add("CLRDataType", typeof(String));
foreach (DataRow row in schema.Rows)
{
   //Show the actual provider type
   OleDbType t = (OleDbType)row["ProviderType"];
   row["OleDbDataType"] = t.ToString();

   //Show the corresponding CLR type while we're doing a hack
   row["CLRDataType"] = row["DataType"].ToString();
}

gridSchema.DataSource = schema;
gridSchema.DataBind();

Answer 1

I might be completely off here, but can't you just set the ReadOnly property of the column to false? Like:

schema.Columns["ProviderType"].ReadOnly = false;

Afterwards you might get a columntype problem as you're trying to put a string value into a integer column. But this should get you into the right direction.

edit:

Solving the columntype issue:

DataTable newTable = schema.Clone();
newTable.Columns["ProviderType"].DataType = typeof(string);

foreach (DataRow dr in schema.Rows)
{
    DataRow newRow = newTable.NewRow();
    // fill newRow with correct data and newly formatted providertype

    newTable.Rows.Add(newRow);
}

Answer 2

Or you could create an object with all the fields from the datatable, pass all the data into your object and modify it or create a read only field that returns the string acording to the integer given.

GridView and other also accept object arrays as datasources so it is automatic.