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



当前回答

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

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

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

其他回答

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

Visual Studio杂志发表了这篇文章:

为SQL查询结果生成。net POCO类

它有一个可下载的项目,你可以构建,给它你的SQL信息,它会为你制作出类。

现在,如果该工具刚刚为SELECT、INSERT和UPDATE创建了SQL命令....

VB版

declare @TableName sysname = 'myTableName'
declare @prop varchar(max)
PRINT 'Public Class ' + @TableName
declare props cursor for
select distinct ' public property ' + ColumnName + ' AS ' + ColumnType AS prop
from ( 
    select  
        replace(col.name, ' ', '_') ColumnName,  column_id, 
        case typ.name  
            when 'bigint' then 'long' 
            when 'binary' then 'byte[]' 
            when 'bit' then 'boolean' 
            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 'integer' 
            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' 
        end ColumnType 
    from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id 
    where object_id = object_id(@TableName) 
) t 
order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
    print @prop
    FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT 'End Class'

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

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

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

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

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