123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604 |
- using System;
- using System.Text;
- using System.Collections;
- using System.Data;
- using System.Data.OleDb;
- using System.Data.Common;
- using System.Collections.Generic;
- using System.Reflection;
- using System.Configuration;
- using Microsoft.Practices.EnterpriseLibrary.Data;
- using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
- using ProjectBase.Data.Logs;
- using ProjectBase.Data.Pager;
- using System.Data.SqlClient;
- using ProjectBase.Data.BaseDAL;
- using ProjectBase.Data.Encrypt;
- namespace WHC.Framework.ControlUtil
- {
- /// <summary>
- /// 数据访问层的基类
- /// </summary>
- public abstract class BaseDALAccess<T> : AbstractBaseDAL<T>, IBaseDAL<T> where T : BaseEntity, new()
- {
- #region 构造函数
- /// <summary>
- /// 默认构造函数
- /// </summary>
- public BaseDALAccess() { }
- /// <summary>
- /// 指定表名以及主键,对基类进构造
- /// </summary>
- /// <param name="tableName">表名</param>
- /// <param name="primaryKey">表主键</param>
- public BaseDALAccess(string tableName, string primaryKey)
- : base(tableName, primaryKey)
- {
- parameterPrefix = "@"; //数据库参数化访问的占位符
- safeFieldFormat = "[{0}]"; //防止和保留字、关键字同名的字段格式,如[value]
- }
- #endregion
- #region 通用操作方法
- /// <summary>
- /// 添加记录
- /// </summary>
- /// <param name="recordField">Hashtable:键[key]为字段名;值[value]为字段对应的值</param>
- /// <param name="targetTable">需要操作的目标表名称</param>
- /// <param name="trans">事务对象,如果使用事务,传入事务对象,否则为Null不使用事务</param>
- public override bool Insert(Hashtable recordField, string targetTable, DbTransaction trans)
- {
- bool result = false;
- string fields = ""; // 字段名
- string vals = ""; // 字段值
- if (recordField == null || recordField.Count < 1)
- {
- return result;
- }
- OleDbParameter[] param = new OleDbParameter[recordField.Count];
- IEnumerator eKeys = recordField.Keys.GetEnumerator();
- int i = 0;
- while (eKeys.MoveNext())
- {
- string field = eKeys.Current.ToString();
- fields += string.Format("[{0}],", field);//加[]为了去除别名引起的错误
- vals += string.Format("@{0},", field);
- object val = recordField[eKeys.Current.ToString()];
- val = val ?? DBNull.Value;
- if (val is DateTime)
- {
- if (Convert.ToDateTime(val) <= Convert.ToDateTime("1753-1-1"))
- {
- val = DBNull.Value;
- }
- }
- param[i] = new OleDbParameter("@" + field, val);
- if (val is DateTime)
- {
- param[i].OleDbType = OleDbType.Date;//日期类型特别处理,否则Access数据库访问出错
- }
- i++;
- }
- 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);
- command.Parameters.AddRange(param);
- if (trans != null)
- {
- result = db.ExecuteNonQuery(command, trans) > 0;
- }
- else
- {
- result = db.ExecuteNonQuery(command) > 0;
- }
- return result;
- }
- /// <summary>
- /// 添加记录
- /// </summary>
- /// <param name="recordField">Hashtable:键[key]为字段名;值[value]为字段对应的值</param>
- /// <param name="targetTable">需要操作的目标表名称</param>
- /// <param name="trans">事务对象,如果使用事务,传入事务对象,否则为Null不使用事务</param>
- public override int Insert2(Hashtable recordField, string targetTable, DbTransaction trans)
- {
- int result = -1;
- string fields = ""; // 字段名
- string vals = ""; // 字段值
- if (recordField == null || recordField.Count < 1)
- {
- return result;
- }
- OleDbParameter[] param = new OleDbParameter[recordField.Count];
- IEnumerator eKeys = recordField.Keys.GetEnumerator();
- int i = 0;
- while (eKeys.MoveNext())
- {
- string field = eKeys.Current.ToString();
- fields += string.Format("[{0}],", field);//加[]为了去除别名引起的错误
- vals += string.Format("@{0},", field);
- object val = recordField[eKeys.Current.ToString()];
- val = val ?? DBNull.Value;
- if (val is DateTime)
- {
- if (Convert.ToDateTime(val) <= Convert.ToDateTime("1753-1-1"))
- {
- val = DBNull.Value;
- }
- }
- param[i] = new OleDbParameter("@" + field, val);
- if (val is DateTime)
- {
- param[i].OleDbType = OleDbType.Date;//日期类型特别处理,否则Access数据库访问出错
- }
- i++;
- }
- fields = fields.Trim(',');//除去前后的逗号
- vals = vals.Trim(',');//除去前后的逗号
- string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2});SELECT @@IDENTITY", targetTable, fields, vals);//SCOPE_IDENTITY()
- Database db = CreateDatabase();
- DbCommand command = db.GetSqlStringCommand(sql);
- command.Parameters.AddRange(param);
- if (trans != null)
- {
- result = Convert.ToInt32(db.ExecuteScalar(command, trans).ToString());
- }
- else
- {
- result = Convert.ToInt32(db.ExecuteScalar(command).ToString());
- }
- return result;
- }
- /// <summary>
- /// 更新某个表一条记录
- /// </summary>
- /// <param name="id">ID值</param>
- /// <param name="recordField">Hashtable:键[key]为字段名;值[value]为字段对应的值</param>
- /// <param name="targetTable">需要操作的目标表名称</param>
- /// <param name="trans">事务对象,如果使用事务,传入事务对象,否则为Null不使用事务</param>
- public override bool PrivateUpdate(object id, Hashtable recordField, string targetTable, DbTransaction trans)
- {
- try
- {
- if (recordField == null || recordField.Count < 1)
- {
- return false;
- }
- string setValue = "";
- foreach (string field in recordField.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 in recordField.Keys)
- {
- object val = recordField[field];
- val = val ?? DBNull.Value;
- if (val is DateTime)
- {
- if (Convert.ToDateTime(val) <= Convert.ToDateTime("1753-1-1"))
- {
- val = DBNull.Value;
- }
- db.AddInParameter(command, field, DbType.Date, val);//Access日期必须使用DbType.Date,如果使用 DbType.DateTime会出现类型不匹配错误
- }
- 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;
- }
- return result;
- }
- catch (Exception ex)
- {
- LogHelper.log.Error(ex.ToString());
- throw;
- }
- }
- /// <summary>
- /// 更新某个表一条记录
- /// </summary>
- /// <param name="id">ID值</param>
- /// <param name="recordField">Hashtable:键[key]为字段名;值[value]为字段对应的值</param>
- /// <param name="targetTable">需要操作的目标表名称</param>
- /// <param name="trans">事务对象,如果使用事务,传入事务对象,否则为Null不使用事务</param>
- public bool PrivateUpdate2(object id, Hashtable recordField, string targetTable, DbTransaction trans)
- {
- string field = ""; // 字段名
- object val = null; // 值
- string setValue = ""; // 更新Set () 中的语句
- if (recordField == null || recordField.Count < 1)
- {
- return false;
- }
- OleDbParameter[] param = new OleDbParameter[recordField.Count];
- int i = 0;
- IEnumerator eKeys = recordField.Keys.GetEnumerator();
- while (eKeys.MoveNext())
- {
- field = eKeys.Current.ToString();
- val = recordField[eKeys.Current.ToString()];
- val = val ?? DBNull.Value;
- if (val is DateTime)
- {
- if (Convert.ToDateTime(val) <= Convert.ToDateTime("1753-1-1"))
- {
- val = DBNull.Value;
- }
- }
- setValue += string.Format("[{0}] = @{0},", field);//加[ ]用来避免关键字错误
- param[i] = new OleDbParameter(string.Format("@{0}", field), val);
- if (val is DateTime)
- {
- param[i].OleDbType = OleDbType.Date;//日期类型特别处理,否则Access数据库访问出错
- }
- i++;
- }
- string sql = "";
- //为了避免整形ID在更新语句Where ID ='1'出现“标准表达式中数据类型不匹配”错误
- ////因此设置不同类型,更新语句不同的条件语句。
- //if (id.GetType() == typeof(int) || ValidateUtil.IsNumber(id.ToString()))
- //{
- // sql = string.Format("UPDATE {0} SET {1} WHERE {2} = {3} ", targetTable, setValue.Substring(0, setValue.Length - 1), primaryKey, id);
- //}
- //else
- // sql = string.Format("UPDATE {0} SET {1} WHERE {2} = '{3}' ", targetTable, setValue.Substring(0, setValue.Length - 1), primaryKey, id);
- //{
- //}
- LogHelper.log.Debug(sql);
- Database db = CreateDatabase();
- DbCommand command = db.GetSqlStringCommand(sql);
- command.Parameters.AddRange(param);
- bool result = false;
- if (trans != null)
- {
- result = db.ExecuteNonQuery(command, trans) > 0;
- }
- else
- {
- result = db.ExecuteNonQuery(command) > 0;
- }
- return result;
- }
- /// <summary>
- /// 测试数据库是否正常连接
- /// </summary>
- public override bool TestConnection(string connectionString)
- {
- bool result = false;
- using (DbConnection connection = new OleDbConnection(connectionString))
- {
- connection.Open();
- if (connection.State == System.Data.ConnectionState.Open)
- {
- result = true;
- }
- }
- return result;
- }
- #endregion
- #region 对象添加、修改、查询接口
- /// <summary>
- /// 查找记录表中最旧的一条记录
- /// </summary>
- /// <param name="trans">事务对象</param>
- /// <returns></returns>
- public override T FindFirst(DbTransaction trans = null)
- {
- string sql = string.Format("Select top 1 {0} From {1} Order by {2} ASC", selectedFields, tableName, GetSafeFileName(sortField));
- Database db = CreateDatabase();
- DbCommand command = db.GetSqlStringCommand(sql);
- T entity = GetEntity(db, command, trans);
- return entity;
- }
- /// <summary>
- /// 查找记录表中最新的一条记录
- /// </summary>
- /// <param name="trans">事务对象</param>
- /// <returns></returns>
- public override T FindLast(DbTransaction trans = null)
- {
- string sql = string.Format("Select top 1 {0} From {1} Order by {2} DESC", selectedFields, tableName, GetSafeFileName(sortField));
- Database db = CreateDatabase();
- DbCommand command = db.GetSqlStringCommand(sql);
- T entity = GetEntity(db, command, trans);
- return entity;
- }
- #region 下面两个覆盖基类函数,指定具体的数据库类型
- /// <summary>
- /// 根据条件查询数据库,并返回对象集合(用于分页数据显示)
- /// </summary>
- /// <param name="condition">查询的条件</param>
- /// <param name="info">分页实体</param>
- /// <param name="fieldToSort">排序字段</param>
- /// <param name="desc">是否降序</param>
- /// <param name="trans">事务对象</param>
- /// <returns>指定对象的集合</returns>
- public override List<T> FindWithPager(string condition, PagerInfo info, string fieldToSort, bool desc, DbTransaction trans = null)
- {
- if (HasInjectionData(condition))
- {
- LogHelper.log.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(DatabaseType.Access, true);
- string strCount = SqlValueList(countSql, trans);
- info.RecordCount = Convert.ToInt32(strCount);
- string dataSql = helper.GetPagingSql(DatabaseType.Access, false);
- List<T> list = GetList(dataSql, null, trans);
- return list;
- }
- /// <summary>
- /// 根据条件查询数据库,并返回DataTable集合(用于分页数据显示)
- /// </summary>
- /// <param name="condition">查询的条件</param>
- /// <param name="info">分页实体</param>
- /// <param name="fieldToSort">排序字段</param>
- /// <param name="desc">是否降序</param>
- /// <param name="trans">事务对象</param>
- /// <returns>指定DataTable的集合</returns>
- public override DataTable FindToDataTable(string condition, PagerInfo info, string fieldToSort, bool desc, DbTransaction trans = null)
- {
- if (HasInjectionData(condition))
- {
- LogHelper.log.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(DatabaseType.Access, true);
- string strCount = SqlValueList(countSql, trans);
- info.RecordCount = Convert.ToInt32(strCount);
- string dataSql = helper.GetPagingSql(DatabaseType.Access, false);
- return GetDataTableBySql(dataSql, trans);
- }
- #endregion
- /// <summary>
- /// 获取前面记录指定数量的记录
- /// </summary>
- /// <param name="sql">查询语句</param>
- /// <param name="count">指定数量</param>
- /// <param name="orderBy">排序条件,例如order by id</param>
- /// <param name="trans">事务对象</param>
- /// <returns></returns>
- public override DataTable GetTopResult(string sql, int count, string orderBy, DbTransaction trans = null)
- {
- string resultSql = string.Format("select top {0} * from ({1} {2}) ", count, sql, orderBy);
- return SqlTable(resultSql, trans);
- }
- #endregion
- #region 特殊的操作
- /// <summary>
- /// 兼容Oracle的字段大写的重写函数
- /// </summary>
- /// <param name="sql">sql语句</param>
- /// <param name="trans">事务对象</param>
- /// <returns></returns>
- public override DataTable SqlTable(string sql, DbTransaction trans = null)
- {
- //由于Sqlite字段查询不区分大小写,因此要返回大写字段,不能通过改变Sql大写方式
- //通过代码改变列的名称为大写即可
- DataTable dt = base.SqlTable(sql, trans);
- foreach (DataColumn col in dt.Columns)
- {
- col.ColumnName = col.ColumnName.ToUpper();
- }
- return dt;
- }
- /// <summary>
- /// 兼容Oracle的字段大写的重写函数
- /// </summary>
- /// <param name="sql">sql语句</param>
- /// <param name="parameters">SQL参数集合</param>
- /// <param name="trans">事务对象</param>
- /// <returns></returns>
- public override DataTable SqlTable(string sql, DbParameter[] parameters, DbTransaction trans = null)
- {
- //由于Sqlite字段查询不区分大小写,因此要返回大写字段,不能通过改变Sql大写方式
- //通过代码改变列的名称为大写即可
- DataTable dt = base.SqlTable(sql, parameters, trans);
- foreach (DataColumn col in dt.Columns)
- {
- col.ColumnName = col.ColumnName.ToUpper();
- }
- return dt;
- }
- /// <summary>
- /// 获取数据库的全部表名称
- /// </summary>
- /// <returns></returns>
- public override List<string> GetTableNames()
- {
- Database db = CreateDatabase();
- List<string> list = new List<string>();
- using (DbConnection connection = db.CreateConnection())
- {
- if (connection.State != ConnectionState.Open)
- {
- connection.Open();
- }
- DataTable schemaTable = connection.GetSchema("TABLES");
- for (int i = 0; i < schemaTable.Rows.Count; i++)
- {
- string tablename = schemaTable.Rows[i]["TABLE_NAME"].ToString();
- list.Add(tablename);
- }
- }
- return list;
- }
- #endregion
- #region 设置数据库的密码
- /// <summary>
- /// 根据配置数据库配置名称生成Database对象
- /// </summary>
- /// <returns></returns>
- protected override Database CreateDatabase()
- {
- Database db = null;
- if (string.IsNullOrEmpty(dbConfigName))
- {
- db = DatabaseFactory.CreateDatabase();
- }
- else
- {
- db = DatabaseFactory.CreateDatabase(dbConfigName);
- }
- DbConnectionStringBuilder sb = db.DbProviderFactory.CreateConnectionStringBuilder();
- sb.ConnectionString = GetConnectionString(dbConfigName);
- GenericDatabase newDb = new GenericDatabase(sb.ToString(), db.DbProviderFactory);
- db = newDb;
- return db;
- }
- /// <summary>
- /// 动态改变或者连接字符串
- /// </summary>
- /// <returns></returns>
- protected virtual string GetConnectionString(string dbConfigName)
- {
- string connectionString = "";
- DatabaseSettings setting = ConfigurationManager.GetSection("dataConfiguration") as DatabaseSettings;
- if (setting != null)
- {
- string connection = string.IsNullOrEmpty(dbConfigName) ? setting.DefaultDatabase : dbConfigName;
- connectionString = ConfigurationManager.ConnectionStrings[connection].ConnectionString;
- #region 加密解密操作
- //使用自定义加密
- //if (!connectionString.EndsWith(";"))
- //{
- // connectionString += ";";
- //}
- //connectionString += string.Format("Jet OLEDB:Database Password=wuhuacong2013;");
- string passwordKey = "Jet OLEDB:Database Password";
- string password = GetSubValue(connectionString, passwordKey);
- if (!string.IsNullOrEmpty(password))
- {
- //尝试使用AES解密
- string decryptStr = password;
- try
- {
- decryptStr = EncodeHelper.AES_Decrypt(password);
- }
- catch
- {
- decryptStr = password;
- //throw new InvalidOperationException("无法解密数据库");
- }
- connectionString += string.Format(";{0}={1};", passwordKey, decryptStr);
- }
- #endregion
- }
- return connectionString;
- }
- #endregion
- }
- }
|