从数据库表生成类

如何从SQL Server表对象生成类?

我说的不是使用ORM。我只需要创建实体(简单类)。喜欢的东西:

    public class Person 
    {
        public string Name { get;set; }
        public string Phone { get;set; }
    }

给定一个表，比如:

+----+-------+----------------+
| ID | Name  |     Phone      |
+----+-------+----------------+
|  1 | Alice | (555) 555-5550 |
|  2 | Bob   | (555) 555-5551 |
|  3 | Cathy | (555) 555-5552 |
+----+-------+----------------+

当前回答

我无法让亚历克斯的答案在Sql Server 2008 R2上工作。所以，我用同样的基本原理重写了它。它现在支持模式，并且对列属性映射(包括将可为空的日期类型映射为可为空的c#值类型)进行了一些修复。下面是Sql语句:

   DECLARE @TableName VARCHAR(MAX) = 'NewsItem' -- Replace 'NewsItem' with your table name
    DECLARE @TableSchema VARCHAR(MAX) = 'Markets' -- Replace 'Markets' with your schema name
    DECLARE @result varchar(max) = ''

    SET @result = @result + 'using System;' + CHAR(13) + CHAR(13) 

    IF (@TableSchema IS NOT NULL) 
    BEGIN
        SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13) 
    END

    SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13) 

    SET @result = @result + '#region Instance Properties' + CHAR(13)  

   SELECT
      @result = @result + CHAR(13)
      + ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
    FROM (SELECT
      c.COLUMN_NAME AS ColumnName,
      CASE c.DATA_TYPE
        WHEN 'bigint' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Int64?'
            ELSE 'Int64'
          END
        WHEN 'binary' THEN 'Byte[]'
        WHEN 'bit' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'bool?'
            ELSE 'bool'
          END
        WHEN 'char' THEN 'string'
        WHEN 'date' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'datetime' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'datetime2' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'datetimeoffset' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTimeOffset?'
            ELSE 'DateTimeOffset'
          END
        WHEN 'decimal' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'float' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Single?'
            ELSE 'Single'
          END
        WHEN 'image' THEN 'Byte[]'
        WHEN 'int' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'int?'
            ELSE 'int'
          END
        WHEN 'money' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'nchar' THEN 'string'
        WHEN 'ntext' THEN 'string'
        WHEN 'numeric' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'nvarchar' THEN 'string'
        WHEN 'real' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Double?'
            ELSE 'Double'
          END
        WHEN 'smalldatetime' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'smallint' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Int16?'
            ELSE 'Int16'
          END
        WHEN 'smallmoney' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'text' THEN 'string'
        WHEN 'time' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'TimeSpan?'
            ELSE 'TimeSpan'
          END
        WHEN 'timestamp' THEN 'Byte[]'
        WHEN 'tinyint' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Byte?'
            ELSE 'Byte'
          END
        WHEN 'uniqueidentifier' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Guid?'
            ELSE 'Guid'
          END
        WHEN 'varbinary' THEN 'Byte[]'
        WHEN 'varchar' THEN 'string'
        ELSE 'Object'
      END AS ColumnType,
      c.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @TableName
    AND ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA) t
    ORDER BY t.ORDINAL_POSITION

    SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)  

    SET @result = @result  + '}' + CHAR(13)

    IF (@TableSchema IS NOT NULL) 
    BEGIN
        SET @result = @result + CHAR(13) + '}' 
    END

    PRINT @result

它生成的c#代码如下所示:

using System;

namespace Markets
{
    public class NewsItem        {
        #region Instance Properties

        public Int32 NewsItemID { get; set; }

        public Int32? TextID { get; set; }

        public String Description { get; set; }

        #endregion Instance Properties
    }

}

It may be an idea to use EF, Linq to Sql, or even Scaffolding; however, there are times when a piece of coding like this comes in handy. Frankly, I do not like using EF navigation properties where the code it generates made 19,200 separate database calls to populate a 1000 row grid. This could have been achieved in a single database call. Nonetheless, it could just be that your technical architect does not want you to use EF and the like. So, you have to revert to code like this... Incidentally, it may also be an idea to decorate each of the properties with attributes for DataAnnotations, etc., but I'm keeping this strictly POCO.

编辑修正了时间戳和Guid?

2012-11-17 13:02:41

其他回答

我喜欢用私有本地成员和公共访问器/突变器来设置我的类。因此，我修改了上面的Alex脚本，以便为任何感兴趣的人做到这一点。

declare @TableName sysname = 'TABLE_NAME'
declare @result varchar(max) = 'public class ' + @TableName + '
{'

SET @result = @result + 
'
    public ' + @TableName + '()
    {}
';

select @result = @result + '
    private ' + ColumnType + ' ' + ' m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';'
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 ColumnType
    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 column_id

SET @result = @result + '
'

select @result = @result + '
    public ' + ColumnType + ' ' + ColumnName + ' { get { return m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';} set {m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ' = value;} }' 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 ColumnType
    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 column_id

set @result = @result  + '
}'

print @result

2014-07-17 20:08:22

商业化，但codessmith Generator做到了:http://www.codesmithtools.com/product/generator

2011-05-03 17:22:38

你刚刚做了，只要你的表包含两列，并被称为'tblPeople'之类的东西。

您总是可以编写自己的SQL包装器。实际上我更喜欢这样做，我讨厌生成代码，以任何方式。

也许创建一个DAL类，并有一个名为GetPerson(int id)的方法，用于查询数据库中的person，然后从结果集中创建person对象。

2011-05-03 17:19:46

从Postgres DB生成

DO $$ DECLARE v_tabela varchar; DECLARE v_cursor_colunas record; DECLARE v_nome_coluna varchar; DECLARE v_classe VARCHAR; DECLARE v_tipo VARCHAR; DECLARE v_schema_name VARCHAR; BEGIN v_schema_name := 'my-schema'; v_tabela := 'my-table'; select table_name INTO v_tabela from information_schema.tables where table_schema = v_schema_name and table_type = 'BASE TABLE' and table_name = v_tabela; v_classe := E'\r\n' || 'public class ' || v_tabela || ' {' || E'\r\n'; FOR v_cursor_colunas IN SELECT column_name as coluna, is_nullable as isnull, data_type as tipo, character_maximum_length as tamanho FROM information_schema.columns WHERE table_schema = v_schema_name AND table_name = v_tabela LOOP IF v_cursor_colunas.tipo='character varying' THEN v_tipo:= 'string'; ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho=1 THEN v_tipo:= 'char'; ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho<>1 THEN v_tipo:= 'string'; ELSIF v_cursor_colunas.tipo like 'timestamp%' THEN v_tipo:= 'DateTime'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'DateTime?'; END IF; ELSIF v_cursor_colunas.tipo='boolean' THEN v_tipo:= 'bool'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'bool?'; END IF; ELSIF v_cursor_colunas.tipo='integer' THEN v_tipo:= 'int'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'int?'; END IF; ELSIF v_cursor_colunas.tipo='numeric' THEN v_tipo:= 'double'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'double?'; END IF; ELSIF v_cursor_colunas.tipo='text' THEN v_tipo:= 'string'; ELSE v_tipo:= 'another'; END IF; v_nome_coluna := v_cursor_colunas.coluna; v_classe := v_classe || 'public ' || v_tipo || ' ' || v_cursor_colunas.coluna || ' { get; set; }' || E'\r\n'; END LOOP;

v_class:= v_class || E'\r\n' || '}'; RAISE NOTICE '%'， v_class; $ $;

2021-05-18 15:25:19

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

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

前提条件:安装node.js

需要更改:

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

节点index.js

输出

2022-06-29 07:28:06

从数据库表生成类

推荐文章

最新文章

标签