ASP.NET分页存储过程，解决搜索时丢失条件信息
存储过程：
-- ============================================= -- Author: -- Create date: -- Description: 分页 --Update Date: --增加了默认排序规则，根据主键升序（防止在视图查询中乱号） -- ============================================= ALTER PROCEDURE [dbo].[getdatabyPageIndex] @tablename nvarchar(200), @columns nvarchar(500)='*', @condition nvarchar(200)='', @pagesize int=10, @pageindex int=0, @pk nvarchar(30), @total int output, --统计总共的条数 @orderculumn nvarchar(50)=@pk, @isasc nvarchar(10)='desc' AS BEGIN DECLARE @sql nvarchar(2000) SET @sql='select top '+cast(@pagesize AS nvarchar(10))+' '+@columns+' from '+@tablename+' where '+ @pk+' not in (select top '+cast((@pagesize*@pageindex) AS nvarchar(10))+ ' '+@pk+' from '+@tablename +' where 1=1 '+@condition+' order by '+@orderculumn+' '+@isasc+')'+@condition +' order by '+@orderculumn+' '+@isasc PRINT @sql EXEC(@sql) DECLARE @sql2 nvarchar(2000) SET @sql2='SELECT @total1 = count(*) FROM '+ @tablename+' WHERE 1=1 '+ @condition EXEC sp_executesql @sql2,N'@total1 int output',@total output END
.cs：
/// <summary> /// 分页功能 /// </summary> /// <param name="tablename">表名</param> /// <param name="columns">列名</param> /// <param name="condition">条件,不需要带where</param> /// <param name="pagesize">每页显示条数</param> /// <param name="pageindex">页码</param> /// <param name="pk">主键</param> /// <returns>DataTable</returns> public DataTable getdatabyPageIndex(string tablename, string columns, string condition, int pagesize, int pageindex, string pk,out int totalcount,string ordercolumn,string isasc) { string order = ""; if (ordercolumn == null) { order = pk; } string asc = ""; if (isasc == null) { isasc = "desc"; } SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@tablename",tablename), new SqlParameter("@columns",columns), new SqlParameter("@condition",condition), new SqlParameter("@pagesize",pagesize), new SqlParameter("@pageindex",pageindex), new SqlParameter("@pk",pk), new SqlParameter("@total",SqlDbType.Int), new SqlParameter("@orderculumn",ordercolumn), new SqlParameter("@isasc",isasc) }; pars[6].Direction = ParameterDirection.Output; DataTable dt= db.ExcuteSelectReturnDataTable("sp_getdatabyPageIndex", CommandType.StoredProcedure, pars); totalcount= int.Parse(pars[6].Value.ToString()); return dt; } /// <summary> /// 执行一个Select语句或者相应的存储过程实现返回数据集合DataSet /// </summary> /// <param name="SelectStr">执行一个Select语句或者相应的存储过程</param> /// <param name="type">指定命令类型</param> /// <param name="pars">相应参数集合</param> /// <returns>DataSet</returns> public DataSet ExcuteSelectReturnDataSet(string SelectStr, CommandType type, SqlParameter[] pars) { DataSet ds = new DataSet(); SqlConnection conn = new SqlConnection(ConnString); SqlDataAdapter sda = new SqlDataAdapter(SelectStr, conn); if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { sda.SelectCommand.Parameters.Add(p); } } sda.SelectCommand.CommandType = type; sda.Fill(ds); return ds; }