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

【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 周边上新:园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试?测试工程师会被淘汰吗?
· 使用C#创建一个MCP客户端