如何从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 |
+----+-------+----------------+
当前回答
抓取QueryFirst, visual studio扩展，从SQL查询生成包装类。你不仅得到…
public class MyClass{
public string MyProp{get;set;}
public int MyNumberProp{get;set;}
...
}
作为奖励，它还会……
public class MyQuery{
public static IEnumerable<MyClass>Execute(){}
public static MyClass GetOne(){}
...
}
您确定要将类直接基于表吗?表是属于DB的静态规范化数据存储概念。类是动态的、流动的、一次性的、特定于上下文的，也许是非规范化的。为什么不为您想要的操作数据编写真正的查询，然后让QueryFirst从中生成类呢?
其他回答
为了感谢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!
要打印带有注释(摘要)的NULLABLE属性，使用这个。 这是对第一个答案的轻微修改
declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result
+ CASE WHEN ColumnDesc IS NOT NULL THEN '
/// <summary>
/// ' + ColumnDesc + '
/// </summary>' ELSE '' END
+ '
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 ColumnType,
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
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
order by column_id
set @result = @result + '
}'
print @result
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
我无法让亚历克斯的答案在Sql Server 2008 R2上工作。所以，我用同样的基本原理重写了它。它现在支持模式，并且对列属性映射(包括将可为空的日期类型映射为可为空的c#值类型)进行了一些修复。下面是Sql语句:
DECLARE @TableName VARCHAR(MAX) = 'NewsItem' -- Replace 'NewsItem' with your table name
DECLARE @TableSchema VARCHAR(MAX) = 'Markets' -- Replace 'Markets' with your schema name
DECLARE @result varchar(max) = ''
SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)
IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + 'namespace ' + @TableSchema + CHAR(13) + '{' + CHAR(13)
END
SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13)
SET @result = @result + '#region Instance Properties' + CHAR(13)
SELECT
@result = @result + CHAR(13)
+ ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
FROM (SELECT
c.COLUMN_NAME AS ColumnName,
CASE c.DATA_TYPE
WHEN 'bigint' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int64?'
ELSE 'Int64'
END
WHEN 'binary' THEN 'Byte[]'
WHEN 'bit' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'bool?'
ELSE 'bool'
END
WHEN 'char' THEN 'string'
WHEN 'date' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'datetime' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'datetime2' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'datetimeoffset' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTimeOffset?'
ELSE 'DateTimeOffset'
END
WHEN 'decimal' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'float' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Single?'
ELSE 'Single'
END
WHEN 'image' THEN 'Byte[]'
WHEN 'int' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'int?'
ELSE 'int'
END
WHEN 'money' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'nchar' THEN 'string'
WHEN 'ntext' THEN 'string'
WHEN 'numeric' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'nvarchar' THEN 'string'
WHEN 'real' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Double?'
ELSE 'Double'
END
WHEN 'smalldatetime' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'smallint' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int16?'
ELSE 'Int16'
END
WHEN 'smallmoney' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'text' THEN 'string'
WHEN 'time' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'TimeSpan?'
ELSE 'TimeSpan'
END
WHEN 'timestamp' THEN 'Byte[]'
WHEN 'tinyint' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Byte?'
ELSE 'Byte'
END
WHEN 'uniqueidentifier' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Guid?'
ELSE 'Guid'
END
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'varchar' THEN 'string'
ELSE 'Object'
END AS ColumnType,
c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
AND ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA) t
ORDER BY t.ORDINAL_POSITION
SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)
SET @result = @result + '}' + CHAR(13)
IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + CHAR(13) + '}'
END
PRINT @result
它生成的c#代码如下所示:
using System;
namespace Markets
{
public class NewsItem {
#region Instance Properties
public Int32 NewsItemID { get; set; }
public Int32? TextID { get; set; }
public String Description { get; set; }
#endregion Instance Properties
}
}
It may be an idea to use EF, Linq to Sql, or even Scaffolding; however, there are times when a piece of coding like this comes in handy. Frankly, I do not like using EF navigation properties where the code it generates made 19,200 separate database calls to populate a 1000 row grid. This could have been achieved in a single database call. Nonetheless, it could just be that your technical architect does not want you to use EF and the like. So, you have to revert to code like this... Incidentally, it may also be an idea to decorate each of the properties with attributes for DataAnnotations, etc., but I'm keeping this strictly POCO.
编辑 修正了时间戳和Guid?
