如何从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 |
+----+-------+----------------+
当前回答
我很困惑你想要从这里得到什么，但这里是在设计你想要设计的东西时的一般选择。
在Visual Studio版本中使用内置的ORM。 自己编写一个，类似于您的代码示例。通常情况下，如果你不确定怎么做，教程是你最好的朋友。 使用另一种ORM，如NHibernate。
其他回答
如果它对其他人有用，使用属性映射处理Code-First方法，我想要一些只需要在对象模型中绑定实体的东西。所以感谢食肉牛的回答，我根据他们自己的建议扩展了它，并做了一些调整。
因此，这依赖于这个解决方案，包括两个部分，这两个部分都是SQL标量值函数:
一个'Initial Caps'函数(取自: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8a58dbe1-7a4b-4287-afdc-bfecb4e69b23/similar-to-initcap-in-sql-server-tsql 并略有修改，以满足我的需求)
ALTER function [dbo].[ProperCase] (@cStringToProper varchar(8000))
returns varchar(8000)
as
begin
declare @Position int
select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1)))
, @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)
while @Position > 0
select @cStringToProper = stuff(@cStringToProper , @Position , 2 , upper(substring(@cStringToProper , @Position , 2)))
, @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)
select @cStringToProper = replace(@cStringToProper, '_','')
return @cStringToProper
end
输出函数本身，它扩展了食肉牛的解决方案: 正确输出换行符 执行一些基本的表格 写出合适的a [Table]映射(如建议的那样) 写出适当的[Column]映射，包括类型名(如建议的那样) 允许实体名称与表的名称不同 修复当表中有大量列时Print @Result截断的限制
CREATE FUNCTION [dbo].[GetEntityObject] (@NameSpace NVARCHAR(MAX), @TableName NVARCHAR(MAX), @EntityName NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN
DECLARE @result NVARCHAR(MAX)
SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)
IF (@NameSpace IS NOT NULL) BEGIN
SET @result = @result + 'namespace ' + @NameSpace + CHAR(13) + '{' + CHAR(13) END
SET @result = @result + '[Table(name: ' + CHAR(34) + @TableName + CHAR(34) + ')]' + CHAR(13) SET @result = @result + 'public class ' + @EntityName + CHAR(13) + '{' + CHAR(13)
SET @result = @result + '#region Instance Properties' + CHAR(13)
SELECT @result = @result + CHAR(13) + '[Column(name: ' + CHAR(34) + OriginalColumnName + CHAR(34) + ', TypeName = ' + CHAR(34) + DataType
+ CHAR(34) + ')]' + CHAR(13)
+ 'public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13) FROM (
SELECT dbo.ProperCase (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 'Boolean?' ELSE 'Boolean' 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 'Int32?' ELSE 'Int32' 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
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'tinyint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'varchar' THEN 'String'
ELSE 'Object'
END AS ColumnType
, c.ORDINAL_POSITION , c.COLUMN_NAME as OriginalColumnName ,c.DATA_TYPE as DataType
FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = @TableName) t ORDER BY t.ORDINAL_POSITION
SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)
SET @result = @result + '}' + CHAR(13)
IF (@TableName IS NOT NULL) BEGIN
SET @result = @result + CHAR(13) + '}' END
return @result END
在MS SQL Management Studio中使用:
选择dbo。GetEntityObject(“我的名字”、“我的名字”、“我的名字”)
将生成一个可以复制粘贴到Visual Studio中的列值。
如果这对任何人都有帮助，那太好了!
商业化，但codessmith Generator做到了:http://www.codesmithtools.com/product/generator
我将几个基于SQL的答案(主要是Alex Aza的根答案)打包到kassify中，这是一个控制台应用程序，可以一次性为指定的数据库生成所有类:
例如，给定一个Users表，它是这样的:
+----+------------------+-----------+---------------------+
| Id | Name | Username | Email |
+----+------------------+-----------+---------------------+
| 1 | Leanne Graham | Bret | Sincere@april.biz |
| 2 | Ervin Howell | Antonette | Shanna@melissa.tv |
| 3 | Clementine Bauch | Samantha | Nathan@yesenia.net |
+----+------------------+-----------+---------------------+
klassify将生成一个名为Users.cs的文件，看起来像这样:
public class User
{
public int Id {get; set; }
public string Name { get;set; }
public string Username { get; set; }
public string Email { get; set; }
}
它将为每个表输出一个文件。丢弃你不用的东西。
使用
--out, -o:
output directory << defaults to the current directory >>
--user, -u:
sql server user id << required >>
--password, -p:
sql server password << required >>
--server, -s:
sql server << defaults to localhost >>
--database, -d:
sql database << required >>
--timeout, -t:
connection timeout << defaults to 30 >>
--help, -h:
show help
你刚刚做了，只要你的表包含两列，并被称为'tblPeople'之类的东西。
您总是可以编写自己的SQL包装器。实际上我更喜欢这样做，我讨厌生成代码，以任何方式。
也许创建一个DAL类，并有一个名为GetPerson(int id)的方法，用于查询数据库中的person，然后从结果集中创建person对象。
我无法让亚历克斯的答案在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?
