百万级分页

/// <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"); }