如何从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 |
+----+-------+----------------+
当前回答
我只是想给感兴趣的人加上我自己的答案。 主要特点有:
It will automatically generate classes for all the tables in the entire schema. Just specify the schema name. It will add System.Data.Linq.Mapping attributes to the class and each property. Useful for anyone using Linq to SQL. declare @TableName sysname declare @Result varchar(max) declare @schema varchar(20) = 'dbo' DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT tablename = rc1.TABLE_NAME FROM INFORMATION_SCHEMA.Tables rc1 where rc1.TABLE_SCHEMA = @schema OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName WHILE (@@FETCH_STATUS = 0) BEGIN set @Result = '[Table(Name = "' + @schema + '.' + @TableName + '")] public class ' + Replace(@TableName, '$', '_') + ' {' select @Result = @Result + ' [Column' + PriKey +'] public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, col.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 'double' 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 'float' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'long' 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, case when pk.CONSTRAINT_NAME is not null and ic.column_id is not null then '(IsPrimaryKey = true, IsDbGenerated = true)' when pk.CONSTRAINT_NAME is not null then '(IsPrimaryKey = true)' when ic.column_id is not null then '(IsDbGenerated = true)' else '' end PriKey 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 outer join sys.identity_columns ic on ic.column_id = col.column_id and col.object_id = ic.object_id left outer join ( SELECT K.TABLE_NAME , K.COLUMN_NAME , K.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME where C.CONSTRAINT_TYPE = 'PRIMARY KEY' ) pk on pk.COLUMN_NAME = col.name and pk.TABLE_NAME = @TableName where col.object_id = object_id(@schema + '.' + @TableName) ) t order by ColumnId set @Result = @Result + ' } ' print @Result FETCH NEXT FROM @Cursor INTO @TableName end CLOSE @Cursor DEALLOCATE @Cursor GO
增加了6-29-22:这是为EF Core (dotNet 6.0)生成模型的更新版本。
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(4000)
SET @OutputString = @InputString
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
SET @Index = @Index + 1
END
RETURN @OutputString
END
go
declare @TableName sysname
declare @Result varchar(max)
declare @schema varchar(20) = 'dbo'
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT tablename = rc1.TABLE_NAME
FROM INFORMATION_SCHEMA.Tables rc1
where rc1.TABLE_SCHEMA = @schema
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @Result = '[Table("' + @TableName + '", Schema = "' + @schema + '")]
public class ' + Replace(@TableName, '$', '_') + '
{'
select @Result = @Result + '
[Column("' + ColumnName + '"' + stringType + ')]
public ' + ColumnType + NullableSign + ' ' + PropertyName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
replace(replace([dbo].[InitCap](col.name), ' ', ''), '_', '') PropertyName,
col.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 'double'
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 'float'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'long'
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
then '?'
else ''
end NullableSign,
case
when typ.name in ('char', 'nchar', 'nvarchar', 'varchar')
then ', TypeName = "' + typ.name + '(' + convert(varchar, col.max_length) + ')"'
else ''
end stringType
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 outer join sys.identity_columns ic on ic.column_id = col.column_id and col.object_id = ic.object_id
left outer join (
SELECT K.TABLE_NAME ,
K.COLUMN_NAME ,
K.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
where C.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk on pk.COLUMN_NAME = col.name and pk.TABLE_NAME = @TableName
where col.object_id = object_id(@schema + '.' + @TableName)
) t
order by ColumnId
set @Result = @Result + '
}
'
print @Result
FETCH NEXT FROM @Cursor INTO @TableName
end
CLOSE @Cursor DEALLOCATE @Cursor
GO
DROP FUNCTION [dbo].[InitCap]
GO
其他回答
我将几个基于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
从顶部回复略有修改:
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; }
}
我很困惑你想要从这里得到什么，但这里是在设计你想要设计的东西时的一般选择。
在Visual Studio版本中使用内置的ORM。 自己编写一个，类似于您的代码示例。通常情况下，如果你不确定怎么做，教程是你最好的朋友。 使用另一种ORM，如NHibernate。
是的，这些是伟大的，如果你使用一个简单的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
商业化，但codessmith Generator做到了:http://www.codesmithtools.com/product/generator
