CREATE PROCEDURE Proc_page_withtopmax
(
@pageIndex int,--Page index
@pageSize int,--display number per page
@pageCount int output,--total pages, output parameters
@totalCount int output--Total number of bars
)
As
Begin
SET NOCOUNT on;
declare @sql nvarchar (1000)
Set @sql = ' select top ' from Tb_testtable where (id> (select Max (ID) from (select Top ' +str ((@pageIndex-1) * @pageSize) + ' ID from tb_testtable ORDER by id] as temp)) Order by ID '
Execute (@sql)
declare @sqlRecordCount nvarchar (1000)--a statement that gets the total number of records
Set @sqlRecordCount =n ' Select @recordCount =count (*) from tb_testtable '
DECLARE @recordCount INT--A variable that holds the total number of record bars
EXEC sp_executesql @sqlRecordCount, N ' @recordCount int output ', @recordCount output
if (@recordCount% @pageSize = 0)--if the total number of records can be divisible by the page size
Set @pageCount = @recordCount/@pageSize-The total number of pages is equal to the total record bar divided by the page size
Else--If the total number of records cannot be divisible by the page size
Set @pageCount = @recordCount/@pageSize + 1--Total pages equals total number of records divided by page size plus 1
Set @totalCount = @recordCount
SET NOCOUNT off;
End
--Execute the stored procedure in the database
declare @pageCount int, @totalCount int
exec proc_page_withtopmax 2,95955, @pageCount output, @totalCount output
Select ' Total pages: ' +str (@pageCount)
Select ' Total number of bars: ' +str (@totalCount)
C # code calls the paging stored procedure with input and output parameters
public static DataSet getrecordbypage (int pageSize, int. pageIndex, out int pagecount, out int totalcount)
{
DataSet ds = new DataSet ();
Try
{
using (SqlConnection conn = new SqlConnection (@ "server=;d atabase=data_test;uid=; pwd=;"))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. Parameters.Add (New SqlParameter ("@pageSize", SqlDbType.Int));
Cmd. Parameters.Add (New SqlParameter ("@pageIndex", SqlDbType.Int));
SqlParameter param = new SqlParameter ("@totalCount", SqlDbType.Int);
Param. Direction = ParameterDirection.Output;
Cmd. Parameters.Add (param);
SqlParameter param1 = new SqlParameter ("@pageCount", SqlDbType.Int);
param1. Direction = ParameterDirection.Output;
Cmd. Parameters.Add (param1);
Cmd. Parameters[0]. Value = pageSize;
Cmd. PARAMETERS[1]. Value = PageIndex;
Cmd. PARAMETERS[2]. Value = 0;
Cmd. PARAMETERS[3]. Value = 0;
Cmd.commandtype = CommandType.StoredProcedure;
Cmd.commandtext = "Proc_page_withtopmax";
Cmd.commandtimeout = 180;
SqlDataAdapter adapter = new SqlDataAdapter ();
Adapter. SelectCommand = cmd;
DataSet Source = new DataSet ();
Adapter. Fill (DS);
Object o = cmd. parameters["@totalCount"]. Value;
TotalCount = (o = = NULL | | o = = dbnull.value)? 0:system.convert.toint32 (o);
Object B = cmd. parameters["@pageCount"]. Value;
PageCount = (b = = NULL | | o = dbnull.value)? 0:system.convert.toint32 (b);
}
}
catch (SqlException e)
{
Throw e;
}
return DS;
}
}
SQL Server paging stored procedure with input and output parameters (most efficient)