UPDATE: After using this code a bit more it has become apparent that the Sql2008 types are all string convertable and that Byte[] is a better choice for a generic reader implementation.
You may also handle single field UDT by getting the name of the system_type_id when is_user_defined = 1 in sys.columns.
date NULL = Nullable<DateTime>
DECLARE @current_column_type VARCHAR(30),
@current_column_nullable BIT,
@generated_property_type NVARCHAR(128)
SET @current_column_type = 'date'
SET @current_column_nullable = 1
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DECLARE @fn_ctype_input_type NVARCHAR(128),
@fn_ctype_input_nullable BIT,
@fn_ctype_output_type NVARCHAR(128)
-- --> ConvertType Function - set input
SET @fn_ctype_input_type = @current_column_type
SET @fn_ctype_input_nullable = @current_column_nullable
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN
SET @fn_ctype_output_type = CASE @fn_ctype_input_type
WHEN 'image' THEN 'Byte[]'
WHEN 'text' THEN 'String'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'date' THEN 'DateTime'
WHEN 'time' THEN 'TimeSpan'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'datetimeoffset' THEN 'DateTimeOffset'
WHEN 'tinyint' THEN 'Byte'
WHEN 'smallint' THEN 'Int16'
WHEN 'int' THEN 'Int32'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'real' THEN 'Single'
WHEN 'money' THEN 'Decimal'
WHEN 'datetime' THEN 'DateTime'
WHEN 'float' THEN 'Double'
WHEN 'sql_variant' THEN 'Byte[]'
WHEN 'ntext' THEN 'String'
WHEN 'bit' THEN 'Boolean'
WHEN 'decimal' THEN 'Decimal'
WHEN 'numeric' THEN 'Decimal'
WHEN 'smallmoney' THEN 'Decimal'
WHEN 'bigint' THEN 'Int64'
WHEN 'hierarchyid' THEN 'String'
WHEN 'geometry' THEN 'String'
WHEN 'geography' THEN 'String'
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'varchar' THEN 'String'
WHEN 'binary' THEN 'Byte[]'
WHEN 'char' THEN 'String'
WHEN 'timestamp' THEN 'Byte[]'
WHEN 'nvarchar' THEN 'String'
WHEN 'nchar' THEN 'String'
WHEN 'xml' THEN 'String'
WHEN 'sysname' THEN 'String'
ELSE 'Byte[]'
END
-- TODO: handle FILESTREAM attribute varbinary(max) Byte[]
-- if the CLR type is a reference type, ignore nullable bit on source field
IF ( @fn_ctype_output_type IN ( 'String', 'Guid', 'Byte[]' ) )
SET @fn_ctype_input_nullable = 0 ; -- is ref type
-- if field type effectively is nullable, indicate with '?'
SET @fn_ctype_output_type = CASE @fn_ctype_input_nullable
WHEN 1 THEN 'Nullable<' + @fn_ctype_output_type + '>'
ELSE @fn_ctype_output_type
END
END
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-- <-- ConvertType Function - get output
SET @generated_property_type = @fn_ctype_output_type
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
PRINT @current_column_type + CASE @current_column_nullable WHEN 1 THEN ' NULL' END + ' = ' + @generated_property_type
Technorati tags: SQL, C#, CodeGen