使用ADO的泛型DAL
避免使用ORM，采用这种相当通用的ADO方法
引言
我先说明一下。我不使用ORM。我憎恶ORM的理念，因为它们试图（并且必须）成为一种“一刀切”的解决方案，并认为您永远不应该偏离它们所倡导的“唯一正确道路”。此外，我不知道是谁决定使用存储过程是个坏主意，但ORM似乎遵循这种糟糕的模式。我更喜欢存储过程而非代码中的查询，因为存储过程能提供更高的内在安全性。请注意，我丝毫不感兴趣争论这一点，甚至理性地讨论它，所以不要浪费您（或我的）时间在评论区写这种废话。
此外，此代码使用SQL Server（如果您感兴趣，是2014版本），并且应兼容2008R2及更早版本，以及当前可用的所有版本。我再次声明，我对有关MySQL、YourSQL或您可能使用的任何SQL的典型粉丝崇拜言论不感兴趣，我也不会乐意帮助您修改代码来实现这一点。你们都是程序员，所以自己解决问题，别来烦我。
我知道使用.NET Core的女士们也可以使用这段代码，但由于我不使用，我不会提供支持它的代码，甚至不会就如何将其转换为支持它的代码提供建议。如果您对自己有一定的认知，您将能够轻松地识别出代码中需要修改的部分来支持您自己混乱的解决方案。
现在我们已经完成了寒暄...
左键单击 gadget 并拖动以移动它。左键单击 gadget 的右下角并拖动以调整其大小。右键单击 gadget 以访问其属性。
本文介绍了我在一个支持两个网络（保密和非保密）的环境中实现的一种技术，每个网络有五个环境（位于不同的虚拟机上），支持20个应用程序，并访问30多个数据库。为了简洁起见，我删除了所有与此代码不直接相关的内容（网络、环境和应用程序），因为a）它们是专有的，b）它们只是使代码混乱，c）它们不适用于本文的预期目的。如果有人在计算，这种方案支持6000种可能的连接字符串（尽管实际上，我们的每个应用程序只需要4-5种可用）。
本文演示了一种使用ADO.NET和支持大量连接字符串的相当通用的方法。不仅如此，所有连接字符串都是按需构建的，从而免去了程序员维护配置文件的工作。
代码
以下是关于此代码的一些“希望您知道”的事实：
- 使用了Visual Studio 2017（您应该可以在不进行大量修改的情况下使用2013或更高版本）
- .NET 4.62（应该可以使用4.5或更高版本）
- SQL Server 2014（应该可以使用2008R2或更高版本）
- Windows 7 VM（是的，我就是野蛮人，但这段代码应该可以在您正在使用的任何充满bug的糟糕系统中运行）
- 代码使用了反射来实现其通用性。我知道反射通常不被推崇，因为它速度慢，但由于使用了泛型，反射的性能在这种情况下不是一个问题。
示例解决方案的架构设计旨在最大限度地减少代码/程序集的占用空间。您可以随意释放您作为程序员的创造力，将其塑造成您自己的风格。我保留了注释，以减少文章叙述的文字量。
连接字符串
如上所述，这里提供的ConnectionString类是由于我们需要支持极为广泛的操作系统环境而开发的。在我们的代码中，我们有一个ConnectionStringList类，它实现了专有代码以支持我们的需求，但没有必要在此说明我们真正关注的原因。然而，如果您想混淆您自己的连接字符串，或者在像我们这样多环境的情况下简化您最终会陷入的web.config文件的泥潭，它可能会很有用。
首先，我们有预期的连接字符串组件。这些属性支持连接string中最常用的属性，它们本身并不特别。
/// <summary>
/// Get/set the server instance name
/// </summary>
protected string Server { get; set; }
/// <summary>
/// Get/set the name of the default catalog
/// </summary>
protected string Database { get; set; }
/// <summary>
/// Get/set the user id (if needed by the server)
/// </summary>
protected string UserID { get; set; }
/// <summary>
/// Get/set the password (if needed by the server)
/// </summary>
protected string Password { get; set; }
接下来，我们有一个简单的健全性检查属性。它的目的是进行初步验证，以确保连接string至少是有效的。
/// <summary>
/// Get a flag indicating whether the parameters for the connection string are valid
/// </summary>
private bool IsValid
{
get
{
// eases debugging
bool hasServer = !string.IsNullOrEmpty(this.Server);
bool hasDB = !string.IsNullOrEmpty(this.Database);
bool hasUid = !string.IsNullOrEmpty(this.UserID);
bool hasPwd = !string.IsNullOrEmpty(this.Password);
bool isValid = (hasServer && hasDB);
isValid &= ((!hasUid && !hasPwd) || (hasUid && hasPwd));
return isValid;
}
}
然后是更具吸引力的属性，它们用于在调用方法请求时实际构建连接字符串。此代码生成的连接字符串决定了它如何根据前面讨论的属性来构造。如您所见，它支持凭据和加密流量（请注意加密流量属性的注释）。为了增加安全性，返回的连接字符串是base64编码的。虽然此代码始终对连接字符串进行编码，但您可以轻松地重构此类，使其根本不进行编码，或者响应一个可以决定所需功能的伴随属性。
/// <summary>
/// Get the credentials needed for the server.
/// </summary>
private string Credentials
{
get
{
string value = "Integrated Security=";
// If the userid OR password are not specified, we assume that we use the windows
// login
if (string.IsNullOrEmpty(this.Password) && string.IsNullOrEmpty(this.UserID))
{
value = string.Format("{0}true;", value);
}
else
{
value = string.Format("{0}false; user id={1}; password={2};",
value, this.UserID, this.Password);
}
return value;
}
}
/// <summary>
/// Get the part of the connection string that indicates that we encrypt the traffic between
/// the database and the app.
/// </summary>
private string WithEncryptedTraffic
{
get
{
// TrustServerCertificate: When set to true, SSL / TLS is used to encrypt the channel
// when bypassing walking the certificate chain to validate trust. If it is set to
// true and Encrypt is set to false, the channel is not encrypted. Beginning in .NET
// Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the
// server name(or IP address) in a SQL Server SSL certificate must exactly match the
// server name(or IP address) specified in the connection string. Otherwise, the
// connection attempt will fail.
string value = string.Empty;
if (this.EncryptTraffic)
{
value = "Encrypt=true; TrustServerCertificate=true;";
}
return value;
}
}
/// <summary>
/// Get the connection string.
/// </summary>
public string ConnectionString
{
get
{
string value = string.Empty;
if (this.IsValid)
{
value = string.Format("data source={0}; initial catalog={1}; {2} {3}",
this.Server, this.Database, this.Credentials, this.WithEncryptedTraffic);
}
else
{
throw new InvalidOperationException
("One or more required connection string parameters were not specified.");
}
// LATE-BREAKING CODE CORRECTION #1
// base64 encode the connection string - this prevents the connection string from
// floating around in memory in un-obfuscated form. A small security concern, but
// you know - security first!
return value.Base64Encode();
}
}
最后，我们来到了构造函数和标准的ToString()重写。这里没有什么特别值得讨论的，因为它只是标准的C#用法。
/// <summary>
/// Constructor
/// </summary>
/// <paramname="name">The connection string name
/// (I've been using the name of the database)</param>
/// <paramname="server">The server instance</param>
/// <paramname="database">The initial database to connect to</param>
/// <paramname="uid">The user id credential</param>
/// <paramname="pwd">The password credential</param>
/// <paramname="encryptTraffic">Whether or not you want traffic encrypted
/// between server and this app (default=false)</param>
public AppConnectionString(string name, string server,
string database, string uid, string pwd, bool encryptTraffic=false)
{
this.Name = name;
this.Server = server;
this.Database = database;
this.UserID = uid;
this.Password = pwd;
this.EncryptTraffic = encryptTraffic;
}
/// <summary>
/// Override that returns the (decorated) connection string
/// </summary>
/// <returns></returns>
public override string ToString()
{
return this.ConnectionString;
}
实体和属性
我决定使用“实体”这个词，是为了让那些已经被他们喜欢的ORM所诱惑的人感到更加平静。
EntityBase
EntityBase类提供了代码，可以将继承对象的属性作为SqlParameter数组返回。这意味着您所有特定于实体的代码都可以保留在实体对象中，而不会使业务逻辑变得混乱。为了对返回的属性作为SQL参数进行一定的控制，您可以使用CanDbInsert属性来装饰这些属性。这意味着您可以在类中混合属性，这些属性不一定需要（或有资格）插入到数据库中。
/// <summary>
/// Represents the base class for your entities.
/// This is handy if you need to do anything like
/// change notification, or other would-be common functionality.
/// </summary>
public partial class EntityBase
{
private bool onlyDecorated = true;
/// <summary>
/// Get/set flag indicating whether the GetEntityProperties
/// restricts array to only properties
/// that are appropriately decorated.
/// </summary>
public bool OnlyDecorated
{
get { return this.onlyDecorated; }
set { this.onlyDecorated = value; }
}
以下属性简化了生成适当的SqlParameter对象数组。它只是调用GetEntityProperties方法来检索所有被装饰的属性，并将它们转换为SQLParameter对象。
/// <summary>
/// Get this object's properties as a SqlParameter array.
/// </summary>
public virtual SqlParameter[] AsSqlParams
{
get
{
PropertyInfo[] properties = this.GetEntityProperties();
SqlParameter[] parameters = this.MakeSqlParameters(properties);
return parameters;
}
}
以下方法由AsSqlParameters属性使用，并使用反射来检索 - 并可选地过滤 - 将作为SqlParameter对象返回的属性。
/// <summary>
/// Retrieve all properties for an insert command.
/// </summary>
/// <returns>
/// If this.OnlyDecorated is true, returns properties decorated with CanDbInsertAttribute.
/// Otherwise, all properties are returned.
/// </returns>
public virtual PropertyInfo[] GetEntityProperties()
{
return GetEntityProperties(this.OnlyDecorated);
}
/// <summary>
/// Gets the entity's properties, ostensibly for updates, where we might need non-insertable
/// properties to determine what to update.
/// </summary>
/// <paramname="onlyDecorated"></param>
/// <returns>
/// If onlyDecorated is true, returns properties decorated with CanDbInsertAttribute.
/// Otherwise, all properties are returned.
/// </returns>
public virtual PropertyInfo[] GetEntityProperties(bool onlyDecorated)
{
PropertyInfo[] properties = (onlyDecorated)
? this.GetType().GetProperties().Where
(x => x.IsDefined(typeof(CanDbInsertAttribute))).ToArray()
: this.GetType().GetProperties();
return properties;
}
最后，我们有一个方法，可以从提供的属性创建SqlParameter数组。
protected virtual SqlParameter[] MakeSqlParameters(PropertyInfo[] properties)
{
List<sqlparameter> parameters = new List<sqlparameter>();
foreach(PropertyInfo property in properties)
{
parameters.Add(new SqlParameter(string.Format("@{0}",property.Name),
property.GetValue(this)));
}
return ((parameters.Count > 0) ? parameters.ToArray() : null);
}
}
CanDbInsertAttribute 对象
这个对象代表一个简单的装饰器属性，由EntityBase类使用，用于筛选掉您EntityBase派生实体中不可插入的属性。如果您需要更多属性，可以使用此属性作为示例。
/// <summary>
/// Allows you to mark a property as insertable (ostensibly for building a bulk insert
/// datatable), but can be used elsewhere.
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class CanDbInsertAttribute : Attribute
{
public string Name { get; set; }
public string Argument { get; set; }
}
示例实体
示例应用程序中使用的示例实体使用了EntityBase类和CanDbInsertAttribute属性。
namespace BLLSample.Entities
{
public class EntityUserInfo : EntityBase
{
//can't insert this because the db generates it automagically
public int UserID { get; set; }
[CanDbInsert]
public string UserName { get; set; }
[CanDbInsert]
public string Description { get; set; }
[CanDbInsert]
public string Gender { get; set; }
public SqlParameter[] AsSqlParametersForUpdate
{
get
{
PropertyInfo[] properties = this.GetEntityProperties(false);
SqlParameter[] parameters = this.MakeSqlParameters(properties);
return parameters;
}
}
}
}
DAL（数据访问层）
终于到了我们真正关心的部分。DAL对象使这一切以一种通用的方式工作。手动识别要插入/更新到数据库的属性的需求已经被充分解决，但是如何检索数据呢？DAL对象大量使用反射来实现此功能。然而，这并不能免除我们创建必要的ADO代码。
首先，我们建立了一些必要的属性，并在构造函数中对其进行初始化。请注意FailOnMismatch属性。此属性允许您告诉DAL，如果返回的数据集比接收实体拥有的匹配属性更多/更少，是否应该抛出异常。此属性的默认值为false。
/// <summary>
/// Get/set flag indicating whether the List<T> ExecuteXXX<T>() methods should
/// throw an exception if the DataTable retrieved by the query does not match the model
/// being created (it compares the number of datatable columns with the number of assigned
/// values in the model). The default value is false.
/// </summary>
public bool FailOnMismatch { get; set; }
/// <summary>
/// Get/set value indicating the timeout value (in seconds)
/// </summary>
public int TimeoutSecs { get; set; }
/// <summary>
/// Get/(protected)set the connection string.
/// </summary>
public string ConnectionString { get; protected set; }
/// <summary>
/// Get/set a flag indicating whether the return value parameter is added to the SQL
/// parameter list if it's missing. This only applies to the SetData method
/// (insert/update/delete functionality). In order for this to work, you MUST return
/// @@ROWCOUNT from your stored proc. For text queries you don't have to do this (I
/// don't think).
/// </summary>
public bool AddReturnParamIfMissing { get; set; }
/// <summary>
/// Get/set the bulk insert batch size
/// </summary>
public int BulkInsertBatchSize { get; set; }
/// <summary>
/// Get/set the number of seconds before the bulk copy times out
/// </summary>
public int BulkCopyTimeout { get; set; }
/// <summary>
/// Create instance of DBObject, and set default values for properties.
/// </summary>
/// <paramname="connStr"></param>
public DAL(string connStr)
{
if (string.IsNullOrEmpty(connStr))
{
throw new ArgumentNullException("connection string");
}
this.ConnectionString = connStr;
// five minutes should be enough, right?
this.TimeoutSecs = 300;
this.FailOnMismatch = false;
this.AddReturnParamIfMissing = true;
this.BulkInsertBatchSize = 100;
this.BulkCopyTimeout = 600;
}
接下来，我们建立了两个用于在数据库中获取和设置数据的方法。请注意，这两个方法请求前面描述的连接string，并对其进行base64解码以供使用。如果您选择不base64编码您的连接string，此方法仍然会返回正确的（未解码的）string值。
/// <summary>
/// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
/// </summary>
/// <paramname="cmdText">The stored proc or query to execute</param>
/// <paramname="parameters">The parameters to use in the storedproc/query</param>
/// <returns></returns>
protected DataTable GetData(string cmdText, SqlParameter[] parameters=null,
CommandType cmdType = CommandType.StoredProcedure)
{
// by defining these variables OUTSIDE the using statements, we can evaluate them in
// the debugger even when the using's go out of scope.
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
DataTable data = null;
// create the connection
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
// open it
conn.Open();
// create the SqlCommand object
using (cmd = new SqlCommand(cmdText, conn)
{ CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
{
// give the SqlCommand object the parameters required for the stored proc/query
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
//create the SqlDataReader
using (reader = cmd.ExecuteReader())
{
// move the data to a DataTable
data = new DataTable();
data.Load(reader);
}
}
}
// return the DataTable object to the calling method
return data;
}
/// <summary>
/// Calls SqlCommand.ExecuteNonQuery to save data to the database.
/// </summary>
/// <paramname="connStr"></param>
/// <paramname="cmdText"></param>
/// <paramname="parameters"></param>
/// <returns></returns>
protected int SetData(string cmdText, SqlParameter[] parameters,
CommandType cmdType = CommandType.StoredProcedure)
{
int result = 0;
SqlConnection conn = null;
SqlCommand cmd = null;
using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
{
conn.Open();
using (cmd = new SqlCommand(cmdText, conn)
{ CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
{
SqlParameter rowsAffected = null;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
// if this is a stored proc and we want to add a return param
if (cmdType == CommandType.StoredProcedure && this.AddReturnParamIfMissing)
{
// see if we already have a return parameter
rowsAffected = parameters.FirstOrDefault
(x=>x.Direction == ParameterDirection.ReturnValue);
// if we don't, add one.
if (rowsAffected == null)
{
rowsAffected = cmd.Parameters.Add(new SqlParameter
("@rowsAffected", SqlDbType.Int)
{ Direction = ParameterDirection.ReturnValue } );
}
}
}
result = cmd.ExecuteNonQuery();
result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
}
}
return result;
}
接下来，我们看到了那些实际使用反射魔法的方法，以确定如何将数据从提供的DataTable移动到指定的实体对象。为了使其正常工作，属性名称必须与查询返回的列名完全相同（是的，比较是区分大小写的）。
/// <summary>
/// Converts a value from its database value to something we can use (we need this because
/// we're using reflection to populate our entities)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <paramname="obj"></param>
/// <paramname="defaultValue"></param>
/// <returns></returns>
protected static T ConvertFromDBValue<T>(object obj, T defaultValue)
{
T result = (obj == null || obj == DBNull.Value) ? default(T) : (T)obj;
return result;
}
/// <summary>
/// Creates the list of entities from the specified DataTable object. We do this because we
/// have two methods that both need to do the same thing.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <paramname="data"></param>
/// <returns></returns>
protected List<T> MakeEntityFromDataTable<T>(DataTable data)
{
Type objType = typeof(T);
List<T> collection = new List<T>();
// if we got back data
if (data != null && data.Rows.Count > 0)
{
// we're going to count how many properties in the model were assigned from the
// datatable.
int matched = 0;
foreach(DataRow row in data.Rows)
{
// create an instance of our object
T item = (T)Activator.CreateInstance(objType);
// get our object type's properties
PropertyInfo[] properties = objType.GetProperties();
// set the object's properties as they are found.
foreach (PropertyInfo property in properties)
{
if (data.Columns.Contains(property.Name))
{
Type pType = property.PropertyType;
var defaultValue = pType.GetDefaultValue();
var value = row[property.Name];
value = ConvertFromDBValue(value, defaultValue );
property.SetValue(item, value);
matched++;
}
}
if (matched != data.Columns.Count && this.FailOnMismatch)
{
throw new Exception("Data retrieved does not match specified model.");
}
collection.Add(item);
}
}
return collection;
}
接下来，我们看到了实际被业务层对象调用的方法。这里有两个重载方法的对。每个对包含一个“getter”和一个“setter”重载。本文摘录的代码在开发人员尝试调用ExecuteQuery时会抛出异常，因为我们所有的数据库交互都是通过存储过程进行的（这样更可靠）。
/// <summary>
/// Executes the named stored proc (using ExecuteReader) that gets data from the database.
/// It uses reflection to set property values in the specified type. If nothing was returned
/// from the stored proc, the returned list will be empty.
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>A list of the specified type.</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
public List<T> ExecuteStoredProc<T>(string storedProc, params SqlParameter[] parameters)
{
if (string.IsNullOrEmpty(storedProc))
{
throw new ArgumentNullException("stored procedure");
}
// get the data from the database
DataTable data = this.GetData(storedProc, parameters, CommandType.StoredProcedure);
List<T> collection = this.MakeEntityFromDataTable<T>(data);
return collection;
}
/// <summary>
/// Executes the named stored proc (using ExecuteNonQuery) that stores data in the database.
/// </summary>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>The number of records affected</returns>
public int ExecuteStoredProc(string storedProc, params SqlParameter[] parameters)
{
if (string.IsNullOrEmpty(storedProc))
{
throw new ArgumentNullException("stored procedure");
}
// Save the data to the database. If you don't explicitly return @@ROWCOUNT from your
// stored proc, the return value will always be -1, regardless of how many rows are
// actually affected.
int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);
return result;
}
/// <summary>
/// Executes the specifid query (using ExecuteReader) that gets data from the database.
/// It uses reflection to set property values in the specified type. If nothing was returned
/// from the stored proc, the returned list will be empty.
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>A list of the specified type.</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
public List<T> ExecuteQuery<T>(string query, params SqlParameter[] parameters)
{
if (string.IsNullOrEmpty(query))
{
throw new ArgumentNullException("query");
}
DataTable data = this.GetData(query, parameters, CommandType.Text);
List<T> collection = this.MakeEntityFromDataTable<T>(data);
return collection;
}
/// <summary>
/// Executes the specified query text (using ExecuteNonQuery) that stores data in the
/// database.
/// </summary>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>The number of records affected (if you didn't use SET NOCOUNT ON in
/// your batch)</returns>
public int ExecuteQuery(string query, params SqlParameter[] parameters)
{
if (string.IsNullOrEmpty(query))
{
throw new ArgumentNullException("query");
}
// Save the data to the database. If you use SET NOCOUNT ON in your query, the return
// value will always be -1, regardless of how many rows are actually affected.
int result = this.SetData(query, parameters, CommandType.Text);
return result;
}
最后，我们有一组方法，允许通过ADO的SqlBulkInsert对象进行批量插入数据库。
/// <summary>
/// Performs a simply bulk insert into a table in the database. The schema MUST be part of
/// the table name.
/// </summary>
/// <paramname="dataTable"></param>
/// <returns></returns>
public int DoBulkInsert(DataTable dataTable)
{
// If you have an auto-incrementing identity column, make sure you decorate the column
// with DbCanInsert attribute. If you don't, it will try to put the first available
// property into that db table column, and will throw an exception if the types don't
// match.
int recordsAffected = 0;
SqlConnection conn = null;
SqlBulkCopy bulk = null;
using (conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
using (bulk = new SqlBulkCopy(conn)
{
BatchSize = this.BulkInsertBatchSize
,BulkCopyTimeout = this.BulkCopyTimeout
,DestinationTableName = dataTable.TableName
})
{
bulk.WriteToServer(dataTable);
}
}
return recordsAffected;
}
/// <summary>
/// Performs a simple bulk insert into a table in the database. The schema MUST be part of
/// the table name if the target schema isn't "dbo".
/// </summary>
/// <paramname="dataTable"></param>
/// <returns></returns>
public int DoBulkInsert<T>(IEnumerable<T> data,
string tableName, bool byDBInsertAttribute=false)
{
int result = 0;
DataTable dataTable = null;
if (data.Count() > 0)
{
using (dataTable = new DataTable(){TableName = tableName })
{
Type type = typeof(T);
MethodInfo method = type.GetMethod("GetEntityProperties");
// get the properties regardless of whether or not the object is using EntityBase
PropertyInfo[] properties = (method == null) ?
type.GetProperties().Where(prop =>
Attribute.IsDefined(prop, typeof(CanDbInsertAttribute))).ToArray()
: (PropertyInfo[])method.Invoke(this, null);
foreach (PropertyInfo property in properties)
{
dataTable.Columns.Add(new DataColumn(property.Name, property.PropertyType));
}
foreach (T entity in data)
{
DataRow row = dataTable.NewRow();
foreach (PropertyInfo property in properties)
{
row[property.Name] = property.GetValue(entity);
}
dataTable.Rows.Add(row);
}
}
result = this.DoBulkInsert(dataTable);
}
return result;
}
BLL（业务逻辑层）
由于后台做了大量工作，实现BLL非常简单，实现的方法可以根据需要简单或复杂。本质上，实体和业务层是您真正需要自己编写代码的唯一部分。您的BLL对象必须继承DAL对象，然后就万事大吉了。如您所见，您的BLL方法通常非常简单（这是我坚持的理念：离核心代码越远，对程序员来说应该越简单）。
namespace BLLSample
{
public partial class BLL : DAL
{
public BLL(string connectionString) : base (connectionString)
{
}
/// <summary>
/// Get all users
/// </summary>
/// <returns>All users in the table</returns>
public List<EntityUserInfo> GetUsers()
{
List<EntityUserInfo> users = this.ExecuteStoredProc<EntityUserInfo>
("dbo.spGetUsers", null);
return users;
}
/// <summary>
/// Get the specified user
/// </summary>
/// <param name="id"></param>
/// <returns>A single EntityUserInfo object</returns>
public EntityUserInfo GetUserInfo(int id)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@userID", id),
};
EntityUserInfo user = this.ExecuteStoredProc<EntityUserInfo>
("dbo.spGetUser", parameters).FirstOrDefault();
return user;
}
/// <summary>
/// Save the specified user to the database
/// </summary>
/// <param name="model"></param>
/// <returns>Number of affected records</returns>
public int SaveUser(EntityUserInfo model)
{
int result = this.ExecuteStoredProc("dbo.spAddUser", model.AsSqlParams);
return result;
}
}
}
用法
在编译和运行应用程序之前，打开（在DALSample项目中）CreateDALSampleDB.sql文件，并按照文件顶部的说明进行操作。
using DataModels.DAL;
using BLLSample;
using BLLSample.Entities;
namespace DALSample
{
class Program
{
static void Main(string[] args)
{
// Create a connectionstring object. The parts of the connection string
// are stored in separate properties, and the connection string itself
// is assembled and base64 encoded
// on demand. This makes the connectionstring itself more secure
// while the program is in memory.
AppConnectionString connectionString =
new AppConnectionString("Sample", "localhost", "DALSample", "", "", false);
BLL bll = new BLL(connectionString.ConnectionString);
List<EntityUserInfo> data = bll.GetUsers();
}
}
}
紧急代码更正
1) 我注意到AppConnectionString类中的ConnectionString属性在返回构造的连接字符串之前没有调用Base64Encode()方法。我在文章代码片段中修复了这一点，但没有在相关的源代码ZIP文件中修复（我的工作环境不允许我下载任何东西，所以无法在工作时进行修复）。缺少Base64Encode调用不会造成任何损害或破坏，但它可能对您很重要，所以请根据您的需要处理。
最后
我对这段代码是否遵循任何人的“最佳实践”不做任何保证，并且我强烈反对使用任何形式的ORM。我也不能保证与任何过去的、现在的或未来的工具版本的兼容性。换句话说，如果代码以某种方式被破坏到无法辨认，请做一个程序员，自己修复它。如果您发现问题并告诉我，我将尽快修复文章。
可能存在大量的拼写错误——我是在手机上写的这篇文章——在卫生间。
在本文的创作过程中，没有有意或无意地使用或伤害液氮。
培根——因为“美国”。
历史
- 2019.07.23 - 添加了一些叙述，并修复了连接字符串代码块中的一些代码（请参阅标记为紧急代码更正的部分）。
- 2019.07.22 - 修复了PRE块
- 2019.07.21 - 初次发布