从数据库表生成类

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

当前回答

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

2013-10-01 18:14:22

其他回答

如果它对其他人有用，使用属性映射处理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中的列值。

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

2015-10-26 11:12:32

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

2011-05-03 17:22:38

设置@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

2011-05-03 17:21:33

是的，这些是伟大的，如果你使用一个简单的ORM像Dapper。

如果你使用。net，你可以在运行时使用WriteXmlSchema方法用任何数据集生成XSD文件。http://msdn.microsoft.com/en-us/library/xt7k72x8 (v = vs.110) . aspx

是这样的:

using (SqlConnection cnn = new SqlConnection(mConnStr)) {
DataSet Data = new DataSet();
cnn.Open();
string sql = "SELECT * FROM Person";

using (SqlDataAdapter Da = new SqlDataAdapter(sql, cnn))
{
try
{
    Da.Fill(Data);
    Da.TableMappings.Add("Table", "Person");
    Data.WriteXmlSchema(@"C:\Person.xsd");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
cnn.Close();

从那里，您可以使用xsd.exe创建一个可从开发人员命令提示符序列化XML的类。 http://msdn.microsoft.com/en-us/library/x6c1kb0s (v = vs.110) . aspx

是这样的:

xsd C:\Person.xsd /classes /language:CS

2014-07-31 07:57:31

为了感谢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!

2015-02-26 13:03:09

从数据库表生成类

推荐文章

最新文章

标签