Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Creates the C# Class and DataTable converter function
declare @TableName sysname = 'TestTable'
IF OBJECT_ID('tempdb..#ClassData') IS NOT NULL
DROP TABLE #ClassData
CREATE TABLE #ClassData
(
ColumnName nvarchar(max),
ColumnId int,
ColumnType nvarchar(max),
NullableSign nvarchar(5)
)
insert into #ClassData (ColumnName, ColumnId, ColumnType, NullableSign)
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 'double'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'char'
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)
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }'
from #ClassData
order by ColumnId
set @Result = @Result + '
public static List<' + @TableName + '> ConvertToList(DataTable dt)
{
return dt.AsEnumerable().Select(item => new ' + @TableName + '()
{'
select @Result = @Result + '
' + ColumnName + ' = item.Field<' + ColumnType + NullableSign + '>(nameof(' + @TableName + '.' + ColumnName + ')),'
from #ClassData
order by ColumnId
set @Result = @Result + '
}).ToList();
}
}'
print @Result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment