构建框架 - 第一部分 (DAL)

4.87/5 (23投票s)

2014年2月5日

CPOL

8分钟阅读

50991

1177

关于创建可重用框架的第一篇文章。

构建框架 - 第二部分 (实用工具)

引言

我们中的一些人使用 Visual Studio 连接到数据库，并通过向导或拖放方法从表中创建类。另一些人则购买第三方应用程序来为您构建这些类，您可以根据自己的需求进行扩展。还有一些人像我一样，自己构建所有这些东西。

本文是关于如何构建一个易于使用且可重用于其他项目的库的系列文章的第一篇。（系列文章的发布将取决于本文的受欢迎程度和“认为”的质量。）

此库的目标是简化数据库访问。您不想每次都创建 Connection、Command 或 DataAdapter 对象以及相应的业务逻辑。

以前是否做过？可能吧，本文旨在为您提供一些见解，并提供一个易于使用的库。

选择最适合您的。

背景

无论您对代码有什么看法。这个组件的基础最初是用 C# 1.0 编写的，大约在十年前，那时我还是一个刚开始学习 C# 的初级开发人员。这也是我写这篇文章的原因，因为它能很好地理解数据库和 N 层开发中的许多概念。事实上，将此与主项目分开开发会迫使您开始模块化或分层思考。

在这十年里，代码得到了改进（Result 类取代了仅仅返回状态，增加了事务处理等），但核心思想基本保持不变。此外，这个库也可以用任何语言（Java、Python 等）重写。

该库还得到了改进，能够处理多个数据库（Oracle、Microsoft Access、MySQL 等），因为在改进的时候，我需要一个可以轻松切换数据库的工具。现在，这个库附属于我编写的一个名为“DbTackler”的工具，该工具可以一键处理（或切换）数据库。例如，当您想针对开发和生产数据库执行查询时，这将非常有用。

关于设计的简要说明

这是适用于中小型桌面或 Web 应用程序的非常基本的设计。在大型应用程序中，可以将层拆分，例如业务对象层和业务逻辑层，或者多个 DAL 组件，其中一个组件与数据库通信，另一个与 Web 服务通信，还有一个读取和写入文件。另一方面，其他应用程序，如界面或服务，则没有 GUI 层。

对于本文，重要的是要注意它属于 DAL 层，而 DAL 层可能包含的不仅仅是数据库访问。

实用工具是一个垂直层，接触代码的每个部分，如日志模块或设置和选项。

从一开始就将各层放在不同的程序集中，极大地促进了功能的重用，同时也防止了代码混乱、代码冗余（复制粘贴）并将大问题分解为小问题。由于小型应用程序甚至原型最终都会变得庞大，因此最好严格执行初始设计。（另请参阅我的文章编程与软件开发。）

提供者

每个数据库都有一个称为“提供程序”的东西。在大多数情况下，至少有一个提供程序会随客户端一起安装。如果库中还没有该提供程序，您可以像现有的提供程序一样添加它。如果客户端没有安装提供程序，您可能可以下载并安装一个，尽管几乎所有供应商至少都支持 ODBC。需要注意的是，许多数据库支持多个提供程序。例如，Oracle 支持 ODBC、OleDb 和 ODP.NET。每个提供程序都有略微不同的属性。

一个建议：如果您可以控制数据库，请确保避免使用可能成为关键字的表/列/序列名称。“Name”、“Key”、“From”、“To”... 我曾遇到过罕见的情况，这会干扰预期的结果。在将列更改为例如 Fname（名字）后，查询按预期工作。调试这样的问题非常有趣（因为数据库客户端中复制粘贴的 SQL 可以正常工作）。

这也意味着，如果库不能立即工作，很可能是您没有安装或配置正确的提供程序。根据机器的不同，您可能需要提供程序的 x86 或 x64 DLL。请确保编译为“Any cpu”。

Using the Code

此库的整个 DAL 组件都基于 IDatabaseConnector 接口。关键在于，每个提供程序在使用该库时的工作方式完全相同，即使它们在底层的工作方式不同。

所有实现都派生自此类，这使得您可以轻松地添加新的提供程序（如果需要）。添加一个类并使用正确的提供程序对象实现接口。

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Framework.Dal
{
    /// <summary>
    /// Gives the transaction status.
    /// </summary>
     public enum TRANSACTION{
        /// <summary>
        /// Transaction commit
        /// </summary>
        Commit,
        /// <summary>
        /// Transaction rollback
        /// </summary>
        Rollback
    };

    /// <summary>
    /// Interface for SQL, OleDb and Odbc database connections.
    /// </summary>
    public interface IDataBaseConnector
    {

        /// <summary>
        /// Executes a select statement.
        /// </summary>
        /// <param name="SQL">The select statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteSelect(string SQL);

    /// <summary>
    /// Executes a select statement.
    /// </summary>
    /// <param name="SQL">The select statement to execute.</param>
    /// <returns>A Result object.</returns>
    Result ExecuteSelect(StringBuilder SQL);

    /// <summary>
    /// Executes a select statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The select statement to execute.</param>
    /// <param name="paramnames">The object array to be used for the parameters.</param>
    /// <param name="paramvals">The object array to be used for the parameters.</param>
    /// <returns>A Result object.</returns>
     Result ExecuteSelectSafe(string SQL, string[] paramnames, object[] paramvals);

    /// <summary>
    /// Executes a select statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The select statement to execute.</param>
    /// <param name="paramnames">The object array to be used for the parameters.</param>
    /// <param name="paramvals">The object array to be used for the parameters.</param>
    /// <returns>A Result object.</returns>
        Result ExecuteSelectSafe(StringBuilder SQL, string[] paramnames, object[] paramvals);

        /// <summary>
        /// Executes an update statement.
        /// </summary>
        /// <param name="SQL">The update statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteUpdate(string SQL);

        /// <summary>
        /// Executes an update statement.
        /// </summary>
        /// <param name="SQL">The update statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteUpdate(StringBuilder SQL);

    /// <summary>
    /// Executes an update statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The update statement to execute.</param>
    /// <param name="paramnames">The string array to be used for the parameter namess.</param>
    /// <param name="paramvals">The object array to be used for the parameter values.</param>
    /// <returns>A Result object.</returns>
        Result ExecuteUpdateSafe(string SQL, string[] paramnames, object[] paramvals);

    /// <summary>
    /// Executes an update statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The update statement to execute.</param>
    /// <param name="paramnames">The string array to be used for the parameter namess.</param>
    /// <param name="paramvals">The object array to be used for the parameter values.</param>
    /// <returns>A Result object.</returns>
        Result ExecuteUpdateSafe(StringBuilder SQL, string[] paramnames, object[] paramvals);

        /// <summary>
        /// Executes an Insert statement.
        /// </summary>
        /// <param name="SQL">The insert statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteInsert(string SQL);

        /// <summary>
        /// Executes an Insert statement.
        /// </summary>
        /// <param name="SQL">The insert statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteInsert(StringBuilder SQL);


    /// <summary>
    /// Executes an Insert statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The insert statement to execute.</param>
    /// <param name="paramnames">The string array to be used for the parameter names.</param>
    /// <param name="paramvals">The object array to be used for the parameter values.</param>
    /// <returns>A Result object.</returns>
    Result ExecuteInsertSafe(string SQL, string [] paramnames, object [] paramvals);

    /// <summary>
    /// Executes an Insert statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The insert statement to execute.</param>
    /// <param name="paramnames">The string array to be used for the parameter names.</param>
    /// <param name="paramvals">The object array to be used for the parameter values.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteInsertSafe(StringBuilder SQL, string [] paramnames, object [] paramvals);

        /// <summary>
        /// Executes a delete statement.
        /// </summary>
        /// <param name="SQL">The delete statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteDelete(string SQL);

        /// <summary>
        /// Executes a delete statement.
        /// </summary>
        /// <param name="SQL">The delete statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteDelete(StringBuilder SQL);

    /// <summary>
    /// Executes a delete statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The delete statement to execute.</param>
    /// <param name="paramnames">The object array to be used for the parameter names.</param>
    /// <param name="paramvals">The object array to be used for the parameter values.</param>
    /// <returns>A Result object.</returns>
        Result ExecuteDeleteSafe(string SQL, string[] paramnames, object[] paramvals);

    /// <summary>
    /// Executes a delete statement by using parameters to prevent SQL-Injection.
    /// </summary>
    /// <param name="SQL">The delete statement to execute.</param>
    /// <param name="paramnames">The object array to be used for the parameter names.</param>
    /// <param name="paramvals">The object array to be used for the parameter values.</param>
    /// <returns>A Result object.</returns>
        Result ExecuteDeleteSafe(StringBuilder SQL, string[] paramnames, object[] paramvals);

        /// <summary>
        /// Executes a stored procedure.
        /// </summary>
        /// <param name="ProcName">The name of the stored procedure.</param>
        /// <param name="paramnames">An array with the names of the parameters.</param>
        /// <param name="paramvals">An array with the values of the parameters.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteStoredProc(string ProcName, string [] paramnames, object [] paramvals);

        /// <summary>
        /// Executes a non-query.
        /// </summary>
        /// <param name="SQL">The statement to execute.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteNonQuery(string SQL);

    /// <summary>
        /// Executes a non-query by using parameters to prevent SQL-Injection.
        /// </summary>
        /// <param name="SQL">The statement to execute.</param>
    /// <param name="paramnames">An array with the names of the parameters.</param>
    /// <param name="paramvals">An array with the values of the parameters.</param>
        /// <returns>A Result object.</returns>
        Result ExecuteNonQuerySafe(string SQL, string[] paramnames, object[] paramvals);

        /// <summary>
        /// Returns the tables of the database.
        /// </summary>
        /// <returns>A string containing all the tablenames.</returns>
        string[] GetTables();

        /// <summary>
        /// Returns column information of a table.
        /// </summary>
        /// <param name="tablename">The tablename for which you want the columninformation.</param>
        /// <returns>A DataColumnCollection containing all necessary information about the columns.</returns>
        DataColumnCollection GetColumnInformation(string tablename);

    }
}

三重斜杠 `///` 注释会插入关于成员的 Intellisense 信息，因此它们很重要。（我相信您需要在 Visual Studio 项目属性的生成选项卡中勾选“XML 文档文件”选项）。此外，我制作了 StringBuilder 的重载，这样您在使用 StringBuilder 对象构建查询时就不必每次都调用“ToString()”方法了。

所以，基本上，您支持

(C) - Insert 语句（string 或 StringBuilder，带参数或不带参数）
(R) - Select 语句（string 或 StringBuilder，带参数或不带参数）
(U) - Update 语句（string 或 StringBuilder，带参数或不带参数）
(D) - Delete 语句（string 或 StringBuilder，带参数或不带参数）
存储过程
Create、alter 等语句（带参数或不带参数的 string）

请注意，大多数方法都返回一个 Result 对象。

这是 Result 类

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Framework.Dal {
    /// <summary>
    /// Holds a Result object of the executed statements.
    /// Contains Exceptions objects, the dataset, number of rows affected, etc ...
    /// </summary>
    public class Result {
        /// <summary>
        /// Holds the status of the execution or connection.
        /// </summary>
        public enum STATUS{
                /** <summary>Undefined Status.</summary>*/
                UNDEFINED = 0,
                /** <summary>Connection with the database failed.</summary>*/
                CONNECTION_FAILED = 1,
                /** <summary>Connection with the database was OK.</summary>*/
                CONNECTION_OK = 2,
                /** <summary>The execution of the statement failed.</summary>*/
                EXECUTE_FAILED = 3,
                /** <summary>The execution of the statement was successful.</summary>*/
                EXECUTE_OK = 4,
            };        
        private STATUS status = STATUS.UNDEFINED;
        private System.Data.DataSet ds = new System.Data.DataSet();
        private Exception exception = null;
        private string sql;
        private string [] paramnames;
        private object [] paramvals;

        /// <summary>
        ///    Returns a status enumeration 
        /// </summary>
        public STATUS Status { 
            get {
                return status;
            }    //end get
            internal set{
                status = value;
            }    //end set
        }        //end property

        /// <summary>
        /// Returns the number of rows affected on Insert, Update and Delete statements (eg.)
        /// </summary>
        public int NumberOfRowsAffected{ get; internal set; }

        /// <summary>
        /// If an exception occurred this property will return it.
        /// </summary>
        public Exception ResultException{ 
            get{
                return exception;
            }    //end get
            internal set{
                exception = value;
            }    //end set 
        }        //end property

        /// <summary>
        /// Returns the Dataset of a select statement.
        /// </summary>
        public System.Data.DataSet ResultDataSet{             
            get{
                return ds;
            }    //end get
            set{
                ds = value;
            }    //end set 
        }        //end property

        /// <summary>
        /// Constructs the SQL by replacing the parameter names with the parameter values.
        /// Note that the values are written out as strings, but are in fact sent as objects to the provider (eg. DateTime)
        /// </summary>
        public string SQLConstructed{
            get{
                StringBuilder builder;
                try{
                    builder = new StringBuilder(sql);
                    if(paramnames != null && paramvals != null && paramnames.Length == paramvals.Length){
                        for(int i = 0; i < paramnames.Length; i++){
                            builder.Replace(Convert.ToString(paramnames[i]), Convert.ToString(paramvals[i]));
                        }    //end if
                    }        //end if
                }            //end try
                catch(Exception ex){
                    builder = new StringBuilder("Error constructing SQL: " + ex.Message);
                }            //end catch
                return builder.ToString();
            }                //end get
        }                    //end property

        /// <summary>
        /// Returns the set SQL.
        /// </summary>
        public string SQL{
            get{
                return sql;
            }        //end get
            internal set{
                sql = value;
            }        //end set
        }            //end property

        /// <summary>
        /// Holds the parameternames on safe queries.
        /// </summary>
        public string [] ParameterNames{
            get{
                return paramnames;
            }        //end get
            internal set{
                paramnames = value;
            }        //end set
        }            //end property

        /// <summary>
        /// Holds the parameter values on safe queries.
        /// </summary>
        public object [] ParameterValues{
            get{
                return paramvals;
            }        //end get
            internal set{
                paramvals = value;
            }                                                //end set
        }                                                    //end property
    }
}

Result 类在尝试调试时特别有用，因为它允许您通过填充参数来重现发送到数据库的 SQL，这样您就可以将 SQL 复制/粘贴到数据库的查询窗口（SQL plus、Toad、SQL Server Management Studio、MySQL workbench 等）中。它还包含异常对象，该对象通常包含数据库的错误代码，这些错误代码很容易在 Google 上找到。

Result 还包含一个成功/失败状态和一个 DataSet 对象（对于 select）或 NumberOfRowsAffected（对于 insert/update/delete）。

使用代码就像这样简单

string connectionstring = "[Connectionstring here]";
//Use any of the provider classes here depending on your needs.
IDataBaseConnector databaseconnector = new OleDbDataBaseConnector(connectionstring);

Result result = databaseconnector.ExecuteSelect("[Select Statement here]");

if(result.Status == Result.STATUS.EXECUTE_OK){
    //use the result.ResultDataSet to get out the dataset object
}
else{
    //Use the result.ResultException to get the Exception which you can log or show the user.
    //Additionally you can log the SQL and/or the parameter names and values.
}

/*
To use a transaction 
call the BeginTransaction method before executing you're first statement.
if all statements executed correctly call EndTransaction(TRANSACTION.Commit), else EndTransaction(TRANSACTION.Rollback)
*/

在可能的情况下，请务必使用 ExecuteXXXSafe 方法来执行参数化查询。互联网上或 CodeProject 上有许多关于 SQL 注入的文章。我建议您阅读一篇并尝试一下！

关注点

您可以利用每个提供程序的 Connector/DataAdapter/Command 基类来实现许多功能，但这个库应该能满足您 90% 以上的需求。整个核心思想是简化思考过程，并丢弃所有您不需要的东西。（而且没有什么可以阻止您添加或删除您认为重要的功能）。
Insert/Update/Delete 函数是冗余的（它们都调用 ExecuteNonQuery），但它们在以后有帮助，因为它们能立即清楚语句的用途。
例如，如果您调用 ExecuteUpdate(MyStatement);，您将知道这是一个 update 语句，而无需调试或查看其他地方。如果您愿意，可以删除这些函数并使用 ExecuteNonQuery 代替。
NonQuery 函数可用于 Create Table、Alter Table 等语句。
我已在发布前清理了代码，因此如果您发现任何错误，请随时告知我。
目前已成功连接并使用的数据库有

Oracle (OleDb 和 ODP.Net)
Sql-Server
MySql
PostgreSQL
Microsoft Access
(甚至还有 DBase)

该系列的后续文章可能包括“日志记录”和“设置”。
您可以将编译选项保留原样，然后通过“添加引用”选项添加程序集，或者对其进行强命名，重新编译并添加到 GAC。
我以前的一位老板认为“可重用性”是将类从一个项目复制粘贴到另一个项目，理由是任何项目都不可能依赖于另一个项目。不要这样做。您编写的框架将是您开发过程中经过最多测试且最终非常健壮的功能，此外，还有其他选项可以确保独立性。
您可以通过使用反射等技术使这些东西更加动态。我不建议这样做，因为我认为这会降低性能，但如果您想这样做，也可以。

准则

框架应尽可能独立于其他程序集/项目/模块，虽然不一定 100% 可能，但应尽可能如此。
使您的代码可重用是不够的，您需要清楚任何人都可以如何使用它。
这将是您的瓶颈，花时间反复测试它，并在新功能上保持严格。您真的需要它吗？

一些可能有用的链接

Oracle Tahiti（文档）
Ask Tom（Oracle 故障排除）
MSDN（SQL Server 文档）
PostgreSQL（文档）
连接字符串

注释

说实话，我从未使用过 ExecuteStoredProcedure 方法。如果它们包含错误，我很抱歉。

历史

版本 1.0 (2014 年 2 月)
版本 1.1 (2014 年 4 月) - 添加了相关文章

构建框架 - 第一部分 (DAL)

相关文章

引言

背景

关于设计的简要说明

提供者

Using the Code

关注点

准则

一些可能有用的链接

注释

历史