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



当前回答

从顶部回复略有修改:

declare @TableName sysname = 'HistoricCommand'

declare @Result varchar(max) = '[System.Data.Linq.Mapping.Table(Name = "' + @TableName + '")]
public class Dbo' + @TableName + '
{'

select @Result = @Result + '
    [System.Data.Linq.Mapping.Column(Name = "' + t.ColumnName + '", IsPrimaryKey = ' + pkk.ISPK + ')]
    public ' + ColumnType + NullableSign + ' ' + t.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 'string'
            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, 
(
                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
where pkk.ColumnName = t.ColumnName
order by ColumnId

set @Result = @Result  + '
}'

print @Result

这使得输出需要完整的LINQ在c#声明

[System.Data.Linq.Mapping.Table(Name = "HistoricCommand")]
public class DboHistoricCommand
{
    [System.Data.Linq.Mapping.Column(Name = "HistoricCommandId", IsPrimaryKey = true)]
    public int HistoricCommandId { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "PHCloudSoftwareInstanceId", IsPrimaryKey = true)]
    public int PHCloudSoftwareInstanceId { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "CommandType", IsPrimaryKey = false)]
    public int CommandType { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "InitiatedDateTime", IsPrimaryKey = false)]
    public DateTime InitiatedDateTime { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "CompletedDateTime", IsPrimaryKey = false)]
    public DateTime CompletedDateTime { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "WasSuccessful", IsPrimaryKey = false)]
    public bool WasSuccessful { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "Message", IsPrimaryKey = false)]
    public string Message { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "ResponseData", IsPrimaryKey = false)]
    public string ResponseData { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "Message_orig", IsPrimaryKey = false)]
    public string Message_orig { get; set; }

    [System.Data.Linq.Mapping.Column(Name = "Message_XX", IsPrimaryKey = false)]
    public string Message_XX { get; set; }

}

其他回答

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

(Select id from sysobjects where name = @TableName)

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

要打印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

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

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

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

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'