C # convert SQL statements into paging SQL statements and obtain the number of records SQL statements

C # convert SQL statements into paging SQL statements and obtain the number of records SQL statements

/// <Summary>
/// Convert the statement into a paging SQL statement. Two SQL statements that contain paging data are used to obtain the number of records. SQL statement Jia Shiyi
/// </Summary>
/// <Param name = "dbtype"> supported data types (sql2005, Oracle, DB2, and infomix) </param>
/// <Param name = "SQL"> SQL statement (sql2005, Oracle, and DB2 must contain order by, a normal SQL statement) </param>
/// <Param name = "pageindex"> Start (from 0) </param>
/// <Param name = "pagesize"> page size </param>
/// <Returns> two statements, including querying data by PAGE and querying the number of records </returns>
Public static string getpagesql (dbtype, string SQL, int pageindex, int pagesize)
{
If (string. isnullorempty (SQL ))
{
Return NULL;
}
// Paging SQL is not supported
If (dbtype = dbtype. SQL2000 | dbtype = dbtype. oledb | dbtype = dbtype. ODBC)
{
Return SQL;
}
String start = "0 ";
If (pageindex> 0)
{
Start = convert. tostring (pageindex * pagesize );
}
Int select = getstartwith (SQL, "select ");
If (dbtype = dbtype. odbcinformix)
{
Return "select skip" + start + "first" + pagesize. tostring ()
+ "" + SQL. substring (select );
}
// Split the SQL statement
String end = convert. tostring (pageindex + 1) * pagesize );
String fromsql = getfromsql (SQL, "from ");
// Select contains select
String selectsql = "select" + SQL. substring (select, SQL. Length-select-fromsql. Length );
String ordersql = getfromsql (SQL, "order ");
If (! String. isnullorempty (ordersql ))
{
// Remove order by after from
Fromsql = fromsql. substring (0, fromsql. Length-ordersql. Length );
}
Else
{
Throw new exception (dbtype + "order by must be included in paging ");
}
// Merge into paging SQL statements
String strsql = "select * from (" + selectsql;
// The Order part contains order
String rownum;
If (dbtype = dbtype. odbcdb2)
{
Rownum = "rownumber ()";
}
Else
{
Rownum = "row_number ()";
}
Strsql + = "," + rownum + "over (" + ordersql + ") as RN" + fromsql
+ ") As data where rn>" + start + "and Rn <=" + end;
If (! String. isnullorempty (getfromsql (fromsql, "group ")))
{
Fromsql = "from (select count (*) as amount" + fromsql + ") TBL ";
}
Return strsql + "; select count (*) as [" + constants. myquery_amount + "]" + fromsql;
}

Welcome to: http: // 121.18.78.216, which is a demonstration platform for ease of query and analysis, workflow, content management, and project management.