step by step 之餐饮管理系统六(数据库访问模块)

　　距上次写的博客已经好几个月，一方面公司里面有很多的东西要学，平时的时候又要写代码，所以没有及时更新，不过现在还好，已经成型了，现在把之前的东西贴出来，先看一下现在做的几个界面吧。第一个界面是用颜色用区分台状态的，后来感觉这样没有图片好，于是用第二个界面

改进后的界面

系统登录界面.

上面的截图主要是前一段时间写的台桌界面，回到数据库访问模块吧.

数据库访问模块在网上的资源也非常的多，所以我也不想讲太多.

首先定义DataAccess层的结构，用不同的文件夹区分它们,核心的类放在DBCore文件夹中，通用的类放在DBUtil中，然后再是配置类，Map中放ORM相关的类，其他的就是不同数据库类型的相关类.

首称定义数据库访问层要实现的接口，其实主要都差不多的啦

using System;
using System.Data.Common;
using System.Data;
using System.Collections.Generic;

namespace RMS.DataAccess
{
    /// <summary>
    /// 数据库通用操作接口.
    /// </summary>
    /// <author>xucj</author>
    /// <date>2013-05-15</date>
    public interface IDbOperation
    {
        /// <summary>
        /// 执行查询,并返回查询所返回的结果集中第一行的第一列.
        /// </summary>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>结果集中第一行的第一列.</returns>
        object ExecuteScalar(string commandText);

        /// <summary>
        /// 执行查询,并返回查询所返回的结果集中第一行的第一列.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>结果集中第一行的第一列.</returns>
        object ExecuteScalar(CommandType commandType, string commandText);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        DbDataReader ExecuteReader(string commandText);

        /// <summary>
        /// 返回DbDataReader对象.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>DbDataReader对象</returns>
        DbDataReader ExecuteReader(CommandType commandType, string commandText);

        /// <summary>
        /// 返回DbDataReader对象.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">命令参数.</param>
        /// <returns>DbDataReader对象</returns>
        DbDataReader ExecuteReader(CommandType commandType, string commandText, params DbParameter[] commandParameters);

        /// <summary>
        /// 对连接对象执行SQL语句.
        /// </summary>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>受影响的行数.</returns>
        int ExecuteNonQuery(string commandText);

        /// <summary>
        /// 对连接对象执行SQL语句.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>受影响的行数.</returns>
        int ExecuteNonQuery(CommandType commandType, string commandText);

        /// <summary>
        /// 对连接对象执行SQL语句.
        /// </summary>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">操作类型.</param>
        /// <returns>受影响的行数.</returns>
        int ExecuteNonQuery(string commandText, params DbParameter[] commandParameters);

        /// <summary>
        /// 对连接对象执行SQL语句.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">命令参数.</param>
        /// <returns>受影响的行数.</returns>
        int ExecuteNonQuery(CommandType commandType, string commandText, params DbParameter[] commandParameters);

        /// <summary>
        /// 对连接对象执行SQL语句.
        /// </summary>
        /// <param name="transaction">事务.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>受影响的行数.</returns>
        int ExecuteNonQuery(DbTransaction transaction, string commandText);

        /// <summary>
        /// 对连接对象执行SQL语句.
        /// </summary>
        /// <param name="transaction">事务.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">命令参数.</param>
        /// <returns>受影响的行数.</returns>
        int ExecuteNonQuery(DbTransaction transaction, string commandText, params DbParameter[] commandParameters);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>DataSet数据集</returns>
        DataSet FillDataset(string commandText);

        /// /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// /// <param name="transaction">事务.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>DataSet数据集</returns>
        DataSet FillDataset(DbTransaction transaction, string commandText);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">命令参数.</param>
        /// <returns>DataSet数据集.</returns>
        DataSet FillDataset(string commandText, params DbParameter[] commandParameters);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="transaction">事务.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">命令参数.</param>
        /// <returns>DataSet数据集.</returns>
        DataSet FillDataset(DbTransaction transaction, string commandText, params DbParameter[] commandParameters);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <returns>DataSet数据集.</returns>
        DataSet FillDataset(CommandType commandType, string commandText);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="parameters">命令参数.</param>
        /// <returns>DataSet数据集.</returns>
        DataTable FillDataTable(CommandType commandType, string commandText, params DbParameter[] parameters);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commText">命令文本(SQL语句).</param>
        /// <returns>DataSet数据集.</returns>
        DataTable FillDataTable(string commText);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commText">命令文本(SQL语句).</param>
        /// <returns>DataSet数据集.</returns>
        DataTable FillDataTable(CommandType commandType, string commText);

        /// <summary>
        /// 返回DataSet数据集.
        /// </summary>
        /// <param name="commandType">操作类型.</param>
        /// <param name="commandText">命令文本(SQL语句).</param>
        /// <param name="commandParameters">命令参数.</param>
        /// <returns>DataSet数据集.</returns>
        DataSet FillDataset(CommandType commandType, string commandText, params DbParameter[] commandParameters);
    }
}

然后定义工厂类，这里主要使用泛型，我们知道不同的数据库访问主要是引擎名称与连接字符串不同，所以把它们定义成泛型

using System;
using System.Data;
using System.Data.Common;
using RMS.Logging;

namespace RMS.DataAccess
{
    /// <summary>
    /// 数据库工厂类.
    /// </summary>
    /// <typeparam name="C">实现IDbConnectionString接口的连接串类.</typeparam>
    /// <typeparam name="P">实现IDbProviderName接口的数据库引擎名称.</typeparam>
    public class DbFactory<C, P>
        where C : IDbConnectionString, new()
        where P : IDbProviderName, new()
    {
        #region private member

        /// <summary>
        /// 数据引擎实现工厂.
        /// </summary>
        private static DbProviderFactory m_factory;

        /// <summary>
        /// 数据引擎实现工厂接口.
        /// </summary>
        private static IDbProvider m_provider = null;

        private static object lockObject = new object();

        #endregion

        /// <summary>
        /// 返回实现IDbProvider接口的具体数据引擎.
        /// </summary>
        public static IDbProvider Provider
        {
            get
            {
                if (m_provider == null)
                {
                    lock (lockObject)
                    {
                        if (m_provider == null)
                        {
                            string providerName = typeof(DbFactory<C, P>).Namespace + "." + new P().DbProviderName + "Provider";  // 用于反射
                       
                            try
                            {
                                m_provider = (IDbProvider)Activator.CreateInstance(Type.GetType(providerName));
                            }
                            catch (DbException dex)
                            {
                                LoggerManager.GetILog("Data Provider").Error(dex.StackTrace);
                                throw new Exception("创建数据库实例失败，请确认存在 " + providerName + " 的类");
                            }
                        }
                    }
                }

                return m_provider;
            }
        }

        /// <summary>
        ///  数据库引擎实现工厂.
        /// </summary>
        public static DbProviderFactory Factory
        {
            get
            {
                if (m_factory == null)
                {
                    m_factory = Provider.Instance();
                }

                return m_factory;
            }
        }

        /// <summary>
        /// 重置数据引擎.
        /// </summary>
        public static void ResetDbProvider()
        {
            m_factory = null;
            m_provider = null;
        }

        #region Make parameter.
        /// <summary>
        /// 生成参数.
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Size">参数大小..</param>
        /// <param name="Value">参数值.</param>
        /// <returns>生成后的参数.</returns>
        public static DbParameter MakeInParam(string ParamName, DbTypeWrapper DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// 生成参数.
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Value">参数值.</param>
        /// <returns>生成后的参数.</returns>
        public static DbParameter MakeInParam(string ParamName, DbTypeWrapper DbType, object Value)
        {
            return MakeParam(ParamName, DbType, 0, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// 生成参数.
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Size">参数大小.</param>
        /// <returns>生成后的参数.</returns>
        public static DbParameter MakeOutParam(string ParamName, DbTypeWrapper DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 生成参数.
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Size">参数大小.</param>
        /// <param name="Direction">参数方向.</param>
        /// <param name="Value">参数值.</param>
        /// <returns>生成后的参数.</returns>
        public static DbParameter MakeParam(string ParamName, DbTypeWrapper DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            DbParameter param;

            param = Provider.MakeParam(ParamName, DbType, Size);

            param.Direction = Direction;
            if(!(Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }

            return param;
        }

        #endregion Make parameter end
    }
}

这样根据不同的数据库使用不同的引擎名称与字符串，就实现了类似多态的功能，下面是实现接口的具体方法，代码太多，就截图了

为了实现一个ORM，定义下面的类， T就是对应数据库表的一个实体类

下面是获取T所表示的对象的属性值，后面会根据它的值来生成Sql语句.

       this.m_type = typeof(T);

            string tablename = string.Empty;
            if(!this.m_type.IsClass)
            {
                throw new Exception("Only class is supported!");
            }

            object[] attributes = this.m_type.GetCustomAttributes(false);
            if(attributes.Length > 0)
            {
                foreach(object attr in attributes)
                {
                    if(attr is DataTableNameAttribute)
                    {
                        tablename = (attr as DataTableNameAttribute).TableName;
                        containsIdentification = (attr as DataTableNameAttribute).ContainsIdentification;
                    }
                }
            }
            else
            {
                tablename = m_type.Name;
            }

            this.m_tableName = tablename.ToUpper();

            List<string> paramnames = new List<string>();
            Dictionary<string, PropertyInfo> dicPropertyInfo = new Dictionary<string, PropertyInfo>();

            foreach(PropertyInfo propertyInfo in m_type.GetProperties())
            {
                paramnames.Add(propertyInfo.Name);
                dicPropertyInfo[propertyInfo.Name] = propertyInfo;
            }

            this.m_propertyInfoDictonary = dicPropertyInfo;
            this.m_propertyNameList = paramnames;

            int colCount = this.m_propertyNameList.Count;

            this.m_dbParams = new string[colCount + 1];
            this.m_fullDbParamsForUpdateOrDelete = new string[colCount * 2 + 1];
            this.m_dbParams[0] = "TransactionStatement";
            this.m_fullDbParamsForUpdateOrDelete[0] = "TransactionStatement";
            for(int index = 1; index <= colCount; index++)
            {
                string colname = this.m_propertyNameList[index - 1].ToUpper();
                this.m_dbParams[index] = colname;
                this.m_fullDbParamsForUpdateOrDelete[index] = colname;
                this.m_fullDbParamsForUpdateOrDelete[index + colCount] = String.Concat("where_", colname);
            }
        }

ORM实现的接口如下图所示：查询所有的实现对象，单个实体对象，然后就是插入，更新与删除.

 上面主要讲主要的构想，当然还要好多代码要去写，这里就不一一贴出来了，就是围绕增删查改来进行的，下次写实体层，就是构建表的实体类，下面是为了生成这些代码写的代码生成器，先一睹为快

写这个工具还真没有用多长时间，下次讲具体的实现.