mssql sqlserver 使用sql脚本将“数据表”生成指定实体类的方法分享


摘要:
下文讲述使用sql脚本将数据表生成实体类的方法分享,如下所示:
实验环境:sql server 2008 R2



实现思路:
主要通过遍历 系统表 sys.columns和 sys.types 表,然后通过case语句将查询的列转换为相应格式的c#代码,如下所示:

例:

    


---1.新建数据表 maomao365.com

create table [maomao365.com]
(keyId int identity,
 info  varchar(30) not null ,
 writeDate datetime not null  )
 go
 


declare @TableName sysname = '[maomao365.com]'   ---数据表名称

declare @classInfo varchar(max) = 'public class ' + @TableName + '
{'

select @classInfo = @classInfo + '
    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 '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)
) t
order by ColumnId

set @classInfo = @classInfo  + '
}'

print @classInfo

go

truncate table [maomao365.com]
drop table [maomao365.com]

mssql_sqlserver_数据表生成实体类方法_9408

mssql_sqlserver_数据表生成实体类方法_9408


相关阅读:
mssql sqlserver数据类型同c#数据类型之间的对应关系分享