如何从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 |
+----+-------+----------------+
当前回答
Postgres DB -> c#的另一个解决方案
兼容NpgSQL 添加DataAnnotations
SELECT
CASE
WHEN c.is_nullable ='NO' THEN '[Required]'||chr(10)
ELSE '' END
||CASE
WHEN c.data_type = 'character varying' THEN format('[StringLength(%s)]',c.character_maximum_length)||chr(10)
ELSE '' END
||'public '
||CASE
WHEN c.data_type = 'integer' THEN 'int'
WHEN c.data_type = 'boolean' THEN 'bool'
WHEN c.data_type = 'double precision' THEN 'double'
WHEN c.data_type = 'uuid' THEN 'Guid'
WHEN c.data_type = 'character varying' THEN 'string'
WHEN c.data_type = 'timestamp without time zone' THEN 'DateTime'
WHEN c.data_type = 'bigint' THEN 'long'
WHEN c.data_type = 'bytea' THEN 'byte[]'
ELSE 'object' END
||CASE
WHEN c.is_nullable='YES' AND NOT c.data_type = 'character varying' THEN '? '
ELSE ' ' END
||c.column_name||' {get;set;}'
,c.*
FROM information_schema."columns" c
WHERE 1=1
AND c.table_name='YOUR_TABLE_NAME'
其他回答
是的，这些是伟大的，如果你使用一个简单的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
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; }
}
之前的解决方案的一个小补充: object_id(@TableName)仅在默认模式中有效。
(Select id from sysobjects where name = @TableName)
只要@tableName是唯一的，就可以在任何模式中工作。
这篇文章救了我好几次。我只是想补充我的意见。 对于那些不喜欢使用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
当然它不是防弹代码，并且可以改进。只是想为这个优秀的解决方案做点贡献
