存储过程代码: CREATE PROCEDURE [dbo].[proc_PageMgr] -- Add the parameters for the stored procedure here @perpagecount int,--每页显示多少条 @pagenumber int,--第几页 @tablename nvarchar(50),--表名 @selectcolumn nvarchar(200),--eg:ID,Username,XXXX @key nvarchar(50),--eg:ID @wherestr nvarchar(200)='',--eg:Isdeleted=0 AND XXXXXXX @orderbystr nvarchar(200)=''--eg:ORDER BY XXXXX AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @frontcount int--定义当前查找之前有多少条记录 DECLARE @SQL nvarchar(500)--拼SQL语句 SET @frontcount = (@pagenumber-1)*@perpagecount--计算出之前的记录数 SET @SQL = 'SELECT TOP '+CAST(@perpagecount AS nvarchar(50))+' '+@selectcolumn+' FROM '+@tablename +' WHERE '+@key+' NOT IN (SELECT TOP '+CAST(@frontcount AS nvarchar(50))+' '+@key+' FROM '+@tablename+' ' IF @wherestr=''--判断是否有查询条件 BEGIN SET @SQL=@SQL+@orderbystr+') '+@orderbystr END ELSE BEGIN SET @SQL = @SQL+'WHERE '+@wherestr+' '+@orderbystr+') AND '+@wherestr+' '+@orderbystr END EXEC (@SQL) END C#代码: public static DataTable ExecuteSP(string connection,string[] parameters,string[] values)//connection为连接字符串,parameters和values数组分别为参数和对应的值 { DataTable table = new DataTable(); if (parameters.Length!=values.Length)//判断参数数组和值数组是否对应 { System.Web.HttpContext.Current.Response.Write("数组不匹配!"); } else { using (SqlConnection conn = new SqlConnection(connection)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType= CommandType.StoredProcedure;//指定类型 cmd.Connection = conn; cmd.CommandText="proc_PageMgr";//存储过程名 for (int i =0;i<parameters.Length;i++) { cmd.Parameters.Add(new SqlParameter(parameters[i],values[i]));//循环插入参数及对应值 } SqlDataReader reader = cmd.ExecuteReader(); table.Load(reader); reader.Close(); reader.Dispose(); cmd.Dispose(); conn.Close(); } } return table; } }