如何从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 |
+----+-------+----------------+
当前回答
为了感谢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!
其他回答
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'
从顶部回复略有修改:
declare @TableName sysname = 'HistoricCommand'
declare @Result varchar(max) = '[System.Data.Linq.Mapping.Table(Name = "' + @TableName + '")]
public class Dbo' + @TableName + '
{'
select @Result = @Result + '
[System.Data.Linq.Mapping.Column(Name = "' + t.ColumnName + '", IsPrimaryKey = ' + pkk.ISPK + ')]
public ' + ColumnType + NullableSign + ' ' + t.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 'string'
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,
(
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
where pkk.ColumnName = t.ColumnName
order by ColumnId
set @Result = @Result + '
}'
print @Result
这使得输出需要完整的LINQ在c#声明
[System.Data.Linq.Mapping.Table(Name = "HistoricCommand")]
public class DboHistoricCommand
{
[System.Data.Linq.Mapping.Column(Name = "HistoricCommandId", IsPrimaryKey = true)]
public int HistoricCommandId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "PHCloudSoftwareInstanceId", IsPrimaryKey = true)]
public int PHCloudSoftwareInstanceId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "CommandType", IsPrimaryKey = false)]
public int CommandType { get; set; }
[System.Data.Linq.Mapping.Column(Name = "InitiatedDateTime", IsPrimaryKey = false)]
public DateTime InitiatedDateTime { get; set; }
[System.Data.Linq.Mapping.Column(Name = "CompletedDateTime", IsPrimaryKey = false)]
public DateTime CompletedDateTime { get; set; }
[System.Data.Linq.Mapping.Column(Name = "WasSuccessful", IsPrimaryKey = false)]
public bool WasSuccessful { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Message", IsPrimaryKey = false)]
public string Message { get; set; }
[System.Data.Linq.Mapping.Column(Name = "ResponseData", IsPrimaryKey = false)]
public string ResponseData { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Message_orig", IsPrimaryKey = false)]
public string Message_orig { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Message_XX", IsPrimaryKey = false)]
public string Message_XX { get; set; }
}
你刚刚做了，只要你的表包含两列，并被称为'tblPeople'之类的东西。
您总是可以编写自己的SQL包装器。实际上我更喜欢这样做，我讨厌生成代码，以任何方式。
也许创建一个DAL类，并有一个名为GetPerson(int id)的方法，用于查询数据库中的person，然后从结果集中创建person对象。
之前的解决方案的一个小补充: object_id(@TableName)仅在默认模式中有效。
(Select id from sysobjects where name = @TableName)
只要@tableName是唯一的，就可以在任何模式中工作。
最简单的方法是反向工程。http://msdn.microsoft.com/en-US/data/jj593170
