如何从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 |
+----+-------+----------------+
当前回答
使用模板创建自定义代码
create PROCEDURE [dbo].[createCode]
(
@TableName sysname = '',
@befor varchar(max)='public class @TableName
{',
@templet varchar(max)='
public @ColumnType @ColumnName { get; set; } // @ColumnDesc ',
@after varchar(max)='
}'
)
AS
BEGIN
declare @result varchar(max)
set @befor =replace(@befor,'@TableName',@TableName)
set @result=@befor
select @result = @result
+ replace(replace(replace(replace(replace(@templet,'@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
END
现在创建自定义代码
例如c#类
exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName { get; set; } // @ColumnDesc '
输出是
public class book
{
public long ID { get; set; } //
public String Title { get; set; } // Book Title
}
对LINQ
exec [createCode] @TableName='book'
, @befor ='[System.Data.Linq.Mapping.Table(Name = "@TableName")]
public class @TableName
{',
@templet ='
[System.Data.Linq.Mapping.Column(Name = "@ColumnName", IsPrimaryKey = @ISPK)]
public @ColumnType @ColumnName { get; set; } // @ColumnDesc
' ,
@after ='
}'
输出是
[System.Data.Linq.Mapping.Table(Name = "book")]
public class book
{
[System.Data.Linq.Mapping.Column(Name = "ID", IsPrimaryKey = true)]
public long ID { get; set; } //
[System.Data.Linq.Mapping.Column(Name = "Title", IsPrimaryKey = false)]
public String Title { get; set; } // Book Title
}
Java类
exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName ; // @ColumnDesc
public @ColumnType get@ColumnName()
{
return this.@ColumnName;
}
public void set@ColumnName(@ColumnType @ColumnName)
{
this.@ColumnName=@ColumnName;
}
'
输出是
public class book
{
public long ID ; //
public long getID()
{
return this.ID;
}
public void setID(long ID)
{
this.ID=ID;
}
public String Title ; // Book Title
public String getTitle()
{
return this.Title;
}
public void setTitle(String Title)
{
this.Title=Title;
}
}
为android sugarOrm模型
exec [createCode] @TableName='book'
, @befor ='@Table(name = "@TableName")
public class @TableName
{',
@templet ='
@Column(name = "@ColumnName")
public @ColumnType @ColumnName ;// @ColumnDesc
' ,
@after ='
}'
输出是
@Table(name = "book")
public class book
{
@Column(name = "ID")
public long ID ;//
@Column(name = "Title")
public String Title ;// Book Title
}
其他回答
Visual Studio杂志发表了这篇文章:
为SQL查询结果生成。net POCO类
它有一个可下载的项目，你可以构建，给它你的SQL信息，它会为你制作出类。
现在，如果该工具刚刚为SELECT、INSERT和UPDATE创建了SQL命令....
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; }
}
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'
使用模板创建自定义代码
create PROCEDURE [dbo].[createCode]
(
@TableName sysname = '',
@befor varchar(max)='public class @TableName
{',
@templet varchar(max)='
public @ColumnType @ColumnName { get; set; } // @ColumnDesc ',
@after varchar(max)='
}'
)
AS
BEGIN
declare @result varchar(max)
set @befor =replace(@befor,'@TableName',@TableName)
set @result=@befor
select @result = @result
+ replace(replace(replace(replace(replace(@templet,'@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
END
现在创建自定义代码
例如c#类
exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName { get; set; } // @ColumnDesc '
输出是
public class book
{
public long ID { get; set; } //
public String Title { get; set; } // Book Title
}
对LINQ
exec [createCode] @TableName='book'
, @befor ='[System.Data.Linq.Mapping.Table(Name = "@TableName")]
public class @TableName
{',
@templet ='
[System.Data.Linq.Mapping.Column(Name = "@ColumnName", IsPrimaryKey = @ISPK)]
public @ColumnType @ColumnName { get; set; } // @ColumnDesc
' ,
@after ='
}'
输出是
[System.Data.Linq.Mapping.Table(Name = "book")]
public class book
{
[System.Data.Linq.Mapping.Column(Name = "ID", IsPrimaryKey = true)]
public long ID { get; set; } //
[System.Data.Linq.Mapping.Column(Name = "Title", IsPrimaryKey = false)]
public String Title { get; set; } // Book Title
}
Java类
exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName ; // @ColumnDesc
public @ColumnType get@ColumnName()
{
return this.@ColumnName;
}
public void set@ColumnName(@ColumnType @ColumnName)
{
this.@ColumnName=@ColumnName;
}
'
输出是
public class book
{
public long ID ; //
public long getID()
{
return this.ID;
}
public void setID(long ID)
{
this.ID=ID;
}
public String Title ; // Book Title
public String getTitle()
{
return this.Title;
}
public void setTitle(String Title)
{
this.Title=Title;
}
}
为android sugarOrm模型
exec [createCode] @TableName='book'
, @befor ='@Table(name = "@TableName")
public class @TableName
{',
@templet ='
@Column(name = "@ColumnName")
public @ColumnType @ColumnName ;// @ColumnDesc
' ,
@after ='
}'
输出是
@Table(name = "book")
public class book
{
@Column(name = "ID")
public long ID ;//
@Column(name = "Title")
public String Title ;// Book Title
}
Postgres DB -> c#的另一个解决方案
兼容NpgSQL 添加DataAnnotations
SELECT
CASE
WHEN c.is_nullable ='NO' THEN '[Required]'||chr(10)
ELSE '' END
||CASE
WHEN c.data_type = 'character varying' THEN format('[StringLength(%s)]',c.character_maximum_length)||chr(10)
ELSE '' END
||'public '
||CASE
WHEN c.data_type = 'integer' THEN 'int'
WHEN c.data_type = 'boolean' THEN 'bool'
WHEN c.data_type = 'double precision' THEN 'double'
WHEN c.data_type = 'uuid' THEN 'Guid'
WHEN c.data_type = 'character varying' THEN 'string'
WHEN c.data_type = 'timestamp without time zone' THEN 'DateTime'
WHEN c.data_type = 'bigint' THEN 'long'
WHEN c.data_type = 'bytea' THEN 'byte[]'
ELSE 'object' END
||CASE
WHEN c.is_nullable='YES' AND NOT c.data_type = 'character varying' THEN '? '
ELSE ' ' END
||c.column_name||' {get;set;}'
,c.*
FROM information_schema."columns" c
WHERE 1=1
AND c.table_name='YOUR_TABLE_NAME'
