Tuesday, December 24, 2013

Convert data types between SQL, C#, and an XML doc

Convert data types between SQL, C#, and an XML doc, and produce a param list for a stored proc, and insert values for an INSERT INTO statement. I'm sure there's tools out there that will do this for me, but this query + an excel spreadsheet will do just fine, especially since I have no time to find YET ANOTHER TOOL.

SELECT        COLUMN_NAME,
'@'+COLUMN_NAME+ ' ' + DATA_TYPE +
(CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(NVARCHAR, CHARACTER_MAXIMUM_LENGTH) + ')'
ELSE
''
END)+
',' as [StoredProc Param],
'@'+COLUMN_NAME+',' as [Values List],
DATA_TYPE,
CASE DATA_TYPE
WHEN 'smallint' THEN 'short'
WHEN 'char' THEN 'string'
WHEN 'tinyint' THEN 'byte'
WHEN 'datetime' THEN 'string'
WHEN 'int' THEN 'int'
WHEN 'numeric' THEN 'decimal'
END as [XML Data Type],
CASE DATA_TYPE
WHEN 'smallint' THEN 'Convert.ToInt16('
WHEN 'char' THEN 'Convert.ToString('
WHEN 'tinyint' THEN 'Convert.ToSByte('
WHEN 'datetime' THEN 'Convert.ToString('
WHEN 'int' THEN 'Convert.ToInt32('
WHEN 'numeric' THEN 'Convert.ToDecimal('
END as [C# Data Type]
FROM            INFORMATION_SCHEMA.COLUMNS
WHERE        (TABLE_NAME = 'CHANGE THIS TO YOUR TABLE NAME')

No comments:

Post a Comment

There was an error in this gadget