kb_ kb_ - 1 month ago 12
C# Question

DataReader get type specification SQL Server

I have a small program to "download" database tables to Excel.

I want to add the column type to the second line and I tried it with the following function. It works fine but the

GetDataTypeName(i)
returns only
int, nvarchar
but I need the complete type specification like this

nvarchar(255), decimal(19, 8)


Is there another function to get this from the database ?

SqlDataReader dataReader = command.ExecuteReader();

// adds the names and the types if the table has no values
if (!dataReader.HasRows || !withValues)
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
names.Add(dataReader.GetName(i));
types.Add(dataReader.GetDataTypeName(i));
}
}

Answer

This kind of information is available through the call GetSchemaTable. It returns a DataTable where you have a row for each column returned by the query. Each column of this table describe a particular information extracted by the metadata relative to the query field

For example

    SqlDataReader dataReader = command.ExecuteReader();

    if (!dataReader.HasRows || !withValues)
    {
        DataTable dt = dataReader.GetSchemaTable();
        foreach(DataRow row in dt.Rows)
        {
            Console.WriteLine("ColumnName: " + row.Field<string>("ColumnName"));
            Console.WriteLine("NET Type: " + row.Field<string>("DataTypeName"));
            Console.WriteLine("Size: " + row.Field<int>("ColumnSize"));
        }
   }

The GetSchemaTable returns a lot of information about your table/query, but a lot of these fields are set to null. I am not sure if this is a limitation of the provider or they are null because, in the context of the call, they have no meaning. In any case use defensive programming in accessing these values (if !(value == DBNull.Value)