Get data type of a SQL Server column using C#

Question

I'm not sure what exactly I'm doing wrong, can someone correct it please? I need to determine the type of a retrieved column from a SQL Server database using C#.

Say, I have this:

SqlConnection cn = new SqlConnection("Sql Connection String");
SqlCommand cmd = new SqlCommand("SELECT * FROM [TableName]", cn);
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    for (int c = 0; c < rdr.VisibleFieldCount; c++)
    {
        System.Type type = rdr.GetFieldType(c);

        //So can I do this? (Pseudo-code)
        //switch(type)
        //{
        //case string:
        //case int:
        //case DateTime:
        //etc.
        //}
    }
}

Is there a reason why you don't use a context? Why don't you use LinqToSql or Entity Framework? — Mathieu
– Mathieu, Commented Mar 15, 2012 at 1:14
Yes. I don't know what those are. Do you have a link I can check? — ahmd0
– ahmd0, Commented Mar 15, 2012 at 1:19
Check out this related question: stackoverflow.com/questions/258840/… — Mathieu
– Mathieu, Commented Mar 15, 2012 at 2:34
It seems to me you should know what your database table's schema is and thus youshould know what those types are..... — marc_s
– marc_s, Commented Mar 15, 2012 at 6:05
It would be a lot easier to use something like Entity Framework - it will handle a lot of those issues for you, and shield you from the boring details of data access... — marc_s
– marc_s, Commented Mar 15, 2012 at 6:07

Alex · Accepted Answer · 2012-03-15 01:15:22Z

13

You can do the following:

/* ... code .... */

System.Type type = rdr.GetFieldType(c);

switch (Type.GetTypeCode(type))
{
    case TypeCode.DateTime:
        break;
    case TypeCode.String:
        break;
    default: break;
}

/* ... code .... */

answered Mar 15, 2012 at 1:15

Alex

35.5k5 gold badges81 silver badges94 bronze badges

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

3 Comments

ahmd0 Over a year ago

Thanks, dude. It works, except two cases: when I have a UNIQUEIDENTIFIER and BINARY data types. Both are returned as Object, when they should be Guid and Byte[]. Any idea how to handle that?

Guvante Over a year ago

@ahmd0: if (type == typeof(System.Guid)) for instance.

Alex Over a year ago

Correct ... you could do that in the default block of the switch statement ...

user3029478 · Accepted Answer · 2020-06-22 18:30:24Z

0

Here is some code that worked for me:

SqlCommand ubCommand = new SqlCommand(sqltext);
ubCommand.Connection = ubConnection;
SqlDataReader ubReader = null;
ubReader = ubCommand.ExecuteReader();
ubReader.Read();
schednum = ToInt32(ubReader["ScheduleNum"].ToString());
int fieldNum;
lbl_schedule.Text = $"Schedule {schednum}";
for (i = 0; i < num_fields; i++)
{
 fieldNum = ubReader.GetOrdinal(fields[i]);
 values[i] = ubReader[fieldNum].ToString();
 types[i] = ubReader.GetFieldType(fieldNum);
 // Check types[i].Name for the results, like "String" or "Decimal"
}
ubReader.Close();
ubConnection.Close();

answered Jun 22, 2020 at 18:30

user3029478

1961 silver badge3 bronze badges

Collectives™ on Stack Overflow

Get data type of a SQL Server column using C#

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