[Asp.Net]百万级分页

最新推荐文章于 2021-01-28 12:48:03 发布

厦门德仔

最新推荐文章于 2021-01-28 12:48:03 发布

阅读量1.8k

点赞数

分类专栏： # WebForm 文章标签：分页

本文链接：https://blog.csdn.net/david_520042/article/details/7356681

版权

WebForm 专栏收录该内容

77 篇文章 0 订阅

订阅专栏

        /// <param name="fieldlist"></param>字段列表，如id,title,content
        /// <param name="condition"></param>条件，如id<100
        /// <param name="pkey"></param>主键，自增
        /// <param name="tablename"></param>表的名字，如News
        /// <param name="sort"></param>排序，0表示降序，1表示升序
        /// <param name="pagesize"></param>每页大小
        /// <param name="cpage"></param>当前页码
        public DataSet GetCurrentDataSet(string fieldlist, string condition, string pkey, string tablename, int sort, int pagesize, int cpage)//得到当前页记录
        {
            DBCon OAcon = new DBCon();
            SqlConnection conn = new SqlConnection(OAcon.getOAcon());
            SqlDataAdapter cmd = new SqlDataAdapter(GetSql(fieldlist, condition, pkey, tablename, sort, pagesize, cpage), conn);
            DataSet ds = new DataSet();
            cmd.Fill(ds);
            return ds;
        }
        static string GetSql(string fieldlist, string condition, string pkey, string tablename, int sort, int pagesize, int cpage)
        {
            string sql = "";
            if (sort == 0)
            {
                if (condition != "")
                {
                    sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + condition + " and " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " where " + condition + " and order by " + pkey + " desc) order by " + pkey + " desc";
                }
                else
                {
                    sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " order by " + pkey + " desc) order by " + pkey + " desc";
                }
            }
            else
            {
                if (condition != "")
                {
                    sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + condition + " and " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " where " + condition + " and order by " + pkey + " asc) order by " + pkey + " asc";
                }
                else
                {
                    sql = "select top " + pagesize.ToString() + " " + fieldlist + " from " + tablename + " where " + pkey + " not in(select top " + pagesize * (cpage - 1) + " " + pkey + " from " + tablename + " order by " + pkey + " asc) order by " + pkey + " asc";
                }
            }
            return sql;
        }
        public int GetCounts(string pkey, string tablename, string condition)//得到总记录数
        {
            string sql;
            if (condition != "")
            {
                sql = "select count(" + pkey + ") from " + tablename + " where " + condition;
            }
            else
            {
                sql = "select count(" + pkey + ") from " + tablename;
            }
            DBCon OAcon = new DBCon();
            SqlConnection conn = new SqlConnection(OAcon.getOAcon());
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            dr.Read();
            string str = dr[0].ToString();
            dr.Close();
            return Convert.ToInt32(str);
        }
        public DataSet GetCurrentDataSet2(string tbName, string tbFields, string OrderField, int PageSize, int PageIndex, bool OrderType)
        {
            SqlParameter[] parameters = new SqlParameter[8];
            parameters[0] = new  SqlParameter("@tbName", SqlDbType.NVarChar,255);
            parameters[1] = new SqlParameter("@tbFields", SqlDbType.NVarChar, 1000);
            parameters[2] =new SqlParameter("@OrderField", SqlDbType.NVarChar,1000 );
            parameters[3] =new SqlParameter("@PageSize", SqlDbType.Int);
            parameters[4] =new SqlParameter("@PageIndex", SqlDbType.Int);
            parameters[5]=new SqlParameter("@OrderType", SqlDbType.Bit);
            parameters[6] =new SqlParameter("@strWhere", SqlDbType.VarChar,1000);
            parameters[7]=new SqlParameter("@Total", SqlDbType.Int);
 
            parameters[0].Value = tbName;
            parameters[1].Value = tbFields;
            parameters[2].Value = OrderField;
            parameters[3].Value = PageSize;
            parameters[4].Value = PageIndex;
            parameters[5].Value = 1;
            parameters[6].Value = "";
            parameters[7].Value = 0;

            TX.DataAccess.DABaseAccess db = new TX.DataAccess.DABaseAccess();
             

            return db.RunprocToDataSet("SqlDataPaging", parameters);  //DbHelperSQL.RunProcedure("SqlDataPaging", parameters, "ds");
        }