多层多数据库模式开发的实验（五）数据层-Access数据库

    经过上篇文章的介绍，我们初步了解了如何实现多数据库的切换。下面，我们要具体实现各种数据库的操作，首先来看看Access数据库。

    因为很多数据访问操作流程很相似，所以，这里将一些可复用的代码抽取出来，编写成助手类，以此减少代码量，提高代码复用性。这个助手类取名SQLHelper，主要负责Access数据库的访问。它包括一个私有方法，三个公有方法：

    //从配置文件中读取配置项，组合成连接字符串。
    private static string GetConnectionString()   

    //执行指定SQL语句，不返回任何值，一般用于Insert，Delete，Update命令。
    public static void ExecuteSQLNonQuery(string SQLCommand, OleDbParameter[] parameters)

    //执行SQL语句返回查询结果，一般用于Select命令。
    public static DataSet ExecuteSQLDataSet(string SQLCommand)

    //重载，一般用于需要传入参数的Select命令
    public static DataSet ExecuteSQLDataSet(string SQLCommand, OleDbParameter[] parameters)

    SQLHelper具体代码如下： 

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

using System.Configuration;
using System.Web;
using System.Web.Caching;
using System.Data;
using MWC.Utility;
using System.Data.OleDb;

namespace MWC.DAL.Access
{
    public sealed class SQLHelper
    {
        public SQLHelper() { }

        private static string GetConnectionString()
        {
            if (CacheUtility.GetFromCache("AccessConnectionString") != null)
            {
                return CacheUtility.GetFromCache("AccessConnectionString").ToString();
            }
            else
            {

                string dbPath = ConfigurationSettings.AppSettings["AccessPath"];
                string dbAbsolutePath = HttpContext.Current.Server.MapPath(dbPath);
                string connectionString = ConfigurationSettings.AppSettings["AccessConnectionString"];

                CacheDependency fileDependency = new CacheDependency(HttpContext.Current.Server.MapPath("Web.Config"));
                CacheUtility.SaveToCache("AccessConnectionString", connectionString.Replace("{DBPath}", dbAbsolutePath), fileDependency);

                return connectionString.Replace("{DBPath}", dbAbsolutePath);
            }
        }

        public static void ExecuteSQLNonQuery(string SQLCommand, OleDbParameter[] parameters)
        {
            OleDbConnection conn = new OleDbConnection(GetConnectionString());
            OleDbCommand comm = new OleDbCommand(SQLCommand, conn);            

            try
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }

                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (OleDbException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Dispose();
                conn.Close();
            }
        }

        public static DataSet ExecuteSQLDataSet(string SQLCommand)
        {
            OleDbConnection conn = new OleDbConnection(GetConnectionString());
            OleDbDataAdapter da = new OleDbDataAdapter(SQLCommand, conn);

            try
            {
                conn.Open();
                DataSet ds = new DataSet();
                da.Fill(ds);

                return ds;
            }
            catch (OleDbException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                da.Dispose();                
                conn.Close();
            }                        
        }

        public static DataSet ExecuteSQLDataSet(string SQLCommand, OleDbParameter[] parameters)
        {
            OleDbConnection conn = new OleDbConnection(GetConnectionString());
            OleDbCommand comm = new OleDbCommand(SQLCommand, conn);
            OleDbDataAdapter da = new OleDbDataAdapter();

            try
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }

                conn.Open();
                da.SelectCommand = comm;
                DataSet ds = new DataSet();
                da.Fill(ds);

                return ds;
            }
            catch (OleDbException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                da.Dispose();
                comm.Dispose();
                conn.Close();
            }
        }
    }
}

 

    在前几篇文章已经说过，客户端选择了Web。因为我们用到了一些特殊的地方，导致无法完全按照替换原则进行层替换（即客户端可以既是web，也可以是winform）。指的就是这里用到了缓存“using System.Web.Caching;”。当然，也可以采用一些方法来避免使用。我这里有点偷懒了，抱歉！

    CacheUtility类的代码如下：

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

using System.Web;
using System.Web.Caching;

namespace MWC.Utility
{
    public sealed class CacheUtility
    {
        /**//// <summary>
        /// 将对象加入到缓存中
        /// </summary>
        /// <param name="cacheKey">缓存键</param>
        /// <param name="cacheObject">缓存对象</param>
        /// <param name="dependency">缓存依赖项</param>
        public static void SaveToCache(string cacheKey, object cacheObject, CacheDependency dependency)
        {
            Cache cache = HttpRuntime.Cache;
            cache.Insert(cacheKey, cacheObject, dependency);
        }

        /**//// <summary>
        /// 从缓存中取得对象，不存在则返回null
        /// </summary>
        /// <param name="cacheKey">缓存键</param>
        /// <returns>获取的缓存对象</returns>
        public static object GetFromCache(string cacheKey)
        {
            Cache cache = HttpRuntime.Cache;

            return cache[cacheKey];
        }

    }
}

 

    加入缓存的好处主要是为了提高性能。程序每次读取设置时首先从缓存中去取，如果存在就使用。否则重新从web.config中读取，并将读取的保存到缓存中，以便下次调用。

    注：这段缓存代码借鉴了“T2噬菌体”的代码，在此予以注明。该作者写的多层多数据库的技术含量比我强多了，建议大家可以去学习。

    有了访问助手类，具体对象的数据访问就简洁明了多了，下面是实现代码：

    用户User：

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

using MWC.Entity;
using MWC.Interface;
using System.Data;
using System.Data.OleDb;

namespace MWC.DAL.Access
{
    public class User : IUser
    {
        public void Insert(Entity.User user)
        {                        
            OleDbParameter[] parameters ={
                new OleDbParameter("UserID",OleDbType.Guid),
                new OleDbParameter("UserName",OleDbType.VarChar,50),
                new OleDbParameter("LoginUID",OleDbType.VarChar,50),
                new OleDbParameter("LoginPWD",OleDbType.VarChar,50)
            };

            parameters[0].Value = user.UserID;
            parameters[1].Value = user.UserName;
            parameters[2].Value = user.LoginUID;
            parameters[3].Value = user.LoginPWD;

            string SQLCommand = "INSERT INTO [User] (UserID,UserName,LoginUID,LoginPWD) VALUES (@UserID,@UserName,@LoginUID,@LoginPWD)";

            SQLHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
        }

        public DataSet GetList()
        {
            string SQLCommand = "Select * FROM [User]";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand);
        }

        public DataSet GetUserFromID(Guid userID)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("UserID",OleDbType.Guid)
            };

            parameters[0].Value = userID;

            string SQLCommand = "Select * FROM [User] WHERE UserID=@UserID";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }

        public DataSet GetUserFromName(string userName)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("UserName",OleDbType.VarChar,50)
            };

            parameters[0].Value = userName;

            string SQLCommand = "Select * FROM [User] WHERE UserName=@UserName";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }
    }  
}

 

    部门、群组Group：

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

using MWC.Entity;
using MWC.Interface;
using System.Data;
using System.Data.OleDb;

namespace MWC.DAL.Access
{
    public class Group : IGroup
    {
        public void Insert(Entity.Group group)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("GroupID",OleDbType.Guid),
                new OleDbParameter("GroupName",OleDbType.VarChar,50)
            };

            parameters[0].Value = group.GroupID;
            parameters[1].Value = group.GroupName;

            string SQLCommand = "INSERT INTO [Group] (GroupID,GroupName) VALUES (@GroupID,@GroupName)";

            SQLHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
        }

        public DataSet GetList()
        {
            string SQLCommand = "Select * FROM [Group]";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand);
        }

        public DataSet GetGroupFromID(Guid groupID)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("GroupID",OleDbType.Guid)
            };

            parameters[0].Value = groupID;

            string SQLCommand = "Select * FROM [Group] WHERE GroupID=@GroupID";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }

        public DataSet GetGroupFromName(string groupName)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("GroupName",OleDbType.VarChar,50)
            };

            parameters[0].Value = groupName;

            string SQLCommand = "Select * FROM [Group] WHERE GroupName=@GroupName";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }

        public void InsertUserToGroup(Guid groupID, Guid userID)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("GroupID",OleDbType.Guid),
                new OleDbParameter("UserID",OleDbType.Guid)
            };

            parameters[0].Value = groupID;
            parameters[1].Value = userID;

            string SQLCommand = "INSERT INTO [GroupOfUser] (GroupID,UserID) VALUES (@GroupID,@UserID)";

            SQLHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
        }

        public DataSet GetGroupOfUser(Guid groupID)
        {
            OleDbParameter[] parameters ={
                new OleDbParameter("GroupID",OleDbType.Guid)
            };

            parameters[0].Value = groupID;

            string SQLCommand = "Select gu.GroupID,g.GroupName,gu.UserID,u.UserName FROM ([GroupOfUser] gu ";
            SQLCommand += "left outer join [Group] g on gu.GroupID=g.GroupID) ";
            SQLCommand += "left outer join [User] u on gu.UserID=u.UserID ";
            SQLCommand += "WHERE gu.GroupID=@GroupID";

            return SQLHelper.ExecuteSQLDataSet(SQLCommand, parameters);
        }
    }
}