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
{
///
/// 数据访问层的基类
///
public abstract class BaseDALAccess : AbstractBaseDAL, IBaseDAL where T : BaseEntity, new()
{
#region 构造函数
///
/// 默认构造函数
///
public BaseDALAccess() { }
///
/// 指定表名以及主键,对基类进构造
///
/// 表名
/// 表主键
public BaseDALAccess(string tableName, string primaryKey)
: base(tableName, primaryKey)
{
parameterPrefix = "@"; //数据库参数化访问的占位符
safeFieldFormat = "[{0}]"; //防止和保留字、关键字同名的字段格式,如[value]
}
#endregion
#region 通用操作方法
///
/// 添加记录
///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
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;
}
///
/// 添加记录
///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
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;
}
///
/// 更新某个表一条记录
///
/// ID值
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
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;
}
}
///
/// 更新某个表一条记录
///
/// ID值
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
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;
}
///
/// 测试数据库是否正常连接
///
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 对象添加、修改、查询接口
///
/// 查找记录表中最旧的一条记录
///
/// 事务对象
///
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;
}
///
/// 查找记录表中最新的一条记录
///
/// 事务对象
///
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 下面两个覆盖基类函数,指定具体的数据库类型
///
/// 根据条件查询数据库,并返回对象集合(用于分页数据显示)
///
/// 查询的条件
/// 分页实体
/// 排序字段
/// 是否降序
/// 事务对象
/// 指定对象的集合
public override List 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 list = GetList(dataSql, null, trans);
return list;
}
///
/// 根据条件查询数据库,并返回DataTable集合(用于分页数据显示)
///
/// 查询的条件
/// 分页实体
/// 排序字段
/// 是否降序
/// 事务对象
/// 指定DataTable的集合
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
///
/// 获取前面记录指定数量的记录
///
/// 查询语句
/// 指定数量
/// 排序条件,例如order by id
/// 事务对象
///
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 特殊的操作
///
/// 兼容Oracle的字段大写的重写函数
///
/// sql语句
/// 事务对象
///
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;
}
///
/// 兼容Oracle的字段大写的重写函数
///
/// sql语句
/// SQL参数集合
/// 事务对象
///
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;
}
///
/// 获取数据库的全部表名称
///
///
public override List GetTableNames()
{
Database db = CreateDatabase();
List list = new List();
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 设置数据库的密码
///
/// 根据配置数据库配置名称生成Database对象
///
///
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;
}
///
/// 动态改变或者连接字符串
///
///
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
}
}