Nudity Nudity - 1 year ago 57
SQL Question

Get .NET Type from SQL ColumnType

I am workin on something like a "SQL Query generator".
To be able to biuld

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))
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
if(valueType == typeof(int))
//Add textbox to criteria selector

So assuming the type in the DB is
this is working fine 'cause
will return

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

Is there any builtin function to convert this ?

Answer Source

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:

A mapping in C# can be found here:

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.