using System;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Reflection;
using System.Configuration;
//using System.Data.SQLite;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using ProjectBase.Data.BaseDAL;
using ProjectBase.Data.Pager;
using ProjectBase.Data.Logs;
using ProjectBase.Data.Encrypt;
namespace ProjectBase.Data.BaseDAL.BaseDatabase
{
///
/// 数据访问层的基类
///
public abstract class BaseDALSQLite : AbstractBaseDAL, IBaseDAL where T : BaseEntity, new()
{
#region 构造函数
///
/// 默认构造函数
///
public BaseDALSQLite() { }
///
/// 指定表名以及主键,对基类进构造
///
/// 表名
/// 表主键
public BaseDALSQLite(string tableName, string primaryKey)
: base(tableName, primaryKey)
{
this.parameterPrefix = "$";//或者为@也可以(数据库参数化访问的占位符)
this.safeFieldFormat = "[{0}]"; //防止和保留字、关键字同名的字段格式(尽量避免)
}
#endregion
#region 通用操作方法
///
/// 添加记录
///
/// Hashtable:键[key]为字段名;值[value]为字段对应的值
/// 需要操作的目标表名称
/// 事务对象,如果使用事务,传入事务对象,否则为Null不使用事务
public override int Insert2(Hashtable recordField, string targetTable, DbTransaction trans = null)
{
int result = -1;
if (recordField == null || recordField.Count < 1)
{
return result;
}
string fields = ""; // 字段名
string vals = ""; // 字段值
foreach (string field in recordField.Keys)
{
fields += string.Format("[{0}],", field);//加[]为了去除别名引起的错误
vals += string.Format("{0}{1},", parameterPrefix, field);
}
fields = fields.Trim(',');//除去前后的逗号
vals = vals.Trim(',');//除去前后的逗号
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2});Select LAST_INSERT_ROWID()", targetTable, fields, vals);
Database db = CreateDatabase();
DbCommand command = db.GetSqlStringCommand(sql);
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, TypeToDbType(val.GetType()), val);
}
if (trans != null)
{
result = Convert.ToInt32(db.ExecuteScalar(command, trans).ToString());
}
else
{
result = Convert.ToInt32(db.ExecuteScalar(command).ToString());
}
return result;
}
///
/// 测试数据库是否正常连接
///
public override bool TestConnection(string connectionString)
{
bool result = false;
//using (DbConnection connection = new SQLiteConnection(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 {0} From {1} Order by {2} ASC LIMIT 1", 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 {0} From {1} Order by {2} DESC LIMIT 1", 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.SQLite, true);
string strCount = SqlValueList(countSql, trans);
info.RecordCount = Convert.ToInt32(strCount);
string dataSql = helper.GetPagingSql(DatabaseType.SQLite, 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.SQLite, true);
string strCount = SqlValueList(countSql, trans);
info.RecordCount = Convert.ToInt32(strCount);
string dataSql = helper.GetPagingSql(DatabaseType.SQLite, 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 * From ({1} {2}) LIMIT {0} ", 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(";Password=wuhuacong2013;");
string password = GetSubValue(connectionString, "password");
if (!string.IsNullOrEmpty(password))
{
//尝试使用AES解密
string decryptStr = password;
try
{
decryptStr = EncodeHelper.AES_Decrypt(password);
}
catch
{
decryptStr = password;
//throw new InvalidOperationException("无法解密数据库");
}
connectionString += string.Format(";Password={0};", decryptStr);
}
#endregion
}
return connectionString;
}
#endregion
}
}