using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web;
using System.Web.Security;
using Wuqi.Webdiyer;

namespace Mtxfw.Utility
{
    /// <summary>
    /// SQL数据操作类 By Telesa 2011.04.1
    /// </summary>
    public class SqlDbHelper_U
    {
        public static string strConn = ConfigurationManager.AppSettings["ConnStr"];
        public static string strConn0 = ConfigurationManager.AppSettings["ConnStr0"];
        public static string strConn1 = ConfigurationManager.AppSettings["ConnStr1"];
        public static string strConn2 = ""; //("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["ConnStr2"].ToString()) + ";Jet OLEDB:database password=");
        #region ExecuteNonQuery

        /// <summary>
        /// 执行ExecuteNonQuery()
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int ExecuteCmd(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                using (SqlCommand Cmd = new SqlCommand(sql, Conn))
                {
                    Conn.Open();
                    return Cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行ExecuteNonQuery() 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static int ExecuteCmd(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                using (SqlCommand Cmd = new SqlCommand(sql, Conn))
                {

                    Conn.Open();
                    for (int i = 0; i <= Params.Length - 1; i++)
                    {
                        Cmd.Parameters.Add(Params[i]);
                    }
                    return Cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 执行ExecuteNonQuery() 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static int ExecuteBCmd(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn0))
            {
                using (SqlCommand Cmd = new SqlCommand(sql, Conn))
                {

                    Conn.Open();
                    for (int i = 0; i <= Params.Length - 1; i++)
                    {
                        Cmd.Parameters.Add(Params[i]);
                    }
                    return Cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 执行ExecuteNonQuery() 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static int BExecuteCmd(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn0))
            {
                using (SqlCommand Cmd = new SqlCommand(sql, Conn))
                {

                    Conn.Open();
                    for (int i = 0; i <= Params.Length - 1; i++)
                    {
                        Cmd.Parameters.Add(Params[i]);
                    }
                    return Cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行ExecuteNonQuery() 事务
        /// </summary>
        /// <param name="list">SQL语句数组</param>
        /// <returns></returns>
        public static bool ExecuteCmd(List<string> list)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                Conn.Open();
                //创建事务
                SqlTransaction st = Conn.BeginTransaction();
                SqlCommand Cmd;
                try
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        Cmd = new SqlCommand(list[i].ToString(), Conn);
                        Cmd.Transaction = st;
                        Cmd.ExecuteNonQuery();
                    }
                    //提交事务
                    st.Commit();
                    return true;
                }
                catch
                {
                    //出错则回滚并返回false
                    st.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 执行ExecuteNonQuery() 事务重载 带参数
        /// </summary>
        /// <param name="list">SQL语句数组</param>
        /// <param name="listParams">参数数组集合</param>
        /// <returns>一定要注意SQL语句和参数数组的顺序对应 否则会出错</returns>
        public static bool ExecuteCmd(List<string> list, List<SqlParameter[]> listParams)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                Conn.Open();
                //创建事务
                SqlTransaction st = Conn.BeginTransaction();
                SqlCommand Cmd;
                try
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        Cmd = new SqlCommand(list[i].ToString(), Conn);
                        Cmd.Transaction = st;
                        for (int x = 0; x < listParams[i].Length; x++)
                        {
                            Cmd.Parameters.Add(listParams[i][x]);
                        }
                        Cmd.ExecuteNonQuery();
                    }
                    //提交事务
                    st.Commit();
                    return true;
                }
                catch
                {
                    //出错则回滚并返回false
                    st.Rollback();
                    return false;
                }
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 执行 ExecuteScalar
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Cmd.CommandTimeout = 720;
                Conn.Open();
                if (Cmd.ExecuteScalar() != null)
                {
                    return (int)Cmd.ExecuteScalar();
                }
                else
                {
                    return 0;
                }
            }
        }
        /// <summary>
        /// 执行 ExecuteScalar2
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static Int64 ExecuteScalar2(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Cmd.CommandTimeout = 720;
                Conn.Open();
                return (Int64)Cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 执行 ExecuteScalar 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                if (sql.Split(' ').Length == 1)
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                }
                Cmd.CommandTimeout = 720;
                Conn.Open();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                return (int)Cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 执行 ExecuteScalar 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static Int64 ExecuteScalar2(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                if (sql.Split(' ').Length == 1)
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                }
                Cmd.CommandTimeout = 720;
                Conn.Open();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                return (Int64)Cmd.ExecuteScalar();
            }
        }
        #endregion

        #region DataSet

        /// <summary>
        /// DataSet类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                Conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter(sql, Conn);
                DataSet Ds = new DataSet();
                Da.SelectCommand.CommandTimeout = 720;
                Da.Fill(Ds);
                return Ds;
            }
        }


        /// <summary>
        /// DataSet类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataSet GetADataSet(string sql)
        {
            using (OleDbConnection Conn = new OleDbConnection(strConn2))
            {
                Conn.Open();
                OleDbDataAdapter Da = new OleDbDataAdapter(sql, Conn);
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                Da.Fill(Ds);
                return Ds;
            }
        }
        /// <summary>
        /// DataSet类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataSet GetBDataSet(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn0))
            {
                Conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter(sql, Conn);
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                Da.Fill(Ds);
                return Ds;
            }
        }
        /// <summary>
        /// DataSet类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataSet GetCDataSet(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn1))
            {
                Conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter(sql, Conn);
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                Da.Fill(Ds);
                return Ds;
            }
        }
        /// <summary>
        /// DataSet类 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                if (sql.Split(' ').Length == 1)
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                }
                Conn.Open();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                SqlDataAdapter Da = new SqlDataAdapter();
                Da.SelectCommand = Cmd;
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                Da.Fill(Ds);
                return Ds;
            }
        }
        /// <summary>
        /// DataSet类 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string TABLE_NAME, string groupby, string orderBy, string fieldlist, string fieldlist2, string filter, int Start, int Limit, out Int32 Recount)
        {
            Recount = 0;

            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlParameter[] Params = { new SqlParameter("@datasrc", SqlDbType.VarChar,50),
                                    new SqlParameter("@groupby", SqlDbType.VarChar,100),
                                    new SqlParameter("@orderBy", SqlDbType.VarChar,100),
                                    new SqlParameter("@fieldlist", SqlDbType.VarChar,2000),
                                    new SqlParameter("@fieldlist2", SqlDbType.VarChar,2000),
                                    new SqlParameter("@filter", SqlDbType.VarChar,2000),
                                    new SqlParameter("@Start", SqlDbType.Int),
                                    new SqlParameter("@Limit", SqlDbType.Int),
                                    new SqlParameter("@recount", SqlDbType.Int)};
                Params[0].Value = TABLE_NAME;
                Params[1].Value = groupby;
                Params[2].Value = orderBy;
                Params[3].Value = fieldlist;
                Params[4].Value = fieldlist2;
                Params[5].Value = filter;
                Params[6].Value = Start;
                Params[7].Value = Limit;
                Params[8].Direction = ParameterDirection.Output;

                SqlCommand Cmd = new SqlCommand("PagingProc", Conn);
                Conn.Open();
                Cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter Da = new SqlDataAdapter();
                Da.SelectCommand = Cmd;
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                Da.Fill(Ds);
                Recount = Convert.ToInt32(DBNull.Value.Equals(Params[Params.Length - 1].Value) ? 0 : Params[Params.Length - 1].Value);
                return Ds;
            }
        }
        /// <summary>
        /// getqiandao类 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static DataSet getqiandao(int gtype, int Q_MemberId)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlParameter[] Params = { new SqlParameter("@gtype", SqlDbType.Int),
                                          new SqlParameter("@Q_MemberId", SqlDbType.Int) };
                Params[0].Value = gtype;
                Params[1].Value = Q_MemberId;
                SqlCommand Cmd = new SqlCommand("getqiandao", Conn);
                Conn.Open();
                Cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter Da = new SqlDataAdapter();
                Da.SelectCommand = Cmd;
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                Da.Fill(Ds);
                return Ds;
            }
        }
        #endregion

        #region DataTable

        /// <summary>
        /// DataTable 类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                Conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter(sql, Conn);
                Da.SelectCommand.CommandTimeout = 720;
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
        }

        /// <summary>
        /// DataSet类 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Cmd.CommandTimeout = 720;
                Conn.Open();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                SqlDataAdapter Da = new SqlDataAdapter();
                Da.SelectCommand = Cmd;
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
        }

        #endregion

        #region AspNetPager分页

        /// <summary>
        /// AspNetPager分页
        /// </summary>
        /// <param name="Page">分页控件</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="Table">表名</param>
        /// 
        /// <returns></returns>
        public static DataSet Pager(AspNetPager Page, string sql, string Table)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                Conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter(sql, Conn);
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                Da.Fill(Ds, Page.PageSize * (Page.CurrentPageIndex - 1), Page.PageSize, Table);
                return Ds;
            }
        }

        /// <summary>
        /// AspNetPager分页 重载
        /// </summary>
        /// <param name="Page">分页控件</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="Table">表名</param>
        /// <param name="Params">标量变量参数</param>
        /// <returns></returns>
        public static DataSet Pager(AspNetPager Page, string sql, string Table, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter();
                Da.SelectCommand = Cmd;
                Da.SelectCommand.CommandTimeout = 720;
                DataSet Ds = new DataSet();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                Da.Fill(Ds, Page.PageSize * (Page.CurrentPageIndex - 1), Page.PageSize, Table);
                return Ds;
            }
        }

        #endregion

        #region DataReader

        /// <summary>
        /// DataReader 类
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string sql)
        {
            SqlConnection Conn = new SqlConnection(strConn);
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(sql, Conn);
            Cmd.CommandTimeout = 720;
            try
            {
                return Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                Conn.Close();
                throw;
            }
        }

        /// <summary>
        /// DataReader 类 重载
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">参数</param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string sql,params SqlParameter[] Params)
        {
            SqlConnection Conn = new SqlConnection(strConn);
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(sql, Conn);
            Cmd.CommandTimeout = 720;
            try
            {
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                return Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                Conn.Close();
                throw;
            }
        }
        
        #endregion

        #region GetObject
        /// <summary>
        /// GetObject
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static object GetObject(string sql)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Cmd.CommandTimeout = 720;
                Conn.Open();
               
                 object execut = (object)Cmd.ExecuteScalar();
                if (execut != null)
                {
                    return execut;
                }
                else
                {
                    return (object)"";
                }
            }
        }

        /// <summary>
        /// GetObject
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Params">参数数组</param>
        /// <returns></returns>
        public static object GetObject(string sql, params SqlParameter[] Params)
        {
            using (SqlConnection Conn = new SqlConnection(strConn))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                if (sql.Split(' ').Length == 1)
                {
                    Cmd.CommandType = CommandType.StoredProcedure;
                }
                Cmd.CommandTimeout = 720;
                Conn.Open();
                for (int i = 0; i <= Params.Length - 1; i++)
                {
                    Cmd.Parameters.Add(Params[i]);
                }
                 object execut = (object)Cmd.ExecuteScalar();
                if (execut != null)
                {
                    return execut;
                }
                else
                {
                    return (object)"";
                }
            }
        }
        #endregion
      
    }
}