经过上篇文章的介绍,我们初步了解了如何实现多数据库的切换。下面,我们要具体实现各种数据库的操作,首先来看看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具体代码如下:

Code
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类的代码如下:

Code
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:

Code
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:

Code
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);
}
}
}
