C#拼接SQL语句 用ROW_NUMBER实现的高效分页排序

/// <summary>
/// 单表（视图）获取分页sql语句
/// </summary>
/// <param name="tablename">表名或视图名</param>
/// <param name="key">唯一键</param>
/// <param name="fields">获取的字段</param>
/// <param name="condition">查询条件（不包含where）</param>
/// <param name="collatingsequence">排序规则（不包含order by）</param>
/// <param name="pagesize">页大小</param>
/// <param name="pageindex">页码（从1开始）</param>
/// <returns>分页sql语句</returns>
public static string getpagingsql(
string tablename,
string key,
string fields,
string condition,
string collatingsequence,
int pagesize,
int pageindex)
{
string whereclause = string.empty;
if (!string.isnullorempty(condition))
{
whereclause = string.format("where {0}", condition);
}

if (string.isnullorempty(collatingsequence))
{
collatingsequence = string.format("{0} asc", key);
}

stringbuilder sbsql = new stringbuilder();

sbsql.appendformat("select {0} ", prependtablename(tablename, fields, ','));
sbsql.appendformat("from ( select top {0} ", pagesize * pageindex);
sbsql.appendformat(" [_rownum_] = row_number() over ( order by {0} ), ", collatingsequence);
sbsql.appendformat(" {0} ", key);
sbsql.appendformat(" from {0} ", tablename);
sbsql.appendformat(" {0} ", whereclause);
sbsql.appendformat(" ) as [_temptable_] ");
sbsql.appendformat(" inner join {0} on [_temptable_].{1} = {0}.{1} ", tablename, key);
sbsql.appendformat("where [_rownum_] > {0} ", pagesize * (pageindex - 1));
sbsql.appendformat("order by [_temptable_].[_rownum_] asc ");

return sbsql.tostring();
}

/// <summary>
/// 给字段添加表名前缀
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="fields">字段</param>
/// <param name="separator">标识字段间的分隔符</param>
/// <returns></returns>
public static string prependtablename(string tablename, string fields, char separator)
{
stringbuilder sbfields = new stringbuilder();

string[] fieldarr = fields.trim(separator).split(separator);
foreach (string str in fieldarr)
{
sbfields.appendformat("{0}.{1}{2}", tablename, str.trim(), separator);
}

return sbfields.tostring().trimend(separator);
}

create table [dbo].[tbl_product]
(
[id] [int] identity(1, 1)
not null ,
[productid] [varchar](50) not null ,
[productname] [nvarchar](50) not null ,
[isdeleted] [int] not null
constraint [df_tbl_product_isdeleted] default ( (0) ) ,
constraint [pk_tbl_product] primary key clustered ( [productid] asc )
with ( pad_index = off, statistics_norecompute = off,
ignore_dup_key = off, allow_row_locks = on,
allow_page_locks = on ) on [primary]
)
on [primary]

select tbl_product.id ,
tbl_product.productid ,
tbl_product.productname
from ( select top 25
[_rownum_] = row_number() over ( order by productname asc, id desc ) ,
id
from tbl_product
where isdeleted = 0
) as [_temptable_]
inner join tbl_product on [_temptable_].id = tbl_product.id
where [_rownum_] > 20
order by [_temptable_].[_rownum_] asc