如何从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 |
+----+-------+----------------+
当前回答
我尝试过node.js，它对我来说工作得很好。
它将为您创建模型文件。您可以创建多个模型文件
前提条件:安装node.js
需要更改:
在你的工作区中创建index.js文件 在“allTable”中添加你的tables对象(截图中高亮显示) 更改文件夹路径(我已经给出了我的系统路径) 执行命令节点index.js
节点index.js
输出
const fs = require('fs/promises'); async function convertToDataType(dataArray, fileName) { let count = 0; let tempArray = []; var dataTypeArray = [ { "key": "bigint", "value": "long" }, { "key": "binary", "value": "byte[]" }, { "key": "bit", "value": "bool" }, { "key": "char", "value": "string" }, { "key": "date", "value": "DateTime" }, { "key": "datetime", "value": "DateTime" }, { "key": "datetime2", "value": "DateTime" }, { "key": "datetimeoffset", "value": "DateTimeOffset" }, { "key": "decimal", "value": "decimal" }, { "key": "float", "value": "double" }, { "key": "image", "value": "byte[]" }, { "key": "int", "value": "int" }, { "key": "money", "value": "decimal" }, { "key": "nchar", "value": "string" }, { "key": "ntext", "value": "string" }, { "key": "numeric", "value": "decimal" }, { "key": "nvarchar", "value": "string" }, { "key": "real", "value": "float" }, { "key": "smalldatetime", "value": "DateTime" }, { "key": "smallint", "value": "short" }, { "key": "smallmoney", "value": "decimal" }, { "key": "text", "value": "string" }, { "key": "time", "value": "TimeSpan" }, { "key": "timestamp", "value": "long" }, { "key": "tinyint", "value": "byte" }, { "key": "uniqueidentifier", "value": "Guid" }, { "key": "varbinary", "value": "byte[]" }, { "key": "varchar", "value": "string" } ] dataArray.map(i => { let objDataType = ''; objDataType = dataTypeArray.filter(data => data.key == i.split(' ')[1].replace('[', '').replace(']', ''))[0].value; if (objDataType == '') { count++; } let isNull = i.includes('NULL') && !(i.includes('varchar') || i.includes('bit')) ? '?' : ''; isNull = i.includes('NOT NULL') ? '' : isNull; const varValue = i.split(' ')[0].replace('[', '').replace(']', ''); if (count != 0) { console.warn(`\n\n\n ======> Error:: Check data type is missing. Datatype => ${i.split(' ')[1]} Object Name: ${fileName} \n\n\n`); } else { tempArray.push(`public ${objDataType}${isNull} ${varValue} { get; set; }`); } }); return tempArray; } async function convertToModel() { try { let allTable = { EmployeeAllowancesHistory: [ "[EmployeeAllowanceHistoryID] [int] NOT NULL", "[EmployeeID] [int] NOT NULL", "[AllowanceID] [int] NOT NULL", "[DateID] [int] NULL", "[Amount] [numeric] NOT NULL", "[Insured] [bit] NULL", "[ChangeDate] [datetime] NOT NULL", "[NewAmount] [numeric] NULL" ], Cities: [ "[CityID] [int] NOT NULL", "[CityCode] [varchar] NOT NULL", "[CityNameAr] [varchar] NULL", "[CityNameEn] [varchar] NULL", "[InKSA] [bit] NOT NULL", "[HighClass] [bit] NOT NULL", "[TravelDays] [int] NULL" ], Regions: [ "[RegionID] [int] NOT NULL", "[RegionCode] [nvarchar] NULL", "[RegionNameEn] [nvarchar] NULL", "[RegionNameAr] [nvarchar] NULL", "[CityID] [int] NULL" ] } for (var file in allTable) { const tableObject = await convertToDataType(allTable[file], file); let tempContent = "[key]"; tableObject.map(obj => { tempContent = `${tempContent} ${obj}` }); const content = `using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace Core.Entities { public class ${file} { ${tempContent} } }` fs.writeFile(`/model_files/${file}.cs`, content); } console.log('Created successfully...'); } catch (err) { console.log(err); } } convertToModel();
其他回答
你刚刚做了，只要你的表包含两列，并被称为'tblPeople'之类的东西。
您总是可以编写自己的SQL包装器。实际上我更喜欢这样做，我讨厌生成代码，以任何方式。
也许创建一个DAL类，并有一个名为GetPerson(int id)的方法，用于查询数据库中的person，然后从结果集中创建person对象。
商业化，但codessmith Generator做到了:http://www.codesmithtools.com/product/generator
抓取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从中生成类呢?
我尝试使用上面的建议，并在这个过程中改进了这个线程中的解决方案。
让我们假设您使用实现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
}
有点晚了，但我已经创建了一个web工具来帮助创建一个c#(或其他)对象从SQL结果，SQL表和SQL SP。
sql2object.com
这可以让你安全地输入所有的属性和类型。
如果无法识别类型，则将选择默认类型。
