无为清净楼资源网 Design By www.qnjia.com
使用这种通用的存储过程进行分页查询,想要防SQL注入,只能对输入的参数进行过滤,例如将一个单引号“'”转换成两个单引号“''”,但这种做法是不安全的,厉害的黑客可以通过编码的方式绕过单引号的过滤,要想有效防SQL注入,只有参数化查询才是最终的解决方案。但问题就出在这种通用分页存储过程是在存储过程内部进行SQL语句拼接,根本无法修改为参数化的查询语句,因此这种通用分页存储过程是不可取的。但是如果不用通用的分页存储过程,则意味着必须为每个具体的分页查询写一个分页存储过程,这会增加不少的工作量。
经过几天的时间考虑之后,想到了一个用代码来生成参数化的通用分页查询语句的解决方案。代码如下:
复制代码 代码如下:
public class PagerQuery
{
private int _pageIndex;
private int _pageSize = 20;
private string _pk;
private string _fromClause;
private string _groupClause;
private string _selectClause;
private string _sortClause;
private StringBuilder _whereClause;
public DateTime DateFilter = DateTime.MinValue;
protected QueryBase()
{
_whereClause = new StringBuilder();
}
/**//// <summary>
/// 主键
/// </summary>
public string PK
{
get { return _pk; }
set { _pk = value; }
}
public string SelectClause
{
get { return _selectClause; }
set { _selectClause = value; }
}
public string FromClause
{
get { return _fromClause; }
set { _fromClause = value; }
}
public StringBuilder WhereClause
{
get { return _whereClause; }
set { _whereClause = value; }
}
public string GroupClause
{
get { return _groupClause; }
set { _groupClause = value; }
}
public string SortClause
{
get { return _sortClause; }
set { _sortClause = value; }
}
/**//// <summary>
/// 当前页数
/// </summary>
public int PageIndex
{
get { return _pageIndex; }
set { _pageIndex = value; }
}
/**//// <summary>
/// 分页大小
/// </summary>
public int PageSize
{
get { return _pageSize; }
set { _pageSize = value; }
}
/**//// <summary>
/// 生成缓存Key
/// </summary>
/// <returns></returns>
public override string GetCacheKey()
{
const string keyFormat = "Pager-SC:{0}-FC:{1}-WC:{2}-GC:{3}-SC:{4}";
return string.Format(keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause);
}
/**//// <summary>
/// 生成查询记录总数的SQL语句
/// </summary>
/// <returns></returns>
public string GenerateCountSql()
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
return string.Format("Select count(0) {0}", sb);
}
/**//// <summary>
/// 生成分页查询语句,包含记录总数
/// </summary>
/// <returns></returns>
public string GenerateSqlIncludeTotalRecords()
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(SelectClause))
SelectClause = "*";
if (string.IsNullOrEmpty(SortClause))
SortClause = PK;
int start_row_num = (PageIndex - 1)*PageSize + 1;
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
string countSql = string.Format("Select count(0) {0};", sb);
string tempSql =
string.Format(
"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4};",
SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
return tempSql + countSql;
}
/**//// <summary>
/// 生成分页查询语句
/// </summary>
/// <returns></returns>
public override string GenerateSql()
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(SelectClause))
SelectClause = "*";
if (string.IsNullOrEmpty(SortClause))
SortClause = PK;
int start_row_num = (PageIndex - 1)*PageSize + 1;
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
return
string.Format(
"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4}",
SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
}
}
使用方法:
复制代码 代码如下:
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以上版本的,希望这些代码对大家有用
经过几天的时间考虑之后,想到了一个用代码来生成参数化的通用分页查询语句的解决方案。代码如下:
复制代码 代码如下:
public class PagerQuery
{
private int _pageIndex;
private int _pageSize = 20;
private string _pk;
private string _fromClause;
private string _groupClause;
private string _selectClause;
private string _sortClause;
private StringBuilder _whereClause;
public DateTime DateFilter = DateTime.MinValue;
protected QueryBase()
{
_whereClause = new StringBuilder();
}
/**//// <summary>
/// 主键
/// </summary>
public string PK
{
get { return _pk; }
set { _pk = value; }
}
public string SelectClause
{
get { return _selectClause; }
set { _selectClause = value; }
}
public string FromClause
{
get { return _fromClause; }
set { _fromClause = value; }
}
public StringBuilder WhereClause
{
get { return _whereClause; }
set { _whereClause = value; }
}
public string GroupClause
{
get { return _groupClause; }
set { _groupClause = value; }
}
public string SortClause
{
get { return _sortClause; }
set { _sortClause = value; }
}
/**//// <summary>
/// 当前页数
/// </summary>
public int PageIndex
{
get { return _pageIndex; }
set { _pageIndex = value; }
}
/**//// <summary>
/// 分页大小
/// </summary>
public int PageSize
{
get { return _pageSize; }
set { _pageSize = value; }
}
/**//// <summary>
/// 生成缓存Key
/// </summary>
/// <returns></returns>
public override string GetCacheKey()
{
const string keyFormat = "Pager-SC:{0}-FC:{1}-WC:{2}-GC:{3}-SC:{4}";
return string.Format(keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause);
}
/**//// <summary>
/// 生成查询记录总数的SQL语句
/// </summary>
/// <returns></returns>
public string GenerateCountSql()
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
return string.Format("Select count(0) {0}", sb);
}
/**//// <summary>
/// 生成分页查询语句,包含记录总数
/// </summary>
/// <returns></returns>
public string GenerateSqlIncludeTotalRecords()
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(SelectClause))
SelectClause = "*";
if (string.IsNullOrEmpty(SortClause))
SortClause = PK;
int start_row_num = (PageIndex - 1)*PageSize + 1;
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
string countSql = string.Format("Select count(0) {0};", sb);
string tempSql =
string.Format(
"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4};",
SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
return tempSql + countSql;
}
/**//// <summary>
/// 生成分页查询语句
/// </summary>
/// <returns></returns>
public override string GenerateSql()
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(SelectClause))
SelectClause = "*";
if (string.IsNullOrEmpty(SortClause))
SortClause = PK;
int start_row_num = (PageIndex - 1)*PageSize + 1;
sb.AppendFormat(" from {0}", FromClause);
if (WhereClause.Length > 0)
sb.AppendFormat(" where 1=1 {0}", WhereClause);
if (!string.IsNullOrEmpty(GroupClause))
sb.AppendFormat(" group by {0}", GroupClause);
return
string.Format(
"WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4}",
SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
}
}
使用方法:
复制代码 代码如下:
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以上版本的,希望这些代码对大家有用
标签:
参数化,分页查询语句
无为清净楼资源网 Design By www.qnjia.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
无为清净楼资源网 Design By www.qnjia.com
暂无评论...
P70系列延期,华为新旗舰将在下月发布
3月20日消息,近期博主@数码闲聊站 透露,原定三月份发布的华为新旗舰P70系列延期发布,预计4月份上市。
而博主@定焦数码 爆料,华为的P70系列在定位上已经超过了Mate60,成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢?
根据目前爆料的消息来看,华为P70系列将推出三个版本,其中P70和P70 Pro采用了三角形的摄像头模组设计,而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智,但辨识度绝对拉满。
更新日志
2024年09月26日
2024年09月26日
- 纯音入心系列纯音乐《中央民族乐团-古筝传奇》1CD[MP3][253.1MB]
- 江玲.1989-这样你才爱我,是吗?【新作有声】【WAV+CUE】
- 郭美美.2010-我是郭美美【华纳】【WAV+CUE】
- 黄心懋1991-轻忧郁【滚石】【WAV+CUE】
- 群星.2000-大地金曲世纪回顾2CD【大地】【WAV+CUE】
- 王菲.1996-浮躁(2024环球MQA-UHQCD限量版)【环球】【WAV+CUE】
- 群星.2022-传家电视剧原声带【东阳欢娱】【FLAC分轨】
- 《摇滚教父 伍佰黄金精选 2CD》 [WAV+CUE][1GB]
- 《凤凰传奇 我从草原来》[WAV/分轨][400MB]
- 《叶倩文 真心真意过一生》[WAV+CUE][400MB]
- 潘越云.1985-世间女子(滚石25周年经典复刻版)【滚石】【WAV+CUE】
- 陈柏宇.2009-CANT.BE.HALF【SONY】【WAV+CUE】
- 李明依.1989-小女生【滚石】【WAV+CUE】
- 《李荣浩 新时代创作新人王》[WAV分轨][310MB]
- 《黑鸭子与马头琴 史上最动听草原音乐》[WAV+CUE][320MB]