How to get the exact type of numeric columns incl. scale and precision?

Question

Is there a way to know the exact type of a column in a DataTable? Right now I am doing this:

DataTable st = dataReader.GetSchemaTable();
foreach (DataColumn col in st.Columns)
{
   var type = col.DataType;
}

Now with type.Name I am able to find if it is a number(int or decimal..) or string but the problem is that I need the exact type, for example if in database let say column Rate is NUMBER(4,3) then here in my code I am only getting type as 'Decimal' and no information about the Format 4,3.

Now the requirement is I need to format the values as per their type for eg. if Rate=1.4 it should be shown as 0001.400 (according to the Format NUMBER(4,3)). Hence here since I do not have info I am not able to process the values further. Is there anyway to know the same?

Thanks

Answer 1

You can use NumericPrecision and NumericScale:

using (var con = new SqlConnection(Properties.Settings.Default.MyConnectionString))
using (var cmd = new SqlCommand("SELECT * FROM dbo.Test", con))
{
    con.Open();
    using (var reader = cmd.ExecuteReader())
    using (var schemaTable = reader.GetSchemaTable())
    {
        foreach (DataRow row in schemaTable.Rows)
        {
            string column = row.Field<string>("ColumnName");
            string type = row.Field<string>("DataTypeName");
            short precision = row.Field<short>("NumericPrecision");
            short scale = row.Field<short>("NumericScale");
            Console.WriteLine("Column: {0} Type: {1} Precision: {2} Scale: {3}", column, type, precision, scale);
        }
    }
}

More informations: GetSchemaTable

I have tested it with a fresh table with a single column NumberColumn of type numeric(4, 3):

Column: NumberColumn Type: decimal Precision: 4 Scale: 3