从数据库表生成类

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

当前回答

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; }
}

2021-08-23 13:39:30

其他回答

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

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

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; }
}

2021-08-23 13:39:30

这篇文章救了我好几次。我只是想补充我的意见。对于那些不喜欢使用orm，而是编写自己的DAL类的人来说，当你在一个表中有20个列，40个不同的表有各自的CRUD操作时，这是痛苦和浪费时间的。我重复了上面的代码，用于基于表实体和属性生成CRUD方法。

 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 '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,
        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

declare @InitDataAccess varchar(max) = 'public class '+ @TableName +'DataAccess 
{ '

declare @ListStatement varchar(max) ='public List<'+@TableName+'> Get'+@TableName+'List()
{
 String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
 var itemList = new List<'+@TableName+'>();
          try
            {
                using (var sqlCon = new SqlConnection(conn))
                {
                    sqlCon.Open();
                    var cmd = new SqlCommand
                    {
                        Connection = sqlCon,
                        CommandType = CommandType.StoredProcedure,
                        CommandText = "StoredProcedureSelectAll"
                    };
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                      var item = new '+@TableName+'();
' 
select @ListStatement = @ListStatement + '
item.'+ ColumnName + '= ('+ ColumnType + NullableSign  +')reader["'+ColumnName+'"];
'
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 '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,
        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

select @ListStatement = @ListStatement +'
                        itemList.Add(item);
                    }

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return itemList;
        }'

declare @GetIndividual varchar(max) =  
'public '+@TableName+' Get'+@TableName+'()
{
 String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
 var item = new '+@TableName+'();
          try
            {
                using (var sqlCon = new SqlConnection(conn))
                {
                    sqlCon.Open();
                    var cmd = new SqlCommand
                    {
                        Connection = sqlCon,
                        CommandType = CommandType.StoredProcedure,
                        CommandText = "StoredProcedureSelectIndividual"
                    };
                     cmd.Parameters.AddWithValue("@ItemCriteria", item.id);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {' 
select @GetIndividual = @GetIndividual + '
item.'+ ColumnName + '= ('+ ColumnType + NullableSign  +')reader["'+ColumnName+'"];
'
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 '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,
        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

select @GetIndividual = @GetIndividual +'

                    }

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return item;
        }'



declare @InsertStatement varchar(max) = 'public void  Insert'+@TableName+'('+@TableName+' item)
{
 String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;

          try
            {
                using (var sqlCon = new SqlConnection(conn))
                {
                    sqlCon.Open();
                    var cmd = new SqlCommand
                    {
                        Connection = sqlCon,
                        CommandType = CommandType.StoredProcedure,
                        CommandText = "StoredProcedureInsert"
                    };

                    ' 
select @InsertStatement = @InsertStatement + '
 cmd.Parameters.AddWithValue("@'+ColumnName+'", item.'+ColumnName+');
'
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 '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,
        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

select @InsertStatement = @InsertStatement +'

                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }'

declare @UpdateStatement varchar(max) = 'public void  Update'+@TableName+'('+@TableName+' item)
{
 String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;

          try
            {
                using (var sqlCon = new SqlConnection(conn))
                {
                    sqlCon.Open();
                    var cmd = new SqlCommand
                    {
                        Connection = sqlCon,
                        CommandType = CommandType.StoredProcedure,
                        CommandText = "StoredProcedureUpdate"
                    };
                    cmd.Parameters.AddWithValue("@UpdateCriteria", item.Id);
                    ' 
select @UpdateStatement = @UpdateStatement + '
 cmd.Parameters.AddWithValue("@'+ColumnName+'", item.'+ColumnName+');
'
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 '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,
        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

select @UpdateStatement = @UpdateStatement +'

                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }'

declare @EndDataAccess varchar(max)  = '
}'
 print @InitDataAccess
 print @GetIndividual
print @InsertStatement
print @UpdateStatement
print @ListStatement
print @EndDataAccess

当然它不是防弹代码，并且可以改进。只是想为这个优秀的解决方案做点贡献

2016-07-02 02:55:04

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'

2012-03-26 13:28:30

从数据库表生成类

推荐文章

最新文章

标签