通用分页 模块

 

1. 创建通用的分页存储过程，sql2000，传入 表名，返回的列，查询条件，排序条件

CREATE procedure sp_Public_List ( @TableName varchar(50), @Cols varchar(1000), @strWhere varchar(8000), @strOrder varchar(100), @startIndex int, @endIndex int, @docount bit) as set nocount on if(@docount=1) exec ('select count(*) from ' + @TableName + ' ' + @strWhere)

else begin exec ('declare @indextable table(id int identity(1,1),nid int) set rowcount ' + @endIndex  + ' insert into @indextable(nid) select AutoID from  ' + @TableName + ' ' + @strWhere + ' ' + @strOrder + ' select '+ @Cols +' from  ' + @TableName + ' O,@indextable t where O.AutoID=t.nid and t.id between ' + @startIndex  + ' and ' + @endIndex + ' order by t.id' )

end set nocount off GO

 

2、c#调用通用分页存储过程的函数

 

        private void ShowGrid(bool ShowPageCount)         {                        if (ShowPageCount)                 this.fishPager1.PageCurrent = 1;

            string strWhere = "";

            if (treeView1.SelectedNode != null  && treeView1.SelectedNode.Tag.ToString()!="" )             {                 strWhere = " where GoodsClassCode like '" + treeView1.SelectedNode.Tag.ToString() + "%'";             }

            SqlParameter[] paras = new SqlParameter[]             {                 new SqlParameter("@TableName","vw_JXC_Goods"),                 new SqlParameter("@Cols","GoodsGuid,GoodsCode,GoodsName,Model,GoodsClassName"),                 new SqlParameter("@strWhere",strWhere),                 new SqlParameter("@strOrder"," order by GoodsCode"),                 new SqlParameter("@startIndex",(this.fishPager1.PageCurrent - 1) * this.fishPager1.nPageSize + 1),                 new SqlParameter("@endIndex",this.fishPager1.PageCurrent * this.fishPager1.nPageSize),                 new SqlParameter("@docount",1)             };

            if (ShowPageCount)             {                 int totalOrders = Convert.ToInt32(SqlHelper.ExecuteScalar(ClsParameter.ConnectionString, CommandType.StoredProcedure, "sp_Public_List", paras));                 this.fishPager1.nRecordCount = totalOrders;                 this.fishPager1.PageCurrent = 1;             }

            paras[6].Value = 0;

            DataTable myDt = SqlHelper.ExecuteDataset(ClsParameter.ConnectionString, CommandType.StoredProcedure, "sp_Public_List", paras).Tables[0];

            this.grid1.AutoRedraw = false;

            this.grid1.Rows = 1;             this.grid1.Rows = myDt.Rows.Count+1;                         for (int r = 0; r <= myDt.Rows.Count - 1; r++)             {                 grid1.Cell(r, 0).Tag = myDt.Rows[r]["GoodsGuid"].ToString();

                for (int c = 1; c <= myDt.Columns.Count - 1; c++)                 {                     grid1.Cell(r, c).Text = myDt.Rows[r][c].ToString();                 }

            }

            this.grid1.AutoRedraw = true;             this.grid1.Refresh();

        }