你的位置:首页 > ASP.net教程

[ASP.net教程]带搜索条件分页的存储过程

这个存储过程主要是有两点:

1、动态拼接查询条件的分页

2、动态获取当前搜索条件的总行数

注意:如果用这种拼接查询条件传进来,为了更加地让系统安全,需要先过滤表单的特殊字符串

ALTER PROCEDURE [dbo].[OrdersListByCondition]   @pagesize int, @pageindex int, @condition nvarchar(1000), @totalcount int outputASBEGINdeclare @sql nvarchar(3000)declare @countSql nvarchar(3000)if @condition=''begin
--需要什么条件自己将*替换掉set @sql=' select * from (select top '+cast(@pagesize as nvarchar(30)) +' * from E_Orders where OrderID not in (select top ('+cast(@pagesize as nvarchar(30)) +' *('+cast(@pageindex as nvarchar(30)) +'-1)) OrderID from E_Orders order by OrderID desc) order by OrderID desc) o left join E_OrdersCommodity oc on o.OrderID=oc.OrderID order by OrderID desc'set @countSql='select @a=count(1) from E_Orders'endelsebegin set @sql=' select * from (select top '+cast(@pagesize as nvarchar(30)) +' * from E_Orders where OrderID not in (select top ('+cast(@pagesize as nvarchar(30)) +' *('+cast(@pageindex as nvarchar(30)) +'-1)) OrderID from E_Orders where '+ @condition+' order by OrderID desc) and '+ @condition+' order by OrderID desc) o left join E_OrdersCommodity oc on o.OrderID=oc.OrderID order by OrderID desc'set @countSql='select @a=count(1) from E_Orders where '+ @condition+''endexec(@sql)execute sp_executesql @countSql,N'@a INT output',@totalcount outputEND

后台拼接参数需要注意格式 :字段名='值',条件如 OrderID='20150721578942'   and Contacts='小四'

 /// <summary>  /// 拼接SQL查询条件  /// </summary>  /// <param name="strOrderStatus"></param>  /// <param name="strOrderID"></param>  /// <param name="strMallName"></param>  /// <param name="strContacts"></param>  /// <returns></returns>  private string[] GetConditionArray(string strOrderStatus, string strOrderID, string strMallName, string strContacts)  {    List<string> list = new List<string>();    if (strOrderStatus != "")    {      list.Add("OrderStatus='" + strOrderStatus + "'");    }if (strOrderID != "")    {      list.Add("OrderID='" + strOrderID + "'");    }    if (strMallName != "")    {      list.Add("MallName='" + strMallName + "'");    }    if (strContacts != "")    {      list.Add("Contacts='" + strContacts + "'");    }    return list.ToArray();  }