Get .NET Type from SQL ColumnType

Question

I am workin on something like a "SQL Query generator". To be able to biuld WHERE clauses I need to find out which type the value has in the database.

i.e: if the value is an int I will use operators like (<,>,=), on DateTime I want to add <,>,= and a combobox where the user can select sth like "DateTime.Now".

So thats why I need to differ between the types.

Is there any way to get the Types from the columns of a table converted to a .NET Type ?

I currently got this code:

using (SqlConnection conn = new SqlConnection(MonitoringContext.Instance.ConnectionString))
{
   conn.Open();
   string query = $"SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{SelectedDatabaseItem}' AND COLUMN_NAME = '{Criteria[currentIndex].ColumnValueComboBox.SelectedItem}'";
   SqlCommand command = new SqlCommand(query, conn);
   object reader = command.ExecuteScalar();
   valueType = Type.GetType(reader.ToString());                    
}

if(valueType != null)
    {
     if (valueType == typeof(DateTime))
     {
         List<string> operators = new List<string>() { "<", ">", "=" };
         Criteria[currentIndex].OperatorValueComboBox.ItemsSource = operators;
         //Add datePicker
         return;
     }
     if(valueType == typeof(int))
     {
          //Add textbox to criteria selector
          return;
     }                    
}

So assuming the type in the DB is int this is working fine 'cause Type.GetType("int") will return typeof(int):

But as soon as it's for example "nvarchar" I cannot convert it to string.

Is there any builtin function to convert this ?

You will have to do it manually, will not be a big thing since the amount of types is very limited — Sami Kuhmonen
– Sami Kuhmonen, Commented Jul 22, 2016 at 14:09
@SamiKuhmonen okay so I get the Types as string from the Database and then check if (varchar => string, int => int, => dateTime => dateTime) ? — Felix D.
– Felix D., Commented Jul 22, 2016 at 14:10
Yep, just build a dictionary for the mapping and you can easily handle it — Sami Kuhmonen
– Sami Kuhmonen, Commented Jul 22, 2016 at 14:11

Community · Accepted Answer · 2017-05-23 12:32:42Z

2

Unfortunately, there is no "SQL Server data type -> .NET data type" mapping table built-in; thus, you'll have to do the mapping yourself.

Since the number of data types is restricted (and the number of data types actually in use by your app might be even more restriced), this should not be too hard. From an implementation point-of-view, this could be done with a huge switch statement or a dictionary.

The mapping itself is documented here:

SQL Server Data Type Mappings

A mapping in C# can be found here:

SQL Server to .Net type conversions

A word of caution: Note that there might not even be an exact correspondence between SQL Server data types and .NET data types: For example, a decimal(4,2) has a much shorter range than .net's Decimal. The same is true for datetime (years 1753-9999 in SQL, 0001-9999 in .NET) and propbably others as well.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Jul 22, 2016 at 14:28

Heinzi

173k61 gold badges386 silver badges555 bronze badges

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

Collectives™ on Stack Overflow

Get .NET Type from SQL ColumnType

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related