CODEWITHSUNDEEP
c#sql.nettypesconverters
Felix D.
Felix D.

Reputation: 5083

Get .NET Type from SQL ColumnType

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 ?

Upvotes: 0

Views: 2416

Answers (1)

Heinzi
Heinzi

Reputation: 172200

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.

Upvotes: 1

Related Questions