以SQL Server提供的offset ..rows fetch next ..rows only为例
e.g.
表名：Tab1----------------------------------IDName1tblAttributeGroupDetail2tblAttributeGroup3tblAttribute.......50tblBRItemTypeAppliesTo51tblBRItemProperties52tblBRItem53tblBRBusinessRule54Test
--创建分页存储过程rTabByCondition
USE [ExampleDB]GOif OBJECT_ID('rTabByCondition','P') is not null drop procedure rTabByConditionGOcreate procedure [dbo].[rTabByCondition](@PageCount int=1--页数,@PageSize int=10--页显示记录数,@Rowcount int=0 output--总记录数)asset nocount on;declare @Rows int;select * from dbo.Tab1 order by ID offset (@PageCount-1)*@PageSize rows fetch next @PageSize rows onlyset @Rows=@@ROWCOUNTselect @Rowcount=count(*) from dbo.Tab1;return @Rowsgodeclare @i int,@j intexec @i=[rTabByCondition] @PageCount=6,@PageSize=10,@Rowcount=@j outputselect @i as "@Rowcount",@j as "Return_Value"go
显示结果：
--打开Visual Studio—创建项目—选择【控制台应用程序】
#region Directivesusing System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data;using System.Data.SqlClient;#endregionnamespace SQLStoredProcedure2{class Program{static void Main(string[] args){SqlConnection thisConnection = new SqlConnection(@"Server=(Local)\SQL16;Integrated Security=True;Database=ExampleDB");thisConnection.Open();SqlCommand thisCommend = thisConnection.CreateCommand();mandType = CommandType.StoredProcedure;mandText = "rTabByCondition";thisCommend.Parameters.AddWithValue("@PageCount", "6");//页数thisCommend.Parameters.AddWithValue("@PageSize", "10");//页显示记录数SqlParameter paraOut = thisCommend.Parameters.Add("@Rowcount", SqlDbType.Int);//输出参数定义paraOut.Direction = ParameterDirection.Output;SqlParameter paraRet = thisCommend.Parameters.Add("return_value", SqlDbType.Int);//返回值paraRet.Direction = ParameterDirection.ReturnValue;SqlDataReader thisReader = thisCommend.ExecuteReader();while (thisReader.Read()){Console.WriteLine("ID:{0}\tName:{1}", thisReader[0], thisReader[1]);}thisReader.Close();thisConnection.Close();Console.WriteLine("Rows:{0};\tReturn_Value:{1};", paraOut.Value, paraRet.Value);Console.WriteLine("Program finished,press Enter/Return to continue:");Console.ReadLine();}}}
显示效果：
