sql分页

1.  sql分页

        public virtual DataSet GetPagerListBySql(string tbname, string FieldShow, string SearchWhere, string FieldOrder, int PageSize, int PageCurrent, out int PageCount, out int RecordCount)
        {
            PageCurrent = PageCurrent < 1 ? 1 : PageCurrent;
            PageSize = PageSize < 1 ? 10 : PageSize;
            RecordCount = 0;
            PageCount = 0;
            FieldShow = string.IsNullOrWhiteSpace(FieldShow) ? "*" : FieldShow;
            SearchWhere = string.IsNullOrWhiteSpace(SearchWhere) ? "" : $"where {SearchWhere}";
            string sqlTotale = $@" select count(*) from {tbname} {SearchWhere};";
            string sql = $@" select RowNumId,{FieldShow} from 
                              (select * , ROW_NUMBER() over(order by {FieldOrder}) as RowNumId from {tbname} {SearchWhere} ) as ttt
                              where  RowNumId between {PageSize*(PageCurrent-1)+1} and {PageSize*PageCurrent};
                             {sqlTotale}";           
            QiDianEntities db = new QiDianEntities();
            DataSet ds = new DataSet();        
            System.Data.SqlClient.SqlConnection con = GetSqlConntion(db.Database.Connection.ConnectionString);          
            try
            {          
                System.Data.SqlClient.SqlDataAdapter dba = new System.Data.SqlClient.SqlDataAdapter(sql, con);       
                dba.Fill(ds, "t1");              
                if (ds != null && ds.Tables.Count > 0)
                {
                  var  dt = ds.Tables[1];
                    RecordCount = Convert.ToInt32(dt.Rows[0][0]);
                    PageCount = Convert.ToInt32(Math.Ceiling((double)RecordCount / PageSize));
                }
            }
            finally
            {
                CloseSqlConntion(con);
            }
            return ds;
        }