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
    }
}