using System;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;

namespace Northwind.DAL.Common {
  
    
    /// <summary>
    /// 使用SQL命令直接存取操作数据库公共类
    /// </summary>
    /// <remarks>
    /// 创建人：zhaoshunlu
    /// 最后修改：2013-11-4
    /// </remarks>
    public class DBUtil {
       
        /// <summary>
        /// 最后错误
        /// </summary>
        private static string strLastError;

        //SqlServer
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
      
        /// <summary>
        /// 单件模式 SqlConnection对象
        /// </summary>
        private static SqlConnection conn;

        /// <summary>
        /// 获取SqlConnection对象
        /// </summary>
        private static void getConn() {
            if (conn == null) {
                conn = new SqlConnection(connectionString);
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        public static void OpenConn() {
            getConn();
            if (conn.State != ConnectionState.Open) {
                conn.Open();
            }
        }

        /// <summary>
        /// 释放连接
        /// </summary>
        public static void CloseConn() {
            if (conn != null) {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 获取最后错误
        /// </summary>
        /// <returns></returns>
        public static string GetLastError() {
            return strLastError;
        }

        #region 执行SQL获取分页记录集   [2013-11-4 ZhaoShunLu]
        /// <summary>
        /// 使用SQL命令获取分页记录
        /// </summary>
        /// <param name="PageSize">页面记录行数</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="SelectSQL">SQL查询命令</param>
        /// <param name="CountSQL">SQL统计命令</param> 
        /// <param name="strOrder">排序方式</param>
        /// <param name="total">输出总记录数</param>
        /// <param name="UnQueryAffixColumns">前端不进行自定义查询的附加列,默认为""</param>
        /// <returns></returns>
        /// <example> 这是一个例子显示如何调用此方法
        ///     <code></code>
        /// </example>
        /// <remarks> 
        /// 创建人及日期  ZhaoShunLu 2013-5-23
        /// <para>修改人、日期及内容:</para>
        /// </remarks>
        public static IList<Hashtable> GetPages(int PageSize, int PageIndex, string SelectSQL, string CountSQL, string strOrder, out int total, string UnQueryAffixColumns = "") {
            total = 0;
            string basdSql = "SELECT TOP {0} * {7} FROM (SELECT ROW_NUMBER() OVER ({1}) AS __RowNumber,* FROM ({2}) AS TB ) AS T WHERE T.__RowNumber > {3} AND T.__RowNumber <= {4} {5};{6}";//{6} SELECT COUNT()获取记录总行数

            PageIndex += 1;//页数必须从1开始计数

            if (PageSize <= 0) PageSize = 10;
            if (PageIndex <= 0) PageIndex = 1;
            int star = PageSize * (PageIndex - 1);
            int end = star + PageSize;
            string mySql = string.Format(
                basdSql,                    //基本SQL命令
                PageSize,                   //{0} 页面大小，每页记录行数   
                strOrder,                   //{1} 排序字段及方式             
                SelectSQL,                  //{2} 完整独立的查询记录集的SQL命令              
                star,                       //{3} 开始行号
                end,                        //{4} 结束行数
                strOrder,                   //{5} 排序字段及方式             
                CountSQL,                   //{6} 统计总记录条数的SQL命令
                UnQueryAffixColumns);       //{7}前端不进行自定义查询的附加列           
            //
            IList<Hashtable> array = new List<Hashtable>();
            //
            SqlDataReader read = GetDataReader(mySql);
            if (read != null) {
                //ArrayList array = new ArrayList();

                while (read.Read()) {
                    Hashtable record = new Hashtable();
                    //方式一、
                    for (int i = 0; i < read.FieldCount; i++) {
                        object cellValue = read.GetValue(i);
                        if (cellValue.GetType() == typeof(DBNull))
                            cellValue = null;

                        record[read.GetName(i)] = cellValue;
                    }
                    array.Add(record);//每一行一个hastable
                }
                if (read.NextResult()) {
                    if (read.Read())
                        total = (int)read[0];//第二个记录集只查询总记录数量
                }
                //关闭连接同时关闭connection
                read.Close();
                read.Dispose();
                //               
            }
            //
            return array;
        }

        /// <summary>
        /// 使用SQL命令获取分页记录
        /// </summary>
        /// <param name="PageSize">页面记录行数</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="SelectSQL">SQL查询命令</param>
        /// <param name="strOrder">排序方式</param>
        /// <param name="UnQueryAffixColumns">前端不进行自定义查询的附加列,默认为""</param>
        /// <returns></returns>
        /// <example> 这是一个例子显示如何调用此方法
        ///     <code></code>
        /// </example>
        /// <remarks> 
        /// 创建人及日期  ZhaoShunLu 2013-5-23
        /// <para>修改人、日期及内容:</para>
        /// </remarks>
        public static IList<Hashtable> GetPages(int PageSize, int PageIndex, string SelectSQL, string strOrder, string UnQueryAffixColumns = "") {
            int total = 0;
            return GetPages(PageSize, PageIndex, SelectSQL, "", strOrder, out total, UnQueryAffixColumns);
        }
        #endregion

        #region 执行SQL获取记录集 （无分页） [2013-2-19 ZhaoShunLu]

        /// <summary>
        /// 查询记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IList<Hashtable> Select(string sql) {
            DataTable data = GetDataTable(sql, null, false);
            return DataTableToList(data);
        }

        /// <summary>
        /// 查询记录
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static IList<Hashtable> Select(string sql, Hashtable args) {
            DataTable data = GetDataTable(sql, args, false);
            return DataTableToList(data);
        }

        /// <summary>
        /// 查询记录，支持分页
        /// 实现一个内存分页，效率低下，实际应该使用SQL分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static ArrayList Select(string sql, int pageSize, int pageIndex, out int total) {
            IList<Hashtable> dataAll = DBUtil.Select(sql);
            total = dataAll.Count;

            //实现一个内存分页，效率低下，实际应该使用SQL分页
            ArrayList data = new ArrayList();
            int start = pageIndex * pageSize;
            int end = start + pageSize;
            if (end > total)
                end = total;

            for (int i = start; i < end; i++) {
                Hashtable record = (Hashtable)dataAll[i];
                if (record == null)
                    continue;
                data.Add(record);
            }
            return data;
        }



        /// <summary>
        /// 执行SQL查询命令返回DataTable
        /// </summary>
        /// <param name="sql">SQL查询命令</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        /// <example> 这是一个例子显示如何调用此方法
        ///     <code></code>
        /// </example>
        /// <remarks> 
        /// 创建人及日期  ZhaoShunLu 2013-5-17
        /// <para>修改人、日期及内容:</para>
        /// </remarks>
        public static DataTable GetDataTable(string sql, Hashtable args) {
            return GetDataTable(sql, args, false);
        }

        /// <summary>
        ///  执行SQL查询命令返回DataTable
        /// </summary>
        /// <param name="sql">SQL查询命令</param>
        /// <param name="args">参数</param>
        /// <param name="IsStore">是否为存储过程</param>
        /// <returns></returns>
        /// <example> 这是一个例子显示如何调用此方法
        ///     <code></code>
        /// </example>
        /// <remarks> 
        /// 调用页面： 
        /// 创建人及日期：ZhaoShunLu 2013-5-17
        /// <para>修改人、日期及内容:</para>
        /// </remarks>
        public static DataTable GetDataTable(string sql, Hashtable args, bool IsStore) {

            using (SqlConnection theconn = new SqlConnection(connectionString)) {
                theconn.Open();
                //
                DataTable data = new DataTable();
                //
                SqlCommand cmd = new SqlCommand(sql, theconn);
                if (IsStore) cmd.CommandType = CommandType.StoredProcedure;
                if (args != null) SetArgs(args, cmd);//sql,
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(data);

                //释放资源
                adapter.Dispose();
                cmd.Dispose();
                theconn.Close();
                //
                return data;
            }

        }

        /// <summary>
        ///  执行SQL查询命令返回DataReader
        ///  在这里先不能关闭conn,等 Reader 关闭后自动关闭
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        /// <example> 这是一个例子显示如何调用此方法
        ///     <code></code>
        /// </example>
        /// <remarks> 
        /// 创建人及日期  ZhaoShunLu 2013-5-17
        /// <para>修改人、日期及内容:</para>
        /// </remarks>
        public static SqlDataReader GetDataReader(string sql) {
            return GetDataReader(sql, null);
        }

        public static SqlDataReader GetDataReader(string sql, Hashtable args) {
            OpenConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (args != null) SetArgs(args, cmd);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);//在这里先不能关闭conn,一定要等 Reader 关闭后才能关闭conn.
        }
        #endregion

        #region 执行SQL 新增、修改、删除命令  [2013-2-19 ZhaoShunLu]

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql">合法的SQL命令</param>
        /// <returns>返回影响行数,int类型</returns>
        public static int Execute(string sql) {
            return Execute(sql, null);
        }
        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql">合法的SQL命令</param>
        /// <param name="args">参数</param>
        /// <returns>返回影响行数,int类型</returns>
        public static int Execute(string sql, Hashtable args) {
            using (SqlConnection theconn = new SqlConnection(connectionString)) {
                SqlCommand cmd = new SqlCommand(sql, theconn);
                try {
                    conn.Open();
                    if (args != null) SetArgs(sql, args, cmd);
                    int cnt = cmd.ExecuteNonQuery();
                    //
                    cmd.Dispose();
                    theconn.Close();
                    //
                    return cnt;
                } catch (SqlException ex) {
                    strLastError = ex.Message;
                    return 0;
                }
            }
        }
        #endregion

        #region 私有方法 设置Command对象参数
        /// <summary>
        /// 设置Command对象参数
        /// 2016-6-3 zhao
        /// </summary>
        /// <param name="args">参数集合</param>
        /// <param name="cmd">Command对象</param>
        private static void SetArgs(Hashtable args, IDbCommand cmd) {
            IDictionaryEnumerator en = args.GetEnumerator();
            while (en.MoveNext()) {
                string key = en.Key.ToString();
                Object value = en.Value;
                if (value == null) {
                    value = DBNull.Value;
                }
                //
                cmd.Parameters.Add(new SqlParameter(key, value));
            }
        }
        /// <summary>
        /// 设置Command对象参数
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <param name="args">参数集合</param>
        /// <param name="cmd">Command对象</param>
        private static void SetArgs(string sql, Hashtable args, IDbCommand cmd) {
            MatchCollection ms = Regex.Matches(sql, @"@\w+"); //从SQL命令中获取参数集合         
            foreach (Match m in ms) {
                string key = m.Value;

                Object value = args[key];//从Hashtable中获取对应的参数值
                if (value == null) {
                    value = args[key.Substring(1)];
                }
                if (value == null) value = DBNull.Value;
                //
                cmd.Parameters.Add(new SqlParameter(key, value));
            }
            cmd.CommandText = sql;
        }
        /// <summary>
        /// 将DataTable转换成IList《Hashtable》
        /// </summary>
        /// <param name="data">DataTable</param>
        /// <returns>IList</returns>
        private static IList<Hashtable> DataTableToList(DataTable data) {
            IList<Hashtable> array = new List<Hashtable>();
            for (int i = 0; i < data.Rows.Count; i++) {
                DataRow row = data.Rows[i];
                //
                Hashtable record = new Hashtable();
                for (int j = 0; j < data.Columns.Count; j++) {
                    object cellValue = row[j];
                    if (cellValue.GetType() == typeof(DBNull)) {
                        cellValue = null;
                    }
                    record[data.Columns[j].ColumnName] = cellValue;
                }
                array.Add(record);//每一行一个hastable
            }
            return array;
        }
        #endregion
    }
}
