SQL2000 database SQL statement C # paging code

Using system;
Using system. Data;
Using system. configuration;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. text;
Using fjt. dbutility;

/// <Summary>
// SQL Query Class http://www.my400800.cn for paging

/// </Summary>
Public class selecttabledataforpage
{
/// <Summary>
/// Constructor
/// </Summary>
Public selecttabledataforpage ()
{
//
// Todo: Add constructor logic here
//
}

# Region Public static int getselectnowpagecount (string strtablename, string strsqlwhere, int ipagesize)
/// <Summary>
/// Specify the total number of pages for the specified condition in the table
/// </Summary>
/// <Param name = "strtablename"> table name </param>
/// <Param name = "strsqlwhere"> query condition </param>
/// <Param name = "ipagesize"> Number of records displayed per page </param>
/// <Returns> </returns>
Public static int getselectnowpagecount (string strtablename, string strsqlwhere, int ipagesize)
{
// If you specify that the number of records displayed on each page is less than 0, the number of records displayed on each page is 10.
If (ipagesize <= 0) ipagesize = 10;
Stringbuilder sbsql = new stringbuilder ("select count (*) from ");
Sbsql. append (strtablename );
If (! String. isnullorempty (strsqlwhere ))
{
Sbsql. append ("where 1 = 1"). append (strsqlwhere );

}
Int rowcount = convert. toint32 (sqlhelper. executescalar (sqlhelper. connectionstring, commandtype. Text, sbsql. tostring ()));
Int pagecount = rowcount % ipagesize> 0? Rowcount/ipagesize + 1: rowcount/ipagesize;

Return pagecount;
}
# Endregion

# Region obtains the public static datatable selectnowpagefords (string strtablename, string strselfeilds, string strpk, string strorderby, string strwhere, int inowpage, int ipagesize) table data sorted by specified page number)
/// <Summary>
/// Obtain the table data sorted by the specified page number specified by the specified condition
/// </Summary>
/// <Param name = "strtablename"> table name </param>
/// <Param name = "strselfeilds"> separate multiple fields with commas (,). </param>
/// <Param name = "strpk"> unique primary key </param>
/// <Param name = "strorderby"> sort fields. Separate multiple fields with commas (,). </param>
/// <Param name = "strwhere"> query condition </param>
/// <Param name = "inowpage"> page number data to be obtained </param>
/// <Param name = "ipagesize"> Number of records displayed on the current page number </param>
/// <Returns> </returns>
Public static datatable selectnowpagefords (string strtablename, string strselfeilds, string strpk, string strorderby, string strwhere, int inowpage, int ipagesize)
{
// If you specify that the number of records displayed on each page is less than 0, the number of records displayed on each page is 10.
If (ipagesize <= 0) ipagesize = 10;

// Obtain by SQL on pages
String strexecsql = getselectforpagesql (strtablename, strselfeilds, strpk, strorderby, strwhere, inowpage, ipagesize );
Datatable dtsel = sqlhelper. executedataset (sqlhelper. connectionstring, commandtype. Text, strexecsql). Tables [0];
Return dtsel;
}
# Endregion

# obtain the SQL query statement of the specified page number specified by the specified condition in the specified order by region

///

// obtain the SQL query statement of the specified sorting by the specified condition on the specified page number
///
/ // table name
// separate multiple fields by commas (,).
/// unique primary key
/// sorting field, separated by commas
// query conditions
// page number data to be obtained
// Number of records displayed on the current page number
// / SQL query statement return
Public static string getselectforpagesql (string strtablename, string strselfeilds, string strpk, string strorderby, string strwhere, int inowpage, int ipagesize)
{

// If you specify that the number of records displayed on each page is less than 0, the number of records displayed on each page is 10.
If (ipagesize <= 0) ipagesize = 10;

// Result after Splicing
// Http://www.my400800.cn
// Statement format:
// Select top page size *
// From testtable
// Where (id not in
// (Select top page size * Page ID
// From table
// Order by ID ))
// Order by ID
// Retrieve the field information if it is null
Strselfeilds = string. isnullorempty (strselfeilds )? "*": Strselfeilds;

stringbuilder sbsql = new stringbuilder ("");
sbsql. append ("(select")
. appendline (strselfeilds)
. appendline ("from")
. appendline (strtablename);
// query condition concatenation processing
If (! String. isnullorempty (strwhere)
{< br> sbsql. appendline ("where 1 = 1 "). append (strwhere);
}< br> sbsql. appendline (") selforpagetemptable");

// Obtain the sorting field SQL using characters
String strsqlorderby = "";
If (! String. isnullorempty (strorderby ))
{
Strsqlorderby = "order by" + strorderby;

}

Stringbuilder sbexecsql = new stringbuilder ("select ");
Sbexecsql. append ("TOP "). append (ipagesize ). append (""). append (strselfeilds ). append ("from "). append (sbsql ). append ("where ("). append (strpk)
. Append ("not in (select top "). append (inowpage-1) * ipagesize ). append (""). append (strpk ). append ("from "). append (strtablename)
. Append (strsqlorderby). append (")"). appendline (strsqlorderby );

Return sbexecsql. tostring ();
}

/// <Summary>
/// Obtain the SQL query statement of the specified sorting by the specified condition specified page number
/// </Summary>
/// <Param name = "strtablename"> table name </param>
/// <Param name = "strpk"> unique primary key </param>
/// <Param name = "strorderby"> sort fields. Separate multiple fields with commas (,). </param>
/// <Param name = "strwhere"> query condition </param>
/// <Param name = "inowpage"> page number data to be obtained </param>
/// <Param name = "ipagesize"> Number of records displayed on the current page number </param>
/// <Returns> SQL query statement return </returns>
/// <Returns> SQL query statement return </returns>
Public static string getselectforpagesql (string strtablename, string strpk, string strorderby, string strwhere, int inowpage, int ipagesize)
{
Return getselectforpagesql (strtablename, null, strpk, strorderby, strwhere, inowpage, ipagesize );
}

/// <Summary>
/// Obtain the SQL query statement of the specified sorting by the specified condition specified page number
/// </Summary>
/// <Param name = "strtablename"> table name </param>
/// <Param name = "strpk"> unique primary key </param>
/// <Param name = "strorderby"> sort fields. Separate multiple fields with commas (,). </param>
/// <Param name = "inowpage"> page number data to be obtained </param>
/// <Param name = "ipagesize"> Number of records displayed on the current page number </param>
Public static string getselectforpagesql (string strtablename, string strpk, string strorderby, int inowpage, int ipagesize)
{
Return getselectforpagesql (strtablename, null, strpk, strorderby, null, inowpage, ipagesize );
}
# Endregion

}