using System;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ProjectBase.Data.BaseDAL;
using ProjectBase.Data.Pager;
namespace WHC.Framework.ControlUtil
{
///
/// MySql数据访问层的基类
///
public abstract class BaseDALMySql : AbstractBaseDAL, IBaseDAL where T : BaseEntity, new()
{
#region 构造函数
///
/// 默认构造函数
///
public BaseDALMySql() { }
///
/// 指定表名以及主键,对基类进构造
///
/// 表名
/// 表主键
public BaseDALMySql(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)
{
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_ID()", 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 SqlConnection(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)
{
PagerHelper helper = new PagerHelper(tableName, this.selectedFields, fieldToSort,
info.PageSize, info.CurrenetPageIndex, desc, condition);
string countSql = helper.GetPagingSql(DatabaseType.MySql, true);
string strCount = SqlValueList(countSql, trans);
info.RecordCount = Convert.ToInt32(strCount);
string dataSql = helper.GetPagingSql(DatabaseType.MySql, 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)
{
PagerHelper helper = new PagerHelper(tableName, this.selectedFields, fieldToSort,
info.PageSize, info.CurrenetPageIndex, desc, condition);
string countSql = helper.GetPagingSql(DatabaseType.MySql, true);
string strCount = SqlValueList(countSql, trans);
info.RecordCount = Convert.ToInt32(strCount);
string dataSql = helper.GetPagingSql(DatabaseType.MySql, false);
return GetDataTableBySql(dataSql, trans);
}
///
/// 获取前面记录指定数量的记录
///
/// 查询语句
/// 指定数量
/// 排序条件,例如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
#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()
{
string sql = string.Format("show tables;");
Database db = CreateDatabase();
DbCommand command = db.GetSqlStringCommand(sql);
List list = new List();
using (IDataReader dr = db.ExecuteReader(command))
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
return list;
}
#endregion
}
}