分求分页存储过程及C#调用示例 最好可以详细一点的,分页存储过程可以和gridview绑定吗? 谢谢
解决方案 »
- winform 中 使用 TreeView 实现 权限树
- System.NullReferenceException: 未将对象引用设置到对象的实例
- 项目中的FreeTextBox在D盘 如何通过FreeTextBox将图片上传到E盘,在线等高手帮忙~~~
- 怎么把Xml中的某个节点的内容放入一个数组中?
- 新手,问个简单的问题!在线等!
- 我的一个网页里,有一个搜索功能,但回车,默认不是去搜索,
- datagrid的某一column做成超链接要实现一定操作,如何触发后台事件
- 请问这个配置错误如何改?
- asp.net中有数据结构一说吗
- try catch怎么用!
- DataGrid的问题.
- 用的aspnetpager分页存储过程,20000行,速度慢?而且无法 Sort?
CREATE PROCEDURE dbo.GetCustomerDataList
(
@pageCount int,
@lineCount int
)
AS
SET NOCOUNT ON DECLARE @initCount int SET @initCount = @lineCount * (@pageCount - 1) if(@pageCount = 1 and @initCount = 0)
set @initCount = 1 SET ROWCOUNT @initCount SELECT
CustomerID
INTO
#WORK
FROM
Customers
ORDER BY
CustomerID SET ROWCOUNT @lineCount
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM
Customers
WHERE
(@pageCount <> 1
AND NOT EXISTS(
SELECT * FROM #WORK
WHERE
#WORK.CustomerID = Customers.CustomerID
)
) OR
(@pageCount = 1 AND 1 = 1) ORDER BY
CustomerID DROP TABLE #WORK
RETURN
GO
ALTER PROCEDURE dbo.GetData
@Size INT = 50, --每页记录数
@Index INT = 1, --页索引
@SQL NVARCHAR(4000), --任何查询语句
@RowCount int = 0 OUTPUT -- 查询结果的总记录数
AS
DECLARE @i INT
SET NOCOUNT ON
SET @Index=(@Index-1)*@Size+1
EXEC SP_CURSOROPEN @i OUTPUT,@SQL ,@scrollopt=1,@ccopt=1, @rowcount= @Count OUTPUT
EXEC SP_CURSORFETCH @i,16,@Index,@Size
EXEC SP_CURSORCLOSE @i
SET NOCOUNT OFF
@TBName nvarchar(2000)='',--表名,如 pinyin
@PageSizeint=10,--每页的记录数,默认为 10
@CurPageint=1,--表示当前页 1
@KeyFieldnvarchar(100)='ID',--关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDescnvarchar(4)='ASC',--关键字的升、降序,默认为升序 ASC , 降序为 DESC
@Fieldsnvarchar(2000)='*',--所选择的列名,默认为全选
@Conditionnvarchar(2000)='',--where 条件,默认为空
@Ordernvarchar(200)=''--排序条件,默认为空
)as
if @TBName = ''
begin
raiserror('请指定表名!',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror('当前页数和每页的记录数都必须大于零!',11,1)
return
end
if @KeyAscDesc = 'DESC'
set @KeyAscDesc = '<'
else
set @KeyAscDesc = '>'
if @Condition <> ''
set @Condition = ' where ' + @Condition
declare @SQL nvarchar(2000)
set @SQL = ''
if @CurPage = 1
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + ' ' + @Order
else
begin
declare @iTopNum int
set @iTopNum = @PageSize * (@CurPage - 1)
set @SQL = @SQL + 'declare @sLastValue nvarchar(100)' + char(13)
set @SQL = @SQL + 'SELECT Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue=' + @KeyField + ' FROM ' + @TBName + @Condition + ' ' + @Order + char(13)
declare @Condition2 nvarchar(200)
if @Condition = ''
set @Condition2 = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '
else
set @Condition2 = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + @Condition2 + @Order
end
EXECUTE sp_executesql @SQL
go
第二种:
CREATE PROCEDURE sp_PublicTurePage
@TBName nvarchar(100)='',--表名,如 pinyin
@PageSizeint=10,--每页的记录数,默认为 10
@CurPageint=1,--表示当前页 1
@KeyFieldnvarchar(100)='ID',--关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDescnvarchar(4)='ASC',--关键字的升、降序,默认为升序 ASC , 降序为 DESC
@Fieldsnvarchar(500)='*',--所选择的列名,默认为全选
@Conditionnvarchar(200)='',--where 条件,默认为空
@Ordernvarchar(200)=''--排序条件,默认为空
as
if @TBName = ''
begin
raiserror('请指定表名!',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror('当前页数和每页的记录数都必须大于零!',11,1)
return
end
if @Condition <> ''
set @Condition = ' and ' + @Condition
DECLARE @Str nVARCHAR(4000)
set @Str='SELECT TOP '+convert(varchar(12),@PageSize)+' ' +@Fields+ ' from ' +@TBName+ ' where ' +@KeyField+ '
not in (select top ' + convert(varchar(12),(@PageSize*(@CurPage-1))) + ' ' +@KeyField+ ' from ' +@TBName+ '
order by ' +@KeyField+ ') ' + @Condition + 'order by ' + @KeyField + ''
EXEC sp_ExecuteSql @Str
GO
----------------------
为正确解释
偶用过可以的
给你个简单有效的分页存储过程吧:
ALTER PROCEDURE dbo.GetData
@Size INT = 50, --每页记录数
@Index INT = 1, --页索引
@SQL NVARCHAR(4000), --任何查询语句
@RowCount int = 0 OUTPUT -- 查询结果的总记录数
AS
DECLARE @i INT
SET NOCOUNT ON
SET @Index=(@Index-1)*@Size+1
EXEC SP_CURSOROPEN @i OUTPUT,@SQL ,@scrollopt=1,@ccopt=1, @rowcount= @Count OUTPUT
EXEC SP_CURSORFETCH @i,16,@Index,@Size
EXEC SP_CURSORCLOSE @i
SET NOCOUNT OFF
public static DataTable RunExecuteQuery(string strSQL, string order, int iCurrentPage, int iPageSize, out int count)
{
if (strSQL != "")
{
IDatabase conn = null;
DataTable dtTmp = null;
try
{
conn = DatabaseFactory.CreateObject();
//要查询的表、视图或查询语句
System.Data.SqlClient.SqlParameter par1 = new System.Data.SqlClient.SqlParameter("@Source", strSQL);
par1.Direction = ParameterDirection.Input;
//order by
System.Data.SqlClient.SqlParameter par2 = new System.Data.SqlClient.SqlParameter("@Order", order);
par2.Direction = ParameterDirection.Input;
//返回列表
System.Data.SqlClient.SqlParameter par3 = new System.Data.SqlClient.SqlParameter("@List", "*");
par3.Direction = ParameterDirection.Input;
//设置返回页码
System.Data.SqlClient.SqlParameter par4 = new System.Data.SqlClient.SqlParameter("@Index", iCurrentPage);
par4.Direction = ParameterDirection.Input;
//每页记录数
System.Data.SqlClient.SqlParameter par5 = new System.Data.SqlClient.SqlParameter("@Size", iPageSize);
par5.Direction = ParameterDirection.Input;
//查询到的记录总数
System.Data.SqlClient.SqlParameter par6 = new System.Data.SqlClient.SqlParameter("@Count", SqlDbType.Int);
par6.Direction = ParameterDirection.Output; conn.Parameters.Add(par1);
conn.Parameters.Add(par2);
conn.Parameters.Add(par3);
conn.Parameters.Add(par4);
conn.Parameters.Add(par5);
conn.Parameters.Add(par6); dtTmp = conn.ExecuteQueryStoreProcedure("pager");
//引用方式
count = int.Parse(par6.Value.ToString());
if (count > 0)
{
return dtTmp;
}
else
{
return null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
count = 0;
return null;
}
finally
{
if (conn != null)
{
conn.Close();
}
conn = null;
dtTmp = null;
}
}
else
{
count = 0;
return null;
}
}SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Proc [dbo].[pager]
@Source nvarchar(4000), --表名、视图名、查询语句
@Size int=10, --每页的大小(行数)
@Index int=1, --要显示的页
@List nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Order nvarchar (1000)='', --排序字段列表
@Count int = null OUTPUT -- 输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@Source)
,@List=case isnull(@List,'') when '' then ' *' else ' '+@List end
,@Order=case isnull(@Order,'') when '' then '' else ' order by '+@Order end
,@Source=case when @Obj_ID is not null then ' '+@Source else ' ('+@Source+') a' end--如果显示第一页,可以直接用top来完成
if @Index=1
begin
if @Count is null
begin
declare @lbtop1 nvarchar(1000)
set @lbtop1 = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbtop1, N'@Count int out', @Count out
end
select @Id1=cast(@Size as varchar(20))
exec('select top '+@Id1+@List+' from '+@Source+@Order)
return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@Size as varchar(20))
,@Id2=cast((@Index-1)*@Size as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理 select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
if @Count is null
begin
declare @lbuseidentity nvarchar(1000)
set @lbuseidentity = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbuseidentity, N'@Count int out', @Count out
end
exec('select top '+@Id1+@List+' from '+@Source
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@Source+@Order
+')'+@Order
)
return/*--表中有复合主键的处理方法--*/
lbusepk:
if @Count is null
begin
declare @lbusepk nvarchar(1000)
set @lbusepk = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbusepk, N'@Count int out', @Count out
end exec('select '+@List+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@Source+@Order+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@Source+@Order+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@Size*(@Index-1) as varchar(20))
,@Id2=cast(@Size*@Index-1 as varchar(20))declare @lbusetemp nvarchar(4000)
set @lbusetemp = 'select '+@FdName+'=identity(int,0,1),'+@List+'
into #tb from'+@Source+@Order
+case when @Count is null then ' set @Count = @@rowcount ' else '' end + '
select '+@List+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
exec sp_executesql @lbusetemp, N'@Count int out', @Count out
CREATE PROCEDURE docount
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@strWhere varchar(5000) = '', -- 查询条件 (注意: 加 where)
@RecordCount int output
AS
declare @strSQL nvarchar(4000)
set @strSQL = 'select @RecordCount=Count('+@fldName+') from [' + @tblName + '] '+@strWhere
exec sp_executesql @strSQL,N'@RecordCount int output',@RecordCount output
GOCREATE procedure select_pagesize
(
@select_list varchar(2000),--不需要select
@table_name varchar(200),
@where varchar(2000),--不需要where
@primary_key varchar(200),--当是表联合时,加表名前缀.
@order_by varchar(400),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名:通用存储过程分页
使用示例:
单表sql调用:exec select_pagesize 'login_id,login_name','tb_login',' login_name like ''%%''','login_id',' order by login_dt desc',20,10
多表sql调用:exec select_pagesize 'a.login_id,a.login_name,b.pro_name','tb_login a,tb_code_province b',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id',' order by a.login_dt desc',20,10
备注:外部程序调用不需要转义单引号
原型结构:select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by*/declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(2000),@newin_where varchar(2000)
if @where=''--重新为梳理,此过程时性能的考虑,因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@where
select @newin_where=' where '+@where
endif @do_count=1
select @sql_str='select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where=''
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
else
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO
RangeValidator1.MaximumValue = LabelPage;
RangeValidator1.MinimumValue = "1";
binddata("Files a","a.Id"," a.Type = "+Compositor,"a.id,a.FileName,a.Up_Date,a.FileAuthor,a.AuthorGroup,a.Type","order by a.Up_Date desc,a.id desc");
private void docount(string talName,string fldName,string strWhere,int Page_Size)
{
SqlCommand Command=new SqlCommand();
Command.Connection=Con;
Command.CommandText="docount";
Command.CommandType=CommandType.StoredProcedure;
Command.Parameters.Add("@tblName",talName);
Command.Parameters.Add("@strWhere",strWhere);//条件要加where
Command.Parameters.Add("@fldName",fldName);
SqlParameter workParm;
workParm = Command.Parameters.Add("@RecordCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;
Con.Open();
Command.ExecuteScalar();
Con.Close();
Int32 RecordCount = (Int32)Command.Parameters["@RecordCount"].Value;
PageSize = Page_Size;
if(RecordCount%PageSize>0)
intPageCount = (RecordCount/PageSize)+1;
else
intPageCount = RecordCount/PageSize;
if(intPageCount >0)
LabelPage = intPageCount.ToString();
else
LabelPage = "1";
LabelRecord = RecordCount.ToString();
//this.Response.Write(RecordCount.ToString());
if(RecordCount == 0)
{
str_Record = "暂无信息 !!!"; }
}
private void binddata(string tblName,string fldName,string strWhere,string allfldName,string Str_Order)//条件要不加where
{
if (Request["CurrentPage"]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request["CurrentPage"]);
}
SqlCommand MyCommand=new SqlCommand();
MyCommand.Connection=Con;
MyCommand.CommandText="select_pagesize";
MyCommand.CommandType=CommandType.StoredProcedure;
MyCommand.Parameters.Add("@table_name",tblName);
MyCommand.Parameters.Add("@primary_key",fldName);
MyCommand.Parameters.Add("@select_list",allfldName);
MyCommand.Parameters.Add("@page_size",PageSize);
MyCommand.Parameters.Add("@page_index",intPageNo);
MyCommand.Parameters.Add("@where",strWhere);//条件要不加where
MyCommand.Parameters.Add("@order_by",Str_Order);
MyCommand.Parameters.Add("@do_count","0");
Con.Open();
Repeater.DataSource = MyCommand.ExecuteReader();
Repeater.DataBind();
Con.Close();
if(LabelRecord != "0")
LabelRow = intPageNo.ToString();
else
LabelRow = "0"; if (intPageNo>1)
{
HLFistPage.NavigateUrl = Request.CurrentExecutionFilePath+"?Compositor="+Compositor+"&CurrentPage=1&Bg_Color="+Bg_Color;
HLPrevPage.NavigateUrl = String.Concat(Request.CurrentExecutionFilePath+"?Compositor="+Compositor+"&Bg_Color="+Bg_Color+"&CurrentPage=","",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = "";
HLPrevPage.NavigateUrl = "";
}
if (intPageNo<intPageCount)
{
HLNextPage.NavigateUrl = String.Concat(Request.CurrentExecutionFilePath+"?Compositor="+Compositor+"&Bg_Color="+Bg_Color+"&CurrentPage=","",intPageNo+1);
HLEndPage.NavigateUrl = String.Concat(Request.CurrentExecutionFilePath+"?Compositor="+Compositor+"&Bg_Color="+Bg_Color+"&CurrentPage=","",intPageCount);
}
else
{
HLNextPage.NavigateUrl = "";
HLEndPage.NavigateUrl = "";
}
}
@sqlstr nvarchar(4000), --查询字符串
@start int, --第几个记录开始
@pagesize int --每页行数
as
declare @rowcount int
set nocount on
declare @P1 int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
exec sp_cursorfetch @P1,16,@start,@pagesize
select @rowcount
http://www.javavsnet.com/bbs/View.aspx?id=503&boardid=16