0

I am trying to fetch following details of columns from SQL Server:

  • Size
  • Precision
  • Scale

I am successfully getting data when I have below schema for table:

Table1:

Col1 : varchar(40),null)
Col2 : varchar(2,null)
Col3 : varchar(57,null)
Col4 : varchar(245 ,null)
Col5 : datetime(not null)

But I'm getting an error

Specified cast is not valid.

in case of this schema:

Col1 : varchar(34,null)
Col2 : varchar(1066,null)
Col3 : varchar(500,null)
Col4 : varchar(600,null)
Col5 : numeric(31,13,null)
Col6 : numeric(31,13,null)
Col7 : datetime(null)

Code:

String[] columnRestrictions = new String[4];
columnRestrictions[0] = 'MyDb';
columnRestrictions[1] = 'dbo';
columnRestrictions[2] = 'Employee';

using (SqlConnection con = new SqlConnection("MyConnectionString"))
{
    con.Open();
    var columns = con.GetSchema("Columns", columnRestrictions).AsEnumerable()
         .Select
          (
                 t => new 
                 {
                     Name = t[3].ToString(),
                     Datatype = t.Field<string>("DATA_TYPE"),
                     IsNullable = t.Field<string>("is_nullable"),
                     Size = t.Field<int?>("character_maximum_length"),
                     NumericPrecision = t.Field<int?>("NUMERIC_PRECISION"), //error on this field
                     NumericScale = t.Field<int?>("NUMERIC_SCALE")
                 }).ToList();

Source :

Reference1

Reference2

Update: this line is causing the issue

NumericPrecision = t.Field<int?>("NUMERIC_PRECISION"), //error on this field

How can I resolve this error and fetch size, precision and scale for columns?

halfer
  • 19,824
  • 17
  • 99
  • 186
I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216
  • So which line is throwing the exception? You should narrow it down to a single field... – Jon Skeet Dec 12 '16 at 11:24
  • @JonSkeet:Name,Datatype,isnullable is working but i guess problem is with rest of the properties – I Love Stackoverflow Dec 12 '16 at 11:25
  • 1
    Rather than guessing, you should do the work to narrow it down. Find one property which *definitely* causes a problem, and ideally in the debugger work out what the *actual* type is. – Jon Skeet Dec 12 '16 at 11:27
  • @JonSkeet:I have identified the property which is causing the issue.see my updated question – I Love Stackoverflow Dec 12 '16 at 11:33
  • @MarcGravell : I have updated my question to include which line is causing the issue.When i comment out the last 2 property then i am successfully getting name,datatype and nullable – I Love Stackoverflow Dec 12 '16 at 11:34
  • 1
    In sys.columns, `precision` and `scale` are `tinyint`, which maps to `byte`. You could always just look at the underlying value or the column def - `GetSchema()` returns a `DataTable`, which has `.Columns["NUMERIC_PRECISION"].DataType` – Marc Gravell Dec 12 '16 at 11:36
  • @MarcGravell :Yes thank you so much you are right.i have 1 column with datatype as varchar(max) then using character_maximum_length i get -1 for this.Can you tell me why -1 in case of max varchar?? – I Love Stackoverflow Dec 12 '16 at 12:16
  • 1
    @Learning because -1 is the setinel value it uses to denote `max` – Marc Gravell Dec 12 '16 at 13:07
  • @MarcGravell:Thank you so much marc.i have seen your lots of answer and as you are 1 of the best user of SO so i had a wished that you will answer my question 1 day so my wish is partially fullfilled with your comments :) – I Love Stackoverflow Dec 12 '16 at 13:15

1 Answers1

2

The problem in your code is that you are casting a byte to int?, the Field-method supports nullable types (if the column can be null), but it doesn't convert from byte? to int?. It throws this exception instead. Source

So just use this:

NumericPrecision = t.Field<byte?>("NUMERIC_PRECISION"),

You can always look at the DataColumn's DataType property.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939