从数据库表生成类

要打印带有注释(摘要)的NULLABLE属性，使用这个。这是对第一个答案的轻微修改

declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result 
+ CASE WHEN ColumnDesc IS NOT NULL THEN '
    /// <summary>
    /// ' + ColumnDesc + '
    /// </summary>' ELSE '' END
+ '
    public ' + ColumnType + ' ' + ColumnName + ' { get; set; }'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        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 + CASE WHEN col.is_nullable=1 AND typ.name NOT IN ('binary', 'varbinary', 'image', 'text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar') THEN '?' ELSE '' END ColumnType,
        colDesc.colDesc AS ColumnDesc
    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
    OUTER APPLY (
    SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
    FROM
       sys.extended_properties
    WHERE
       major_id = col.object_id
       AND
       minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
    ) colDesc            
    where object_id = object_id(@TableName)
) t
order by column_id

set @result = @result  + '
}'

print @result

2014-09-09 12:03:12

我只是想表达我的意见

0) QueryFirst https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst Query-first is a visual studio extension for working intelligently with SQL in C# projects. Use the provided .sql template to develop your queries. When you save the file, Query-first runs your query, retrieves the schema and generates two classes and an interface: a wrapper class with methods Execute(), ExecuteScalar(), ExecuteNonQuery() etc, its corresponding interface, and a POCO encapsulating a line of results.

1) Sql2Objects 从查询结果开始创建类(但不是DAL)

2) https://learn.microsoft.com/en-us/ef/ef6/resources/tools

3) https://visualstudiomagazine.com/articles/2012/12/11/sqlqueryresults-code-generation.aspx

4) http://www.codesmithtools.com/product/generator#features

2018-08-21 16:13:06

要打印带有注释(摘要)的NULLABLE属性，使用这个。这是对第一个答案的轻微修改

declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result 
+ CASE WHEN ColumnDesc IS NOT NULL THEN '
    /// <summary>
    /// ' + ColumnDesc + '
    /// </summary>' ELSE '' END
+ '
    public ' + ColumnType + ' ' + ColumnName + ' { get; set; }'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        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 + CASE WHEN col.is_nullable=1 AND typ.name NOT IN ('binary', 'varbinary', 'image', 'text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar') THEN '?' ELSE '' END ColumnType,
        colDesc.colDesc AS ColumnDesc
    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
    OUTER APPLY (
    SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
    FROM
       sys.extended_properties
    WHERE
       major_id = col.object_id
       AND
       minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
    ) colDesc            
    where object_id = object_id(@TableName)
) t
order by column_id

set @result = @result  + '
}'

print @result

2014-09-09 12:03:12

最简单的方法是反向工程。http://msdn.microsoft.com/en-US/data/jj593170

2014-03-13 14:43:03

我尝试过node.js，它对我来说工作得很好。

它将为您创建模型文件。您可以创建多个模型文件

前提条件:安装node.js

需要更改:

在你的工作区中创建index.js文件在“allTable”中添加你的tables对象(截图中高亮显示) 更改文件夹路径(我已经给出了我的系统路径) 执行命令节点index.js

节点index.js

输出

2022-06-29 07:28:06

Postgres DB -> c#的另一个解决方案

兼容NpgSQL 添加DataAnnotations

    SELECT
            CASE 
                WHEN c.is_nullable ='NO'  THEN '[Required]'||chr(10)
                ELSE '' END
            ||CASE 
                WHEN c.data_type = 'character varying' THEN format('[StringLength(%s)]',c.character_maximum_length)||chr(10)
                ELSE '' END
            ||'public '
            ||CASE 
                WHEN c.data_type = 'integer' THEN 'int'
                WHEN c.data_type = 'boolean' THEN 'bool'
                WHEN c.data_type = 'double precision' THEN 'double'
                WHEN c.data_type = 'uuid' THEN 'Guid'
                WHEN c.data_type = 'character varying' THEN 'string'
                WHEN c.data_type = 'timestamp without time zone' THEN 'DateTime'    
                WHEN c.data_type = 'bigint' THEN 'long'   
                WHEN c.data_type = 'bytea' THEN 'byte[]'    
            ELSE 'object' END
            ||CASE
                WHEN c.is_nullable='YES' AND NOT  c.data_type = 'character varying' THEN '? '
                ELSE ' ' END
            ||c.column_name||' {get;set;}'
            ,c.*
    FROM information_schema."columns" c
    WHERE 1=1
    AND c.table_name='YOUR_TABLE_NAME'

2021-06-14 20:12:07

从数据库表生成类

推荐文章

最新文章

标签