using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Data.SqlClient;
|
using System.Configuration;
|
using System.Data;
|
using System.Collections;
|
using System.Web;
|
using CY.Infrastructure.Configuration;
|
using CY.Infrastructure.Logging;
|
using CY.Infrastructure.Domain;
|
using CY.IBaseDAL;
|
namespace CY.SQLDAL
|
{
|
/// <summary>
|
/// 数据库交换类
|
/// </summary>
|
public class Database : IDisposable
|
{
|
#region 变量
|
private SqlConnection _Connection;
|
private SqlTransaction _Tran;
|
private ILogger Log;
|
#endregion
|
|
#region 属性
|
/// <summary>
|
/// 数据库连接驱动器
|
/// </summary>
|
public SqlConnection Connection
|
{
|
get
|
{
|
try
|
{
|
if (_Connection.State.Equals(ConnectionState.Closed))
|
{
|
_Connection.Open();
|
}
|
//if (ConnectionState.Open != _Connection.State)
|
// _Connection.Open();
|
return _Connection;
|
}
|
catch (Exception)
|
{
|
return _Connection;
|
}
|
}
|
set
|
{
|
_Connection = value;
|
}
|
}
|
/// <summary>
|
/// 数据库事物
|
/// </summary>
|
public SqlTransaction SqlTransaction
|
{
|
get
|
{
|
return this._Tran;
|
}
|
set
|
{
|
this._Tran = value;
|
}
|
}
|
/// <summary>
|
/// 查询超时时间（毫秒，0为无限）
|
/// </summary>
|
public static int QueryTimeOut
|
{
|
get
|
{
|
return int.Parse(ConfigurationSetting.QueryTimeOut);
|
}
|
}
|
#endregion
|
|
#region 方法
|
|
/// <summary>
|
/// 创建一个新的默认数据库连接（不包含事务）
|
/// </summary>
|
public Database()
|
{
|
LoggingFactory.InitializeLogFactory(new Log4NetAdapter());
|
Log = LoggingFactory.GetLogger();
|
this.Connection = new SqlConnection(ConfigurationSetting.SQLDB_ConnectionString);
|
this.Open(false);
|
}
|
|
/// <summary>
|
/// 创建一个新的默认数据库连接
|
/// </summary>
|
/// <param name="isBeginTran">是否包含事务</param>
|
public Database(bool isBeginTran)
|
{
|
LoggingFactory.InitializeLogFactory(new Log4NetAdapter());
|
Log = LoggingFactory.GetLogger();
|
this.Connection = new SqlConnection(ConfigurationSetting.SQLDB_ConnectionString);
|
this.Open(isBeginTran);
|
}
|
|
/// <summary>
|
/// 使用特定连接字符串
|
/// </summary>
|
/// <param name="ConnectionString">连接字符串</param>
|
public Database(string ConnectionString)
|
{
|
LoggingFactory.InitializeLogFactory(new Log4NetAdapter());
|
Log = LoggingFactory.GetLogger();
|
this.Connection = new SqlConnection(ConnectionString);
|
this.Open(false);
|
}
|
|
/// <summary>
|
/// 数据库开连接
|
/// </summary>
|
/// <param name="isBeginTran">是否开启事务</param>
|
private void Open(bool isBeginTran)
|
{
|
if (this.Connection.State != System.Data.ConnectionState.Open)
|
{
|
this.Connection.Open();
|
}
|
if (isBeginTran)
|
{
|
this._Tran = this.Connection.BeginTransaction();
|
}
|
}
|
|
/// <summary>
|
/// 数据库关连接
|
/// </summary>
|
private void Close()
|
{
|
if (this._Tran != null)
|
{
|
this._Tran.Commit();
|
}
|
if (_Connection.State.Equals(ConnectionState.Open))
|
{
|
this.Connection.Close();
|
}
|
}
|
|
/// <summary>
|
/// 无返回查询分析
|
/// </summary>
|
/// <param name="cmdText">Transaction-SQL语句</param>
|
/// <param name="cmdType">语句类型</param>
|
/// <param name="param">SQL参数</param>
|
public void Query(string cmdText, CommandType cmdType, params SqlParameter[] param)
|
{
|
SqlCommand command = new SqlCommand(cmdText, this.Connection);
|
command.CommandType = cmdType;
|
if (this._Tran != null)
|
{
|
command.Transaction = this._Tran;
|
}
|
command.CommandTimeout = Database.QueryTimeOut;
|
command.Parameters.Clear();
|
if (param != null)
|
{
|
//command.Parameters.AddRange(param);
|
|
foreach (SqlParameter p in param)
|
{
|
command.Parameters.Add(p);
|
}
|
}
|
try
|
{
|
command.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
SqlException sqlEx = ex as SqlException;
|
if (null != sqlEx)
|
{
|
Log.Log("SQL Linker Number" + sqlEx.LineNumber.ToString() + ":" + sqlEx.ToString() + "\r\n" + command.ToString());
|
}
|
throw ex;
|
}
|
finally
|
{
|
Close();
|
}
|
}
|
|
/// <summary>
|
/// 返回DataTable
|
/// </summary>
|
/// <param name="cmdText">Transaction-SQL语句</param>
|
/// <param name="cmdType">语句类型</param>
|
/// <param name="param">SQL参数</param>
|
/// <returns>DataTable数据集</returns>
|
public DataTable QueryDataTable(string cmdText, CommandType cmdType, params SqlParameter[] param)
|
{
|
SqlCommand command = new SqlCommand(cmdText, this.Connection);
|
command.CommandType = cmdType;
|
if (this._Tran != null)
|
{
|
command.Transaction = this._Tran;
|
}
|
command.CommandTimeout = Database.QueryTimeOut;
|
command.Parameters.Clear();
|
if (param != null)
|
{
|
foreach (SqlParameter sp in param)
|
{
|
command.Parameters.Add(sp);
|
}
|
}
|
SqlDataAdapter sda = new SqlDataAdapter(command);
|
DataTable dt = new DataTable();
|
try
|
{
|
sda.Fill(dt);
|
}
|
catch (Exception ex)
|
{
|
SqlException sqlEx = ex as SqlException;
|
if (null != sqlEx)
|
{
|
Log.Log("SQL Linker Number" + sqlEx.LineNumber.ToString() + ":" + sqlEx.ToString() + "\r\n" + command.ToString());
|
}
|
}
|
finally
|
{
|
Close();
|
}
|
return dt;
|
}
|
|
/// <summary>
|
/// 返回DataSet
|
/// </summary>
|
/// <param name="cmdText">Transaction-SQL语句</param>
|
/// <param name="cmdType">语句类型</param>
|
/// <param name="param">SQL参数</param>
|
/// <returns>DataSet数据集</returns>
|
public DataSet QueryDataSet(string cmdText, CommandType cmdType, params SqlParameter[] param)
|
{
|
SqlCommand command = new SqlCommand(cmdText, this.Connection);
|
command.CommandType = cmdType;
|
if (this._Tran != null)
|
{
|
command.Transaction = this._Tran;
|
}
|
command.CommandTimeout = Database.QueryTimeOut;
|
if (command.Parameters != null && command.Parameters.Count > 0)
|
command.Parameters.Clear();
|
if (param != null)
|
{
|
foreach (SqlParameter sp in param)
|
{
|
command.Parameters.Add(sp);
|
}
|
}
|
SqlDataAdapter sda = new SqlDataAdapter(command);
|
DataSet ds = new DataSet();
|
try
|
{
|
sda.Fill(ds);
|
}
|
catch (Exception ex)
|
{
|
SqlException sqlEx = ex as SqlException;
|
if (null != sqlEx)
|
{
|
Log.Log("SQL Linker Number" + sqlEx.LineNumber.ToString() + ":" + sqlEx.ToString() + "\r\n" + command.ToString());
|
}
|
}
|
finally
|
{
|
Close();
|
}
|
return ds;
|
}
|
|
/// <summary>
|
/// 返回DataTable
|
/// </summary>
|
/// <param name="cmdText">Transaction-SQL语句</param>
|
/// <param name="cmdType">语句类型</param>
|
/// <param name="param">SQL参数</param>
|
/// <returns>DataTable数据集</returns>
|
public IDataReader QueryDataReader(string cmdText, CommandType cmdType, params SqlParameter[] param)
|
{
|
SqlCommand command = new SqlCommand(cmdText, this.Connection);
|
command.CommandType = cmdType;
|
if (this._Tran != null)
|
{
|
command.Transaction = this._Tran;
|
}
|
command.CommandTimeout = Database.QueryTimeOut;
|
command.Parameters.Clear();
|
if (param != null)
|
{
|
command.Parameters.AddRange(param);
|
|
}
|
|
return command.ExecuteReader(CommandBehavior.CloseConnection);//释放Reader时关闭连接
|
}
|
|
/// <summary>
|
/// 执行SQL语句，返回影响的记录数
|
/// </summary>
|
/// <param name="SQLString">SQL语句</param>
|
/// <returns>影响的记录数</returns>
|
public int ExecuteSql(string SQLString, params SqlParameter[] param)
|
{
|
using (SqlCommand cmd = new SqlCommand(SQLString, this.Connection))
|
{
|
try
|
{
|
if (this._Tran != null)
|
{
|
cmd.Transaction = this._Tran;
|
}
|
|
if (param != null)
|
{
|
cmd.Parameters.AddRange(param);
|
}
|
cmd.CommandTimeout = Database.QueryTimeOut;
|
int rows = cmd.ExecuteNonQuery();
|
cmd.Parameters.Clear();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
throw e;
|
}
|
finally
|
{
|
Close();
|
}
|
}
|
|
}
|
|
#region 泛型扩展方法
|
|
/// <summary>
|
/// 根据编号集合批量删除数据
|
/// </summary>
|
/// <typeparam name="T">实现IAggregateRoot接口的可实例化类</typeparam>
|
/// <param name="opear">操作对象</param>
|
/// <param name="ids">编号集合</param>
|
/// <returns>true:成功,false:失败</returns>
|
public static bool DeleteDataByIds<T>(ICommonDAL operatorDal, string currentOperator, params int[] ids) where T : IAggregateRoot, new()
|
{
|
if (null == ids || 0 == ids.Length)
|
return false;
|
else
|
{
|
}
|
//加载事务并提交
|
IUnitOfWork unit = new UnitOfWorkForSql();
|
int i = -1;
|
T model;
|
|
try
|
{
|
while (++i < ids.Length)
|
{
|
model = new T();
|
model.Visiter("Keyid", -1, true, ids[i]);
|
model.Visiter("Operator", -1, true, currentOperator);
|
unit.RegisterRemoved(model, operatorDal);
|
}
|
unit.Commit();
|
}
|
catch (Exception ex)
|
{
|
|
return false;
|
}
|
|
|
return true;
|
}
|
|
/// <summary>
|
/// 读取数据为指定实体列表并释放掉reader
|
/// </summary>
|
/// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
|
/// <param name="reader">查询结果集</param>
|
/// <returns></returns>
|
public List<T> ReadDataToModelAndDispose<T>(IDataReader reader) where T : IAggregateRoot, new()
|
{
|
using (reader)
|
{
|
return ReadDataToModel<T>(reader);
|
}
|
}
|
|
/// <summary>
|
/// 读取数据为指定实体列表
|
/// </summary>
|
/// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
|
/// <param name="reader">查询结果集</param>
|
/// <returns></returns>
|
public List<T> ReadDataToModel<T>(IDataReader reader) where T : IAggregateRoot, new()
|
{
|
List<T> models = new List<T>();
|
if (null == reader || !reader.Read())
|
return models;
|
|
T model = new T();
|
string tempName = string.Empty;
|
int maxCount = reader.FieldCount;//5
|
int i = 0;
|
|
while (true)
|
{
|
tempName = reader.GetName(i);
|
model.Visiter(tempName, -1, true, reader[tempName]);
|
|
if (++i >= maxCount)// 0,1
|
{
|
i = 0;
|
models.Add(model);
|
if (!reader.Read())
|
break;
|
model = new T();
|
}
|
}
|
return models;
|
}
|
|
/// <summary>
|
/// 通用分页查询
|
/// </summary>
|
/// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
|
/// <param name="pagination">分页参数</param>
|
/// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
|
/// <param name="fromSouce">数据源(Form后的部分)</param>
|
/// <param name="orderBy">排序字段</param>
|
/// <param name="resultOrderby">结果排序字段(不填时默认为排序字段)</param>
|
/// <param name="condition">筛选条件</param>
|
/// <returns>实体集合(传入参数有误时返回空。另：Visiter内的设置值名称必须与查询字段都相符才能将全部值设置到实体)</returns>
|
public IList<T> SelectModelPage<T>(Infrastructure.Query.Pagination pagination, string selectTarget, string fromSouce, string orderBy, string resultOrderby = "", string condition = "") where T : IAggregateRoot, new()
|
{
|
IList<T> result = null;
|
SqlParameter[] parameters = GetPagingParams(pagination, selectTarget, fromSouce, orderBy, resultOrderby, condition);
|
if (null == parameters)
|
return null;
|
|
using (IDataReader reader = QueryDataReader("sp_CurrencyPage", CommandType.StoredProcedure, parameters))
|
{
|
result = ReadDataToModel<T>(reader);
|
reader.Close();
|
Close();
|
}
|
int? resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[8].Value);
|
pagination.RecordCount = resultValue.HasValue ? resultValue.Value : 0;
|
resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[7].Value);
|
pagination.PageCount = resultValue.HasValue ? resultValue.Value : 0;
|
return result;
|
}
|
|
/// <summary>
|
/// 通用分页查询(返回DataTable)
|
/// </summary>
|
/// <param name="pagination">分页参数</param>
|
/// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
|
/// <param name="fromSouce">数据源(Form后的部分)</param>
|
/// <param name="orderBy">排序字段</param>
|
/// <param name="resultOrderby">结果排序字段(不填时默认为排序字段)</param>
|
/// <param name="condition">筛选条件</param>
|
/// <returns>DataTable(传入参数有误时返回空)</returns>
|
public DataTable SelectModelPage(Infrastructure.Query.Pagination pagination, string selectTarget, string fromSouce, string orderBy, string resultOrderby = "", string condition = "")
|
{
|
|
|
SqlParameter[] parameters = GetPagingParams(pagination, selectTarget, fromSouce, orderBy, resultOrderby, condition);
|
if (null == parameters)
|
return null;
|
|
DataTable dt = QueryDataTable("sp_CurrencyPage", CommandType.StoredProcedure, parameters);
|
|
int? resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[8].SqlValue);
|
pagination.RecordCount = resultValue.HasValue ? resultValue.Value : 0;
|
resultValue = CY.Infrastructure.Common.MyConvert.ConvertToInt32(parameters[7].SqlValue);
|
pagination.PageCount = resultValue.HasValue ? resultValue.Value : 0;
|
|
return dt;
|
|
|
|
}
|
|
/// <summary>
|
/// 获取分页参数
|
/// </summary>
|
/// <param name="pagination">分页参数</param>
|
/// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
|
/// <param name="fromSouce">数据源(Form后的部分)</param>
|
/// <param name="orderBy">排序字段</param>
|
/// <param name="resultOrderby">结果排序字段(不填时默认为排序字段)</param>
|
/// <param name="condition">筛选条件</param>
|
/// <returns>参数列表(传入参数有误时返回空)</returns>
|
private static SqlParameter[] GetPagingParams(Infrastructure.Query.Pagination pagination, string selectTarget, string fromSouce, string orderBy, string resultOrderby, string condition)
|
{
|
if (null == pagination || string.IsNullOrEmpty(selectTarget) || string.IsNullOrEmpty(fromSouce) || string.IsNullOrEmpty(orderBy))
|
{
|
return null;
|
}
|
|
if (string.IsNullOrEmpty(resultOrderby))
|
{
|
int splitindex = orderBy.IndexOf('.');
|
resultOrderby = string.Format("TempT.{0}", splitindex > 0 ? orderBy.Substring(splitindex + 1) : orderBy);
|
}
|
|
SqlParameter[] parameters = {
|
new SqlParameter(){ParameterName="@Columns", SqlDbType=SqlDbType.VarChar,Value=selectTarget},
|
new SqlParameter(){ParameterName="@TableName", SqlDbType=SqlDbType.VarChar,Value=fromSouce},
|
new SqlParameter(){ParameterName="@Condition", SqlDbType=SqlDbType.VarChar,Value=condition},
|
new SqlParameter(){ParameterName="@OrderByRow", SqlDbType=SqlDbType.VarChar,Value=orderBy},
|
new SqlParameter(){ParameterName="@OrderByTable", SqlDbType=SqlDbType.VarChar,Value=resultOrderby},
|
new SqlParameter(){ParameterName="@PageNum ", SqlDbType=SqlDbType.Int,Value= pagination.PageIndex},
|
new SqlParameter(){ParameterName="@PageSize", SqlDbType=SqlDbType.Int,Value=pagination.PageSize},
|
new SqlParameter(){ParameterName="@PageCount", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output},
|
new SqlParameter(){ParameterName="@RecordCount", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output}
|
};
|
return parameters;
|
}
|
|
/// <summary>
|
/// 暂用查询
|
/// </summary>
|
/// <typeparam name="T">实现IAggregateRoot接口的非抽象类实体(必须保证可实例化)</typeparam>
|
/// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
|
/// <param name="fromSouce">数据源(Form后的部分)</param>
|
/// <param name="condition">筛选条件</param>
|
/// <returns>实体集合(传入参数有误时返回空。另：Visiter内的设置值名称必须与查询字段都相符才能将全部值设置到实体)</returns>
|
public IList<T> SelectModel<T>(string selectTarget, string fromSouce, string condition = "") where T : IAggregateRoot, new()
|
{
|
IList<T> result = null;
|
SqlParameter[] parameters = GetModelGeterParams(selectTarget, fromSouce, condition);
|
if (null == parameters)
|
return null;
|
using (IDataReader reader = QueryDataReader("sp_SelectModel", CommandType.StoredProcedure, parameters))
|
{
|
result = ReadDataToModel<T>(reader);
|
reader.Close();
|
Close();
|
}
|
return result;
|
|
}
|
|
/// <summary>
|
/// 暂用查询
|
/// </summary>
|
/// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
|
/// <param name="fromSouce">数据源(Form后的部分)</param>
|
/// <param name="condition">筛选条件</param>
|
/// <returns>DataTable(传入参数有误时返回空)</returns>
|
public DataTable SelectModel(string selectTarget, string fromSouce, string condition = "")
|
{
|
|
SqlParameter[] parameters = GetModelGeterParams(selectTarget, fromSouce, condition);
|
if (null == parameters)
|
return null;
|
return QueryDataTable("sp_SelectModel", CommandType.StoredProcedure, parameters);
|
|
}
|
|
/// <summary>
|
/// 获取实体查询参数
|
/// </summary>
|
/// <param name="selectTarget">要查询的目标(Select后From前部分)</param>
|
/// <param name="fromSouce">数据源(Form后的部分)</param>
|
/// <param name="orderBy">排序字段</param>
|
/// <returns></returns>
|
private SqlParameter[] GetModelGeterParams(string selectTarget, string fromSouce, string condition)
|
{
|
|
|
return string.IsNullOrEmpty(selectTarget) || string.IsNullOrEmpty(fromSouce) ? null :
|
new SqlParameter[] {
|
new SqlParameter(){ParameterName="@Columns", SqlDbType=SqlDbType.VarChar,Value=selectTarget},
|
new SqlParameter(){ParameterName="@TableName", SqlDbType=SqlDbType.VarChar,Value=fromSouce},
|
new SqlParameter(){ParameterName="@Condition", SqlDbType=SqlDbType.VarChar,Value=condition}
|
};
|
}
|
|
#endregion
|
|
#endregion
|
|
#region IDisposable接口方法
|
public void Dispose()
|
{
|
try
|
{
|
this.Close();
|
}
|
catch (Exception ex)
|
{
|
if (this._Tran != null)
|
{
|
this._Tran.Rollback();
|
}
|
throw ex;
|
}
|
}
|
#endregion
|
}
|
}
|
