本文档详细介绍了基于MySQL的抽象数据访问层基类AbstractBaseDAL，该基类为所有数据库访问提供了统一接口。内容涵盖构造函数、属性、方法，包括插入、更新、删除记录的操作，以及日志记录事件处理。此外，还涉及到防止字段与数据库保留字冲突的处理和通用操作方法。
usingSystem;usingSystem.Collections;usingSystem.Data;usingSystem.Data.Common;usingSystem.Collections.Generic;usingSystem.Reflection;usingSystem.Text;usingSystem.Text.RegularExpressions;usingWHC.Pager.Entity;usingWHC.Framework.Commons;usingMicrosoft.Practices.EnterpriseLibrary.Data;namespaceWHC.Framework.ControlUtil
{///
///定义一个记录操作日志的事件处理///
/// 操作的用户ID
/// 操作表名称
/// 操作类型：增加、修改、删除
/// 操作的详细记录信息
///
public delegate bool OperationLogEventHandler(string userId, string tableName, string operationType, string note, DbTransaction trans = null);///
///数据访问层的超级基类，所有数据库的数据访问基类都继承自这个超级基类，包括Oracle、SqlServer、Sqlite、MySql、Access等///
public abstract class AbstractBaseDAL where T : BaseEntity, new()
{#region 构造函数
protected string dbConfigName = ""; //数据库配置名称
protected string parameterPrefix = "@";//数据库参数化访问的占位符
protected string safeFieldFormat = "[{0}]";//防止和保留字、关键字同名的字段格式，如[value]
protected string tableName;//需要初始化的对象表名
protected string primaryKey;//数据库的主键字段名
protected string sortField;//排序字段
protected bool isDescending = true;//是否为降序
protected string selectedFields = "*";//选择的字段，默认为所有(*)
public event OperationLogEventHandler OnOperationLog;//定义一个操作记录的事件处理
///
///数据库配置名称，默认为空。///可在子类指定不同的配置名称，用于访问不同的数据库///
public stringDbConfigName
{get { returndbConfigName; }set { dbConfigName =value; }
}///
///数据库参数化访问的占位符///
public stringParameterPrefix
{get { returnparameterPrefix; }set { parameterPrefix =value; }
}///
///防止和保留字、关键字同名的字段格式，如[value]。///不同数据库类型的BaseDAL需要进行修改///
public stringSafeFieldFormat
{get { returnsafeFieldFormat; }set { safeFieldFormat =value; }
}///
///排序字段///
public stringSortField
{get{returnsortField;
}set{
sortField=value;
}
}///
///是否为降序///
public boolIsDescending
{get { returnisDescending; }set { isDescending =value; }
}///
///选择的字段，默认为所有(*)///
public stringSelectedFields
{get { returnselectedFields; }set { selectedFields =value; }
}///
///数据库访问对象的表名///
public stringTableName
{get{returntableName;
}
}///
///数据库访问对象的外键约束///
public stringPrimaryKey
{get{returnprimaryKey;
}
}///
///默认构造函数///
publicAbstractBaseDAL()
{
}///
///指定表名以及主键,对基类进构造///
/// 表名
/// 表主键
public AbstractBaseDAL(string tableName, string primaryKey) : this()
{this.tableName =tableName;this.primaryKey =primaryKey;this.sortField = primaryKey;//默认为主键排序
}///
///设置数据库配置项名称///
/// 数据库配置项名称
public virtual void SetDbConfigName(stringdbConfigName)
{this.dbConfigName =dbConfigName;
}///
///根据配置数据库配置名称生成Database对象///
///
protected virtualDatabase CreateDatabase()
{
Database db= null;if (string.IsNullOrEmpty(dbConfigName))
{
db=DatabaseFactory.CreateDatabase();
}else{
db=DatabaseFactory.CreateDatabase(dbConfigName);
}returndb;
}///
///获取指定字符串中的子项的值///
/// 字符串值
/// 以分号(;)为分隔符的子项名称
/// 对应子项名称的值(即是=号后面的值)
protected string GetSubValue(string connectionString, stringsubKeyName)
{string[] item = connectionString.Split(new char[] { ';'});for (int i = 0; i < item.Length; i++)
{string itemValue =item[i].ToLower();if (itemValue.IndexOf(subKeyName, StringComparison.OrdinalIgnoreCase) >= 0) //如果含有指定的关键字
{int startIndex = item[i].IndexOf("="); //等号开始的位置
return item[i].Substring(startIndex + 1).Trim(); //获取等号后面的值即为Value
}
}return string.Empty;
}///
///生成防止和保留字、关键字同名的字段格式，如[value]。///
/// 字段名，如value
protected string GetSafeFileName(stringfieldName)
{return string.Format(safeFieldFormat, fieldName);
}#endregion
#region 通用操作方法
///
///添加记录///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual boolInsert(Hashtable recordField, DbTransaction trans)
{return this.Insert(recordField, tableName, trans);
}///
///添加记录///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual bool Insert(Hashtable recordField, stringtargetTable, DbTransaction trans)
{bool result = false;if (recordField == null || recordField.Count < 1)
{returnresult;
}string fields = ""; //字段名
string vals = ""; //字段值
foreach (string field inrecordField.Keys)
{
fields+= string.Format("{0},", GetSafeFileName(field));
vals+= string.Format("{0}{1},", parameterPrefix, field);
}
fields= fields.Trim(',');//除去前后的逗号
vals = vals.Trim(',');//除去前后的逗号
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", targetTable, fields, vals);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);foreach (string field inrecordField.Keys)
{object val =recordField[field];
val= val ??DBNull.Value;if (val isDateTime)
{if (Convert.ToDateTime(val) <= Convert.ToDateTime("1753-1-1"))
{
val=DBNull.Value;
}
}
db.AddInParameter(command, field, TypeToDbType(val.GetType()), val);
}if (trans != null)
{
result= db.ExecuteNonQuery(command, trans) > 0;
}else{
result= db.ExecuteNonQuery(command) > 0;
}returnresult;
}///
///添加记录///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual intInsert2(Hashtable recordField, DbTransaction trans)
{return this.Insert2(recordField, tableName, trans);
}///
///添加记录///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual int Insert2(Hashtable recordField, stringtargetTable, DbTransaction trans)
{throw newNotSupportedException();
}///
///更新某个表一条记录(只适用于用单键,用int类型作键值的表)///
/// ID值
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual bool Update(objectid, Hashtable recordField, DbTransaction trans)
{return this.PrivateUpdate(id, recordField, tableName, trans);
}///
///更新某个表一条记录(只适用于用单键,用int类型作键值的表)///
/// ID值
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual bool Update(object id, Hashtable recordField, stringtargetTable, DbTransaction trans)
{returnPrivateUpdate(id, recordField, targetTable, trans);
}///
///更新某个表一条记录///
/// ID值
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public virtual bool PrivateUpdate(object id, Hashtable recordField, stringtargetTable, DbTransaction trans)
{try{if (recordField == null || recordField.Count < 1)
{return false;
}string setValue = "";foreach (string field inrecordField.Keys)
{
setValue+= string.Format("{0} = {1}{2},", GetSafeFileName(field), parameterPrefix, field);
}string sql = string.Format("UPDATE {0} SET {1} WHERE {2} = {3}{2}",
targetTable, setValue.Substring(0, setValue.Length - 1), primaryKey, parameterPrefix);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);bool foundID = false;foreach (string field inrecordField.Keys)
{object val =recordField[field];
val= val ??DBNull.Value;if (val isDateTime)
{if (Convert.ToDateTime(val) <= Convert.ToDateTime("1753-1-1"))
{
val=DBNull.Value;
}
db.AddInParameter(command, field, DbType.DateTime, val);
}else{
db.AddInParameter(command, field, TypeToDbType(val.GetType()), val);
}if(field.Equals(primaryKey, StringComparison.OrdinalIgnoreCase))
{
foundID= true;
}
}if (!foundID)
{
db.AddInParameter(command, primaryKey, TypeToDbType(id.GetType()), id);
}bool result = false;if (trans != null)
{
result= db.ExecuteNonQuery(command, trans) > 0;
}else{
result= db.ExecuteNonQuery(command) > 0;
}returnresult;
}catch(Exception ex)
{
LogTextHelper.WriteLine(ex.ToString());throw;
}
}///
///执行SQL查询语句，返回查询结果的所有记录的第一个字段,用逗号分隔。///
/// SQL语句
/// 事务对象
///
///返回查询结果的所有记录的第一个字段,用逗号分隔。///
public virtual string SqlValueList(string sql, DbTransaction trans = null)
{
StringBuilder result= newStringBuilder();
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);if (trans != null)
{using (IDataReader dr =db.ExecuteReader(command, trans))
{while(dr.Read())
{
result.AppendFormat("{0},", dr[0].ToString());
}
}
}else{using (IDataReader dr =db.ExecuteReader(command))
{while(dr.Read())
{
result.AppendFormat("{0},", dr[0].ToString());
}
}
}string strResult = result.ToString().Trim(',');returnstrResult;
}///
///执行一些特殊的语句///
/// SQL语句
/// 事务对象
public virtual int SqlExecute(string sql, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);if (trans != null)
{returndb.ExecuteNonQuery(command, trans);
}else{returndb.ExecuteNonQuery(command);
}
}///
///执行存储过程函数。///
/// 存储过程函数
/// 参数集合
/// 事务对象
///
public virtual int StoreProcExecute(string storeProcName, DbParameter[] parameters, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetStoredProcCommand(storeProcName);foreach (DbParameter param inparameters)
{
db.AddInParameter(command, param.ParameterName, param.DbType, param.Value);
}int result = -1;if (trans != null)
{
result=db.ExecuteNonQuery(command, trans);
}else{
result=db.ExecuteNonQuery(command);
}returnresult;
}///
///执行SQL查询语句，返回所有记录的DataTable集合。///
/// SQL查询语句
/// 事务对象
///
public virtual DataTable SqlTable(string sql, DbTransaction trans = null)
{return SqlTable(sql, null, trans);
}///
///执行SQL查询语句，返回所有记录的DataTable集合。///
/// SQL查询语句
/// 参数集合
/// 事务对象
///
public virtual DataTable SqlTable(string sql, DbParameter[] parameters, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);if (parameters != null)
{foreach (DbParameter param inparameters)
{
db.AddInParameter(command, param.ParameterName, param.DbType, param.Value);
}
}
DataTable dt= null;if (trans != null)
{
dt= db.ExecuteDataSet(command, trans).Tables[0];
}else{
dt= db.ExecuteDataSet(command).Tables[0];
}if (dt != null)
{
dt.TableName= "tableName";//增加一个表名称，防止WCF方式因为TableName为空出错
}returndt;
}///
///打开数据库连接，并创建事务对象///
public virtualDbTransaction CreateTransaction()
{
Database db=CreateDatabase();
DbConnection connection=db.CreateConnection();if (connection.State ==System.Data.ConnectionState.Closed)
{
connection.Open();
}returnconnection.BeginTransaction();
}///
///打开数据库连接，并创建事务对象///
/// 事务级别
public virtualDbTransaction CreateTransaction(IsolationLevel level)
{
Database db=CreateDatabase();
DbConnection connection=db.CreateConnection();if (connection.State ==System.Data.ConnectionState.Closed)
{
connection.Open();
}returnconnection.BeginTransaction(level);
}///
///测试数据库是否正常连接///
public virtual bool TestConnection(stringconnectionString)
{throw newNotSupportedException();
}#endregion
#region 对象添加、修改
///
///插入指定对象到数据库中///
/// 指定的对象
/// 事务对象
/// 执行成功返回true或false
public virtual bool Insert(T obj, DbTransaction trans = null)
{
ArgumentValidation.CheckForNullReference(obj,"传入的对象obj为空");
OperationLogOfInsert(obj, trans);//根据设置记录操作日志
Hashtable hash=GetHashByEntity(obj);returnInsert(hash, trans);
}///
///插入指定对象到数据库中,并返回自增长的键值///
/// 指定的对象
/// 事务对象
/// 执行成功返回True
public virtual int Insert2(T obj, DbTransaction trans = null)
{
ArgumentValidation.CheckForNullReference(obj,"传入的对象obj为空");
OperationLogOfInsert(obj, trans);//根据设置记录操作日志
Hashtable hash=GetHashByEntity(obj);returnInsert2(hash, trans);
}///
///更新对象属性到数据库中///
/// 指定的对象
/// 主键的值
/// 事务对象
/// 执行成功返回true，否则为false。
public virtual bool Update(T obj, object primaryKeyValue, DbTransaction trans = null)
{
ArgumentValidation.CheckForNullReference(obj,"传入的对象obj为空");
OperationLogOfUpdate(obj, primaryKeyValue, trans);//根据设置记录操作日志
Hashtable hash=GetHashByEntity(obj);returnUpdate(primaryKeyValue, hash, trans);
}///
///更新数据///
/// 数据类型
/// sql
/// 事务对象
/// bool
public virtual bool Update(CommandType commandType, string sql, DbTransaction trans = null)
{
Database db=CreateDatabase();if (trans != null)
{return db.ExecuteNonQuery(trans, CommandType.Text, sql) > 0;
}else{return db.ExecuteNonQuery(CommandType.Text, sql) > 0;
}
}///
///插入或更新对象属性到数据库中///
/// 指定的对象
/// 主键的值
/// 事务对象
/// 执行成功返回true，否则为false。
public virtual bool InsertUpdate(T obj, object primaryKeyValue, DbTransaction trans = null)
{bool result =Update(obj, primaryKey, trans);if (!result)
{
result=Insert(obj, trans);
}returnresult;
}///
///如果不存在记录，则插入对象属性到数据库中///
/// 指定的对象
/// 主键的值
/// 事务对象
/// 执行插入成功返回true，否则为false。
public virtual bool InsertIfNew(T obj, object primaryKeyValue, DbTransaction trans = null)
{bool result = false;string sql = string.Format("Update {0} set {1}={2}ID Where {1} = {2}ID", tableName, primaryKey, parameterPrefix);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);
db.AddInParameter(command,"ID", TypeToDbType(primaryKeyValue.GetType()), primaryKeyValue);int count =db.ExecuteNonQuery(command, trans);if (count <= 0)
{
result=Insert(obj, trans);
}returnresult;
}#endregion
#region 返回实体类操作
///
///查询数据库,检查是否存在指定ID的对象///
/// 对象的ID值
/// 事务对象
/// 存在则返回指定的对象,否则返回Null
public virtual T FindByID(object key, DbTransaction trans = null)
{returnPrivateFindByID(key, trans);
}///
///提供对FindByID的私有方法实现///
/// 主键的值
/// 事务对象
///
private T PrivateFindByID(object key, DbTransaction trans = null)
{string sql = string.Format("Select {0} From {1} Where ({2} = {3}ID)", selectedFields, tableName, primaryKey, parameterPrefix);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);
db.AddInParameter(command,"ID", TypeToDbType(key.GetType()), key);
T entity=GetEntity(db, command, trans);returnentity;
}///
///封装通用获取实体对象的私有方法///
/// Database对象
/// DbCommand对象
/// 事务对象，可为空
///
protected T GetEntity(Database db, DbCommand command, DbTransaction trans = null)
{
T entity= null;if (trans != null)
{using (IDataReader dr =db.ExecuteReader(command, trans))
{if(dr.Read())
{
entity=DataReaderToEntity(dr);
}
}
}else{using (IDataReader dr =db.ExecuteReader(command))
{if(dr.Read())
{
entity=DataReaderToEntity(dr);
}
}
}returnentity;
}///
///根据条件查询数据库,如果存在返回第一个对象///
/// 查询的条件
/// 事务对象
/// 指定的对象
public virtual T FindSingle(string condition, DbTransaction trans = null)
{return FindSingle(condition, null, null, trans);
}///
///根据条件查询数据库,如果存在返回第一个对象///
/// 查询的条件
/// 自定义排序语句，如Order By Name Desc；如不指定，则使用默认排序
/// 事务对象
/// 指定的对象
public virtual T FindSingle(string condition, string orderBy, DbTransaction trans = null)
{return FindSingle(condition, orderBy, null, trans);
}///
///根据条件查询数据库,如果存在返回第一个对象///
/// 查询的条件
/// 自定义排序语句，如Order By Name Desc；如不指定，则使用默认排序
/// 参数列表
/// 事务对象
/// 指定的对象
public virtual T FindSingle(string condition, string orderBy, IDbDataParameter[] paramList, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}if(HasInjectionData(orderBy))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", orderBy));throw new Exception("检测出SQL注入的恶意数据");
}string sql = string.Format("Select {0} From {1}", selectedFields, tableName);if (!string.IsNullOrEmpty(condition))
{
sql+= string.Format("Where {0}", condition);
}if (!string.IsNullOrEmpty(orderBy))
{
sql+= " " +orderBy;
}else{
sql+= string.Format("Order by {0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
}#region 获取单条记录Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);if (paramList != null)
{
command.Parameters.AddRange(paramList);
}
T entity=GetEntity(db, command, trans);returnentity;#endregion}///
///查找记录表中最旧的一条记录///
/// 事务对象
///
public virtual T FindFirst(DbTransaction trans = null)
{throw newNotSupportedException();
}///
///查找记录表中最新的一条记录///
/// 事务对象
///
public virtual T FindLast(DbTransaction trans = null)
{throw newNotSupportedException();
}#endregion
#region 返回集合的接口
///
///根据ID字符串(逗号分隔)获取对象列表///
/// ID字符串(逗号分隔)
/// 事务对象
/// 符合条件的对象列表
public virtual List FindByIDs(string idString, DbTransaction trans = null)
{string condition = string.Format("{0} in({1})", primaryKey, idString);return this.Find(condition, trans);
}///
///根据条件查询数据库,并返回对象集合///
/// 查询的条件
/// 事务对象
/// 指定对象的集合
public virtual List Find(string condition, DbTransaction trans = null)
{return Find(condition, null, null, trans );
}///
///根据条件查询数据库,并返回对象集合///
/// 查询的条件
/// 排序条件
/// 事务对象
/// 指定对象的集合
public virtual List Find(string condition, string orderBy, DbTransaction trans = null)
{return Find(condition, orderBy, null, trans);
}///
///根据条件查询数据库,并返回对象集合///
/// 查询的条件
/// 自定义排序语句，如Order By Name Desc；如不指定，则使用默认排序
/// 参数列表
/// 事务对象
/// 指定对象的集合
public virtual List Find(string condition, string orderBy, IDbDataParameter[] paramList, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}//串连条件语句为一个完整的Sql语句
string sql = string.Format("Select {0} From {1}", selectedFields, tableName);if (!string.IsNullOrEmpty(condition))
{
sql+= string.Format("Where {0}", condition);
}if (!string.IsNullOrEmpty(orderBy))
{
sql+= " " +orderBy;
}else{
sql+= string.Format("Order by {0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
}
List list =GetList(sql, paramList, trans);returnlist;
}///
///通用获取集合对象方法///
/// 查询的Sql语句
/// 参数列表，如果没有则为null
/// 事务对象
///
public virtual List GetList(string sql, IDbDataParameter[] paramList = null, DbTransaction trans = null)
{
T entity= null;
List list = new List();
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);if (paramList != null)
{
command.Parameters.AddRange(paramList);
}if (trans != null)
{using (IDataReader dr =db.ExecuteReader(command, trans))
{while(dr.Read())
{
entity=DataReaderToEntity(dr);
list.Add(entity);
}
}
}else{using (IDataReader dr =db.ExecuteReader(command))
{while(dr.Read())
{
entity=DataReaderToEntity(dr);
list.Add(entity);
}
}
}returnlist;
}///
///以分页方式通用获取集合对象方法///
/// 查询的Sql语句
/// 分页实体
/// 参数列表，如果没有则为null
/// 事务对象
///
public virtual List GetListWithPager(string sql, PagerInfo info, IDbDataParameter[] paramList = null, DbTransaction trans = null)
{
PagerHelper helper= new PagerHelper(sql, this.selectedFields, this.sortField,
info.PageSize, info.CurrenetPageIndex,this.isDescending, "");string countSql = helper.GetPagingSql(true);string strCount =SqlValueList(countSql);
info.RecordCount=Convert.ToInt32(strCount);string dataSql = helper.GetPagingSql(false);
List list =GetList(dataSql, paramList, trans);returnlist;
}///
///根据条件查询数据库,并返回对象集合(用于分页数据显示)///
/// 查询的条件
/// 分页实体
/// 事务对象
/// 指定对象的集合
public virtual List FindWithPager(string condition, PagerInfo info, DbTransaction trans = null)
{return FindWithPager(condition, info, this.sortField, this.isDescending, trans);
}///
///根据条件查询数据库,并返回对象集合(用于分页数据显示)///
/// 查询的条件
/// 分页实体
/// 排序字段
/// 事务对象
/// 指定对象的集合
public virtual List FindWithPager(string condition, PagerInfo info, string fieldToSort, DbTransaction trans = null)
{return FindWithPager(condition, info, fieldToSort, this.isDescending, trans);
}///
///根据条件查询数据库,并返回对象集合(用于分页数据显示)///
/// 查询的条件
/// 分页实体
/// 排序字段
/// 是否降序
/// 事务对象
/// 指定对象的集合
public virtual List FindWithPager(string condition, PagerInfo info, string fieldToSort, bool desc, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}
PagerHelper helper= new PagerHelper(tableName, this.selectedFields, fieldToSort,
info.PageSize, info.CurrenetPageIndex, desc, condition);string countSql = helper.GetPagingSql(true);string strCount =SqlValueList(countSql);
info.RecordCount=Convert.ToInt32(strCount);string dataSql = helper.GetPagingSql(false);
List list = GetList(dataSql, null, trans);returnlist;
}///
///返回数据库所有的对象集合///
/// 事务对象
/// 指定对象的集合
public virtual List GetAll(DbTransaction trans = null)
{return GetAll("", trans);
}///
///返回数据库所有的对象集合///
/// 自定义排序语句，如Order By Name Desc；如不指定，则使用默认排序
/// 事务对象
/// 指定对象的集合
public virtual List GetAll(string orderBy, DbTransaction trans = null)
{if(HasInjectionData(orderBy))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", orderBy));throw new Exception("检测出SQL注入的恶意数据");
}string sql = string.Format("Select {0} From {1}", selectedFields, tableName);if (!string.IsNullOrEmpty(orderBy))
{
sql+=orderBy;
}else{
sql+= string.Format("Order by {0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
}
List list = GetList(sql, null);returnlist;
}///
///返回数据库所有的对象集合(用于分页数据显示)///
/// 分页实体信息
/// 事务对象
/// 指定对象的集合
public virtual List GetAll(PagerInfo info, DbTransaction trans = null)
{return FindWithPager("", info, this.sortField, this.isDescending, trans);
}///
///返回数据库所有的对象集合(用于分页数据显示)///
/// 分页实体信息
/// 排序字段
/// 是否降序
/// 事务对象
/// 指定对象的集合
public virtual List GetAll(PagerInfo info, string fieldToSort, bool desc, DbTransaction trans = null)
{return FindWithPager("", info, fieldToSort, desc, trans);
}///
///返回所有记录到DataTable集合中///
/// 事务对象
///
public virtual DataTable GetAllToDataTable(DbTransaction trans = null)
{return GetAllToDataTable("", trans);
}///
///返回所有记录到DataTable集合中///
/// 自定义排序语句，如Order By Name Desc；如不指定，则使用默认排序
/// 事务对象
///
public virtual DataTable GetAllToDataTable(string orderBy, DbTransaction trans = null)
{if(HasInjectionData(orderBy))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", orderBy));throw new Exception("检测出SQL注入的恶意数据");
}string sql = string.Format("Select {0} From {1}", selectedFields, tableName);if (!string.IsNullOrEmpty(orderBy))
{
sql+=orderBy;
}else{
sql+= string.Format("Order by {0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");
}returnGetDataTableBySql(sql, trans);
}///
///根据分页条件，返回DataTable对象///
/// 分页条件
/// 事务对象
///
public virtual DataTable GetAllToDataTable(PagerInfo info, DbTransaction trans = null)
{return FindToDataTable("", info, this.sortField, this.isDescending, trans);
}///
///根据分页条件，返回DataTable对象///
/// 分页条件
/// 排序字段
/// 是否降序
/// 事务对象
///
public virtual DataTable GetAllToDataTable(PagerInfo info, string fieldToSort, bool desc, DbTransaction trans = null)
{return FindToDataTable("", info, fieldToSort, desc, trans);
}///
///根据查询条件，返回记录到DataTable集合中///
/// 查询条件
/// 事务对象
///
public virtual DataTable FindToDataTable(string condition, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}//串连条件语句为一个完整的Sql语句
string sql = string.Format("Select {0} From {1}", selectedFields, tableName);if (!string.IsNullOrEmpty(condition))
{
sql+= string.Format("Where {0}", condition);
}
sql+= string.Format("Order by {0} {1}", GetSafeFileName(sortField), isDescending ? "DESC" : "ASC");returnGetDataTableBySql(sql, trans);
}///
///根据查询条件，返回记录到DataTable集合中///
/// 查询条件
/// 分页条件
/// 事务对象
///
public virtual DataTable FindToDataTable(string condition, PagerInfo info, DbTransaction trans = null)
{return FindToDataTable(condition, info, this.sortField, this.isDescending, trans);
}///
///根据条件查询数据库,并返回DataTable集合(用于分页数据显示)///
/// 查询的条件
/// 分页实体
/// 排序字段
/// 是否降序
/// 事务对象
/// 指定DataTable的集合
public virtual DataTable FindToDataTable(string condition, PagerInfo info, string fieldToSort, bool desc, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}
PagerHelper helper= new PagerHelper(tableName, this.selectedFields, fieldToSort,
info.PageSize, info.CurrenetPageIndex, desc, condition);string countSql = helper.GetPagingSql(true);string strCount =SqlValueList(countSql, trans);
info.RecordCount=Convert.ToInt32(strCount);string dataSql = helper.GetPagingSql(false);returnGetDataTableBySql(dataSql, trans);
}///
///操根据条件返回DataTable记录辅助类///
///
/// 事务对象
///
protected DataTable GetDataTableBySql(string sql, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);
DataTable dt= null;if (trans != null)
{
dt= db.ExecuteDataSet(command, trans).Tables[0];
}else{
dt= db.ExecuteDataSet(command).Tables[0];
}if (dt != null)
{
dt.TableName= "tableName";//增加一个表名称，防止WCF方式因为TableName为空出错
}returndt;
}///
///获取某字段数据字典列表///
/// 字段名称
/// 事务对象
///
public virtual List GetFieldList(string fieldName, DbTransaction trans = null)
{return GetFieldListByCondition(fieldName, null, trans);
}///
///根据条件，获取某字段数据字典列表///
/// 字段名称
/// 事务对象
///
public List GetFieldListByCondition(string fieldName, string condition, DbTransaction trans = null)
{string safeFieldName =GetSafeFileName(fieldName);string sql = string.Format("Select distinct {0} From {1}", safeFieldName, tableName);if(!string.IsNullOrEmpty(condition))
{
sql+= string.Format("Where {0}", condition);
}
sql+= string.Format("order by {0}", safeFieldName);
List list = new List();
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);string number = string.Empty;if (trans != null)
{using (IDataReader dr =db.ExecuteReader(command, trans))
{while(dr.Read())
{
number=dr[fieldName].ToString();if (!string.IsNullOrEmpty(number))
{
list.Add(number);
}
}
}
}else{using (IDataReader dr =db.ExecuteReader(command))
{while(dr.Read())
{
number=dr[fieldName].ToString();if (!string.IsNullOrEmpty(number))
{
list.Add(number);
}
}
}
}returnlist;
}///
///根据条件，从视图里面获取记录///
/// 视图名称
/// 查询条件
/// 事务对象
///
public DataTable FindByView(string viewName, string condition, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}//串连条件语句为一个完整的Sql语句
string sql = string.Format("Select * From {0} Where", viewName);
sql+=condition;//sql += string.Format(" Order by {0} {1}", SortField, IsDescending ? "DESC" : "ASC");
returnGetDataTableBySql(sql, trans);
}///
///根据条件，从视图里面获取记录///
/// 视图名称
/// 查询条件
/// 排序字段
/// 是否为降序
/// 事务对象
///
public DataTable FindByView(string viewName, string condition, string sortField, bool isDescending, DbTransaction trans = null)
{//串连条件语句为一个完整的Sql语句
string sql = string.Format("Select * From {0} Where", viewName);
sql+=condition;
sql+= string.Format("Order by {0} {1}", sortField, isDescending ? "DESC" : "ASC");returnGetDataTableBySql(sql, trans);
}///
///获取前面记录指定数量的记录///
/// 查询语句
/// 指定数量
/// 排序条件，例如order by id
/// 事务对象
///
public abstract DataTable GetTopResult(string sql, int count, string orderBy, DbTransaction trans = null);///
///根据条件，从视图里面获取记录///
/// 视图名称
/// 查询条件
/// 排序字段
/// 是否为降序
/// 分页条件
/// 事务对象
///
public DataTable FindByViewWithPager(string viewName, string condition, string sortField, bool isDescending, PagerInfo info, DbTransaction trans = null)
{//从视图中获取数据
PagerHelper helper = new PagerHelper(viewName, "*", sortField,
info.PageSize, info.CurrenetPageIndex, isDescending, condition);string countSql = helper.GetPagingSql(true);string strCount =SqlValueList(countSql, trans);
info.RecordCount=Convert.ToInt32(strCount);string dataSql = helper.GetPagingSql(false);returnGetDataTableBySql(dataSql, trans);
}#endregion
#region 子类必须实现的函数(用于更新或者插入)
///
///将DataReader的属性值转化为实体类的属性值，返回实体类///(提供了默认的反射机制获取信息，为了提高性能，建议重写该函数)///
/// 有效的DataReader对象
/// 实体类对象
protected virtualT DataReaderToEntity(IDataReader dr)
{
T obj= newT();
PropertyInfo[] pis=obj.GetType().GetProperties();foreach (PropertyInfo pi inpis)
{try{if (dr[pi.Name].ToString() != "")
{
pi.SetValue(obj, dr[pi.Name]?? "", null);
}
}catch{ }
}returnobj;
}///
///将实体对象的属性值转化为Hashtable对应的键值(用于插入或者更新操作)///(提供了默认的反射机制获取信息，为了提高性能，建议重写该函数)///
/// 有效的实体对象
/// 包含键值映射的Hashtable
protected virtualHashtable GetHashByEntity(T obj)
{
Hashtable ht= newHashtable();
PropertyInfo[] pis=obj.GetType().GetProperties();for (int i = 0; i < pis.Length; i++)
{//if (pis[i].Name != PrimaryKey)
{object objValue = pis[i].GetValue(obj, null);
objValue= (objValue == null) ?DBNull.Value : objValue;if (!ht.ContainsKey(pis[i].Name))
{
ht.Add(pis[i].Name, objValue);
EntityTypeHash.Add(pis[i].Name, pis[i].GetType());
}
}
}returnht;
}private Hashtable EntityTypeHash = newHashtable();#endregion
#region IBaseDAL接口
///
///获取表的所有记录数量///
/// 事务对象
///
public virtual int GetRecordCount(DbTransaction trans = null)
{string sql = string.Format("Select Count(*) from {0}", tableName);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);returnGetExecuteScalarValue(db, command, trans);
}///
///获取表的所有记录数量///
/// 查询条件
/// 事务对象
///
public virtual int GetRecordCount(string condition, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}string sql = string.Format("Select Count(*) from {0} WHERE {1}", tableName, condition);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);returnGetExecuteScalarValue(db, command, trans);
}///
///获取单一的记录值///
/// Database对象
/// DbCommand对象
/// DbTransaction对象，可为空
///
protected int GetExecuteScalarValue(Database db, DbCommand command, DbTransaction trans = null)
{int result = 0;object objResult = null;if (trans != null)
{
objResult=db.ExecuteScalar(command, trans);
}else{
objResult=db.ExecuteScalar(command);
}if (objResult != null && objResult !=DBNull.Value)
{
result=Convert.ToInt32(objResult);
}returnresult;
}///
///根据condition条件，判断是否存在记录///
/// 查询的条件
/// 事务对象
/// 如果存在返回True，否则False
public virtual bool IsExistRecord(string condition, DbTransaction trans = null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}string sql = string.Format("Select Count(*) from {0} WHERE {1}", tableName, condition);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);int result =GetExecuteScalarValue(db, command, trans);return result > 0;
}///
///查询数据库,检查是否存在指定键值的对象///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 事务对象
/// 存在则返回true，否则为false。
public virtual bool IsExistKey(Hashtable recordTable, DbTransaction trans = null)
{string fields = "";//字段名
foreach (string field inrecordTable.Keys)
{
fields+= string.Format("{0} = {1}{2} AND", GetSafeFileName(field), parameterPrefix, field);
}
fields= fields.Substring(0, fields.Length - 3);//除去最后的AND
string sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}", tableName, fields);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);foreach (string field inrecordTable.Keys)
{object objValue =recordTable[field];
db.AddInParameter(command, field, TypeToDbType(objValue.GetType()), objValue);
}return GetExecuteScalarValue(db, command, trans) > 0;
}///
///查询数据库,检查是否存在指定键值的对象///
/// 指定的属性名
/// 指定的值
/// 事务对象
/// 存在则返回true，否则为false。
public virtual bool IsExistKey(string fieldName, object key, DbTransaction trans = null)
{
Hashtable table= newHashtable();
table.Add(fieldName, key);returnIsExistKey(table, trans);
}///
///获取数据库中该对象的最大ID值///
/// 事务对象
/// 最大ID值
public virtual int GetMaxID(DbTransaction trans = null)
{string sql = string.Format("SELECT MAX({0}) AS MaxID FROM {1}", primaryKey, tableName);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);object obj = null;if (trans != null)
{
obj=db.ExecuteScalar(command, trans);
}else{
obj=db.ExecuteScalar(command);
}if(Convert.IsDBNull(obj))
{return 0;//没有记录的时候为0
}returnConvert.ToInt32(obj);
}///
///根据主键和字段名称，获取对应字段的内容///
/// 指定对象的ID
/// 字段名称
/// 事务对象
///
public virtual string GetFieldValue(object key, string fieldName, DbTransaction trans = null)
{string condition = string.Format("{0} = {1}{0}", primaryKey, parameterPrefix);string sql = string.Format("Select {0} FROM {1} WHERE {2}", fieldName, tableName, condition);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);
db.AddInParameter(command, primaryKey, TypeToDbType(key.GetType()), key);objectobj;if (trans != null)
{
obj=db.ExecuteScalar(command, trans);
}else{
obj=db.ExecuteScalar(command);
}string result = "";if (obj != null && obj !=DBNull.Value)
{
result=obj.ToString();
}returnresult;
}///
///根据指定对象的ID,从数据库中删除指定对象///
/// 指定对象的ID
/// 事务对象
/// 执行成功返回true，否则为false。
public virtual bool Delete(object key, DbTransaction trans = null)
{
OperationLogOfDelete(key,null, trans); //根据设置记录操作日志
string condition = string.Format("{0} = {1}{0}", primaryKey, parameterPrefix);string sql = string.Format("DELETE FROM {0} WHERE {1}", tableName, condition);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);
db.AddInParameter(command, primaryKey, TypeToDbType(key.GetType()), key);bool result = false;if (trans != null)
{
result= db.ExecuteNonQuery(command, trans) > 0;
}else{
result= db.ExecuteNonQuery(command) > 0;
}returnresult;
}///
///根据指定对象的ID和用户ID,从数据库中删除指定对象(用于记录人员的操作日志)///
/// 指定对象的ID
/// 用户ID
/// 事务对象
/// 执行成功返回true，否则为false。
public virtual bool DeleteByUser(object key, string userId, DbTransaction trans = null)
{
OperationLogOfDelete(key, userId, trans);//根据设置记录操作日志
string condition = string.Format("{0} = {1}{0}", primaryKey, parameterPrefix);string sql = string.Format("DELETE FROM {0} WHERE {1}", tableName, condition);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);
db.AddInParameter(command, primaryKey, TypeToDbType(key.GetType()), key);bool result = false;if (trans != null)
{
result= db.ExecuteNonQuery(command, trans) > 0;
}else{
result= db.ExecuteNonQuery(command) > 0;
}returnresult;
}///
///根据指定条件,从数据库中删除指定对象///
/// 删除记录的条件语句
/// 事务对象
/// Sql参数列表
/// 事务对象
/// 执行成功返回true，否则为false。
public virtual bool DeleteByCondition(string condition, DbTransaction trans=null, IDbDataParameter[] paramList=null)
{if(HasInjectionData(condition))
{
LogTextHelper.Error(string.Format("检测出SQL注入的恶意数据, {0}", condition));throw new Exception("检测出SQL注入的恶意数据");
}string sql = string.Format("DELETE FROM {0} WHERE {1}", tableName, condition);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);if(paramList != null)
{
command.Parameters.AddRange(paramList);
}bool result = false;if (trans != null)
{
result= db.ExecuteNonQuery(command, trans) > 0;
}else{
result= db.ExecuteNonQuery(command) > 0;
}returnresult;
}#endregion
#region 用户操作记录的实现
///
///插入操作的日志记录///
/// 数据对象
/// 事务对象
protected virtual void OperationLogOfInsert(T obj, DbTransaction trans = null)
{if (OnOperationLog != null)
{string operationType = "增加";string userId =obj.CurrentLoginUserId;
Hashtable recordField=GetHashByEntity(obj);
Dictionary dictColumnNameAlias =GetColumnNameAlias();
StringBuilder sb= newStringBuilder();foreach (string field inrecordField.Keys)
{string columnAlias =field;bool result = dictColumnNameAlias.TryGetValue(field, outcolumnAlias);if (result && !string.IsNullOrEmpty(columnAlias))
{
columnAlias= string.Format("({0})", columnAlias);//增加一个括号显示
}
sb.AppendLine(string.Format("{0}{1}:{2}", field, columnAlias, recordField[field]));
sb.AppendLine();
}
sb.AppendLine();string note =sb.ToString();
OnOperationLog(userId,this.tableName, operationType, note, trans);
}
}///
///修改操作的日志记录///
/// 记录ID
/// 数据对象
/// 事务对象
protected virtual void OperationLogOfUpdate(T obj, object id, DbTransaction trans = null)
{if (OnOperationLog != null)
{string operationType = "修改";string userId =obj.CurrentLoginUserId;
Hashtable recordField=GetHashByEntity(obj);
Dictionary dictColumnNameAlias =GetColumnNameAlias();
T objInDb=FindByID(id, trans);if (objInDb != null)
{
Hashtable dbrecordField= GetHashByEntity(objInDb);//把数据库里的实体对象数据转换为哈希表
StringBuilder sb= newStringBuilder();foreach (string field inrecordField.Keys)
{string newValue =recordField[field].ToString();string oldValue =dbrecordField[field].ToString();if (newValue != oldValue)//只记录变化的内容
{string columnAlias = "";bool result = dictColumnNameAlias.TryGetValue(field, outcolumnAlias);if (result && !string.IsNullOrEmpty(columnAlias))
{
columnAlias= string.Format("({0})", columnAlias);//增加一个括号显示
}
sb.AppendLine(string.Format("{0}{1}:", field, columnAlias));
sb.AppendLine(string.Format("\t {0} -> {1}", dbrecordField[field], recordField[field]));
sb.AppendLine();
}
}
sb.AppendLine();string note =sb.ToString();
OnOperationLog(userId,this.tableName, operationType, note, trans);
}
}
}///
///删除操作的日志记录///
/// 记录ID
/// 用户ID
/// 事务对象
protected virtual void OperationLogOfDelete(object id, string userId, DbTransaction trans = null)
{if (OnOperationLog != null)
{string operationType = "删除";
Dictionary dictColumnNameAlias =GetColumnNameAlias();
T objInDb=FindByID(id, trans);if (objInDb != null)
{
Hashtable dbrecordField= GetHashByEntity(objInDb);//把数据库里的实体对象数据转换为哈希表
StringBuilder sb= newStringBuilder();foreach (string field indbrecordField.Keys)
{string columnAlias = "";bool result = dictColumnNameAlias.TryGetValue(field, outcolumnAlias);if (result && !string.IsNullOrEmpty(columnAlias))
{
columnAlias= string.Format("({0})", columnAlias);//增加一个括号显示
}
sb.AppendLine(string.Format("{0}{1}:", field, columnAlias));
sb.AppendLine(string.Format("\t {0}", dbrecordField[field]));
sb.AppendLine();
}
sb.AppendLine();string note =sb.ToString();
OnOperationLog(userId,this.tableName, operationType, note, trans);
}
}
}#endregion
#region 辅助类方法
///
///转换.NET的对象类型到数据库类型///
/// .NET的对象类型
///
public virtualDbType TypeToDbType(Type t)
{
DbType dbt;try{if (t.Name.ToLower() == "byte[]")
{
dbt=DbType.Binary;
}else{
dbt= (DbType)Enum.Parse(typeof(DbType), t.Name);
}
}catch{
dbt=DbType.String;
}returndbt;
}///
///初始化数据库表名///
/// 数据库表名
public virtual void InitTableName(stringtableName)
{this.tableName =tableName;
}///
///验证是否存在注入代码(条件语句)///
///
public virtual bool HasInjectionData(stringinputData)
{if (string.IsNullOrEmpty(inputData))return false;//里面定义恶意字符集合//验证inputData是否包含恶意集合
if(Regex.IsMatch(inputData.ToLower(), GetRegexString()))
{return true;
}else{return false;
}
}///
///获取正则表达式///
///
private stringGetRegexString()
{//构造SQL的注入关键字符
string[] strBadChar ={//"select\\s",//"from\\s",
"insert\\s","delete\\s","update\\s","drop\\s","truncate\\s","exec\\s","count\\(","declare\\s","asc\\(","mid\\(","char\\(","net user","xp_cmdshell","/add\\s","exec master.dbo.xp_cmdshell","net localgroup administrators"};//构造正则表达式
string str_Regex = ".*(";for (int i = 0; i < strBadChar.Length - 1; i++)
{
str_Regex+= strBadChar[i] + "|";
}
str_Regex+= strBadChar[strBadChar.Length - 1] + ").*";returnstr_Regex;
}///
///获取数据库的全部表名称///
///
public virtual ListGetTableNames()
{return new List();
}///
///获取表的字段名称和数据类型列表。///
///
public virtualDataTable GetFieldTypeList()
{
DataTable dt= DataTableHelper.CreateTable("ColumnName,DataType");
DataTable schemaTable=GetReaderSchema(tableName);if (schemaTable != null)
{foreach (DataRow dr inschemaTable.Rows)
{string columnName = dr["ColumnName"].ToString().ToUpper();string netType = dr["DataType"].ToString().ToLower();
DataRow row=dt.NewRow();
row["ColumnName"] =columnName;
row["DataType"] =netType;
dt.Rows.Add(row);
}
}if (dt != null)
{
dt.TableName= "tableName";//增加一个表名称，防止WCF方式因为TableName为空出错
}returndt;
}///
///获取指定表的元数据，包括字段名称、类型等等///
/// 数据库表名
///
private DataTable GetReaderSchema(stringtableName)
{
DataTable schemaTable= null;string sql = string.Format("Select * FROM {0}", tableName);
Database db=CreateDatabase();
DbCommand command=db.GetSqlStringCommand(sql);try{using (IDataReader reader =db.ExecuteReader(command))
{
schemaTable=reader.GetSchemaTable();
}
}catch(Exception ex)
{
LogTextHelper.Error(ex);
}returnschemaTable;
}///
///获取字段中文别名(用于界面显示)的字典集合///
///
public virtual DictionaryGetColumnNameAlias()
{return new Dictionary();
}///
///获取指定字段的报表数据///
/// 表字段
/// 查询条件
///
public virtual DataTable GetReportData(string fieldName, stringcondition)
{string where = "";if (!string.IsNullOrEmpty(condition))
{where = string.Format("Where {0}", condition);
}string sql = string.Format("select {0} as argument, count(*) as datavalue from {1} {2} group by {0} order by count(*) desc", fieldName, tableName, where);returnSqlTable(sql);
}#endregion
#region 存储过程执行通用方法
///
///执行存储过程，如果影响记录数，返回True，否则为False，修改并输出外部参数outParameters(如果有)。///
/// 存储过程名称
/// 输入参数，可为空
/// 输出参数，可为空
/// 事务对象，可为空
/// 如果影响记录数，返回True，否则为False
public bool StorePorcExecute(string storeProcName, Hashtable inParameters = null, Hashtable outParameters = null, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetStoredProcCommand(storeProcName);//参数传入
SetStoreParameters(db, command, inParameters, outParameters);//获取执行结果
bool result = false;if (trans != null)
{
result= db.ExecuteNonQuery(command, trans) > 0;
}else{
result= db.ExecuteNonQuery(command) > 0;
}//获取输出参数的值
EditOutParameters(db, command, outParameters);returnresult;
}///
///执行存储过程，返回实体列表集合，修改并输出外部参数outParameters(如果有)。///
/// 存储过程名称
/// 输入参数，可为空
/// 输出参数，可为空
/// 事务对象，可为空
/// 返回实体列表集合
public List StorePorcToList(string storeProcName, Hashtable inParameters = null, Hashtable outParameters = null, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetStoredProcCommand(storeProcName);//参数传入
SetStoreParameters(db, command, inParameters, outParameters);#region 获取执行结果List result = new List();
T entity= null;if (trans != null)
{using (IDataReader dr =db.ExecuteReader(command, trans))
{while(dr.Read())
{
entity=DataReaderToEntity(dr);
result.Add(entity);
}
}
}else{using (IDataReader dr =db.ExecuteReader(command))
{while(dr.Read())
{
entity=DataReaderToEntity(dr);
result.Add(entity);
}
}
}#endregion
//获取输出参数的值
EditOutParameters(db, command, outParameters);returnresult;
}///
///执行存储过程，返回DataTable集合，修改并输出外部参数outParameters(如果有)。///
/// 存储过程名称
/// 输入参数，可为空
/// 输出参数，可为空
/// 事务对象，可为空
/// 返回DataTable集合
public DataTable StorePorcToDataTable(string storeProcName, Hashtable inParameters = null, Hashtable outParameters = null, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetStoredProcCommand(storeProcName);//参数传入
SetStoreParameters(db, command, inParameters, outParameters);#region 获取执行结果DataTable result= null;if (trans != null)
{
result= db.ExecuteDataSet(command, trans).Tables[0];
}else{
result= db.ExecuteDataSet(command).Tables[0];
}if (result != null)
{
result.TableName= "tableName";//增加一个表名称，防止WCF方式因为TableName为空出错
}#endregion
//获取输出参数的值
EditOutParameters(db, command, outParameters);returnresult;
}///
///执行存储过程，返回实体对象，修改并输出外部参数outParameters(如果有)。///
/// 存储过程名称
/// 输入参数，可为空
/// 输出参数，可为空
/// 事务对象，可为空
/// 返回实体对象
public T StorePorcToEntity(string storeProcName, Hashtable inParameters = null, Hashtable outParameters = null, DbTransaction trans = null)
{
Database db=CreateDatabase();
DbCommand command=db.GetStoredProcCommand(storeProcName);//参数传入
SetStoreParameters(db, command, inParameters, outParameters);#region 获取执行结果T result= null;if (trans != null)
{using (IDataReader dr =db.ExecuteReader(command, trans))
{if(dr.Read())
{
result=DataReaderToEntity(dr);
}
}
}else{using (IDataReader dr =db.ExecuteReader(command))
{if(dr.Read())
{
result=DataReaderToEntity(dr);
}
}
}#endregion
//获取输出参数的值
EditOutParameters(db, command, outParameters);returnresult;
}///
///传入输入参数和输出参数到Database和DbCommand对象。///
/// Database对象
/// DbCommand对象
/// 输入参数的哈希表
/// 输出参数的哈希表
private void SetStoreParameters(Database db, DbCommand command, Hashtable inParameters = null, Hashtable outParameters = null)
{#region 参数传入
//传入输入参数
if (inParameters != null)
{foreach (string param ininParameters.Keys)
{object value =inParameters[param];
db.AddInParameter(command, param, TypeToDbType(value.GetType()), value);
}
}//传入输出参数
if (outParameters != null)
{foreach (string param inoutParameters.Keys)
{object value =outParameters[param];
db.AddOutParameter(command, param, TypeToDbType(value.GetType()),0);//size统一设置为0
}
}#endregion}///
///执行存储过程后，获取需要输出的参数值，修改存储在哈希表里///
/// Database对象
/// DbCommand对象
/// 输出参数的哈希表
private void EditOutParameters(Database db, DbCommand command, Hashtable outParameters = null)
{#region 获取输出参数的值
if (outParameters != null)
{
ArrayList keys= new ArrayList(outParameters.Keys);//使用临时集合对象，避免迭代错误
foreach (string param inkeys)
{object retValue =db.GetParameterValue(command, param);object value =outParameters[param];
outParameters[param]=Convert.ChangeType(retValue, value.GetType());
}
}#endregion}#endregion}
}
扫一扫
485
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
