Sql type to CLR type string

Question

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,

float,money,real and any type that can represended as decimal shall produce 'Decimal'
nvarchar,ncchar,text and anytype that represents a string shall produce 'String'
datetime,date shall produce 'DateTime'
Int shall produce an 'Int'
bit shall produce a 'Bool'

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

Anon · Accepted Answer · 2013-11-22 00:40:21Z

1

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.

answered Nov 22, 2013 at 0:40

Anon

10.9k1 gold badge32 silver badges31 bronze badges

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

2 Comments

FPGA Over a year ago

i am generating the sql queries using a graphical interface, so i need it to work that way, but i get your suggestion ive chosen to do it fast and ignored the accurcy

FPGA Over a year ago

by using linq you mean entity frameork? what i am trying to do is get the sql column schema and convert it to an object

Collectives™ on Stack Overflow

Sql type to CLR type string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related