如何从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 |
+----+-------+----------------+



当前回答

之前的解决方案的一个小补充: object_id(@TableName)仅在默认模式中有效。

(Select id from sysobjects where name = @TableName)

只要@tableName是唯一的,就可以在任何模式中工作。

其他回答

我将几个基于SQL的答案(主要是Alex Aza的根答案)打包到kassify中,这是一个控制台应用程序,可以一次性为指定的数据库生成所有类:


例如,给定一个Users表,它是这样的:

+----+------------------+-----------+---------------------+
| Id |       Name       | Username  |        Email        |
+----+------------------+-----------+---------------------+
|  1 | Leanne Graham    | Bret      | Sincere@april.biz   |
|  2 | Ervin Howell     | Antonette | Shanna@melissa.tv   |
|  3 | Clementine Bauch | Samantha  | Nathan@yesenia.net  |
+----+------------------+-----------+---------------------+

klassify将生成一个名为Users.cs的文件,看起来像这样:

    public class User 
    {
        public int Id {get; set; }
        public string Name { get;set; }
        public string Username { get; set; }
        public string Email { get; set; }
    }

它将为每个表输出一个文件。丢弃你不用的东西。

使用

 --out, -o:
        output directory     << defaults to the current directory >>
 --user, -u:
        sql server user id   << required >>
 --password, -p:
        sql server password  << required >>
 --server, -s:
        sql server           << defaults to localhost >>
 --database, -d:
        sql database         << required >>
 --timeout, -t:
        connection timeout   << defaults to 30 >>
 --help, -h:
        show help

Java类生成

declare @TableName varchar(max) = 'Restaurants'
declare @Templete varchar(max) = ' 
     public @ColumnType @ColumnName ; // @ColumnDesc  
     public @ColumnType get@ColumnName()
     {
        return this.@ColumnName;
     }
     public void set@ColumnName(@ColumnType @ColumnName)
     {
        this.@ColumnName=@ColumnName;
     }

     '
declare @before varchar(max)='public class  @TableName  
{'
   
declare @after varchar(max)='
}'



declare @result varchar(max)

set @before =replace(@before,'@TableName',@TableName)

set @result=@before

select @result = @result 
+ replace(replace(replace(replace(replace(@Templete,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)

from  
(
    select 
    column_id,
    replace(col.name, ' ', '_') ColumnName,
    typ.name as sqltype,
    typ.max_length,
    is_identity,
    pkk.ISPK, 
        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,
      isnull(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
            left join
            (
                SELECT c.name  AS 'ColumnName', CASE WHEN dd.pk IS NULL THEN 'false' ELSE 'true' END ISPK           
                FROM        sys.columns c
                    JOIN    sys.tables  t   ON c.object_id = t.object_id    
                    LEFT JOIN (SELECT   K.COLUMN_NAME , C.CONSTRAINT_TYPE as pk  
                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
                            LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
                        ON K.TABLE_NAME = C.TABLE_NAME
                            AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
                            AND K.CONSTRAINT_CATALOG = C.CONSTRAINT_CATALOG
                            AND K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA            
                        WHERE K.TABLE_NAME = @TableName) as dd
                     ON dd.COLUMN_NAME = c.name
                 WHERE       t.name = @TableName       
            ) pkk  on ColumnName=col.name

    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

    set @result=@result+@after

    select @result
    --print @result

如果你可以访问SQL Server 2016,你可以使用FOR JSON(包含INCLUDE_NULL_VALUES)选项从选择语句中获取JSON输出。复制输出,然后在Visual Studio中,粘贴special ->,粘贴JSON作为类。

算是预算上的解决方案,但可能会节省一些时间。

我无法让亚历克斯的答案在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?

使用模板创建自定义代码

create PROCEDURE [dbo].[createCode]
(   
   @TableName sysname = '',
   @befor varchar(max)='public class  @TableName  
{',
   @templet varchar(max)=' 
     public @ColumnType @ColumnName   { get; set; }  // @ColumnDesc  ',
   @after varchar(max)='
}'

)
AS
BEGIN 


declare @result varchar(max)

set @befor =replace(@befor,'@TableName',@TableName)

set @result=@befor

select @result = @result 
+ replace(replace(replace(replace(replace(@templet,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)

from  
(
    select 
    column_id,
    replace(col.name, ' ', '_') ColumnName,
    typ.name as sqltype,
    typ.max_length,
    is_identity,
    pkk.ISPK, 
        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,
      isnull(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
            left join
            (
                SELECT c.name  AS 'ColumnName', CASE WHEN dd.pk IS NULL THEN 'false' ELSE 'true' END ISPK           
                FROM        sys.columns c
                    JOIN    sys.tables  t   ON c.object_id = t.object_id    
                    LEFT JOIN (SELECT   K.COLUMN_NAME , C.CONSTRAINT_TYPE as pk  
                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
                            LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
                        ON K.TABLE_NAME = C.TABLE_NAME
                            AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
                            AND K.CONSTRAINT_CATALOG = C.CONSTRAINT_CATALOG
                            AND K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA            
                        WHERE K.TABLE_NAME = @TableName) as dd
                     ON dd.COLUMN_NAME = c.name
                 WHERE       t.name = @TableName       
            ) pkk  on ColumnName=col.name

    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

    set @result=@result+@after

    select @result
    --print @result

END

现在创建自定义代码

例如c#类

exec [createCode] @TableName='book',@templet =' 
     public @ColumnType @ColumnName   { get; set; }  // @ColumnDesc  '

输出是

public class  book  
{ 
     public long ID   { get; set; }  //    
     public String Title   { get; set; }  // Book Title  
}

对LINQ

exec [createCode] @TableName='book'
, @befor  ='[System.Data.Linq.Mapping.Table(Name = "@TableName")]
public class @TableName
{',

   @templet  =' 
     [System.Data.Linq.Mapping.Column(Name = "@ColumnName", IsPrimaryKey = @ISPK)]
     public @ColumnType @ColumnName   { get; set; }  // @ColumnDesc  
     ' ,

   @after  ='
}'

输出是

[System.Data.Linq.Mapping.Table(Name = "book")]
public class book
{ 
     [System.Data.Linq.Mapping.Column(Name = "ID", IsPrimaryKey = true)]
     public long ID   { get; set; }  //   

     [System.Data.Linq.Mapping.Column(Name = "Title", IsPrimaryKey = false)]
     public String Title   { get; set; }  // Book Title  

}

Java类

exec [createCode] @TableName='book',@templet =' 
     public @ColumnType @ColumnName ; // @ColumnDesc  
     public @ColumnType get@ColumnName()
     {
        return this.@ColumnName;
     }
     public void set@ColumnName(@ColumnType @ColumnName)
     {
        this.@ColumnName=@ColumnName;
     }

     '

输出是

public class  book  
{ 
     public long ID ; //   
     public long getID()
     {
        return this.ID;
     }
     public void setID(long ID)
     {
        this.ID=ID;
     }


     public String Title ; // Book Title  
     public String getTitle()
     {
        return this.Title;
     }
     public void setTitle(String Title)
     {
        this.Title=Title;
     } 
}

为android sugarOrm模型

exec [createCode] @TableName='book'
, @befor  ='@Table(name = "@TableName")
public class @TableName
{',
   @templet  =' 
     @Column(name = "@ColumnName")
     public @ColumnType @ColumnName ;// @ColumnDesc  
     ' ,
   @after  ='
}'

输出是

@Table(name = "book")
public class book
{ 
     @Column(name = "ID")
     public long ID ;//   

     @Column(name = "Title")
     public String Title ;// Book Title  

}