CODEWITHSUNDEEP
sqlsql-server
FPGA
FPGA

Reputation: 3855

Sql type to CLR type string

consider the following query

SELECT table_name  AS SqlColumnTableName, 
       column_name AS SqlColumnName, 
       data_type   AS SqlColumnType, 
       CASE 
         WHEN is_nullable = 'YES' THEN 1 
         ELSE 0 
       END         AS IsNullable, 
       CASE 
         WHEN numeric_scale = 0 THEN 'Int' 
         WHEN Isnull(numeric_precision, 0) > 0 THEN 'Decimal' 
         WHEN Isnull(datetime_precision, 0) > 0 THEN 'DateTime' 
         WHEN character_set_name IS NOT NULL THEN 'String' 
         WHEN data_type = 'bit' THEN 'Bool' 
       END         AS CLR_Type, 
       CASE 
         WHEN column_name IN(SELECT column_name 
                             FROM   information_schema.key_column_usage) THEN 1 
         ELSE 0 
       END         AS IsPrimaryKey 
FROM   information_schema.columns

with this query i get some info about a column including its name and its table name and if its a primary key column, however i am trying to find a good way to produce a string depending on the sql type of the column as you can see in this case statement

CASE 
  WHEN numeric_scale = 0 THEN 'Int' 
  WHEN Isnull(numeric_precision, 0) > 0 THEN 'Decimal' 
  WHEN Isnull(datetime_precision, 0) > 0 THEN 'DateTime' 
  WHEN character_set_name IS NOT NULL THEN 'String' 
  WHEN data_type = 'bit' THEN 'Bool' 
END AS CLR_Type,

is there a more convenient way than the one i suggested?

Upvotes: 0

Views: 456

Answers (1)

Anon
Anon

Reputation: 10908

Make a small table that maps DATA_TYPE to CLR_TYPE using SQL-CLR Type Mapping and join against that. It shouldn't be much cutting and pasting to get all of the types.

Watch out for SQL-CLR Type Mismatches

Also, are you sure you don't want to use LINQ for this? You may be reinventing the wheel.

Upvotes: 1

Related Questions