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

namespace Mammonth.Common {


    /// <summary>
    /// 使用SQL命令直接存取操作数据库公共类
    /// </summary>
    /// <remarks>
    /// 创建人：zhaoshunlu
    /// 最后修改：2013-11-11
    /// </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.Closed) {
                conn.Open();
            } else if (conn.State == ConnectionState.Broken) {
                conn.Close();
                conn.Open();
            }
        }

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

        /// <summary>
        /// 获取最内部的异常信息   
        /// <see cref="CruiseLine.strLastErr"/> 缓存最后错误,为了返回底层错误给客户端.
        /// </summary>
        /// <param name="ex">异常对象</param>        
        /// <remarks> 
        /// 创建人及日期：ZhaoShunLu 2013年10月10日 16:16:04
        /// <para>修改人、日期及内容:</para>
        /// </remarks>
        private static void SetLastError(System.Exception ex) {
            System.Exception baseex = ex.GetBaseException();//获取最内部的异常信息
            if (baseex != null) {
                strLastError = baseex.Message;
            } else {
                strLastError = ex.Message;
            }
        }

        #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 IList<Hashtable> Select(string sql, int pageSize, int pageIndex, out int total) {
            IList<Hashtable> dataAll = DBUtil.Select(sql);
            total = dataAll.Count;

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

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

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

        /// <summary>
        ///  执行SQL查询命令返回DataTable
        /// </summary>
        /// <param name="sql">SQL查询命令或存储过程名称</param>
        /// <param name="args">参数</param>
        /// <param name="IsStore">是否为存储过程，默认false为普通sql查询</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 = false) {

            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>
        /// <returns>返回影响行数,int类型</returns>
        public static int Execute(IList<string> sqls) {
            using (SqlConnection theconn = new SqlConnection(connectionString)) {

                int cnt = 0;
                theconn.Open();
                SqlTransaction transaction = theconn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = theconn;
                cmd.Transaction = transaction;

                if (sqls.Count > 0) {
                    for (int i = 0; i < sqls.Count; i++) {
                        cmd.CommandText = sqls[i];
                        cnt += cmd.ExecuteNonQuery();
                    }
                }
                transaction.Commit();//提交事务
                //
                cmd.Dispose();
                theconn.Close();
                //
                return cnt;
            }
        }
        /// <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)) {
                //try {
                theconn.Open();
                SqlCommand cmd = new SqlCommand(sql, theconn);
                if (args != null) SetArgs(sql, args, cmd);
                int cnt = cmd.ExecuteNonQuery();
                //
                cmd.Dispose();
                theconn.Close();
                //
                return cnt;
                //} catch (SqlException ex) {
                //    SetLastError(ex);
                //    return 0;
                //}
            }
        }
       
        /// <summary>
        /// 执行SQL命令,返回一行一列
        /// </summary>
        /// <param name="sql">合法的SQL命令</param>
        /// <returns>返回object类型</returns>
        public static object ExecuteScalar(string sql) {
            using (SqlConnection theconn = new SqlConnection(connectionString)) {
                try {
                    theconn.Open();
                    SqlCommand cmd = new SqlCommand(sql, theconn);

                    object o = cmd.ExecuteScalar();

                    cmd.Dispose();
                    theconn.Close();
                    //
                    return o;
                } catch (SqlException ex) {
                    SetLastError(ex);
                    return null;
                }
            }
        }

        
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="StoredName">要执行存的储过程名称</param>
        /// <param name="args">HashtableSQL参数</param>
        /// <returns>返回影响行数,int类型</returns>
        public static int ExecuteStoredProcedure(string StoredName, Hashtable args) {
            using (SqlConnection theconn = new SqlConnection(connectionString)) {
                //try {
                theconn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = theconn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = StoredName;
                if (args != null) SetArgs(args, cmd);
                int cnt = cmd.ExecuteNonQuery();
                //
                cmd.Dispose();
                theconn.Close();
                //
                return cnt;
                ////} catch (SqlException ex) {
                //    SetLastError(ex);
                //    return 0;
                //}
            }
        }
        #endregion

        #region 私有方法 设置Command对象参数
        /// <summary>
        /// 设置Command对象参数 
        /// </summary>
        /// <param name="args">参数集合</param>
        /// <param name="cmd">Command对象</param>
        /// <remarks>
        /// 创建 zhao 2013-6-3 
        /// </remarks>
        private static void SetArgs(Hashtable args, IDbCommand cmd) {
            IDictionaryEnumerator en = args.GetEnumerator();
            while (en.MoveNext()) {
                string key = en.Key.ToString();
                if (!key.StartsWith("@")) {
                    key = "@" + key;
                }
                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>();
            if (data == null) {
                return array;
            }
            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

        /// <summary>
        /// 更新 DataSet,可以批量保存数据更改
        /// </summary>
        /// <param name="ds">要更新的</param>
        /// <param name="ls_sql">用于初始化的SQL查询命令</param>
        /// <param name="ls_table">更新的表格名称</param>
        /// <returns></returns>
        /// <remarks>
        /// 创建人：蒙永斌
        /// 创建日期：2014-4-229
        /// </remarks>
        public static int UpdateDataSet(DataSet ds, string ls_sql, string ls_table) {
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                //bcontinue = true 表示不关闭数据库连接，方便其他地方继续调用数据
                int _updateOk = 0; //判断保存是否成功 
                try {
                    SqlCommand sqlCmd = new SqlCommand();
                    sqlCmd.CommandText = ls_sql;
                    sqlCmd.Connection = connection;

                    if (ConnectionState.Closed == connection.State) {
                        connection.Open();
                    }
                    SqlDataAdapter Ad = new SqlDataAdapter(sqlCmd.CommandText, connection);

                    Ad.ContinueUpdateOnError = true; //继续存储没有错的行

                    SqlCommandBuilder bldr = new SqlCommandBuilder(Ad); //自动生成update,insert,delete语句
                    bldr.QuotePrefix = "[";
                    bldr.QuoteSuffix = "]";

                    ds.EnforceConstraints = false;//关闭数据约束检查

                    Ad.Fill(ds, ls_table);

                    _updateOk = Ad.Update(ds, ls_table); //修改数据               

                    Ad.Dispose();
                    ds.Dispose();


                } catch (Exception ex) {
                    String s = ex.Message;
                    int n = s.Length;
                }
                return _updateOk;
            }
        }


        public static int UpdateDataSet(DataSet ds, string ls_sql, string ls_table, SqlConnection conn, SqlTransaction trans)
        {
            
            {
                //bcontinue = true 表示不关闭数据库连接，方便其他地方继续调用数据
                int _updateOk = 0; //判断保存是否成功 
                try
                {
                    SqlCommand sqlCmd = new SqlCommand();
                    sqlCmd.Transaction = trans;
                    sqlCmd.CommandText = ls_sql;
                    sqlCmd.Connection = conn;
                    

       
                    SqlDataAdapter Ad = new SqlDataAdapter(sqlCmd.CommandText, conn);

                    Ad.ContinueUpdateOnError = true; //继续存储没有错的行

                    SqlCommandBuilder bldr = new SqlCommandBuilder(Ad); //自动生成update,insert,delete语句
                    bldr.QuotePrefix = "[";
                    bldr.QuoteSuffix = "]";

                    ds.EnforceConstraints = false;//关闭数据约束检查
                    Ad.SelectCommand.Transaction = trans;
                    Ad.Fill(ds, ls_table);

                    _updateOk = Ad.Update(ds, ls_table); //修改数据               

                    Ad.Dispose();
                    ds.Dispose();


                }
                catch (Exception ex)
                {
                    String s = ex.Message;
                    int n = s.Length;
                }
                return _updateOk;
            }
        }
    }
}
