存储过程 + AspNetPager 实现分页
/////////////存储过程////////////////////////////////////////////////////////////////////////////////////////////
/****** 对象: StoredProcedure [dbo].[sp_AllQuery] 脚本日期: 10/26/2009 11:27:26 ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- [sp_AllQuery] '','','','','','',1,10,0
ALTER procedure [dbo].[sp_AllQuery]
( @tableName nvarchar(500), @keyField nvarchar(100), @keyField_NO nvarchar(100), @queryField nvarchar(1000), @orderField nvarchar(200), @strWhere nvarchar(500), @startIndex int, @endIndex int, @docount bit ) as set nocount on if(@docount=1) begin exec('select count(' + @keyField + ') from ' + @tableName + ' where '+ @strWhere +'') end else begin --declare @indextable table(id int identity(1,1),nid int) create table #indextable (id int identity(1,1),nid int) set rowcount @endIndex exec('insert into #indextable(nid) select ' + @keyField + ' from ' + @tableName + ' where '+ @strWhere +' order by ' + @orderField +' ') exec('select t.id as noiid,a.* from (select top 10000000 '+@queryField+' from '+@tableName+' where '+@strWhere+') a,#indextable t where a.' + @keyField_NO +'=t.nid and t.id between ' + @startIndex +' and ' + @endIndex + ' order by t.id ') drop table #indextable end ///////////////////实现代码///////////////////////////////////////////////////////////////////////////////////////
ArrayList arr = new ArrayList();
if (tableName == "") { tableName = " "; //表名 或 多表连接 table1 a left join table2 b on a.id=b.id } if (keyField == "")
{ keyField = " "; //a 表的主键 a.id } if (keyField_NO == "") { keyField_NO = " ";//a 表的主键 ,但写的时候去掉[a.] ,直接写 id } if (queryField == "") { queryField = " "; //查询的字段 a.id,b.id,a...... } if (orderField == "") { orderField = " "; //排序字段 a.addtime desc } if (strWhere == "")
{ strWhere = " "; //判断条件 1=1 and a.isok=1 } arr.Insert(0, tableName); arr.Insert(1, keyField); arr.Insert(2, keyField_NO); arr.Insert(3, queryField); arr.Insert(4, orderField); arr.Insert(5, strWhere); ViewState["QueryStr"] = arr;
if (ShowPageCount)
{ AspNetPager1.CurrentPageIndex = 1; } using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DataBaseString"].ConnectionString))
{ SqlCommand myCmd = new SqlCommand("sp_AllQuery", connection); myCmd.CommandType = CommandType.StoredProcedure; SqlParameter spParamenter;
spParamenter = myCmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 500); myCmd.Parameters["@tableName"].Value = tableName; spParamenter = myCmd.Parameters.Add("@keyField", SqlDbType.NVarChar, 100);
myCmd.Parameters["@keyField"].Value = keyField; spParamenter = myCmd.Parameters.Add("@keyField_NO", SqlDbType.NVarChar, 100);
myCmd.Parameters["@keyField_NO"].Value = keyField_NO; spParamenter = myCmd.Parameters.Add("@queryField", SqlDbType.NVarChar, 1000);
myCmd.Parameters["@queryField"].Value = queryField; spParamenter = myCmd.Parameters.Add("@orderField", SqlDbType.NVarChar, 200);
myCmd.Parameters["@orderField"].Value = orderField; spParamenter = myCmd.Parameters.Add("@strWhere", SqlDbType.NVarChar, 500);
myCmd.Parameters["@strWhere"].Value = strWhere; spParamenter = myCmd.Parameters.Add("@startIndex", SqlDbType.Int);
myCmd.Parameters["@startIndex"].Value = Convert.ToString((this.AspNetPager1.CurrentPageIndex - 1) * Convert.ToInt32(Cls_System.getWebListCount()) + 1); spParamenter = myCmd.Parameters.Add("@endIndex", SqlDbType.Int); myCmd.Parameters["@endIndex"].Value = Convert.ToString(this.AspNetPager1.CurrentPageIndex * Convert.ToInt32(Cls_System.getWebListCount())); spParamenter = myCmd.Parameters.Add("@docount", SqlDbType.Int); myCmd.Parameters["@docount"].Value = 1; try
{ connection.Open(); if (ShowPageCount)
{ int totalOrders = (int)myCmd.ExecuteScalar(); //得到总记录数 AspNetPager1.RecordCount = totalOrders; AspNetPager1.CurrentPageIndex = 1; AspNetPager1.PageSize = Convert.ToInt32(Cls_System.getWebListCount());//每页数量 } myCmd.Parameters["@docount"].Value = 0; SqlDataReader dr = myCmd.ExecuteReader();
this.rep_JobList.DataSource = dr; this.rep_JobList.DataBind(); dr.Close(); } catch { Page.ClientScript.RegisterStartupScript(this.Page.GetType(), "error1", "<script>alert('网络异常,请稍候刷新重试1!');</script>"); return; } finally { connection.Close(); } } ////////////////////////////////////////////////////////////////////////////////////////////////
附件为 aspnetpager.dll 本文出自 51CTO.COM技术博客 |
附件下载:
aspnetpager.dll
aspnetpager.dll