目录
介绍
让我们把这个放在前面。我不使用ORM。我厌恶他们的想法，因为他们试图（并且需要）成为一个适合所有人的解决方案，并且认为你不应该偏离他们所提倡的任何“一条真正的道路”。此外，我不知道是谁决定使用存储过程是一个坏主意，但ORM似乎遵循这个范例，而且由于存储过程具有更高的固有安全性，我更喜欢在代码查询中使用存储过程。众所周知，我对争论这一点毫不感兴趣，甚至没有理性地讨论它，所以不要浪费你的时间（或我的）进行评论。
此外，此代码使用SQL Server（2014，如果您感兴趣），并且应该与2008R2的所有内容兼容，并且当前可用的任何版本都可以使用。
概述
本文反映了我在支持两个网络（分类和非分类）的环境中实现的一种技术，每个网络都有五个环境（在不同的虚拟机上），支持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
- 反射用于实现代码的通用性。我意识到反射通常是不受欢迎的，因为它很慢，但由于使用泛型类型的性质，反射性能不能成为一个因素。
示例解决方案体系结构以最小化代码/组件占用空间的方式实现。
连接字符串
如上所述，这里介绍的ConnectionString类是由于我们必须支持的极端广泛的操作环境而开发的。在我们的代码中，我们有一个ConnectionStringList类，它实现了专有代码来支持我们的需求，但没有必要说明我们在这里的实际原因。但是，如果您想要混淆自己的连接字符串，或者在多环境情况（例如我们的情况下）中最终使web.config文件陷入困境，那么它可能会很有用。
首先，我们有预期的连接字符串组件。这些属性支持连接字符串中最常用的属性，并且不起眼。
/// <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; }接下来，我们有一个简单的健全检查属性。它的目的是执行基本验证，以确保最低限度有效的连接字符串是可能的：
/// <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 wether 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对象的所有ddecorated属性。
/// <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);
}
}
</sqlparameter></sqlparameter>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;
}接下来，我们建立两种在数据库中获取和设置数据的方法。请注意，这两种方法请求上述连接字符串，base64对它们进行解码以供使用。如果选择NOT对连接字符串进行base64编码，则此方法仍将返回正确的（未解码的）字符串值。
/// <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;
}
}
}用法
在编译和运行应用程序之前，请打开CreateDALSampleDB.sql文件（在DALSample项目中），然后按照文件顶部的说明进行操作。
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。我也不能保证与所引用的任何工具的任何过去，现在或未来版本兼容。换句话说，如果代码在某种程度上超出了所有的识别范围，那么请成为一个程序员，并自己修复它。
原文地址：https://www.codeproject.com/Articles/5162854/Generic-DAL-using-ADO
扫一扫
3285
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
