从数据库表生成类

如何从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 |
+----+-------+----------------+
293798 次浏览
小开

你刚刚做了，只要你的表包含两列，并被称为'tblPeople'之类的东西。

您总是可以编写自己的SQL包装器。实际上我更喜欢这样做，我讨厌生成代码，以任何方式。

也许创建一个DAL类，并有一个名为GetPerson(int id)的方法，用于查询数据库中的此人，然后从结果集中创建你的Person对象。

小开
最佳答案

设置@TableName为你的表名。

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 '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
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
小开

商业性的，但是codessmith Generator这样做:http://www.codesmithtools.com/product/generator

小开

我很困惑你想要从这里得到什么，但这里是在设计你想要设计的东西时的一般选择。

  1. 在Visual Studio版本中使用内置的ORM。
  2. 自己编写一个，类似于您的代码示例。通常情况下，如果你不确定怎么做，教程是你最好的朋友。
  3. 使用另一种ORM，如NHibernate。
小开

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'
小开

我无法让亚历克斯的答案在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
}


}

它可能是一个想法使用EF, Linq到Sql，甚至脚手架;然而，有时像这样的一段代码会派上用场。坦率地说，我不喜欢使用EF导航属性，它生成的代码需要19,200个单独的数据库调用来填充1000行网格。这可以在单个数据库调用中实现。尽管如此，可能只是您的技术架构师不希望您使用EF之类的。所以，你必须恢复到这样的代码…顺便说一句，用DataAnnotations等属性来装饰每个属性也是一种想法，但我将严格地保持这种POCO。

<强>编辑 修正了时间戳和Guid吗?

小开

打印NULLABLE属性，使用这个 它对Alex Aza的CASE语句块脚本进行了轻微修改 

declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'


select @result = @result + '
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 AS [ColumnType]
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 column_id


set @result = @result  + '
}'


print @result
小开

我尝试使用上面的建议，并在这个过程中改进了这个线程中的解决方案。

让我们假设你使用一个基类(在本例中是ObservableObject)来实现PropertyChanged事件，你会这样做。有一天我可能会在我的博客上写一篇博客文章 sqljana.wordpress.com < / p >

请将前三个变量的值替换为:

    --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/ < / p >

我确实将类重命名为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/ < / p >

一旦有了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


}
小开

最简单的方法是反向工程。http://msdn.microsoft.com/en-US/data/jj593170

小开
我喜欢用私有本地成员和公共访问器/突变器来设置我的类。 所以我修改了上面的Alex的脚本，以便任何人都感兴趣 
declare @TableName sysname = 'TABLE_NAME'
declare @result varchar(max) = 'public class ' + @TableName + '
{'


SET @result = @result +
'
public ' + @TableName + '()
{}
';


select @result = @result + '
private ' + ColumnType + ' ' + ' m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';'
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 ColumnType
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 column_id


SET @result = @result + '
'


select @result = @result + '
public ' + ColumnType + ' ' + ColumnName + ' { get { return m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';} set {m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ' = value;} }' 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 ColumnType
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 column_id


set @result = @result  + '
}'


print @result
小开

是的，这些是伟大的，如果你使用一个简单的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 < / p >

是这样的:

xsd C:\Person.xsd /classes /language:CS
小开

之前的解决方案的一个小补充: object_id(@TableName)只在默认模式下有效 

(Select id from sysobjects where name = @TableName)

只要@tableName是唯一的，就可以在任何模式中工作。

小开

打印带有注释(摘要)的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
小开

为了感谢Alex的解决方案和Guilherme的要求，我为MySQL生成c#类做了这个

set @schema := 'schema_name';
set @table := 'table_name';
SET group_concat_max_len = 2048;
SELECT
concat('public class ', @table, '\n{\n', GROUP_CONCAT(a.property_ SEPARATOR '\n'), '\n}') class_
FROM
(select
CONCAT(
'\tpublic ',
case
when DATA_TYPE = 'bigint' then 'long'
when DATA_TYPE = 'BINARY' then 'byte[]'
when DATA_TYPE = 'bit' then 'bool'
when DATA_TYPE = 'char' then 'string'
when DATA_TYPE = 'date' then 'DateTime'
when DATA_TYPE = 'datetime' then 'DateTime'
when DATA_TYPE = 'datetime2' then 'DateTime'
when DATA_TYPE = 'datetimeoffset' then 'DateTimeOffset'
when DATA_TYPE = 'decimal' then 'decimal'
when DATA_TYPE = 'double' then 'double'
when DATA_TYPE = 'float' then 'float'
when DATA_TYPE = 'image' then 'byte[]'
when DATA_TYPE = 'int' then 'int'
when DATA_TYPE = 'money' then 'decimal'
when DATA_TYPE = 'nchar' then 'char'
when DATA_TYPE = 'ntext' then 'string'
when DATA_TYPE = 'numeric' then 'decimal'
when DATA_TYPE = 'nvarchar' then 'string'
when DATA_TYPE = 'real' then 'double'
when DATA_TYPE = 'smalldatetime' then 'DateTime'
when DATA_TYPE = 'smallint' then 'short'
when DATA_TYPE = 'smallmoney' then 'decimal'
when DATA_TYPE = 'text' then 'string'
when DATA_TYPE = 'time' then 'TimeSpan'
when DATA_TYPE = 'timestamp' then 'DateTime'
when DATA_TYPE = 'tinyint' then 'byte'
when DATA_TYPE = 'uniqueidentifier' then 'Guid'
when DATA_TYPE = 'varbinary' then 'byte[]'
when DATA_TYPE = 'varchar' then 'string'
else '_UNKNOWN_'
end, ' ',
COLUMN_NAME, ' {get; set;}') as property_
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table AND table_schema = @schema) a
;

Thanks Alex and Guilherme!
小开

如果它对其他人有用，使用属性映射处理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

  1. 输出函数本身，它扩展了食肉动物的解决方案:

    • 正确输出换行符
    • 执行一些基本的表格
    • 写出合适的a [Table]映射(如建议的那样)
    • 写出适当的[Column]映射，包括类型名(如建议的那样)
    • 允许实体名称与表的名称不同
    • 修复当表中有大量列时Print @Result截断的限制
      • 李< / ul > < / > 
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('MyNameSpace'， 'MyTableName'， 'MyEntityName')

将生成一个可以复制粘贴到Visual Studio中的列值。

如果这对任何人都有帮助，那太好了!

小开

Visual Studio杂志发表了这篇文章:

为SQL查询结果生成。net POCO类

它有一个可下载的项目，你可以构建，给它你的SQL信息，它会为你制作出类。

现在，如果该工具刚刚为SELECT、INSERT和UPDATE创建了SQL命令....

小开

抓取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从中生成类呢?

小开
这篇文章救了我好几次。我只是想补充我的意见。 对于那些不喜欢使用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

当然它不是防弹代码，并且可以改进。只是想为这个优秀的解决方案做点贡献

小开

如果你可以访问SQL Server 2016，你可以使用FOR JSON(包含INCLUDE_NULL_VALUES)选项从选择语句中获取JSON输出。复制输出，然后在Visual Studio中，粘贴special ->，粘贴JSON作为类。

算是预算上的解决方案，但可能会节省一些时间。

小开

从顶部回复略有修改:

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; }


}
小开

使用模板创建自定义代码

create PROCEDURE [dbo].[createCode]
(
@TableName sysname = '',
@befor varchar(max)='public class  @TableName
{',
@templet varchar(max)='
public @ColumnType @ColumnName   { get; set; }  // @ColumnDesc  ',
@after varchar(max)='
}'


)
AS
BEGIN




declare @result varchar(max)


set @befor =replace(@befor,'@TableName',@TableName)


set @result=@befor


select @result = @result
+ replace(replace(replace(replace(replace(@templet,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)


from
(
select
column_id,
replace(col.name, ' ', '_') ColumnName,
typ.name as sqltype,
typ.max_length,
is_identity,
pkk.ISPK,
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,
isnull(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
left join
(
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  on ColumnName=col.name


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


set @result=@result+@after


select @result
--print @result


END

现在创建自定义代码

例如c#类

exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName   { get; set; }  // @ColumnDesc  '

输出是

public class  book
{
public long ID   { get; set; }  //
public String Title   { get; set; }  // Book Title
}

对LINQ

exec [createCode] @TableName='book'
, @befor  ='[System.Data.Linq.Mapping.Table(Name = "@TableName")]
public class @TableName
{',


@templet  ='
[System.Data.Linq.Mapping.Column(Name = "@ColumnName", IsPrimaryKey = @ISPK)]
public @ColumnType @ColumnName   { get; set; }  // @ColumnDesc
' ,


@after  ='
}'

输出是

[System.Data.Linq.Mapping.Table(Name = "book")]
public class book
{
[System.Data.Linq.Mapping.Column(Name = "ID", IsPrimaryKey = true)]
public long ID   { get; set; }  //


[System.Data.Linq.Mapping.Column(Name = "Title", IsPrimaryKey = false)]
public String Title   { get; set; }  // Book Title


}

Java类

exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName ; // @ColumnDesc
public @ColumnType get@ColumnName()
{
return this.@ColumnName;
}
public void set@ColumnName(@ColumnType @ColumnName)
{
this.@ColumnName=@ColumnName;
}


'

输出是

public class  book
{
public long ID ; //
public long getID()
{
return this.ID;
}
public void setID(long ID)
{
this.ID=ID;
}




public String Title ; // Book Title
public String getTitle()
{
return this.Title;
}
public void setTitle(String Title)
{
this.Title=Title;
}
}

为android sugarOrm模型

exec [createCode] @TableName='book'
, @befor  ='@Table(name = "@TableName")
public class @TableName
{',
@templet  ='
@Column(name = "@ColumnName")
public @ColumnType @ColumnName ;// @ColumnDesc
' ,
@after  ='
}'

输出是

@Table(name = "book")
public class book
{
@Column(name = "ID")
public long ID ;//


@Column(name = "Title")
public String Title ;// Book Title


}
小开

有点晚了，但我已经创建了一个web工具来帮助创建一个c#(或其他)对象从SQL结果，SQL表和SQL SP。

sql2object.com

这可以让你安全地输入所有的属性和类型。

如果无法识别类型，则将选择默认类型。

小开

我只是想表达我的意见

< p > 0) QueryFirst https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst enter image description here Query-first是一个visual studio扩展，用于在c#项目中智能地使用SQL。使用提供的.sql模板来开发查询。当您保存文件时，query -first运行您的查询，检索模式并生成两个类和一个接口:具有Execute()、ExecuteScalar()、ExecuteNonQuery()等方法的包装器类，其对应的接口，以及封装一行结果的POCO

<强> 1)Sql2Objects 从查询结果开始创建类(但不是DAL) enter image description here < / p >

<强> 2)https://learn.microsoft.com/en-us/ef/ef6/resources/tools enter image description here < / p >

<强> 3)https://visualstudiomagazine.com/articles/2012/12/11/sqlqueryresults-code-generation.aspx enter image description here < / p >

4) http://www.codesmithtools.com/product/generator#features

小开

我在这里将几个基于SQL的答案(主要是Alex Aza的根答案)打包到klassify中，这是一个控制台应用程序，可以一次性为指定的数据库生成所有类:

例如，给定一个表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
小开
只是想给感兴趣的人添加我自己的顶部答案的变化。 主要特征是:

  • 它将自动为整个模式中的所有表生成类。只需指定模式名。

  • 它将添加System.Data.Linq.Mapping属性到类和每个属性。对任何使用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
小开

Java类生成

declare @TableName varchar(max) = 'Restaurants'
declare @Templete varchar(max) = '
public @ColumnType @ColumnName ; // @ColumnDesc
public @ColumnType get@ColumnName()
{
return this.@ColumnName;
}
public void set@ColumnName(@ColumnType @ColumnName)
{
this.@ColumnName=@ColumnName;
}


'
declare @before varchar(max)='public class  @TableName
{'
   

declare @after varchar(max)='
}'






declare @result varchar(max)


set @before =replace(@before,'@TableName',@TableName)


set @result=@before


select @result = @result
+ replace(replace(replace(replace(replace(@Templete,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)


from
(
select
column_id,
replace(col.name, ' ', '_') ColumnName,
typ.name as sqltype,
typ.max_length,
is_identity,
pkk.ISPK,
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,
isnull(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
left join
(
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  on ColumnName=col.name


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


set @result=@result+@after


select @result
--print @result
小开

因为之前没有人提到它，所以还有Scaffold-DbContext

在NuGet Package Manager控制台类型如下: 你的连接字符串;Microsoft.EntityFrameworkCore.SqlServer -OutputDir " < /代码> < / p >

小开

从Postgres DB生成

< p > <代码> $ $ v_tabela varchar; 声明v_cursor_colunas记录; 声明v_nome_coluna varchar; 声明v_class VARCHAR; 声明v_tipo VARCHAR; 声明v_schema_name; 开始 V_schema_name:= 'my-schema'; V_tabela:= 'my-table'; select table_name INTO v_tabela from information_schema其中table_schema = v_schema_name的表 and table_type = 'BASE TABLE' And table_name = v_tabela; v_classe: = E ' \ r \ n”| |“公开课”| | v_tabela || ' {' || E ' \ r \ n '; FOR v_cursor_colunas IN SELECT column_name为coluna, is_nullable为isnull, data_type为tipo, character_maximum_length为tamanho 从information_schema.columns WHERE table_schema = v_schema_name AND table_name = v_tabela 循环
如果v_cursor_colunas。tipo='字符变化' THEN v_tipo: = '字符串'; ELSIF v_cursor_colunas。Tipo ='character'和v_cursor_coluna。然后tamanho = 1 v_tipo: =“字符”; ELSIF v_cursor_colunas。tipo='character' and v_cursor_colunas.tamanho<>1 THEN v_tipo: = '字符串'; ELSIF v_cursor_colunas。像'timestamp%' THEN v_tipo: =“DateTime”; 如果v_cursor_colunas。isnull = '是的' v_tipo: =“DateTime ?”; 如果; ELSIF v_cursor_colunas。蒂波=“布尔” v_tipo: =“bool”; 如果v_cursor_colunas。isnull = '是的' v_tipo: =“bool ?”; 如果; ELSIF v_cursor_colunas。蒂波=“整数” v_tipo: =“int”; 如果v_cursor_colunas。isnull = '是的' v_tipo: = ' int ?”; 如果; ELSIF v_cursor_colunas。蒂波=“数字” v_tipo: =“双重”; 如果v_cursor_colunas。isnull = '是的' v_tipo: =“双?”; 如果; ELSIF v_cursor_colunas。蒂波= '文本' v_tipo: = '字符串'; 其他的 v_tipo: =“另一个”;
. END IF V_nome_coluna:= v_cursor_coluna .coluna; v_class:= v_class || 'public ' || v_tipo || ' ' || v_cursor_colunas。Coluna || ' {get;设置;}' || E'\r\n';
结束循环;< / p >

v_class:= v_class || E'\r\n' || '}';
RAISE NOTICE '%'， v_class; $ $; < /代码> < / p >

小开

Postgres DB ->的另一种解决方案c#

    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'
小开

Oracle数据库的另一种解决方案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; }
}
小开

我尝试过node.js，它对我来说工作得很好。

  • 它将为您创建模型文件。您可以创建多个模型文件

先决条件:安装node . js

需要更改:

  • 在你的工作区中创建index.js文件
  • 将您的tables对象添加到“alltable”;(截图高亮显示)
  • 更改文件夹路径(我已经给出了我的系统路径)
  • 执行命令节点index.js

节点index.js

enter image description here

输出

enter image description here

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();


提交答案