防SQL注入 生成参数化的通用分页查询语句(2)

PagerQuery query = new PagerQuery();
query.PageIndex = 1;
query.PageSize = 20;
query.PK = "ID";
query.SelectClause = "*";
query.FromClause = "TestTable";
query.SortClause = "ID DESC";
if (!string.IsNullOrEmpty(code))
{
query.WhereClause.Append(" and ID= @ID");
}

a) GenerateCountSql ()方法生成的语句为：
Select count(0) from TestTable Where 1=1 and ID= @ID
b) GenerateSql()方法生成的语句为：
WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY ECID DESC) as row_number, * from TestTable where 1=1 and ID= @ID) Select * from t where row_number BETWEEN 1 and 20
c) GenerateSqlIncludetTotalRecords()方法生成的语句为：
WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY E.ECID DESC) as row_number,* from TestTable where 1=1 and ID= @ID) Select * from t where row_number BETWEEN 1 and 20;Select count(0) from ECBasicInfo where 1=1 and ID= @ID;

注意：以上代码生成的SQL语句是曾对SQL SERVER 2005以上版本的，希望这些代码对大家有用

您可能感兴趣的文章: