DataReader get type specification SQL Server

Question

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));
    }
}

Neither of those questions seem to address getting the size/precision of the field. dataReader.GetSchemaTable() gives you full metadata for all columns. — Jeroen Mostert
– Jeroen Mostert, Commented Oct 26, 2016 at 12:38
For the size go via .GetSchemaTable() (Not sure if it exposes precision/scale however) — Alex K.
– Alex K., Commented Oct 26, 2016 at 12:38
You don't need that information to export a table to Excel. Either use SQL Server's own SSIS or use a library like EPPlus to generate a real Excel (ie xlsx) file with a call as simple as sheet.LoadFromDataTable as shown here. You can format the resulting columns as needed — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 26, 2016 at 12:38
@AlexK. It does - they're called NumericPrecision and NumericScale listed on the documentation you linked — Bridge
– Bridge, Commented Oct 26, 2016 at 12:41
Voted to reopen the question because the duplicates don't answer the question about the field size. — Steve
– Steve, Commented Oct 26, 2016 at 12:44

Steve · Accepted Answer · 2016-10-26 12:59:59Z

4

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)

edited Oct 26, 2016 at 12:59

answered Oct 26, 2016 at 12:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kb_ Over a year ago

thanks for that! short question, when i have a decimal like this decimal(19,8) can i get this in the same way ? at the moment he says for the decimal (19,8) the following --> decimal(17)

Steve Over a year ago

Uhm, the values should be in NumericPrecision and NumericScale, but I need to check

Steve Over a year ago

Yes, ColumnSize is 17 but NumericPrecision get 19 and NumericScale = 8, so if you have a decimal type you should look at these two columns. I think that the explanation is in the description for ColumnSize: For columns that use a fixed-length data type, this is the size of the data type.

Ronak Patel · Accepted Answer · 2016-10-26 12:55:11Z

Please using TableSchema method for getting all details of column .

SqlDataReader reader= command.ExecuteReader();

using (var schemaTable = reader.GetSchemaTable())
    {
        foreach (DataRow row in schemaTable.Rows)
        {
            string ColumnName= row.Field<string>("ColumnName");
            string DataTypeName= row.Field<string>("DataTypeName");
            short NumericPrecision= row.Field<short>("NumericPrecision");
            short NumericScale= row.Field<short>("NumericScale");
            int ColumnSize= row.Field<int>("ColumnSize");
            Console.WriteLine("Column: {0} Type: {1} Precision: {2} Scale: {3} ColumnSize {4}",      
            ColumnName, DataTypeName, NumericPrecision, scale,ColumnSize);
        }
    }

Thanks .

Collectives™ on Stack Overflow

DataReader get type specification SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related