星空网 > 软件开发 > 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();  }

 




原标题:带搜索条件分页的存储过程

关键词:分页

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

揭秘莱西国际商标注册要多少费用?:https://www.kjdsnews.com/a/1313960.html
揭秘美国商标注册价格行情:真实定价一览无余!:https://www.kjdsnews.com/a/1313961.html
揭秘美国商标发证费用的真相:https://www.kjdsnews.com/a/1313962.html
提示欧盟商标注册:7个必须注意的事项:https://www.kjdsnews.com/a/1313963.html
推荐!境外商标注册查询官网,一键获取新商标信息!:https://www.kjdsnews.com/a/1313964.html
推荐太原市:品牌保护专业机构——商标复审服务名录:https://www.kjdsnews.com/a/1313965.html
活动回顾 | 千亚麦佳携手MoonSees呈现精彩亚马逊运营管理课程:https://www.kjdsnews.com/a/1836386.html
生日景点免费 上海景点生日免费景点:https://www.vstour.cn/a/363173.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流