上回说到,各种数据库的差异比较明显.
Access和MySql不支持存储过程,Sql支持
Access和MySql不支持数据库备份,Sql支持
Access和Sql不支持优化,MySql支持
Access和MySql不支持全文搜索,Sql支持
等等,,数据库之前的差异,那么我们为何不顺水推舟呢.把DbHelper中的switch(数据库类型){....}这个毒瘤一块干掉呢.
这样就能更好的改造我们的DbHelper
小菜的设计登场了.
数据库驱动接口
using System;using System.Data.Common;namespace Discuz.Data
{
public interface IDbProvider
{
/// <summary> /// 返回DbProviderFactory实例 /// </summary>
/// <returns></returns> DbProviderFactory Instance(); /// <summary> /// 是否支持全文搜索 /// </summary> /// <returns></returns> bool IsFullTextSearchEnabled(); /// <summary> /// 是否支持压缩数据库 /// </summary> /// <returns></returns> bool IsCompactDatabase(); /// <summary> /// 是否支持备份数据库 /// </summary> /// <returns></returns> bool IsBackupDatabase(); /// <summary> /// 返回刚插入记录的自增ID值, 如不支持则为"" /// </summary> /// <returns></returns> string GetLastIdSql(); /// <summary> /// 是否支持数据库优化 /// </summary> /// <returns></returns> bool IsDbOptimize(); /// <summary> /// 是否支持数据库收缩 /// </summary> /// <returns></returns> bool IsShrinkData(); /// <summary> /// 是否支持存储过程 /// </summary> /// <returns></returns> bool IsStoreProc(); }
}Access数据库驱动
using System;using System.Data.Common;using System.Data.OleDb;namespace Discuz.Data{ public class AccessProvider : IDbProvider { /// <summary> /// 返回DbProviderFactory实例 /// </summary> /// <returns></returns> public DbProviderFactory Instance() { return OleDbFactory.Instance; } /// <summary> /// 是否支持全文搜索 /// </summary> /// <returns></returns> public bool IsFullTextSearchEnabled() { return false; } /// <summary> /// 是否支持压缩数据库 /// </summary> /// <returns></returns> public bool IsCompactDatabase() { return false; } /// <summary> /// 是否支持备份数据库 /// </summary> /// <returns></returns> public bool IsBackupDatabase() { return false; } /// <summary> /// 返回刚插入记录的自增ID值, 如不支持则为"" /// </summary> /// <returns></returns> public string GetLastIdSql() { return "SELECT @@IDENTITY"; } /// <summary> /// 是否支持数据库优化 /// </summary> /// <returns></returns> public bool IsDbOptimize() { return false; } /// <summary> /// 是否支持数据库收缩 /// </summary> /// <returns></returns> public bool IsShrinkData() { return false; } /// <summary> /// 是否支持存储过程 /// </summary> /// <returns></returns> public bool IsStoreProc() { return false; } }}
SqlServer数据库驱动
using System;using System.Data.Common;using System.Data.SqlClient;namespace Discuz.Data{ public class SqlServerProvider : IDbProvider { /// <summary> /// 返回DbProviderFactory实例 /// </summary> /// <returns></returns> public DbProviderFactory Instance() { return SqlClientFactory.Instance; } /// <summary> /// 是否支持全文搜索 /// </summary> /// <returns></returns> public bool IsFullTextSearchEnabled() { return true; } /// <summary> /// 是否支持压缩数据库 /// </summary> /// <returns></returns> public bool IsCompactDatabase() { return true; } /// <summary> /// 是否支持备份数据库 /// </summary> /// <returns></returns> public bool IsBackupDatabase() { return true; } /// <summary> /// 返回刚插入记录的自增ID值, 如不支持则为"" /// </summary> /// <returns></returns> public string GetLastIdSql() { return "SELECT SCOPE_IDENTITY()"; } /// <summary> /// 是否支持数据库优化 /// </summary> /// <returns></returns> public bool IsDbOptimize() { return false; } /// <summary> /// 是否支持数据库收缩 /// </summary> /// <returns></returns> public bool IsShrinkData() { return true; } /// <summary> /// 是否支持存储过程 /// </summary> /// <returns></returns> public bool IsStoreProc() { return true; } }}
那我们开始改进DbHelper吧.
using System;using System.Web;using System.Data;using System.Data.Common;using Discuz.Config;namespace Discuz.Data{ public abstract class DbHelper { private static string m_connString = BaseConfigFileManager.GetDbConnectString; //数据库连接串 private static DbProviderFactory m_factory = null; //DbProviderFactory实例 private static IDbProvider m_provider = null; //数据库驱动接口 /// <summary> /// IDbProvider接口 /// </summary> public static IDbProvider Provider { get { if (m_provider == null) { try { m_provider = (IDbProvider)Activator.CreateInstance(Type.GetType(string.Format("Discuz.Data.{0}Provider, Discuz.Data.{0}", BaseConfigFileManager.GetDbType), false, true)); } catch { throw new Exception("请检查DNT.config中Dbtype节点数据库类型是否正确，例如：SqlServer、Access、MySql"); } } return m_provider; } } /// <summary> /// DbFactory实例 /// </summary> public static DbProviderFactory Factory { get { if (m_factory == null) { m_factory = Provider.Instance(); } return m_factory; } } /// <summary> /// 执行一个DbCommand返回一个记录集 /// </summary> /// <remarks> /// 例如: /// DbDataReader r = ExecuteReader(CommandType.Text, "select id,name from dnt_forums", null); /// </remarks> /// <param name="cmdType">DbCommand类型</param> /// <param name="cmdText">DbCommand文本</param> /// <param name="cmdParms">DbCommand的参数DbParameter</param> /// <returns>一个包含记录的DbDataReader</returns> public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { DbCommand cmd = Factory.CreateCommand(); DbConnection conn = Factory.CreateConnection(); conn.ConnectionString = m_connString; try { PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms); DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch (Exception ex) { conn.Close(); throw ex; } } /// <summary> /// 执行一个DbCommand并返回第一条记录的第一列的值 /// </summary> /// <remarks> /// 例如: /// SqlParameter parm = new SqlParameter("@fid", SqlDbType.Int, 4); /// parm.Value = fid; /// Object obj = ExecuteScalar(CommandType.Text, "select name from dnt_forums where fid=@fid", parm); /// 或者 /// OleDbParameter parm = new OleDbParameter("@fid", OleDbType.Integer, 4); /// parm.Value = fid; /// Object obj = ExecuteScalar(CommandType.Text, "select name from dnt_forums where fid=@fid", parm); /// </remarks> /// <param name="cmdType">DbCommand类型</param> /// <param name="cmdText">DbCommand文本</param> /// <param name="cmdParms">DbCommand的参数DbParameter</param> /// <returns>返回第一条记录的第一列的值</returns> public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { DbCommand cmd = Factory.CreateCommand(); using (DbConnection conn = Factory.CreateConnection()) { conn.ConnectionString = m_connString; PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 执行一个DbCommand返回受该DbCommand影响的行数 /// </summary> /// <remarks> /// 例如: /// SqlParameter parm = new SqlParameter("@fid", SqlDbType.Int, 4); /// parm.Value = fid; /// int result = ExecuteNonQuery(CommandType.Text, "delete from dnt_forums where fid=@fid", parm); /// 或者 /// OleDbParameter parm = new OleDbParameter("@fid", OleDbType.Integer, 4); /// parm.Value = fid; /// int result = ExecuteNonQuery(CommandType.Text, "delete from dnt_forums where fid=@fid", parm); /// </remarks> /// <param name="cmdType">DbCommand类型</param> /// <param name="cmdText">DbCommand文本</param> /// <param name="cmdParms">DbCommand的参数DbParameter</param> /// <returns>返回受DbCommand影响的行数</returns> public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { DbCommand cmd = Factory.CreateCommand(); using (DbConnection conn = Factory.CreateConnection()) { conn.ConnectionString = m_connString; PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 输入参数DbParameter /// </summary> /// <param name="parmName">参数名</param> /// <param name="dbType">参数类型</param> /// <param name="size">参数大小</param> /// <param name="value">参数值</param> /// <returns>参数DbParameter</returns> public static DbParameter MakeInParam(string parmName, DbType dbType, int size, object value) { return MakeParam(parmName, dbType, size, ParameterDirection.Input, value); } /// <summary> /// 输出参数DbParameter /// </summary> /// <param name="parmName">参数名</param> /// <param name="dbType">参数类型</param> /// <param name="size">参数大小</param> /// <returns>参数DbParameter</returns> public static DbParameter MakeOutParam(string parmName, DbType dbType, int size) { return MakeParam(parmName, dbType, size, ParameterDirection.Output, null); } /// <summary> /// 参数DbParameter /// </summary> /// <param name="parmName">参数名</param> /// <param name="dbType">参数类型</param> /// <param name="size">参数大小</param> /// <param name="direction">参数方向(输入或输出)</param> /// <param name="value">参数值</param> /// <returns>参数DbParameter</returns> private static DbParameter MakeParam(string parmName, DbType dbType, Int32 size, ParameterDirection direction, object value) { DbParameter parm; parm = Factory.CreateParameter(); parm.ParameterName = parmName; parm.DbType = dbType; parm.Size = size; parm.Direction = direction; if (!(direction == ParameterDirection.Output && value == null)) parm.Value = value; return parm; } /// <summary> /// 准备一个DbCommand用来执行 /// </summary> /// <param name="cmd">DbCommand对象</param> /// <param name="conn">DbConnection对象</param> /// <param name="cmdType">DbCommand类型</param> /// <param name="cmdText">DbCommand文本</param> /// <param name="cmdParms">DbCommand的参数DbParameter</param> private static void PrepareCommand(DbCommand cmd, DbConnection conn, CommandType cmdType, string cmdText, DbParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (DbParameter parm in cmdParms) cmd.Parameters.Add(parm); } } }}
看吧.我们的DbHelper修改的代码并没有太多.
然而仅仅通过引入IDbProvider这个接口,就让我们的设计更加灵活.
而且我们还在DbHelper中加入了输出参数与输出参数的支持MakeInParam()和MakeOutParam()
到此我们的DbHelper的改进就暂告一段落了.
多数据库的支持也大体完成了.
休息.休息.
