积极答复者
请教一个存储过程

问题
-
ALTER PROCEDURE Pagination ( @TableName varchar(255), --表名或视图表 @FieldList varchar(1000)='*', --欲选择字段列表 @OrderField varchar(255)='', --排序字段 @PageSize int=10, --页尺寸 @PageIndex int=1, --页号 @TotalRecords bit=0, --返回记录总数, 非0 值则返回 @OrderType bit=0, --排序,1,降序,0,升序 @StrWhere varchar(1500)='' -- 查询条件(注意: 不要加where) ) AS /* 名称:Pagination 作用:按任意字段进行排序分页 作者:菩提树(MARK MA) 时间:2004-12-14 声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明 */ SET NOCOUNT ON declare @sqlstr varchar(6000) set @sqlstr='declare @rcount int;' if @TotalRecords != 0 -----------------------------------------------如果 @TotalRecords 传递过来的不是0,就执行统计总记录数 begin if @StrWhere !='' begin set @sqlstr = 'select count(*) as Total from ' + @TableName + ' where '+@StrWhere set @StrWhere=replace(@StrWhere,'''','''''') ------处理SQL中危险字符,并且将条件处理成易嵌入的形式 set @StrWhere=replace(@StrWhere,'--','') set @StrWhere=replace(@StrWhere,';','') end else begin set @sqlstr = 'select count(*) as Total from ' + @TableName end end else -----------------------------------------------------------分页显示记录 begin if @StrWhere !='' ----------------------------------------当查询条件不为空 begin set @sqlstr=@sqlstr+'set @rcount=(select count(*) from '+@TableName+' where '+@StrWhere+');' set @StrWhere=replace(@StrWhere,'''','''''') set @StrWhere=replace(@StrWhere,'--','') set @StrWhere=replace(@StrWhere,';','') set @sqlstr=@sqlstr+'declare @rnum int;' set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@PageSize as varchar)+'*('+cast(@PageIndex as varchar)+'-1);' set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);' if @OrderType=1 --如果 @OrderType 等于0,就执行降序! begin set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@PageSize as varchar)+' '+@FieldList+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@TableName+' where '+@StrWhere+' order by '+@OrderField+' asc) as b order by '+@OrderField+' desc) as a order by '+@OrderField+' desc '';' end else --如果 @OrderType 不等于0,就执行升序! begin set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@PageSize as varchar)+' '+@FieldList+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@TableName+' where '+@StrWhere+' order by '+@OrderField+' desc) as b order by '+@OrderField+' asc) as a order by '+@OrderField+' asc '';' end set @sqlstr=@sqlstr+'execute(@sqlstr)' end else --------------------------------------------------------------当查询条件为空 begin set @sqlstr=@sqlstr+'set @rcount=(select count(*) from '+@TableName+');' set @sqlstr=@sqlstr+'declare @rnum int;' set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@PageSize as varchar)+'*('+cast(@PageIndex as varchar)+'-1);' set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);' if @OrderType=1 --如果 @OrderType 等于0,就执行降序! begin set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@PageSize as varchar)+' '+@FieldList+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@TableName+' order by '+@OrderField+' asc) as b order by '+@OrderField+' desc) as a order by '+@OrderField+' desc '';' end else --如果 @OrderType 不等于0,就执行升序! begin set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@PageSize as varchar)+' '+@FieldList+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@TableName+' order by '+@OrderField+' desc) as b order by '+@OrderField+' asc) as a order by '+@OrderField+' asc '';' end set @sqlstr=@sqlstr+'execute(@sqlstr)' end end --print @sqlstr execute(@sqlstr)
请问这段存储过程的效率如何!
我想为他添加一个 Return @TotalRecords
还有请为他写个C#调用函数!.我对存储过程实在不明白.
万分感谢!- 已移动 邹俊才 2010年5月30日 3:26 (发件人:ASP.NET 与 AJAX)
答案
-
这个只是一个通用的分页存储过程。
效率当然没有你单独为你的应用写的好。不过一般的分页,对效率要求不是很高,可以使用。建议你自己也测试一下。
你可以通过 增加一个output 参数来返回总记录个数。
c#调用存储过程很简单,你可以在bol或者google搜索到,这里不多说。
family as water- 已标记为答案 Nai-dong Jin - MSFTModerator 2010年6月4日 3:38
全部回复
-
补充: Return @记录计数 可否用上面的存储过程实现类似于 Membership的 "aspnet_Membership_GetAllUsers"
public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords) { if(pageIndex < 0) throw new ArgumentException(SR.GetString(SR.PageIndex_bad), "pageIndex"); if(pageSize < 1) throw new ArgumentException(SR.GetString(SR.PageSize_bad), "pageSize"); long upperBound = (long)pageIndex * pageSize + pageSize - 1; if(upperBound > Int32.MaxValue) throw new ArgumentException(SR.GetString(SR.PageIndex_PageSize_bad), "pageIndex and pageSize"); MembershipUserCollection users = new MembershipUserCollection(); totalRecords = 0; try { SqlConnectionHolder holder = null; try { holder = SqlConnectionHelper.GetConnection(_sqlConnectionString, true); CheckSchemaVersion(holder.Connection); SqlCommand cmd = new SqlCommand("aspnet_Membership_GetAllUsers", holder.Connection); SqlDataReader reader = null; SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int); cmd.CommandTimeout = CommandTimeout; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName)); cmd.Parameters.Add(CreateInputParam("@PageIndex", SqlDbType.Int, pageIndex)); cmd.Parameters.Add(CreateInputParam("@PageSize", SqlDbType.Int, pageSize)); p.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(p); try { reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); while(reader.Read()) { string username, email, passwordQuestion, comment; bool isApproved; DateTime dtCreate, dtLastLogin, dtLastActivity, dtLastPassChange; Guid userId; bool isLockedOut; DateTime dtLastLockoutDate; username = GetNullableString(reader, 0); email = GetNullableString(reader, 1); passwordQuestion = GetNullableString(reader, 2); comment = GetNullableString(reader, 3); isApproved = reader.GetBoolean(4); dtCreate = reader.GetDateTime(5).ToLocalTime(); dtLastLogin = reader.GetDateTime(6).ToLocalTime(); dtLastActivity = reader.GetDateTime(7).ToLocalTime(); dtLastPassChange = reader.GetDateTime(8).ToLocalTime(); userId = reader.GetGuid(9); isLockedOut = reader.GetBoolean(10); dtLastLockoutDate = reader.GetDateTime(11).ToLocalTime(); users.Add(new MembershipUser(this.Name, username, userId, email, passwordQuestion, comment, isApproved, isLockedOut, dtCreate, dtLastLogin, dtLastActivity, dtLastPassChange, dtLastLockoutDate)); } } finally { if(reader != null) reader.Close(); if(p.Value != null && p.Value is int) totalRecords = (int)p.Value; } } finally { if(holder != null) { holder.Close(); holder = null; } } } catch { throw; } return users; }
- 已编辑 Money.Live 2010年5月29日 6:56 补充
-
这个只是一个通用的分页存储过程。
效率当然没有你单独为你的应用写的好。不过一般的分页,对效率要求不是很高,可以使用。建议你自己也测试一下。
你可以通过 增加一个output 参数来返回总记录个数。
c#调用存储过程很简单,你可以在bol或者google搜索到,这里不多说。
family as water- 已标记为答案 Nai-dong Jin - MSFTModerator 2010年6月4日 3:38