using System;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Reflection;
using WHC.Pager.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using WHC.Framework.Commons;
namespace WHC.Framework.ControlUtil
{
/// <summary>
/// 数据访问层的基类
/// </summary>
public abstract class BaseDALSQL<T> : AbstractBaseDAL<T>, IBaseDAL<T> where T : BaseEntity, new()
{
#region 构造函数
/// <summary>
/// 默认构造函数
/// </summary>
public BaseDALSQL()
{}
/// <summary>
/// 指定表名以及主键,对基类进构造
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="primaryKey">表主键</param>
public BaseDALSQL(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 int Insert2(Hashtable recordField, string targetTable, DbTransaction trans)
{
int result = -1;
string fields = ""; // 字段名
string vals = ""; // 字段值
if (recordField == null || recordField.Count < 1)
{
return result;
}
SqlParameter[] param = new SqlParameter[recordField.Count];
IEnumerator eKeys = recordField.Keys.GetEnumerator();
int i = 0;
while (eKeys.MoveNext())
{
string field = eKeys.Current.ToString();
fields += field + ",";
vals += string.Format("@{0},", field);
object val = recordField[eKeys.Current.ToString()];
param[i] = new SqlParameter("@" + field, val);
i++;
}
fields = fields.Trim(',');//除去前后的逗号
vals = vals.Trim(',');//除去前后的逗号
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2});SELECT SCOPE_IDENTITY()", 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;
}
/// <summary>
/// 测试数据库是否正常连接
/// </summary>
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 对象添加、修改、查询接口
/// <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))
{
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(DatabaseType.SqlServer, true);
string strCount = SqlValueList(countSql, trans);
info.RecordCount = Convert.ToInt32(strCount);
string dataSql = helper.GetPagingSql(DatabaseType.SqlServer, 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))
{
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(DatabaseType.SqlServer, true);
string strCount = SqlValueList(countSql, trans);
info.RecordCount = Convert.ToInt32(strCount);
string dataSql = helper.GetPagingSql(DatabaseType.SqlServer, 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)
{
return base.SqlTable(sql.ToUpper(), trans);
}
/// <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)
{
return base.SqlTable(sql.ToUpper(), parameters, trans);
}
/// <summary>
/// 获取数据库的全部表名称
/// </summary>
/// <returns></returns>
public override List<string> GetTableNames()
{
string sql = string.Format("SELECT name FROM sysobjects WHERE (xtype = 'U') order by name");
Database db = CreateDatabase();
DbCommand command = db.GetSqlStringCommand(sql);
List<string> list = new List<string>();
using (IDataReader dr = db.ExecuteReader(command))
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
return list;
}
#endregion
}
}
