C#自己封裝的SQL幫助類

這裡用的Dapper
using static Dapper.SqlMapper;
using Dapper;

        /// <summary>
        /// 分页-可以多表(推荐参数查询)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql">必须是个表名或视图(可含条件)，视图比如 (select * from table) tt</param>
        /// <param name="orderby">比如 id asc</param>
        /// <param name="parames">参数，比如where age>=@Age，new {Category=1, SubCategory=2}</param>
        /// <returns></returns>
        public static Pager<T> PageMulite<T>(int pageIndex, int pageSize, string sql, string orderby, object parames)
        {            
            bool CheckNullOrEmpty = string.IsNullOrEmpty(orderby) || string.IsNullOrWhiteSpace(orderby);
            CheckHelper.isTrue(CheckNullOrEmpty, "orderby不能为空");

            if (pageSize == 0)
                pageSize = 10;
            if (pageIndex == 0)
                pageIndex = 1;
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;

            StringBuilder sqlText = new StringBuilder();
            sqlText.AppendFormat(" select * from (SELECT ROW_NUMBER() OVER(ORDER BY {0} ) as  RID,* from ({1}) tt", orderby, sql);
            sqlText.Append(") as tempTable WHERE RID BETWEEN " + start + " AND " + end);
            //查询总条数
            string sqlCount = string.Format("select count(1) from ({0}) tt", sql);

            using (IDbConnection conn = Db.GetDbConnectionRead())
            {
                var list = SqlMapper.Query<T>(conn, sqlText.ToString(), parames);

                var result = new Pager<T>();
                result.PageIndex = pageIndex;
                result.PageSize = pageSize;
                result.TotalCount = SqlMapper.QuerySingle<int>(conn, sqlCount, parames); 
                result.TotalPages = (result.TotalCount + pageSize - 1) / pageSize; //分页
                result.Items = list.ToList();
                return result;
            }
        }

        /// <summary>
        /// 执行事务,批量（sql和参数装一个object数组中，数组里面下标0为sql语句，1为SqlParameter[]参数）集合，返回受影响行数
        /// </summary>
        /// <param name="cmdList">参数列表，数组里面下标0为sql语句，1为SqlParameter[]参数</param>
        /// <returns>返回执行成功数</returns>
        public static int ExecSqlTransacation(List<object[]> cmdList)
        {
            int count = 0;
            SqlCommand cmd = null;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            try
            {
                conn = new SqlConnection(Db.connString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                tran = conn.BeginTransaction();
                foreach (object[] arr in cmdList)
                {
                    string sql = arr[0] as string; //sql语句                   
                    cmd = new SqlCommand(sql, conn);
                    if (arr[1] != null)
                    {
                        SqlParameter[] parmes = arr[1] as SqlParameter[];//参数
                        cmd.Parameters.AddRange(parmes);
                    }
                    cmd.Transaction = tran;
                    int efCount = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    count += efCount;
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw new DbException(ex.Message, ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return count;
        }