none
请教一个存储过程 RRS feed

  • 问题

  • 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)
    2010年5月29日 6:51

答案

  • 这个只是一个通用的分页存储过程。

    效率当然没有你单独为你的应用写的好。不过一般的分页,对效率要求不是很高,可以使用。建议你自己也测试一下。

    你可以通过 增加一个output 参数来返回总记录个数。

    c#调用存储过程很简单,你可以在bol或者google搜索到,这里不多说。

     


    family as water
    2010年5月30日 6:24

全部回复

  • 补充: 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 补充
    2010年5月29日 6:55
  • 大侠捏! 救救偶撒!

    2010年5月29日 9:28
  • 这个只是一个通用的分页存储过程。

    效率当然没有你单独为你的应用写的好。不过一般的分页,对效率要求不是很高,可以使用。建议你自己也测试一下。

    你可以通过 增加一个output 参数来返回总记录个数。

    c#调用存储过程很简单,你可以在bol或者google搜索到,这里不多说。

     


    family as water
    2010年5月30日 6:24