using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace VueWebCoreApi.Tools
{
    public class DBHelper
    {
        #region DataTable GetTable(string sql) 获取DataTable
        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetTable(string sql)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getTable(sql);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                //throw (ex);
                return null;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetTable(string sql, params SqlParameter[] parameters)
        {


            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getTable(sql, parameters);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw (ex);
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        /// <summary>
        /// Dataset获取数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        #region
        public static DataSet GetData(string sql, params SqlParameter[] parameters)
        {


            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getdate(sql, parameters);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                //throw (ex);
                //创建日志记录组件实例
                return null;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region GetCount(string sql) 获取数据行数,如果出错返回"-1"
        /// <summary>
        /// 获取数据行数,如果出错返回"-1"
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <returns>如果出错返回"-1"</returns>
        public static int GetCount(string sql)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getCount(sql);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                //throw (ex);
                //创建日志记录组件实例
                return 0;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }

        /// <summary>
        /// 执行多条sql语句的事物
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static int Executesqltran(List<string> list, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.ExecuteSqlTran(list, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw (ex);
                //创建日志记录组件实例
                return 0;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }

        /// <summary>
        /// 获取数据行数,如果出错返回"-1"
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>如果出错返回"-1"</returns>
        public static int GetCount(string sql, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getCount(sql, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw (ex);
                //创建日志记录组件实例
                return 0;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region GetObject(string sql) 获取表格第一行第一列
        /// <summary>
        /// 获取表格第一行第一列
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <returns>如果出错返回"null"</returns>
        public static object GetObject(string sql)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getObject(sql);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw (ex);
                //创建日志记录组件实例
                return null;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }

        /// <summary>
        /// 获取表格第一行第一列
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回Object</returns>
        public static object GetObject(string sql, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.getObject(sql, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw (ex);
                //创建日志记录组件实例
                return null;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region ExecuteSql(string sql) 执行SQL语句返回是否成功
        /// <summary>
        /// 执行SQL语句返回是否成功
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <returns>返回是否成功</returns>
        public static bool ExecuteSql(string sql)
        {
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.executeSql(sql);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                //throw (ex);
                //创建日志记录组件实例
                return false;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL语句返回是否成功
        /// </summary>
        /// <param name="sql">传入SQL语句</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回是否成功</returns>
        public static bool ExecuteSql(string sql, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            //ParamCollections pc = new ParamCollections();
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.executeSql(sql, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw (ex);
                //创建日志记录组件实例
                return false;
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region ExecuteProduct(string productName, params SqlParameter[] parameters) 执行存储过程返回返回值
        /// <summary>
        /// 执行存储过程返回返回值
        /// </summary>
        /// <param name="productName">存储过程名字</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回值Hash表</returns>
        public static Hashtable ExecuteProduct(string productName, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            //ParamCollections pc = new ParamCollections();
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.executeProduct(productName, parameters);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                //throw (ex);
                //创建日志记录组件实例
                return new Hashtable();
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region ExecuteProductData(string productName, params SqlParameter[] parameters) 执行存储过程返回DataTable
        /// <summary>
        /// 执行存储过程返回返回值
        /// </summary>
        /// <param name="productName">存储过程名字</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回值Hash表</returns>
        public static DataTable ExecuteProductData(string productName, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            //ParamCollections pc = new ParamCollections();
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.ExecuteProductData(productName, parameters);
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region ExecuteProductData(string productName, params SqlParameter[] parameters) 执行存储过程返回List<DataTable>
        /// <summary>
        /// 执行存储过程返回返回值
        /// </summary>
        /// <param name="productName">存储过程名字</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回值Hash表</returns>
        public static List<DataTable> ExecuteProductDataList(string productName, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            //ParamCollections pc = new ParamCollections();
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.ExecuteProductDataList(productName, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region ExecuteProductData(string productName, params SqlParameter[] parameters) 执行存储过程返回ArrayList
        /// <summary>
        /// 执行存储过程返回返回值
        /// </summary>
        /// <param name="productName">存储过程名字</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回值List</returns>
        public static ArrayList executeProductList(string produreName, bool returnDt, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            //ParamCollections pc = new ParamCollections();
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.executeProduct(produreName, parameters, returnDt);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region ExecuteProductData(string productName) 执行存储过程返回bool
        /// <summary>
        /// 执行存储过程返回返回值
        /// </summary>
        /// <param name="productName">存储过程名字</param>
        /// <param name="parameters">SqlParameter 参数</param>
        /// <returns>返回值DataTable表</returns>
        public static bool ExecuteProductbool(string productName, params SqlParameter[] parameters)
        {
            DataOperator dbo = null;
            //ParamCollections pc = new ParamCollections();
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                return dbo.ExecuteProduct(productName, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw (ex);
                //创建日志记录组件实例

            }
            finally
            {
                if (dbo != null)
                {
                    dbo.Close();
                }
            }
        }
        #endregion

        #region GetDataByPage() 分页查询
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="filename">表名</param>
        /// <param name="filename">需要返回的列</param>
        /// <param name="sortfilename">排序字段名</param>
        /// <param name="PageSize">页尺寸</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="OrderType">设置排序类型, 非 0 值则降序</param>
        /// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
        /// <param name="RecordCount">总记录</param>
        /// <returns></returns>
        public static DataTable GetDataByPage(string tablename, string filename, string sortfilename, int PageSize, int PageIndex, int OrderType, string strWhere, out int RecordCount)
        {
            RecordCount = 0;
            //创建参数
            SqlParameter[] param = {
                new SqlParameter("@tblName", SqlDbType.VarChar, 50),
                new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@OrderType", SqlDbType.Bit,1),
                new SqlParameter("@strWhere", SqlDbType.VarChar,500),
                new SqlParameter("@RecordCount", SqlDbType.Int)
            };
            param[0].Value = tablename;
            param[1].Value = filename;
            param[2].Value = sortfilename;
            param[3].Value = PageSize;
            param[4].Value = PageIndex;
            param[5].Value = OrderType;
            param[6].Value = strWhere;
            param[7].Direction = ParameterDirection.Output;
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                DataTable dt = dbo.ExecuteProductData("GetDataByPage", param);
                RecordCount = (int)param[7].Value;
                return dt;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw ex;
            }
            finally
            {
                if (null != dbo)
                {
                    dbo.Close();
                    dbo = null;
                }
            }
        }
        /// <summary>
        /// 分页查询(支持JOIN)
        /// </summary>
        /// <param name="filename">表名</param>
        /// <param name="filename">需要返回的列</param>
        /// <param name="sortfilename">排序字段名(默认升序，降序需在排序字段名后面DESC关键字：sortField Desc)</param>
        /// <param name="PageSize">页尺寸</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
        /// <param name="RecordCount">总记录</param>
        /// <returns></returns>
        public static DataTable GetDataByPage_V2(string tablename, string filename, string sortfilename, int PageSize, int PageIndex, string strWhere, out int RecordCount)
        {
            RecordCount = 0;
            //创建参数
            SqlParameter[] param = {
                new SqlParameter("@tblName", SqlDbType.VarChar, 200),
                new SqlParameter("@strGetFields", SqlDbType.VarChar, 500),
                new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@strWhere", SqlDbType.VarChar,500),
                new SqlParameter("@RecordCount", SqlDbType.Int),
                new SqlParameter("@TotalPage", SqlDbType.Int)
            };
            param[0].Value = tablename;//表名
            param[1].Value = filename;//需要返回的列
            param[2].Value = sortfilename;//排序字段名
            param[3].Value = PageSize;//页尺寸
            param[4].Value = PageIndex;//页码
            param[5].Value = strWhere;//查询条件
            param[6].Direction = ParameterDirection.ReturnValue;//总记录
            param[7].Direction = ParameterDirection.Output;//总页数
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                DataTable dt = dbo.ExecuteProductData("GetDataByPage_V2", param);
                RecordCount = (int)param[6].Value;
                return dt;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw ex;
            }
            finally
            {
                if (null != dbo)
                {
                    dbo.Close();
                    dbo = null;
                }
            }
        }
        /// <summary>
        /// 分页查询(支持JOIN)
        /// </summary>
        /// <param name="filename">表名</param>
        /// <param name="filename">需要返回的列</param>
        /// <param name="sortfilename">排序字段名(默认升序，降序需在排序字段名后面DESC关键字：sortField Desc)</param>
        /// <param name="PageSize">页尺寸</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
        /// <param name="RecordCount">总记录</param>
        /// <returns></returns>
        public static DataTable GetDataByPage_V2_Join(string tablename, string filename, string sortfilename, int PageSize, int PageIndex, string strWhere, out int RecordCount)
        {
            RecordCount = 0;
            //创建参数
            SqlParameter[] param = {
                new SqlParameter("@tblName", SqlDbType.VarChar, 200),
                new SqlParameter("@strGetFields", SqlDbType.VarChar, 500),
                new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@strWhere", SqlDbType.VarChar,500),
                new SqlParameter("@RecordCount", SqlDbType.Int),
                new SqlParameter("@TotalPage", SqlDbType.Int)
            };
            param[0].Value = tablename;//表名
            param[1].Value = filename;//需要返回的列
            param[2].Value = sortfilename;//排序字段名
            param[3].Value = PageSize;//页尺寸
            param[4].Value = PageIndex;//页码
            param[5].Value = strWhere;//查询条件
            param[6].Direction = ParameterDirection.ReturnValue;//总记录
            param[7].Direction = ParameterDirection.Output;//总页数
            DataOperator dbo = null;
            try
            {
                if (dbo == null)
                {
                    dbo = new DataOperator();
                }
                DataTable dt = dbo.ExecuteProductData("GetDataByPage_V2_Join", param);
                RecordCount = (int)param[6].Value;
                return dt;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                throw ex;
            }
            finally
            {
                if (null != dbo)
                {
                    dbo.Close();
                    dbo = null;
                }
            }
        }
        #endregion

        #region ParametersStrGet(SqlParameter[] parameters) [用于日志文本输出] 将SqlParameter数组参数转成字符串.
        /// <summary>
        /// [用于日志文本输出] 将SqlParameter数组参数转成字符串.
        /// </summary>
        /// <param name="parameters">参数数组</param>
        /// <returns></returns>
        private static string ParametersStrGet(SqlParameter[] parameters)
        {
            string parametersHeadStr = @"参数:{{{0}}}";
            string parametersContentStr = "";
            int count = 0;
            foreach (SqlParameter sp in parameters)
            {
                if (count++ > 0)
                    parametersContentStr += @",";
                parametersContentStr += string.Format("{0}:{1}", sp.ParameterName, sp.Value);
            }
            return string.Format(parametersHeadStr, parametersContentStr);
        }
        #endregion

    }
}