MESProductionWeb/Ant.Service.Utility/MakePageSql.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ant.Service.Utility
{
    public class MakePageSql
    {
        public static StringBuilder StrShowSql = new StringBuilder(); //组装外显的SQL语句

        /// <summary>
        /// 拼接分页查询SQL
        /// </summary>
        /// <param name="orderByName"></param>
        /// <param name="columnName"></param>
        /// <param name="joinStr"></param>
        /// <param name="strWhere"></param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public static string GetPageSql(string orderByName, string columnName, string joinStr, string strWhere, int page, int pagesize)
        {

            StrShowSql = new StringBuilder();
            StrShowSql.AppendFormat("SELECT * FROM (SELECT ROW_NUMBER() OVER (order by {0} ) AS rowNumber, {1} ", orderByName, (columnName.IsEmpty()) ? "*" : columnName);
            StrShowSql.AppendFormat(" from {0} ", joinStr);
            if (!strWhere.IsEmpty())
                StrShowSql.AppendFormat(" where 1=1 and {0}", strWhere);
            StrShowSql.AppendFormat(") AS sp WHERE rowNumber BETWEEN {0}  and {1} ", (((page - 1) * pagesize) + 1), (page * pagesize));
            return StrShowSql.ToString();

        }
        /// <summary>
        /// 获取分页统计记录的SQL语句
        /// </summary>
        /// <param name="joinStr"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static string GetPageCount(string joinStr, string strWhere)
        {
            StrShowSql = new StringBuilder();
            StrShowSql.AppendFormat(" SELECT count(1) from {0} ", joinStr);
            if (!strWhere.IsEmpty())
                StrShowSql.AppendFormat(" where 1=1 and {0}", strWhere);
            return StrShowSql.ToString();
        }

    }
}