如何从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 |
+----+-------+----------------+
当前回答
商业化，但codessmith Generator做到了:http://www.codesmithtools.com/product/generator
其他回答
这篇文章救了我好几次。我只是想补充我的意见。 对于那些不喜欢使用orm，而是编写自己的DAL类的人来说，当你在一个表中有20个列，40个不同的表有各自的CRUD操作时，这是痛苦和浪费时间的。我重复了上面的代码，用于基于表实体和属性生成CRUD方法。
declare @TableName sysname = 'Tablename'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + 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 '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 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
order by ColumnId
set @Result = @Result + '
}'
print @Result
declare @InitDataAccess varchar(max) = 'public class '+ @TableName +'DataAccess
{ '
declare @ListStatement varchar(max) ='public List<'+@TableName+'> Get'+@TableName+'List()
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
var itemList = new List<'+@TableName+'>();
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureSelectAll"
};
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var item = new '+@TableName+'();
'
select @ListStatement = @ListStatement + '
item.'+ ColumnName + '= ('+ ColumnType + NullableSign +')reader["'+ColumnName+'"];
'
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 '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 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
order by ColumnId
select @ListStatement = @ListStatement +'
itemList.Add(item);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return itemList;
}'
declare @GetIndividual varchar(max) =
'public '+@TableName+' Get'+@TableName+'()
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
var item = new '+@TableName+'();
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureSelectIndividual"
};
cmd.Parameters.AddWithValue("@ItemCriteria", item.id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{'
select @GetIndividual = @GetIndividual + '
item.'+ ColumnName + '= ('+ ColumnType + NullableSign +')reader["'+ColumnName+'"];
'
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 '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 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
order by ColumnId
select @GetIndividual = @GetIndividual +'
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return item;
}'
declare @InsertStatement varchar(max) = 'public void Insert'+@TableName+'('+@TableName+' item)
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureInsert"
};
'
select @InsertStatement = @InsertStatement + '
cmd.Parameters.AddWithValue("@'+ColumnName+'", item.'+ColumnName+');
'
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 '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 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
order by ColumnId
select @InsertStatement = @InsertStatement +'
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}'
declare @UpdateStatement varchar(max) = 'public void Update'+@TableName+'('+@TableName+' item)
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureUpdate"
};
cmd.Parameters.AddWithValue("@UpdateCriteria", item.Id);
'
select @UpdateStatement = @UpdateStatement + '
cmd.Parameters.AddWithValue("@'+ColumnName+'", item.'+ColumnName+');
'
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 '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 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
order by ColumnId
select @UpdateStatement = @UpdateStatement +'
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}'
declare @EndDataAccess varchar(max) = '
}'
print @InitDataAccess
print @GetIndividual
print @InsertStatement
print @UpdateStatement
print @ListStatement
print @EndDataAccess
当然它不是防弹代码，并且可以改进。只是想为这个优秀的解决方案做点贡献
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; }
}
如果它对其他人有用，使用属性映射处理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中的列值。
如果这对任何人都有帮助，那太好了!
从Postgres DB生成
DO $$ DECLARE v_tabela varchar; DECLARE v_cursor_colunas record; DECLARE v_nome_coluna varchar; DECLARE v_classe VARCHAR; DECLARE v_tipo VARCHAR; DECLARE v_schema_name VARCHAR; BEGIN v_schema_name := 'my-schema'; v_tabela := 'my-table'; select table_name INTO v_tabela from information_schema.tables where table_schema = v_schema_name and table_type = 'BASE TABLE' and table_name = v_tabela; v_classe := E'\r\n' || 'public class ' || v_tabela || ' {' || E'\r\n'; FOR v_cursor_colunas IN SELECT column_name as coluna, is_nullable as isnull, data_type as tipo, character_maximum_length as tamanho FROM information_schema.columns WHERE table_schema = v_schema_name AND table_name = v_tabela LOOP IF v_cursor_colunas.tipo='character varying' THEN v_tipo:= 'string'; ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho=1 THEN v_tipo:= 'char'; ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho<>1 THEN v_tipo:= 'string'; ELSIF v_cursor_colunas.tipo like 'timestamp%' THEN v_tipo:= 'DateTime'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'DateTime?'; END IF; ELSIF v_cursor_colunas.tipo='boolean' THEN v_tipo:= 'bool'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'bool?'; END IF; ELSIF v_cursor_colunas.tipo='integer' THEN v_tipo:= 'int'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'int?'; END IF; ELSIF v_cursor_colunas.tipo='numeric' THEN v_tipo:= 'double'; IF v_cursor_colunas.isnull='YES' then v_tipo:= 'double?'; END IF; ELSIF v_cursor_colunas.tipo='text' THEN v_tipo:= 'string'; ELSE v_tipo:= 'another'; END IF; v_nome_coluna := v_cursor_colunas.coluna; v_classe := v_classe || 'public ' || v_tipo || ' ' || v_cursor_colunas.coluna || ' { get; set; }' || E'\r\n'; END LOOP;
v_class:= v_class || E'\r\n' || '}'; RAISE NOTICE '%'， v_class; $ $;
我只是想给感兴趣的人加上我自己的答案。 主要特点有:
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
