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