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
– johnny 5, Commented Sep 19, 2017 at 16:52
I'd also remove system_type_id from your join condition and just use user_type_id — Xedni
– Xedni, Commented Sep 19, 2017 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. github.com/Fodsuk/EntityFrameworkExtras — ToDevAndBeyond
– ToDevAndBeyond, Commented Sep 19, 2017 at 17:23

Jamie Pearcey · Accepted Answer · 2019-10-24 15:38:48Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related