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.WebService.Logging;
|
|
namespace CY.WebService
|
{
|
/// <summary>
|
/// 数据库交换类
|
/// </summary>
|
public class Database : IDisposable
|
{
|
#region 变量
|
private SqlConnection _Connection;
|
private SqlTransaction _Tran;
|
#endregion
|
|
#region 属性
|
/// <summary>
|
/// 数据库连接驱动器
|
/// </summary>
|
public SqlConnection Connection
|
{
|
get
|
{
|
if (ConnectionState.Open != _Connection.State)
|
_Connection.Open();
|
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(ConfigurationManager.AppSettings["QueryTimeOut"].ToString());
|
}
|
}
|
#endregion
|
|
#region 方法
|
/// <summary>
|
/// 创建一个新的默认数据库连接（不包含事务）
|
/// </summary>
|
public Database()
|
{
|
this.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection"].ToString());
|
this.Open(false);
|
}
|
/// <summary>
|
/// 创建一个新的默认数据库连接
|
/// </summary>
|
/// <param name="isBeginTran">是否包含事务</param>
|
public Database(bool isBeginTran)
|
{
|
this.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection"].ToString());
|
this.Open(isBeginTran);
|
}
|
|
/// <summary>
|
/// 使用特定连接字符串
|
/// </summary>
|
/// <param name="ConnectionString">连接字符串</param>
|
public Database(string ConnectionString)
|
{
|
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 (this.Connection.State != System.Data.ConnectionState.Closed)
|
{
|
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)
|
{
|
new Log4NetAdapter().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)
|
{
|
new Log4NetAdapter().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)
|
{
|
new Log4NetAdapter().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>
|
/// <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
|
}
|
}
|
