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



当前回答

我只是想给感兴趣的人加上我自己的答案。 主要特点有:

It will automatically generate classes for all the tables in the entire schema. Just specify the schema name. It will add System.Data.Linq.Mapping attributes to the class and each property. Useful for anyone using Linq to SQL. declare @TableName sysname declare @Result varchar(max) declare @schema varchar(20) = 'dbo' DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT tablename = rc1.TABLE_NAME FROM INFORMATION_SCHEMA.Tables rc1 where rc1.TABLE_SCHEMA = @schema OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName WHILE (@@FETCH_STATUS = 0) BEGIN set @Result = '[Table(Name = "' + @schema + '.' + @TableName + '")] public class ' + Replace(@TableName, '$', '_') + ' {' select @Result = @Result + ' [Column' + PriKey +'] public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, col.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 'string' when 'ntext' then 'string' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'float' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'long' 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, case when pk.CONSTRAINT_NAME is not null and ic.column_id is not null then '(IsPrimaryKey = true, IsDbGenerated = true)' when pk.CONSTRAINT_NAME is not null then '(IsPrimaryKey = true)' when ic.column_id is not null then '(IsDbGenerated = true)' else '' end PriKey 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 outer join sys.identity_columns ic on ic.column_id = col.column_id and col.object_id = ic.object_id left outer join ( SELECT K.TABLE_NAME , K.COLUMN_NAME , K.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME where C.CONSTRAINT_TYPE = 'PRIMARY KEY' ) pk on pk.COLUMN_NAME = col.name and pk.TABLE_NAME = @TableName where col.object_id = object_id(@schema + '.' + @TableName) ) t order by ColumnId set @Result = @Result + ' } ' print @Result FETCH NEXT FROM @Cursor INTO @TableName end CLOSE @Cursor DEALLOCATE @Cursor GO

增加了6-29-22:这是为EF Core (dotNet 6.0)生成模型的更新版本。

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN
    DECLARE @Index          INT
    DECLARE @Char           CHAR(1)
    DECLARE @PrevChar       CHAR(1)
    DECLARE @OutputString   VARCHAR(4000)

    SET @OutputString = @InputString
    SET @Index = 1

    WHILE @Index <= LEN(@InputString)
    BEGIN
        SET @Char     = SUBSTRING(@InputString, @Index, 1)
        SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                             ELSE SUBSTRING(@InputString, @Index - 1, 1)
                        END

        IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))

        SET @Index = @Index + 1
    END

    RETURN @OutputString
END
go


declare @TableName sysname
declare @Result varchar(max)
declare @schema varchar(20) = 'dbo'
DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT tablename = rc1.TABLE_NAME
FROM INFORMATION_SCHEMA.Tables rc1
where rc1.TABLE_SCHEMA = @schema

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName

WHILE (@@FETCH_STATUS = 0)
BEGIN
set @Result = '[Table("' + @TableName + '", Schema = "' + @schema + '")]
public class ' + Replace(@TableName, '$', '_') + '
{'

select @Result = @Result + '
    [Column("' + ColumnName + '"' + stringType + ')]
    public ' + ColumnType + NullableSign + ' ' + PropertyName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        replace(replace([dbo].[InitCap](col.name), ' ', ''), '_', '') PropertyName,
        col.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 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            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
            then '?' 
            else '' 
        end NullableSign,
        case
            when typ.name in ('char', 'nchar', 'nvarchar', 'varchar')
            then ', TypeName = "' + typ.name + '(' + convert(varchar, col.max_length) + ')"'
            else ''
        end stringType
    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 outer join sys.identity_columns ic on ic.column_id = col.column_id and col.object_id = ic.object_id
    left outer join (
        SELECT  K.TABLE_NAME ,
            K.COLUMN_NAME ,
            K.CONSTRAINT_NAME
        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
                JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
                                                                 AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
                                                                 AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
                                                                 AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
        where C.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) pk on pk.COLUMN_NAME = col.name and pk.TABLE_NAME = @TableName
    where col.object_id = object_id(@schema + '.' + @TableName)
) t
order by ColumnId

set @Result = @Result  + '
}

'

print @Result

FETCH NEXT FROM @Cursor INTO @TableName
end

CLOSE @Cursor DEALLOCATE @Cursor
GO

DROP FUNCTION [dbo].[InitCap]
GO

其他回答

我喜欢用私有本地成员和公共访问器/突变器来设置我的类。 因此,我修改了上面的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

有点晚了,但我已经创建了一个web工具来帮助创建一个c#(或其他)对象从SQL结果,SQL表和SQL SP。

sql2object.com

这可以让你安全地输入所有的属性和类型。

如果无法识别类型,则将选择默认类型。

如果它对其他人有用,使用属性映射处理Code-First方法,我想要一些只需要在对象模型中绑定实体的东西。所以感谢食肉牛的回答,我根据他们自己的建议扩展了它,并做了一些调整。

因此,这依赖于这个解决方案,包括两个部分,这两个部分都是SQL标量值函数:

一个'Initial Caps'函数(取自: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8a58dbe1-7a4b-4287-afdc-bfecb4e69b23/similar-to-initcap-in-sql-server-tsql 并略有修改,以满足我的需求)

ALTER function [dbo].[ProperCase] (@cStringToProper varchar(8000))
returns varchar(8000)
as
begin
   declare  @Position int
    select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1)))
        , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)

   while @Position > 0
         select @cStringToProper = stuff(@cStringToProper , @Position , 2 , upper(substring(@cStringToProper , @Position , 2)))
              , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)

  select @cStringToProper = replace(@cStringToProper, '_','')

   return @cStringToProper
end

输出函数本身,它扩展了食肉牛的解决方案: 正确输出换行符 执行一些基本的表格 写出合适的a [Table]映射(如建议的那样) 写出适当的[Column]映射,包括类型名(如建议的那样) 允许实体名称与表的名称不同 修复当表中有大量列时Print @Result截断的限制

CREATE FUNCTION [dbo].[GetEntityObject] (@NameSpace NVARCHAR(MAX), @TableName NVARCHAR(MAX), @EntityName NVARCHAR(MAX))  RETURNS NVARCHAR(MAX) AS BEGIN

DECLARE @result NVARCHAR(MAX)

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

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

SET @result = @result + '[Table(name: ' + CHAR(34) + @TableName + CHAR(34) + ')]' + CHAR(13) SET @result = @result + 'public class ' + @EntityName + CHAR(13) + '{' + CHAR(13) 

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

SELECT @result = @result + CHAR(13)     + '[Column(name: ' + CHAR(34) + OriginalColumnName + CHAR(34) + ', TypeName = ' + CHAR(34) + DataType
+ CHAR(34) + ')]' + CHAR(13)
    + 'public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)  FROM (
    SELECT dbo.ProperCase (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 'Boolean?' ELSE 'Boolean' 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 'Int32?' ELSE 'Int32' 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 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'tinyint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END                                                
            WHEN 'uniqueidentifier' THEN 'Guid'
            WHEN 'varbinary' THEN 'Byte[]'
            WHEN 'varchar' THEN 'String'
            ELSE 'Object'
        END AS ColumnType
        , c.ORDINAL_POSITION        , c.COLUMN_NAME as OriginalColumnName       ,c.DATA_TYPE as DataType

FROM    INFORMATION_SCHEMA.COLUMNS c WHERE   c.TABLE_NAME = @TableName) t ORDER BY t.ORDINAL_POSITION

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

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

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

return @result END

在MS SQL Management Studio中使用:

选择dbo。GetEntityObject(“我的名字”、“我的名字”、“我的名字”)

将生成一个可以复制粘贴到Visual Studio中的列值。

如果这对任何人都有帮助,那太好了!

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

要打印NULLABLE属性,请使用此命令。 它对Alex Aza的CASE语句块脚本进行了轻微修改。

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

select @result = @result + '
    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 AS [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