C# DbHelper

/* 类名称: DbHelper
* 类描述:
* 创建人: Emotion
* 创建时间: 2018/8/29 15:12:54
* 修改人: 
* 修改时间:
* 版本： @version 1.0
*/
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;

namespace Emotion
{
    public class DbHelper
    {
       
        public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];

        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (commandParameters != null)
            {
                foreach (SqlParameter parameter in commandParameters)
                {
                    if (parameter != null)
                    {
                        if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
        }

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        public static void ExecuteCommandWithSplitter(string commandText)
        {
            ExecuteCommandWithSplitter(commandText, "\r\nGO\r\n");
        }

        public static void ExecuteCommandWithSplitter(string commandText, string splitter)
        {
            int num2;
            int startIndex = 0;
            Label_0002:
            num2 = commandText.IndexOf(splitter, startIndex);
            int length = ((num2 > startIndex) ? num2 : commandText.Length) - startIndex;
            string sql = commandText.Substring(startIndex, length);
            if (sql.Trim().Length > 0)
            {
                try
                {
                    ExecuteNonQuery(sql);
                }
                catch
                {
                }
            }
            if (num2 != -1)
            {
                startIndex = num2 + splitter.Length;
                if (startIndex < commandText.Length)
                {
                    goto Label_0002;
                }
            }
        }

        public static DataSet ExecuteDataSet(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                return null;
            }
            return ExecuteDataSet(sql, null);
        }

        public static DataSet ExecuteDataSet(string sql, params SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(sql) && (parameters == null))
            {
                return null;
            }
            return ExecuteDataSet(CommandType.Text, sql, parameters);
        }

        public static DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            DataSet set2;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand();
                try
                {
                    PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);
                        command.Parameters.Clear();
                        set2 = dataSet;
                    }
                }
                catch
                {
                    connection.Close();
                    throw;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return set2;
        }

        public static async Task<bool>  ExecuteNonQueryAsync(string sql) {
            if (string.IsNullOrEmpty(sql)) {
                return false;
            }
            return await ExecuteNonQueryAsync(sql, null);
        }

        public static async Task<bool> ExecuteNonQueryAsync(string sql, SqlParameter[] parameters) {
            CommandType text = CommandType.Text;
            return await ExecuteNonQueryAsync(text, sql, parameters);
        }

        public static async Task<bool> ExecuteNonQueryAsync(CommandType commandType, string commandText, SqlParameter[] parameters) {
            int num2;
            int num = -1;
            if (string.IsNullOrEmpty(commandText) && (parameters == null)) {
                return false;
            }
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                SqlCommand command = new SqlCommand();
                try {
                    PrepareCommand(command, connection, null, commandType, commandText, parameters);
                 
                    command.Parameters.Clear();
                    return await command.ExecuteNonQueryAsync()>0;
                   // num2 = num;
                }
                catch {
                    connection.Close();
                    num = -1;
                    throw;
                }
                finally {
                    if (command != null) {
                        command.Dispose();
                    }
                }
            }
        }
        public static int ExecuteNonQuery(string sql)
        {
            int num = -1;
            if (string.IsNullOrEmpty(sql))
            {
                return num;
            }
            return ExecuteNonQuery(sql, null);
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
        {
            CommandType text = CommandType.Text;
            return ExecuteNonQuery(text, sql, parameters);
        }

        public static int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            int num2;
            int num = -1;
            if (string.IsNullOrEmpty(commandText) && (parameters == null))
            {
                return num;
            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand();
                try
                {
                    PrepareCommand(command, connection, null, commandType, commandText, parameters);
                    num = command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    num2 = num;
                }
                catch
                {
                    connection.Close();
                    num = -1;
                    throw;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return num2;
        }

        public static DataTable ExecutePage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return ExecutePage(connection, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, commandParameters);
            }
        }

        private static DataTable ExecutePage(SqlConnection connection, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, null, CommandType.Text, SqlAllFields, commandParameters);
            string str = GetPageSql(command, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount);
            command.CommandText = str;
            SqlDataAdapter adapter = new SqlDataAdapter
            {
                SelectCommand = command
            };
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "PageResult");
            command.Parameters.Clear();
            return dataSet.Tables["PageResult"];
        }

        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlDataReader reader2;
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                reader2 = command.ExecuteReader();
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return reader2;
        }

        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlDataReader reader2;
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand();
            try
            {
                PrepareCommand(command, connection, null, CommandType.Text, SQLString, cmdParms);
                SqlDataReader reader = command.ExecuteReader();
                command.Parameters.Clear();
                reader2 = reader;
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return reader2;
        }

        private static SqlDataReader ExecuteReaderPage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string GroupClause, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
        {
            SqlDataReader reader2;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand();
                try
                {
                    PrepareCommand(command, connection, null, CommandType.Text, SqlAllFields, commandParameters);
                    string str = GetPageSql(command, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount);
                    if ((GroupClause != null) && (GroupClause.Trim() != ""))
                    {
                        int length = str.ToLower().LastIndexOf(" order by ");
                        str = str.Substring(0, length) + " " + GroupClause + " " + str.Substring(length);
                    }
                    command.CommandText = str;
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    command.Parameters.Clear();
                    reader2 = reader;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return reader2;
        }

        public static object ExecuteScalar(string commandText)
        {
            CommandType text = CommandType.Text;
            return ExecuteScalar(text, commandText);
        }

        public static object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(commandType, commandText, null);
        }

        public static object ExecuteScalar(string commandText, params SqlParameter[] commandParameters)
        {
            CommandType text = CommandType.Text;
            return ExecuteScalar(text, commandText, commandParameters);
        }

        public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            object obj3;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand();
                try
                {
                    PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
                    object obj2 = command.ExecuteScalar();
                    command.Parameters.Clear();
                    obj3 = obj2;
                }
                catch
                {
                    connection.Close();
                    throw;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return obj3;
        }

        public static int ExecuteSP(string procName)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, procName, null);
        }

        public static int ExecuteSP(string procName, SqlParameter[] prams)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, procName, prams);
        }

        public static void ExecuteSP(string procName, out DataSet ds)
        {
            ds = ExecuteDataSet(CommandType.StoredProcedure, procName, null);
        }

        public static void ExecuteSP(string procName, out object obj)
        {
            obj = ExecuteScalar(CommandType.StoredProcedure, procName, null);
        }

        public static DataSet ExecuteSP(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static void ExecuteSP(string procName, SqlParameter[] prams, out DataSet ds)
        {
            ds = ExecuteDataSet(CommandType.StoredProcedure, procName, prams);
        }

        public static void ExecuteSP(string procName, SqlParameter[] prams, out object obj)
        {
            obj = ExecuteScalar(CommandType.StoredProcedure, procName, prams);
        }

        public static DataTable ExecuteTable(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                return null;
            }
            return ExecuteTable(sql, null);
        }

        public static DataTable ExecuteTable(string cmdText, params SqlParameter[] commandParameters)
        {
            return ExecuteTable(CommandType.Text, cmdText, commandParameters);
        }

        public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataTable table;
            SqlCommand command = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    PrepareCommand(command, connection, null, cmdType, cmdText, commandParameters);
                    SqlDataAdapter adapter = new SqlDataAdapter
                    {
                        SelectCommand = command
                    };
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, "Result");
                    command.Parameters.Clear();
                    table = dataSet.Tables["Result"];
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    connection.Dispose();
                }
            }
            return table;
        }

        public static DataTable GetPagedTable(DataTable dt, int _PageIndex, int _PageSize)
        {
            if (_PageIndex == 0)
            {
                return dt;
            }
            DataTable table = dt.Clone();
            int num = (_PageIndex - 1) * _PageSize;
            int count = _PageIndex * _PageSize;
            if (num < dt.Rows.Count)
            {
                if (count > dt.Rows.Count)
                {
                    count = dt.Rows.Count;
                }
                for (int i = num; i <= (count - 1); i++)
                {
                    DataRow row = table.NewRow();
                    DataRow row2 = dt.Rows[i];
                    foreach (DataColumn column in dt.Columns)
                    {
                        row[column.ColumnName] = row2[column.ColumnName];
                    }
                    table.Rows.Add(row);
                }
            }
            return table;
        }

        public static string GetPageGoogle(int RecordCount, int PageCount, int PageSize, int PageNo)
        {
            int num4 = 0;
            if (PageNo < 1)
            {
                PageNo = 1;
            }
            int num = PageNo + 1;
            int num2 = PageNo - 1;
            int num3 = ((PageNo + 5) > PageCount) ? (PageCount - 9) : (PageNo - 1);
            num4 = (PageNo < 5) ? 10 : (PageNo + 5);
            if (num3 < 1)
            {
                num3 = 1;
            }
            if (PageCount < num4)
            {
                num4 = PageCount;
            }
            string str = "";
            object obj2 = str;
            str = string.Concat(new object[] { obj2, "共", RecordCount, "条记录，共", PageCount, "页&nbsp;" }) + ((PageNo > 1) ? string.Concat(new object[] { "<a href=\"?PageNo=1", QueryUrl(), "\">首页</a>&nbsp;<a href=\"?PageNo=", num2, QueryUrl(), "\">上一页</a>" }) : "首页 上一页");
            for (int i = num3; i <= num4; i++)
            {
                str = str + ((PageNo == i) ? ("&nbsp;<font color=\"#ff0000\">" + i + "</font>") : string.Concat(new object[] { "&nbsp;<a href=\"?PageNo=", i, QueryUrl(), "\">", i, "</a>" }));
            }
            return (str + ((PageNo != PageCount) ? string.Concat(new object[] { "&nbsp;<a href=\"?PageNo=", num, QueryUrl(), "\">下一页</a>&nbsp;<a href=\"?PageNo=", PageCount, QueryUrl(), "\">末页</a>" }) : " 下一页 末页"));
        }

        public static string GetPageNormal(int RecordCount, int PageCount, int PageSize, int PageNo)
        {
            string str = "";
            object obj2 = "共" + RecordCount + "条记录 ";
            str = string.Concat(new object[] { obj2, "页次：", PageNo, "/", PageCount, "页 " }) + PageSize + "条/页 ";
            if (Convert.ToInt32(PageNo) < 2)
            {
                str = str + "首页 上页 ";
            }
            else
            {
                object obj3 = str + "<a href=\"?PageNo=1" + QueryUrl() + "\">首页</a> ";
                str = string.Concat(new object[] { obj3, "<a href=\"?PageNo=", PageNo - 1, QueryUrl(), "\">上页</a> " });
            }
            if ((PageCount - Convert.ToInt32(PageNo)) < 1)
            {
                return (str + "下页 尾页 ");
            }
            object obj4 = str;
            object obj5 = string.Concat(new object[] { obj4, "<a href=\"?PageNo=", PageNo + 1, QueryUrl(), "\">下页</a> " });
            return string.Concat(new object[] { obj5, "<a href=\"?PageNo=", PageCount, QueryUrl(), "\">尾页</a>  " });
        }

        public static string GetPageNormalAdv(int RecordCount, int PageCount, int PageSize, int PageNo, string Name)
        {
            string str = "";
            str = "共" + RecordCount + "条记录 ";
            if (Convert.ToInt32(PageNo) < 2)
            {
                str = str + "首页 上页 ";
            }
            else
            {
                object obj2 = str + "<a href=\"?PageNo=1" + QueryUrl() + "\">首页</a> ";
                str = string.Concat(new object[] { obj2, "<a href=\"?PageNo=", PageNo - 1, QueryUrl(), "\">上页</a> " });
            }
            if ((PageCount - Convert.ToInt32(PageNo)) < 1)
            {
                str = str + "下页 尾页 ";
            }
            else
            {
                object obj3 = str;
                object obj4 = string.Concat(new object[] { obj3, "<a href=\"?PageNo=", PageNo + 1, QueryUrl(), "\">下页</a> " });
                str = string.Concat(new object[] { obj4, "<a href=\"?PageNo=", PageCount, QueryUrl(), "\">尾页</a>  " });
            }
            object obj5 = str;
            str = string.Concat(new object[] { obj5, "页码：<input type=\"text\" id=\"InputPageNo", Name, "\" name=\"InputPageNo", Name, "\" class=\"list_search_input\" value=\"", PageNo, "\" style=\"width:35px;text-align:center\" maxlength=\"6\" onkeyup=\"this.value=this.value.replace(/[^0-9.]/g,'')\" onblur=\"SetPageNo(this);\" onchange=\"SetPageNo(this);\"/> <input type=\"button\" value=\"跳转\" class=\"list_search_btn\" style=\"width:45px;color:#666\" onclick=\"PageBtnGo('InputPageNo", Name, "',", PageCount, ",'", QueryUrl(), "');\"/> " }) + "<select style=\"width:100px\" onchange=\"PageSelectGo(this.options[this.selectedIndex],'" + QueryUrl() + "');\">";
            for (int i = 1; i <= PageCount; i++)
            {
                if (i == PageNo)
                {
                    str = str + string.Format("<option value=\"{0}\" selected=\"selected\">第{0}页/共{1}页</option>", i, PageCount);
                }
                else
                {
                    str = str + string.Format("<option value=\"{0}\">第{0}页/共{1}页</option>", i, PageCount);
                }
            }
            str = str + "</select> ";
            str = str + "<select style=\"width:80px\" onchange=\"PageSizeNo(this.options[this.selectedIndex],'" + QueryUrl() + "');\">";
            string[] strArray = new string[] { "10", "20", "50", "100", "200" };
            foreach (string str2 in strArray)
            {
                if (Convert.ToInt32(str2) == PageSize)
                {
                    str = str + string.Format("<option value=\"{0}\" selected=\"selected\">{0}条/页</option>", str2);
                }
                else
                {
                    str = str + string.Format("<option value=\"{0}\">{0}条/页</option>", str2);
                }
            }
            return (str + "</select> ");
        }

        private static string GetPageSql(SqlCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        {
            RecordCount = 0;
            PageCount = 0;
            if (PageSize <= 0)
            {
                PageSize = 10;
            }
            string str = "select count(" + IndexField + ") from " + SqlTablesAndWhere;
            cmd.CommandText = str;
            RecordCount = (int)cmd.ExecuteScalar();
            if ((RecordCount % PageSize) == 0)
            {
                PageCount = RecordCount / PageSize;
            }

            else
            {
                PageCount = (RecordCount / PageSize) + 1;
            }

            if (PageIndex > PageCount)
            {
                PageIndex = PageCount;
            }

            if (PageIndex < 1)
            {
                PageIndex = 1;
            }
            string str2 = null;
            if (PageIndex == 1)
            {
                return string.Concat(new object[] { "select top ", PageSize, " ", SqlAllFields, " from ", SqlTablesAndWhere, " ", OrderFields });
            }
            str2 = string.Concat(new object[] { "select top ", PageSize, " ", SqlAllFields, " from " });
            if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
            {
                string str3 = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.Compiled );
                str2 = str2 + str3 + ") and (";
            }
            else
            {
                str2 = str2 + SqlTablesAndWhere + " where (";
            }
            object obj2 = str2;
            return (string.Concat(new object[] { obj2, IndexField, " not in (select top ", (PageIndex - 1) * PageSize, " ", IndexField, " from ", SqlTablesAndWhere, " ", OrderFields }) + ")) " + OrderFields);
        }

        public static bool IsExistTable(string TableName)
        {
            bool flag = false;
            DataTable table = ExecuteTable("select * from dbo.sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1", null);
            if ((table != null) && (table.Rows.Count > 0))
            {
                flag = true;
            }
            return flag;
        }

        public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }

        public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value)
        {
            SqlParameter parameter;
            if (Size > 0)
            {
                parameter = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                parameter = new SqlParameter(ParamName, DbType);
            }
            parameter.Direction = Direction;
            if ((Direction != ParameterDirection.Output) || (Value != null))
            {
                parameter.Value = Value;
            }
            return parameter;
        }

        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if ((commandText == null) || (commandText.Length == 0))
            {
                throw new ArgumentNullException("commandText");
            }
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                if (transaction.Connection == null)
                {
                    throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                }
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        private static string QueryUrl()
        {
            Regex regex = new Regex(@"^&PageNo=\d+", RegexOptions.Compiled);
            string input = HttpContext.Current.Request.Url.Query.Replace("?", "&");
            return regex.Replace(input, string.Empty);
        }
    }


}