webform中实现SQL Sever2008数据库数据分页查询

1
分页
   1.1
       数据库中存储过程
           已知当前页 pageIndex 页容量 pageSize
           求总页数 pageCount 和当前页的数据
           //例子：
           create proc usp_Example
           @pageIndex int,---------参数之间用 , 分割最后一个参数后必须没有 ,
           @pageSize int,
           @pageCount int output
           as
               begin
              declare @count int
              select @count=count(*) from <tableName>
              set @pageCount=ceiling(@count*1.0/@pageSize)

              select * from
              (select *,row_number() over(order by <columnName> asc/desc) as 编号 from <tableName>) tempTable
              where 编号 between (@pageIndex -1)*@pageSize+1 and @pageIndex*@pageSize
           and
   --测试
   declare @count int
   exec usp_Example 1,2,@count output
   print @count

   1.2    SQLHelper中
   private static DataTable ExecuteDataTable(string sql,CommandType ct,params SqlParameter[] param)
   {
       using(SqlConnection conn = new SqlConnection(connStr))
       {
           using(SqlCommand cmd= new SqlCommand(sql,conn))
           {
               cmd.Parameters.AddRange(param);
               cmd.CommandType=ct;
               conn.Open();
               try{
                   using(SqlDataAdapter da = new SqlDataAdapter(cmd))
                   {
                       DataTable dt = new DataTable();
                       da.Fill(dt);
                       return dt;
                   }
               }catch(Exception ex)
               {
               throw ex;
               }
           }
       }
   }
   1.3 DAL 中
   public List<Model> GetModelsByProc(int pageIndex,int pageSize,out int pageCount)
   {
       SqlParameter[] param={
           new SqlParameter("@pageIndex",SqlDbType.Int){Value=pageIndex},
           new SqlParameter("@pageSize",SqlDbType.Int){Value=PageSize},
           new SqlParametre("@pageCount",SqlDbType.Int){Direction=ParameterDirection.OutPut}
       };
       DataTable dt =SqlHelper.ExecuteDataTable("ProcName",CommandType.StoredProcdure,param);
       pageCount=param[2].Value;
       List<Model> list=new List<Model>();
       foreach(DataRow row in dt.Rows)
       {
           list.Add(row);
       }
       return list;
   }
   1.4 BLL
   public List<Model> GetModelsByProc(int pageIndex,int pageSize,out int pageCount)
   {
       return new DAL().GetModelsByProc(pageIndex,pageSize,out pageCount);
   }
   1.5 UI
   .CS界面
   protected string table;
   private int pageIndex=1;//默认第一页
   private int pageSize=3;//默认每页显示3条数据可从前台获取进行设定
   private int pageCount;//总页数
   protected string pageBar;

   //生成表格
   private string CreateDataTable()
   {
       List<Model> list=new BLL().GetModelsByProc(pageIndex,pageSize,out pageCount);
       StringBuilder sb=new StringBuilder();
       sb.Append("<table>");
       sb.Append("<tr><th>Column1</th><th>Column2</th><th>Column3</th></tr>");
       for(int i=0;i<list.Count;i++)
       {
           Model model=List[i];
           sb.Append("<tr>");
           sb.Append("<td><a href='ModelDetails.aspx?);
           sb.Append("<td><a href='MySelf.aspx?);
           sb.Append("<td><a href='WaterMaker.ashx?);
           sb.Append("</tr>");

       }
       sb.Append("</table>");
       return sb.ToString();
   }
   //创建分页栏
   private string CreatePageBar()
   {
       StringBuilder sb =new StringBuilder();
       sb.Append("<a href='MySelf.aspx?page=1'>首页</a>");
       int num=pageIndex;
       num=num>1?num-1:1;
       sb.Append("  <a href='MySelf.aspx?page="+num+"'>上一页</a>");
       num=num<pageCount?num+1:pageCount;
       sb.Append("  <a href='MySelf.aspx?page="+num+"'>下一页</a>");
       sb.Append("  <a href='MySelf.aspx?page="+pageCount+"></a>");
       sb.Append("  "+pageIndex+"/"+pageCount+"");
       return sb.ToString();
   }
   //页面加载
   protected void Page_Load(object sender,EventArgs e)
   {
       //每次加载都执行
       string page=Request.QueryString["page"];
       if(int.tryParse(page,out pageIndex))
       {
           pageIndex=1;
       }
       table=CreateDataTable();
       pageBar=CreatePageBar();

   }
   1.6 .aspx 前台页面
   <%=table%>
   <%-pageBar%>

webform中实现SQL Sever2008数据库数据分页查询

相关推荐