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



当前回答

抓取QueryFirst, visual studio扩展,从SQL查询生成包装类。你不仅得到…

public class MyClass{
    public string MyProp{get;set;}
    public int MyNumberProp{get;set;}
    ...
}

作为奖励,它还会……

public class MyQuery{
    public static IEnumerable<MyClass>Execute(){}
    public static MyClass GetOne(){}
    ...
}

您确定要将类直接基于表吗?表是属于DB的静态规范化数据存储概念。类是动态的、流动的、一次性的、特定于上下文的,也许是非规范化的。为什么不为您想要的操作数据编写真正的查询,然后让QueryFirst从中生成类呢?

其他回答

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

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

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

设置@TableName为你的表名。

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

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + 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 '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
    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 ColumnId

set @Result = @Result  + '
}'

print @Result

我将几个基于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

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

为了感谢Alex的解决方案和Guilherme的要求,我为MySQL生成c#类做了这个

set @schema := 'schema_name';
set @table := 'table_name';
SET group_concat_max_len = 2048;
SELECT 
    concat('public class ', @table, '\n{\n', GROUP_CONCAT(a.property_ SEPARATOR '\n'), '\n}') class_
FROM 
    (select
        CONCAT(
        '\tpublic ',
        case 
            when DATA_TYPE = 'bigint' then 'long'
            when DATA_TYPE = 'BINARY' then 'byte[]'
            when DATA_TYPE = 'bit' then 'bool'
            when DATA_TYPE = 'char' then 'string'
            when DATA_TYPE = 'date' then 'DateTime'
            when DATA_TYPE = 'datetime' then 'DateTime'
            when DATA_TYPE = 'datetime2' then 'DateTime'
            when DATA_TYPE = 'datetimeoffset' then 'DateTimeOffset'
            when DATA_TYPE = 'decimal' then 'decimal'
            when DATA_TYPE = 'double' then 'double'
            when DATA_TYPE = 'float' then 'float'
            when DATA_TYPE = 'image' then 'byte[]'
            when DATA_TYPE = 'int' then 'int'
            when DATA_TYPE = 'money' then 'decimal'
            when DATA_TYPE = 'nchar' then 'char'
            when DATA_TYPE = 'ntext' then 'string'
            when DATA_TYPE = 'numeric' then 'decimal'
            when DATA_TYPE = 'nvarchar' then 'string'
            when DATA_TYPE = 'real' then 'double'
            when DATA_TYPE = 'smalldatetime' then 'DateTime'
            when DATA_TYPE = 'smallint' then 'short'
            when DATA_TYPE = 'smallmoney' then 'decimal'
            when DATA_TYPE = 'text' then 'string'
            when DATA_TYPE = 'time' then 'TimeSpan'
            when DATA_TYPE = 'timestamp' then 'DateTime'
            when DATA_TYPE = 'tinyint' then 'byte'
            when DATA_TYPE = 'uniqueidentifier' then 'Guid'
            when DATA_TYPE = 'varbinary' then 'byte[]'
            when DATA_TYPE = 'varchar' then 'string'
            else '_UNKNOWN_'
        end, ' ', 
        COLUMN_NAME, ' {get; set;}') as property_
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @table AND table_schema = @schema) a
;

谢谢Alex和Guilherme!