Also released a paging code in asp.net

The code and stored procedure are for AspNetPager. The environment is VS2005 + sqlserver2005.
The CS code is as follows:

/**//*
This class is for visual studio 2005 only
*/
Using System;
Using System. Text;
Using System. Data;
Using System. Data. SqlClient;

Public class Pager
{
Private static readonly string selectSqlTp = "select * from (select [columns], row_number () over ([orderby]) as _ row _ from [table] [where]) t where t. _ row _> = [low] and t. _ row _ <= [high] ";
Private static readonly string selectTotalTp = "select count (*) from [table] [where]";

// After sorting, the content items on each page will change
Public static DataTable getTable (string table, string columns, string where, string orderby, string ascDesc, int pageIndex, int pageCount, ref int total)
{
String selectSql = getSelectSql (table, columns, where, orderby, ascDesc, pageIndex, pageCount );
String totalSql = getTotalSql (table, where );

System. Data. DataTable dt = new System. Data. DataTable ();
SqlConnection conn = new SqlConnection ("[replaceConnectionString]");
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. CommandText = "up_pager ";

SqlParameter [] paras = {new SqlParameter ("@ select_total_ SQL", totalSql ),
New SqlParameter ("@ select_ SQL", selectSql ),
New SqlParameter ("@ total", SqlDataType. Int )};
Paras [2]. Direction = ParameterDirection. Output;
Cmd. Parameters. AddRange (paras );

Try {
Conn. Open ();
SqlDataReader dr = cmd. ExcuteReader ();
If (dt. HasRows)
{
Dt. Load (dr );
Total = (int) paras [2]. Value;
}
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
If (conn. Status = ConnectionStatus. Open)
Conn. Close ();
Cmd. Parameters. Clear ();
}
Return dt;
}

Private static string getSelectSql (string table, string columns, string where, string orderby, string ascDesc, int pageIndex, int pageCount)
{
System. Text. StringBuilder sbSelect = new System. Text. StringBuilder (selectSqlTp );
SbSelect. Replace ("[table]", table );
SbSelect. Replace ("[columns]", columns );
If (where = null | where = "")
SbSelect. Replace ("[where]", "where 1 = 1 ");
Else sbSelect. Replace ("[where]", where );
String order = "order by" + orderby;
If (ascDesc! = Null & ascDesc! = "")
Order + = "" + ascDesc;
SbSelect. Replace ("[orderby]", order );
Int low = (int) (pageIndex * pageCount );
Int high = (int) (pageIndex + 1) * pageCount );
SbSelect. Replace ("[low]", "" + low );
SbSelect. Replace ("[high]", "" + high );
Return sbSelect. ToString ();
}

Private static string getSelectTotal (string table, string where)
{
System. Text. StringBuilder sbTotal = new System. Text. StringBuilder (selectTotalTp );
SbTotal. Replace ("[table]", table );
If (where = null)
Where = "where 1 = 1 ";
SbTotal. Replace ("[where]", where );
Return sbTotal. ToString ();
}
}

The stored procedure is as follows:

-- The pager procedure
-- Zyl
-- Version: 2.0
Create procedure up_pager
@ Select_total_ SQL nvarchar (1000 ),
@ Select_ SQL nvarchar (2000 ),
@ Total int output
As
Exec sp_executesql @ select_ SQL
Exec @ total = sp_executesql @ select_total_ SQL

Return @ rowcount

GO

The above code was written by referring to the code of a senior engineer. I would like to express my gratitude.