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



当前回答

Java类生成

declare @TableName varchar(max) = 'Restaurants'
declare @Templete varchar(max) = ' 
     public @ColumnType @ColumnName ; // @ColumnDesc  
     public @ColumnType get@ColumnName()
     {
        return this.@ColumnName;
     }
     public void set@ColumnName(@ColumnType @ColumnName)
     {
        this.@ColumnName=@ColumnName;
     }

     '
declare @before varchar(max)='public class  @TableName  
{'
   
declare @after varchar(max)='
}'



declare @result varchar(max)

set @before =replace(@before,'@TableName',@TableName)

set @result=@before

select @result = @result 
+ replace(replace(replace(replace(replace(@Templete,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)

from  
(
    select 
    column_id,
    replace(col.name, ' ', '_') ColumnName,
    typ.name as sqltype,
    typ.max_length,
    is_identity,
    pkk.ISPK, 
        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 ColumnType,
      isnull(colDesc.colDesc,'') AS ColumnDesc 
    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 join
            (
                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  on ColumnName=col.name

    OUTER APPLY (
    SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
    FROM
       sys.extended_properties
    WHERE
       major_id = col.object_id
       AND
       minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
    ) colDesc      
    where object_id = object_id(@TableName)

    ) t

    set @result=@result+@after

    select @result
    --print @result

其他回答

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

Oracle DB的另一个解决方案-> c#

单查询 无功能无步骤 Mulitple表

添加DataAnnotations

(例子) (需要) (表) (StringLength) (列) 可以为空

查询

https://gist.github.com/omansak/f19eefffd2d639ac72a1f4b506d8471a

输出

[Table("AGENTS")]
public class Agents
{
    [Key]
    [Required]
    [Column("INT_ID", TypeName = "NUMBER(10,0)", Order = 1)]
    public long IntId { get; set; }
    [Key]
    [Required]
    [StringLength(15)]
    [Column("REFERENCE_CODE", TypeName = "VARCHAR2(15)", Order = 2)]
    public string ReferenceCode { get; set; }
    [Required]
    [Column("PARENT_INT_ID", TypeName = "NUMBER(10,0)", Order = 3)]
    public long ParentIntId { get; set; }
    [Required]
    [StringLength(200)]
    [Column("TITLE", TypeName = "VARCHAR2(200)", Order = 4)]
    public string Title { get; set; }
    [Required]
    [Column("START_DATE", TypeName = "DATE", Order = 5)]
    public DateTime StartDate { get; set; }
    [Required]
    [Column("END_DATE", TypeName = "DATE", Order = 6)]
    public DateTime EndDate { get; set; }
    [Required]
    [StringLength(1)]
    [Column("AGENT_TYPE", TypeName = "VARCHAR2(1)", Order = 7)]
    public string AgentType { get; set; }
    [Required]
    [Column("CREATE_DATE", TypeName = "DATE", Order = 8)]
    public DateTime CreateDate { get; set; }
    [Required]
    [StringLength(32)]
    [Column("CREATE_USER", TypeName = "VARCHAR2(32)", Order = 9)]
    public string CreateUser { get; set; }
    [StringLength(200)]
    [Column("RESPONSIBLE_CONTACT", TypeName = "VARCHAR2(200)", Order = 10)]
    public string ResponsibleContact { get; set; }
    [StringLength(100)]
    [Column("RESPONSIBLE_TITLE", TypeName = "VARCHAR2(100)", Order = 11)]
    public string ResponsibleTitle { get; set; }
    [StringLength(100)]
    [Column("AGENCY_PLATE_NO", TypeName = "VARCHAR2(100)", Order = 12)]
    public string AgencyPlateNo { get; set; }
    [Column("AGENCY_COVER_AMOUNT", TypeName = "NUMBER(24,2)", Order = 13)]
    public double? AgencyCoverAmount { get; set; }
    [StringLength(100)]
    [Column("MERSIS_NO", TypeName = "VARCHAR2(100)", Order = 14)]
    public string MersisNo { get; set; }
    [StringLength(100)]
    [Column("TECH_PERSONEL_NO", TypeName = "VARCHAR2(100)", Order = 15)]
    public string TechPersonelNo { get; set; }
    [StringLength(100)]
    [Column("TECH_PERSONEL_NAME", TypeName = "VARCHAR2(100)", Order = 16)]
    public string TechPersonelName { get; set; }
    [Column("COVER_END_DATE", TypeName = "DATE", Order = 17)]
    public DateTime? CoverEndDate { get; set; }
    [Column("BRANCH_NUMBER", TypeName = "NUMBER(10,0)", Order = 18)]
    public long? BranchNumber { get; set; }
    [Column("ACTION_NUMBER", TypeName = "NUMBER(10,0)", Order = 19)]
    public long? ActionNumber { get; set; }
    [Column("CLUB_PARTICIPATION_COUNT", TypeName = "NUMBER(10,0)", Order = 20)]
    public long? ClubParticipationCount { get; set; }
    [Column("AGENCY_CONTRACT_DATE", TypeName = "DATE", Order = 21)]
    public DateTime? AgencyContractDate { get; set; }
    [StringLength(200)]
    [Column("KEP_ADDRESS", TypeName = "VARCHAR2(200)", Order = 22)]
    public string KepAddress { get; set; }
}

Java类生成

declare @TableName varchar(max) = 'Restaurants'
declare @Templete varchar(max) = ' 
     public @ColumnType @ColumnName ; // @ColumnDesc  
     public @ColumnType get@ColumnName()
     {
        return this.@ColumnName;
     }
     public void set@ColumnName(@ColumnType @ColumnName)
     {
        this.@ColumnName=@ColumnName;
     }

     '
declare @before varchar(max)='public class  @TableName  
{'
   
declare @after varchar(max)='
}'



declare @result varchar(max)

set @before =replace(@before,'@TableName',@TableName)

set @result=@before

select @result = @result 
+ replace(replace(replace(replace(replace(@Templete,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)

from  
(
    select 
    column_id,
    replace(col.name, ' ', '_') ColumnName,
    typ.name as sqltype,
    typ.max_length,
    is_identity,
    pkk.ISPK, 
        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 ColumnType,
      isnull(colDesc.colDesc,'') AS ColumnDesc 
    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 join
            (
                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  on ColumnName=col.name

    OUTER APPLY (
    SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
    FROM
       sys.extended_properties
    WHERE
       major_id = col.object_id
       AND
       minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
    ) colDesc      
    where object_id = object_id(@TableName)

    ) t

    set @result=@result+@after

    select @result
    --print @result

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'

是的,这些是伟大的,如果你使用一个简单的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