如何从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 |
+----+-------+----------------+
当前回答
这篇文章救了我好几次。我只是想补充我的意见。 对于那些不喜欢使用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
当然它不是防弹代码，并且可以改进。只是想为这个优秀的解决方案做点贡献
其他回答
商业化，但codessmith Generator做到了:http://www.codesmithtools.com/product/generator
我尝试过node.js，它对我来说工作得很好。
它将为您创建模型文件。您可以创建多个模型文件
前提条件:安装node.js
需要更改:
在你的工作区中创建index.js文件 在“allTable”中添加你的tables对象(截图中高亮显示) 更改文件夹路径(我已经给出了我的系统路径) 执行命令节点index.js
节点index.js
输出
const fs = require('fs/promises'); async function convertToDataType(dataArray, fileName) { let count = 0; let tempArray = []; var dataTypeArray = [ { "key": "bigint", "value": "long" }, { "key": "binary", "value": "byte[]" }, { "key": "bit", "value": "bool" }, { "key": "char", "value": "string" }, { "key": "date", "value": "DateTime" }, { "key": "datetime", "value": "DateTime" }, { "key": "datetime2", "value": "DateTime" }, { "key": "datetimeoffset", "value": "DateTimeOffset" }, { "key": "decimal", "value": "decimal" }, { "key": "float", "value": "double" }, { "key": "image", "value": "byte[]" }, { "key": "int", "value": "int" }, { "key": "money", "value": "decimal" }, { "key": "nchar", "value": "string" }, { "key": "ntext", "value": "string" }, { "key": "numeric", "value": "decimal" }, { "key": "nvarchar", "value": "string" }, { "key": "real", "value": "float" }, { "key": "smalldatetime", "value": "DateTime" }, { "key": "smallint", "value": "short" }, { "key": "smallmoney", "value": "decimal" }, { "key": "text", "value": "string" }, { "key": "time", "value": "TimeSpan" }, { "key": "timestamp", "value": "long" }, { "key": "tinyint", "value": "byte" }, { "key": "uniqueidentifier", "value": "Guid" }, { "key": "varbinary", "value": "byte[]" }, { "key": "varchar", "value": "string" } ] dataArray.map(i => { let objDataType = ''; objDataType = dataTypeArray.filter(data => data.key == i.split(' ')[1].replace('[', '').replace(']', ''))[0].value; if (objDataType == '') { count++; } let isNull = i.includes('NULL') && !(i.includes('varchar') || i.includes('bit')) ? '?' : ''; isNull = i.includes('NOT NULL') ? '' : isNull; const varValue = i.split(' ')[0].replace('[', '').replace(']', ''); if (count != 0) { console.warn(`\n\n\n ======> Error:: Check data type is missing. Datatype => ${i.split(' ')[1]} Object Name: ${fileName} \n\n\n`); } else { tempArray.push(`public ${objDataType}${isNull} ${varValue} { get; set; }`); } }); return tempArray; } async function convertToModel() { try { let allTable = { EmployeeAllowancesHistory: [ "[EmployeeAllowanceHistoryID] [int] NOT NULL", "[EmployeeID] [int] NOT NULL", "[AllowanceID] [int] NOT NULL", "[DateID] [int] NULL", "[Amount] [numeric] NOT NULL", "[Insured] [bit] NULL", "[ChangeDate] [datetime] NOT NULL", "[NewAmount] [numeric] NULL" ], Cities: [ "[CityID] [int] NOT NULL", "[CityCode] [varchar] NOT NULL", "[CityNameAr] [varchar] NULL", "[CityNameEn] [varchar] NULL", "[InKSA] [bit] NOT NULL", "[HighClass] [bit] NOT NULL", "[TravelDays] [int] NULL" ], Regions: [ "[RegionID] [int] NOT NULL", "[RegionCode] [nvarchar] NULL", "[RegionNameEn] [nvarchar] NULL", "[RegionNameAr] [nvarchar] NULL", "[CityID] [int] NULL" ] } for (var file in allTable) { const tableObject = await convertToDataType(allTable[file], file); let tempContent = "[key]"; tableObject.map(obj => { tempContent = `${tempContent} ${obj}` }); const content = `using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace Core.Entities { public class ${file} { ${tempContent} } }` fs.writeFile(`/model_files/${file}.cs`, content); } console.log('Created successfully...'); } catch (err) { console.log(err); } } convertToModel();
抓取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从中生成类呢?
从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; $ $;
我无法让亚历克斯的答案在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?
