如何从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生成
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; $ $;
其他回答
我尝试使用上面的建议，并在这个过程中改进了这个线程中的解决方案。
让我们假设您使用实现PropertyChanged事件的基类(在本例中为ObservableObject)，您将像这样做。有一天我可能会在我的博客上写一篇博客文章 sqljana.wordpress.com
请将前三个变量的值替换为:
--These three things have to be substituted (when called from Powershell, they are replaced before execution)
DECLARE @Schema VARCHAR(MAX) = N'&Schema'
DECLARE @TableName VARCHAR(MAX) = N'&TableName'
DECLARE @Namespace VARCHAR(MAX) = N'&Namespace'
DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10);
DECLARE @result VARCHAR(max) = ' '
DECLARE @PrivateProp VARCHAR(100) = @CRLF +
CHAR(9) + CHAR(9) + 'private <ColumnType> _<ColumnName>;';
DECLARE @PublicProp VARCHAR(255) = @CRLF +
CHAR(9) + CHAR(9) + 'public <ColumnType> <ColumnName> ' + @CRLF +
CHAR(9) + CHAR(9) + '{ ' + @CRLF +
CHAR(9) + CHAR(9) + ' get { return _<ColumnName>; } ' + @CRLF +
CHAR(9) + CHAR(9) + ' set ' + @CRLF +
CHAR(9) + CHAR(9) + ' { ' + @CRLF +
CHAR(9) + CHAR(9) + ' _<ColumnName> = value;' + @CRLF +
CHAR(9) + CHAR(9) + ' base.RaisePropertyChanged();' + @CRLF +
CHAR(9) + CHAR(9) + ' } ' + @CRLF +
CHAR(9) + CHAR(9) + '}' + @CRLF;
DECLARE @RPCProc VARCHAR(MAX) = @CRLF +
CHAR(9) + CHAR(9) + 'public event PropertyChangedEventHandler PropertyChanged; ' + @CRLF +
CHAR(9) + CHAR(9) + 'private void RaisePropertyChanged( ' + @CRLF +
CHAR(9) + CHAR(9) + ' [CallerMemberName] string caller = "" ) ' + @CRLF +
CHAR(9) + CHAR(9) + '{ ' + @CRLF +
CHAR(9) + CHAR(9) + ' if (PropertyChanged != null) ' + @CRLF +
CHAR(9) + CHAR(9) + ' { ' + @CRLF +
CHAR(9) + CHAR(9) + ' PropertyChanged( this, new PropertyChangedEventArgs( caller ) ); ' + @CRLF +
CHAR(9) + CHAR(9) + ' } ' + @CRLF +
CHAR(9) + CHAR(9) + '}';
DECLARE @PropChanged VARCHAR(200) = @CRLF +
CHAR(9) + CHAR(9) + 'protected override void AfterPropertyChanged(string propertyName) ' + @CRLF +
CHAR(9) + CHAR(9) + '{ ' + @CRLF +
CHAR(9) + CHAR(9) + ' System.Diagnostics.Debug.WriteLine("' + @TableName + ' property changed: " + propertyName); ' + @CRLF +
CHAR(9) + CHAR(9) + '}';
SET @result = 'using System;' + @CRLF + @CRLF +
'using MyCompany.Business;' + @CRLF + @CRLF +
'namespace ' + @Namespace + @CRLF + '{' + @CRLF +
' public class ' + @TableName + ' : ObservableObject' + @CRLF +
' {' + @CRLF +
' #region Instance Properties' + @CRLF
SELECT @result = @result
+
REPLACE(
REPLACE(@PrivateProp
, '<ColumnName>', ColumnName)
, '<ColumnType>', ColumnType)
+
REPLACE(
REPLACE(@PublicProp
, '<ColumnName>', ColumnName)
, '<ColumnType>', ColumnType)
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 '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
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
AND ISNULL(@Schema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
) t
ORDER BY t.ORDINAL_POSITION
SELECT @result = @result + @CRLF +
CHAR(9) + '#endregion Instance Properties' + @CRLF +
--CHAR(9) + @RPCProc + @CRLF +
CHAR(9) + @PropChanged + @CRLF +
CHAR(9) + '}' + @CRLF +
@CRLF + '}'
--SELECT @result
PRINT @result
基类基于Josh Smith的文章 从http://joshsmithonwpf.wordpress.com/2007/08/29/a-base-class-which-implements-inotifypropertychanged/
我确实将类重命名为ObservableObject，并使用CallerMemberName属性利用了c# 5的一个特性
//From http://joshsmithonwpf.wordpress.com/2007/08/29/a-base-class-which-implements-inotifypropertychanged/
//
//Jana's change: Used c# 5 feature to bypass passing in the property name using [CallerMemberName]
// protected void RaisePropertyChanged([CallerMemberName] string propertyName = "")
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.Reflection;
using System.Runtime.CompilerServices;
namespace MyCompany.Business
{
/// <summary>
/// Implements the INotifyPropertyChanged interface and
/// exposes a RaisePropertyChanged method for derived
/// classes to raise the PropertyChange event. The event
/// arguments created by this class are cached to prevent
/// managed heap fragmentation.
/// </summary>
[Serializable]
public abstract class ObservableObject : INotifyPropertyChanged
{
#region Data
private static readonly Dictionary<string, PropertyChangedEventArgs> eventArgCache;
private const string ERROR_MSG = "{0} is not a public property of {1}";
#endregion // Data
#region Constructors
static ObservableObject()
{
eventArgCache = new Dictionary<string, PropertyChangedEventArgs>();
}
protected ObservableObject()
{
}
#endregion // Constructors
#region Public Members
/// <summary>
/// Raised when a public property of this object is set.
/// </summary>
[field: NonSerialized]
public event PropertyChangedEventHandler PropertyChanged;
/// <summary>
/// Returns an instance of PropertyChangedEventArgs for
/// the specified property name.
/// </summary>
/// <param name="propertyName">
/// The name of the property to create event args for.
/// </param>
public static PropertyChangedEventArgs
GetPropertyChangedEventArgs(string propertyName)
{
if (String.IsNullOrEmpty(propertyName))
throw new ArgumentException(
"propertyName cannot be null or empty.");
PropertyChangedEventArgs args;
// Get the event args from the cache, creating them
// and adding to the cache if necessary.
lock (typeof(ObservableObject))
{
bool isCached = eventArgCache.ContainsKey(propertyName);
if (!isCached)
{
eventArgCache.Add(
propertyName,
new PropertyChangedEventArgs(propertyName));
}
args = eventArgCache[propertyName];
}
return args;
}
#endregion // Public Members
#region Protected Members
/// <summary>
/// Derived classes can override this method to
/// execute logic after a property is set. The
/// base implementation does nothing.
/// </summary>
/// <param name="propertyName">
/// The property which was changed.
/// </param>
protected virtual void AfterPropertyChanged(string propertyName)
{
}
/// <summary>
/// Attempts to raise the PropertyChanged event, and
/// invokes the virtual AfterPropertyChanged method,
/// regardless of whether the event was raised or not.
/// </summary>
/// <param name="propertyName">
/// The property which was changed.
/// </param>
protected void RaisePropertyChanged([CallerMemberName] string propertyName = "")
{
this.VerifyProperty(propertyName);
PropertyChangedEventHandler handler = this.PropertyChanged;
if (handler != null)
{
// Get the cached event args.
PropertyChangedEventArgs args =
GetPropertyChangedEventArgs(propertyName);
// Raise the PropertyChanged event.
handler(this, args);
}
this.AfterPropertyChanged(propertyName);
}
#endregion // Protected Members
#region Private Helpers
[Conditional("DEBUG")]
private void VerifyProperty(string propertyName)
{
Type type = this.GetType();
// Look for a public property with the specified name.
PropertyInfo propInfo = type.GetProperty(propertyName);
if (propInfo == null)
{
// The property could not be found,
// so alert the developer of the problem.
string msg = string.Format(
ERROR_MSG,
propertyName,
type.FullName);
Debug.Fail(msg);
}
}
#endregion // Private Helpers
}
}
下面是你们会更喜欢的部分。我构建了一个Powershell脚本来为SQL数据库中的所有表生成。它是基于一个名为Chad Miller的Powershell大师的Invoke-SQLCmd2 cmdlet，可以从这里下载: http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/
一旦有了cmdlet，为所有表生成的Powershell脚本就变得简单了(一定要用特定的值替换变量)。
. C:\MyScripts\Invoke-Sqlcmd2.ps1
$serverInstance = "MySQLInstance"
$databaseName = "MyDb"
$generatorSQLFile = "C:\MyScripts\ModelGen.sql"
$tableListSQL = "SELECT name FROM $databaseName.sys.tables"
$outputFolder = "C:\MyScripts\Output\"
$namespace = "MyCompany.Business"
$placeHolderSchema = "&Schema"
$placeHolderTableName = "&TableName"
$placeHolderNamespace = "&Namespace"
#Get the list of tables in the database to generate c# models for
$tables = Invoke-Sqlcmd2 -ServerInstance $serverInstance -Database $databaseName -Query $tableListSQL -As DataRow -Verbose
foreach ($table in $tables)
{
$table1 = $table[0]
$outputFile = "$outputFolder\$table1.cs"
#Replace variables with values (returns an array that we convert to a string to use as query)
$generatorSQLFileWSubstitutions = (Get-Content $generatorSQLFile).
Replace($placeHolderSchema,"dbo").
Replace($placeHolderTableName, $table1).
Replace($placeHolderNamespace, $namespace) | Out-String
"Ouputing for $table1 to $outputFile"
#The command generates .cs file content for model using "PRINT" statements which then gets written to verbose output (stream 4)
# ...capture the verbose output and redirect to a file
(Invoke-Sqlcmd2 -ServerInstance $serverInstance -Database $databaseName -Query $generatorSQLFileWSubstitutions -Verbose) 4> $outputFile
}
从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; $ $;
如果它对其他人有用，使用属性映射处理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中的列值。
如果这对任何人都有帮助，那太好了!
要打印带有注释(摘要)的NULLABLE属性，使用这个。 这是对第一个答案的轻微修改
declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result
+ CASE WHEN ColumnDesc IS NOT NULL THEN '
/// <summary>
/// ' + ColumnDesc + '
/// </summary>' ELSE '' END
+ '
public ' + ColumnType + ' ' + ColumnName + ' { get; set; }'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
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 + CASE WHEN col.is_nullable=1 AND typ.name NOT IN ('binary', 'varbinary', 'image', 'text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar') THEN '?' ELSE '' END ColumnType,
colDesc.colDesc AS ColumnDesc
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
OUTER APPLY (
SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
FROM
sys.extended_properties
WHERE
major_id = col.object_id
AND
minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
) colDesc
where object_id = object_id(@TableName)
) t
order by column_id
set @result = @result + '
}'
print @result
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'
