Generating a C# class from a user defined table type SQL

Question

I need to generate strings that follow C# class specifications from SQL Server user-defined types.

I have this code that I got from a different stack overflow answer that does what I want and generates a C# class from a SQL Server table. I want to modify it so that it generates a similar class from a SQL Server user-defined table type.

declare @TableName sysname = 'YOURTABLENAME'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result

My attempt at modifying the select statement to return the string based on the user defined table types. However, this only returns the ModifiedBy column

from 
    sys.table_types as tt
inner join 
    sys.columns as col on tt.type_table_object_id = col.object_id
join 
    sys.types typ on col.system_type_id = typ.system_type_id 
                  and col.user_type_id = typ.user_type_id
where 
    tt.name = @TableName

What are you doing, EDMX Model Generate does this for you... — johnny 5, Sep 19 '17 at 16:52
https://stackoverflow.com/questions/10126871/entity-framework-generating-classes — Xedni, Sep 19 '17 at 16:56
I'd also remove `system_type_id` from your join condition and just use `user_type_id` — Xedni, Sep 19 '17 at 16:57
I see how EDMX generates tables into C# classes, however I don't see how EDMX generates a user-defined table type from DB first. Entity Framework extras looks promising, but still doesn't generate the class for me. https://github.com/Fodsuk/EntityFrameworkExtras — ToDevAndBeyond, Sep 19 '17 at 17:23

Jamie Pearcey · Answer 1 · 2019-10-24T15:38:48.157

The following SQL script should do the trick

declare @TypeName sysname = 'VehicleActivity_Raw'
declare @Result varchar(max) = 'public class ' + @TypeName + '
{'

select @Result = @Result + '
 public ' + ColumnType + NullableSign + ' ' + ColumnName + ' {get; set;}'
 FROM (
  Select 
   column_id,
   replace(c.name, ' ', '_') ColumnName,
    case y.name 
     when 'bigint' then 'long'
     when 'binary' then 'byte[]'
     when 'bit' then 'bool'
     when 'char' then 'string'
     when 'date' then 'DateTime'
     when 'datetime' then 'DateTime'
     when 'datetime2' then 'DateTime'
     when 'datetimeoffset' then 'DateTimeOffset'
     when 'decimal' then 'decimal'
     when 'float' then 'float'
     when 'image' then 'byte[]'
     when 'int' then 'int'
     when 'money' then 'decimal'
     when 'nchar' then 'string'
     when 'ntext' then 'string'
     when 'numeric' then 'decimal'
     when 'nvarchar' then 'string'
     when 'real' then 'double'
     when 'smalldatetime' then 'DateTime'
     when 'smallint' then 'short'
     when 'smallmoney' then 'decimal'
     when 'text' then 'string'
     when 'time' then 'TimeSpan'
     when 'timestamp' then 'DateTime'
     when 'tinyint' then 'byte'
     when 'uniqueidentifier' then 'Guid'
     when 'varbinary' then 'byte[]'
     when 'varchar' then 'string'
     when 'sysname' then 'string'
     else 'UNKNOWN_' + y.name
    end ColumnType,
    case 
     when c.is_nullable = 1 and y.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
     then '?' 
     else '' 
    end NullableSign
  From sys.table_types t
  Inner join sys.columns c on c.object_id = t.type_table_object_id
  Inner join sys.types y ON y.system_type_id = c.system_type_id
  WHERE t.is_user_defined = 1
    AND t.is_table_type = 1
    AND t.name = @TypeName
 ) AS C
 ORDER BY column_id

set @Result = @Result  + '
}'

print @Result

Generating a C# class from a user defined table type SQL

1 Answers1