本文介绍了一种通用的数据库访问层设计方案，包括抽象基类DBHandler、参数类Parameter、SQL Server具体实现类DBhANDlerSQLServer、以及工厂类DBHandlerFactory等。此方案支持多种数据库类型，并提供了丰富的数据库操作功能。
1、新建一个 数据库访问基类DBHandler
/// <summary>
/// 数据库访问基类
/// </summary>
public abstract class DBHandler
{
public DBHandler() { }
#region 需要在子类中初始化的与数据库相关的特征类
protected DbConnection dbConnection = null; //连接对象
protected DbTransaction dbTransaction = null; //事务对象
protected abstract DbCommand CreateCommand(); //从子类中构建DbCommand对象
protected abstract DbDataAdapter CreateAdapter(); //从子类中构建DbDataAdapter对象
protected abstract void BuilderCommand(DbDataAdapter adapter); //用于Update方法，构建DbDataAdapter中的UpdateCommand/InsertCommand/DeleteCommand
protected abstract int GetTotalCount(); //用于分页查询，获取总记录数
#endregion
#region 子类中要用的数据或方法
protected List<Parameter> parameters = new List<Parameter>();
protected bool IsInTransaction = false; //是否处于事务当中
//用于分页查询，检查当前SQL是否符合基本查询要求
protected void CheckPageSQL()
{
this.CommandType = CommandType.Text;
if (!this.CommandText.StartsWith("select", true, null))
{
throw new Exception("sql语句必须是select开头");
}
if (IsInTransaction)
{
throw new Exception("分页查询不能在事务中");
}
}
#endregion
#region 用于输入初始条件的属性和方法
/// <summary>
/// sql语句或存储过程名称
/// </summary>
public string CommandText { get; set; }
/// <summary>
/// 执行类型是SQL还是存储过程
/// </summary>
public CommandType CommandType { get; set; }
/// <summary>
/// 添加参数，将参数添加到List<T>列表中保存起来
/// </summary>
/// <param name="paraName">参数名</param>
/// <param name="paraValue">参数值</param>
public void AddParameter(string paraName, string paraValue)
{
this.parameters.Add(new Parameter(paraName,paraValue));
}
/// <summary>
/// 清空当前参数列表
/// </summary>
public void ClearParameter()
{
this.parameters.Clear();
}
/// <summary>
/// 用于分页查询，获取查询到的总记录数
/// </summary>
public int TotalCount
{
get
{
return this.GetTotalCount();
}
}
#endregion
#region 获取数据库的返回值
/// <summary>
/// 获取执行结果的第一行第一列的值
/// </summary>
/// <returns></returns>
public object ExecuteScalar()
{
try
{
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
DbCommand cmd = this.CreateCommand();
object r = cmd.ExecuteScalar();
if (!this.IsInTransaction)
{
dbConnection.Close();
}
return r;
}
catch (Exception ex)
{
this.dbConnection.Close();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 执行没有数据集的命令 Update/Delete/Insert
/// </summary>
/// <returns></returns>
public int ExecuteNonQuery()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbCommand cmd = this.CreateCommand();
int r = cmd.ExecuteNonQuery();
if (!IsInTransaction)
{
dbConnection.Close();
}
return r;
}
catch (Exception ex)
{
dbConnection.Close();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 执行结果的DataTable集
/// </summary>
/// <returns></returns>
public DataTable ExecuteDataTable()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbDataAdapter adapter = this.CreateAdapter();
DataTable dt = new DataTable();
adapter.FillSchema(dt, SchemaType.Mapped);
adapter.Fill(dt);
if (!IsInTransaction)
{
dbConnection.Close();
}
return dt;
}
catch (Exception ex)
{
dbConnection.Close();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 用于分页查询，获取指定页码的数据集
/// </summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="currentPageIndex">当前页码</param>
/// <returns>指定页的记录集</returns>
public abstract DataTable ExecuteDataTable(int pageSize,int currentPageIndex);
#endregion
#region 将DataTable更新到数据库中
public int UpdateData(DataTable dt)
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbDataAdapter adapter = this.CreateAdapter();
if (this.CommandType == CommandType.StoredProcedure)
{
this.CommandType = CommandType.Text;
}
this.BuilderCommand(adapter);
int r = adapter.Update(dt);
if (!IsInTransaction)
{
dbConnection.Close();
}
return r;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
#region 事务处理
/// <summary>
/// 开始一个事务
/// </summary>
public void BegionTransaction()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
this.dbConnection.BeginTransaction();
this.IsInTransaction = true;
}
catch (Exception ex)
{
this.dbConnection.Close();
this.IsInTransaction = false;
throw ex;
}
}
/// <summary>
/// 回滚一个事务
/// </summary>
public void RollbackTransaction()
{
try
{
this.dbTransaction.Rollback();
this.dbConnection.Close();
this.IsInTransaction = false;
}
catch (Exception ex)
{
this.dbConnection.Close();
this.IsInTransaction = false;
throw ex;
}
}
/// <summary>
/// 提交一个事务
/// </summary>
public void CommitTransaction()
{
try
{
this.dbTransaction.Commit();
this.dbConnection.Close();
this.IsInTransaction = false;
}
catch (Exception ex)
{
this.dbConnection.Close();
this.IsInTransaction = false;
throw ex;
}
}
#endregion
#region 建立数据行对象
#endregion
#region 对序列的读取
/// <summary>
/// 获取序列的值，对于非oracle数据库，必须建立一个名为System_Sequence的表，表的字段为（Name(nvarchar,50),Value(int))
/// </summary>
/// <param name="sequenceName"></param>
/// <returns></returns>
public abstract int GetSequenceValue(string sequenceName);
#endregion
} public class Parameter
{
public string Name = string.Empty;
public object Value = null;
public Parameter(string name,string value)
{
this.Name = name;
this.Value = value;
}
} internal class DBhANDlerSQLServer:DBHandler
{
public DBhANDlerSQLServer(string connectionString)
: base()
{
this.dbConnection = new SqlConnection(connectionString);
}
protected override DbCommand CreateCommand()
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = (SqlConnection)this.dbConnection;
if (this.IsInTransaction)
{
cmd.Transaction = (SqlTransaction)this.dbTransaction;
}
if (this.CommandType == CommandType.TableDirect)
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("select * from {0}", this.CommandText);
}
else
{
cmd.CommandType=CommandType;
cmd.CommandText = CommandText;
}
if (this.parameters.Count > 0)
{
foreach (Parameter p in parameters)
{
cmd.Parameters.AddWithValue(p.Name, p.Value);
}
}
return cmd;
}
protected override DbDataAdapter CreateAdapter()
{
SqlCommand cmd = (SqlCommand)this.CreateCommand();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
return adapter;
}
protected override void BuilderCommand(DbDataAdapter adapter)
{
new SqlCommandBuilder((SqlDataAdapter)adapter);
}
protected override int GetTotalCount()
{
this.CheckPageSQL();
string sql = this.CommandText; //保留原始SQL
string sqlWithOutOrderField = string.Empty; //将原始SQL语句去掉 order by 后的部分，用于查询总记录数
int startIndex = sql.LastIndexOf("order by");
if (startIndex >= 0)
{
sqlWithOutOrderField = sql.Substring(0, startIndex);
}
else
{
sqlWithOutOrderField = sql;
}
this.CommandText = string.Format("select count(*）from ({0}) t1",sqlWithOutOrderField);
int r = int.Parse(this.ExecuteScalar().ToString());
this.CommandText = sql;
return r;
}
public override DataTable ExecuteDataTable(int pageSize, int currentPageIndex)
{
this.CheckPageSQL();
string sql = this.CommandText; //保留原始SQL
string orderBy = string.Empty; //将order by字句保留下来
string sqlWithSelectAndOrder = sql.Substring(6); //去掉select以及整个order by
int startIndex = sqlWithSelectAndOrder.ToLower().LastIndexOf("order by");
if (startIndex > 0)
{
orderBy = sqlWithSelectAndOrder.Substring(startIndex);
sqlWithSelectAndOrder = sqlWithSelectAndOrder.Substring(0, startIndex);
}
else
{
throw new Exception("sql的分页查询必须有order by");
}
if (pageSize == 0) //返回所有数据
{
this.CommandText = sql;
this.ExecuteDataTable();
}
DataTable dt = new DataTable();
if (currentPageIndex == 1) //如果当前页为1
{
this.CommandText = string.Format("select top {0} {1} {2}", pageSize, sqlWithSelectAndOrder, orderBy);
dt = this.ExecuteDataTable();
}
else //适合sqlserver2005及以上的版本，但必须带 order by子句
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from ");
sb.AppendFormat("(select Row_Number() over ({0}) as RowNum,{1})t1",orderBy,sqlWithSelectAndOrder);
sb.AppendFormat(" where RowNum between {0} and {1}",pageSize*(currentPageIndex-1),pageSize*currentPageIndex-1);
this.CommandText = sb.ToString();
dt = this.ExecuteDataTable();
}
this.CommandText = sql;
return dt;
}
public override int GetSequenceValue(string sequenceName)
{
//sqlserver 先要建立配套的系列表 System_Sequence
/*建表语句：if not exists(select * from sysobjects where Name='System_Sequence')
create table System_Sequence
{
Name varchar(50),
Value int,
constraint "PK_SsystemSequence" primary key(Name)
};
insert into System_Sequence(Name,Value) values('Sequence_<表名>',0); */
this.CommandType = CommandType.Text;
this.BegionTransaction();
this.CommandText = string.Format("Update System_Sequence set Value=Value+1 where Name='{0}'", sequenceName);
this.ExecuteNonQuery();
this.CommandText = string.Format("select Value from System_Sequence where Name='{0}'", sequenceName);
int r = this.ExecuteNonQuery();
this.CommitTransaction();
return r;
}
} public enum DatabaseType
{
SqlServer = 1,
Oracle = 2,
ODBC = 3,
OLEDB = 4
}
public class DBHandlerFactory
{
//禁止产生类的实例
private DBHandlerFactory() { }
/// <summary>
/// 读取webconfig中的ConnectionString配置节点构造实例
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
public static DBHandler GetHandler(string connStr)
{
ConnectionStringSettings ccs = ConfigurationManager.ConnectionStrings[connStr];
string providerName = ccs.ProviderName.ToLower();
DatabaseType dbType = DatabaseType.SqlServer;
switch (providerName)
{
case "":
case "sqlserver":
case "system.data.sqlclient":
dbType = DatabaseType.SqlServer;
break;
case "oracle":
case"system.data.oracleclient":
dbType = DatabaseType.Oracle;
break;
case "odbc":
case "system.data.odbc":
dbType = DatabaseType.ODBC;
break;
case "oledb":
case "system.data.oledb":
dbType = DatabaseType.OLEDB;
break;
default:
throw new Exception("请按照格式定义ProviderName属性");
}
switch (dbType)
{
case DatabaseType.SqlServer:
return new DBhANDlerSQLServer(ccs.ConnectionString);
case DatabaseType.Oracle:
return null;
case DatabaseType.ODBC:
return null;
case DatabaseType.OLEDB:
return null;
default:
return null;
}
}
/// <summary>
/// 直接以连接字符串和数据库类型构造
/// </summary>
/// <param name="connStr"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public static DBHandler GetHandler(string connStr,DatabaseType dbType)
{
switch (dbType)
{
case DatabaseType.SqlServer:
return new DBhANDlerSQLServer(connStr);
case DatabaseType.Oracle:
return null;
case DatabaseType.ODBC:
return null;
case DatabaseType.OLEDB:
return null;
default:
return null;
}
}
}
扫一扫
5176
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
