相信大家基本都是用存储过程来做分页的,个人感觉用存储过程有点不好,其中就是调试不好。
所以刚花了点时间把以前的存储过程转换成代码了。希望大家给点意见或建议,因为是刚写的,所以错误难免。呵呵/// <summary>
/// 数据库分页类
/// </summary>
public class SqlPager
{
private int pageIndex = 1;
private int pageSize = 20;
private string group;
private string order;
private string fields;
private string filter; #region 公共属性 /// <summary>
/// 第几页
/// </summary>
public int PageIndex
{
get
{
return pageIndex;
}
set
{
pageIndex = value;
}
} /// <summary>
/// 每页显示几条数据
/// </summary>
public int PageSize
{
get
{
return pageSize;
}
set
{
pageSize = value;
}
} /// <summary>
/// 排序语句,不加order by
/// </summary>
public string Order
{
get
{
return order;
}
set
{
if (!string.IsNullOrEmpty(value))
{
order = " ORDER BY " + value;
}
}
} /// <summary>
/// 表id字段,当调用GetTopSql方法时使用,即 ID NOT IN(...)
/// </summary>
public string ID { get; set; } /// <summary>
/// 分组语句,不加group by
/// </summary>
public string Group
{
get
{
return group;
}
set
{
if (!string.IsNullOrEmpty(value))
{
group = " GROUP BY " + value;
}
}
} /// <summary>
/// 查询字段,默认为*
/// </summary>
public string Fields
{
get
{
return fields;
}
set
{
if (!string.IsNullOrEmpty(value))
{
fields = " " + value;
}
else
{
fields = " * ";
}
}
} /// <summary>
/// where语句,不加where
/// </summary>
public string Filter
{
get
{
return filter;
}
set
{
if (!string.IsNullOrEmpty(value))
{
filter = " WHERE " + value;
}
else
{
filter = " WHERE 1=1 ";
}
}
} /// <summary>
/// 表名、视图名、查询语句
/// </summary>
public string TableName { get; set; } #endregion /// <summary>
/// 获取参数
/// </summary>
/// <returns></returns>
public SqlParameter[] GetParameters()
{
return new SqlParameter[] {
new SqlParameter("@TableName", TableName),
new SqlParameter("@Fields", Fields),
new SqlParameter("@Filter", Filter),
new SqlParameter("@Group", Group),
new SqlParameter("@Order", Order),
new SqlParameter("@ID", ID)
};
} /// <summary>
/// 获取TOP分页数据
/// </summary>
/// <returns></returns>
public string GetTopSql()
{
StringBuilder sql = new StringBuilder(); if (PageIndex <= 1)
{
sql.Append(GetTop1Sql());
}
else
{
sql.Append("'SELECT TOP " + PageSize + " ' + @Fields + ' FROM ' + " + GetTableString() + (IsTable ? "AS TT1" : "") +
" + ' WHERE TT1.' + @ID + ' NOT IN (SELECT TOP " + PageSize * (PageIndex - 1));
sql.Append(" ' + @ID + ' FROM ' + " + GetTableString() + " + ' ' + @Order + ')' + @Order");
}
return "EXEC(" + sql.ToString() + ")";
} /// <summary>
/// 获取Count查询语句
/// </summary>
/// <returns></returns>
public string GetCountSql()
{
System.Text.StringBuilder sql = new StringBuilder();
if (!string.IsNullOrEmpty(Group))
{
sql.Append("'SELECT COUNT(0) FROM (SELECT 1 AS num FROM ' + " + GetTableString() + " + @Filter + @Group + ')T'");
}
else
{
sql.Append("'SELECT COUNT(0) FROM ' + " + GetTableString() + " + @Filter");
}
return "EXEC(" + sql.ToString() + ")";
} /// <summary>
/// 获取RowNum分页语句(sql2005)
/// </summary>
/// <returns></returns>
public string GetRowNumSql()
{
StringBuilder sql = new StringBuilder(); if (PageIndex <= 1)
{
sql.Append(GetTop1Sql());
}
else
{
sql.Append("' SELECT * FROM (SELECT ROW_NUMBER() OVER(' + @Order + ') AS rownum,' + @Fields + ' FROM ' + " + GetTableString() + " + @Filter + @Group");
sql.Append(" + ') AS T WHERE rownum BETWEEN ");
sql.Append(((PageIndex - 1) * PageSize + 1) + " AND " + (PageIndex * PageSize) + "'"); }
return "EXEC(" + sql.ToString() + ")";
} /// <summary>
/// 第一页时获取的分页语句
/// </summary>
/// <returns></returns>
private string GetTop1Sql()
{
StringBuilder sql = new StringBuilder();
sql.Append("'SELECT TOP " + PageSize + " ' + @Fields + ' FROM ' + " + GetTableString() + " + @Filter + @Group + @Order");
return sql.ToString();
} private string GetTableString()
{
if (IsTable)
{
return "@TableName";
}
else
{
return " '(' + @TableName + ')TT1'";
}
} private bool IsTable
{
get
{
return !string.IsNullOrEmpty(TableName) && TableName.ToLower().IndexOf("select ") == -1 && TableName.ToLower().IndexOf("from ") == -1;
}
}
}
所以刚花了点时间把以前的存储过程转换成代码了。希望大家给点意见或建议,因为是刚写的,所以错误难免。呵呵/// <summary>
/// 数据库分页类
/// </summary>
public class SqlPager
{
private int pageIndex = 1;
private int pageSize = 20;
private string group;
private string order;
private string fields;
private string filter; #region 公共属性 /// <summary>
/// 第几页
/// </summary>
public int PageIndex
{
get
{
return pageIndex;
}
set
{
pageIndex = value;
}
} /// <summary>
/// 每页显示几条数据
/// </summary>
public int PageSize
{
get
{
return pageSize;
}
set
{
pageSize = value;
}
} /// <summary>
/// 排序语句,不加order by
/// </summary>
public string Order
{
get
{
return order;
}
set
{
if (!string.IsNullOrEmpty(value))
{
order = " ORDER BY " + value;
}
}
} /// <summary>
/// 表id字段,当调用GetTopSql方法时使用,即 ID NOT IN(...)
/// </summary>
public string ID { get; set; } /// <summary>
/// 分组语句,不加group by
/// </summary>
public string Group
{
get
{
return group;
}
set
{
if (!string.IsNullOrEmpty(value))
{
group = " GROUP BY " + value;
}
}
} /// <summary>
/// 查询字段,默认为*
/// </summary>
public string Fields
{
get
{
return fields;
}
set
{
if (!string.IsNullOrEmpty(value))
{
fields = " " + value;
}
else
{
fields = " * ";
}
}
} /// <summary>
/// where语句,不加where
/// </summary>
public string Filter
{
get
{
return filter;
}
set
{
if (!string.IsNullOrEmpty(value))
{
filter = " WHERE " + value;
}
else
{
filter = " WHERE 1=1 ";
}
}
} /// <summary>
/// 表名、视图名、查询语句
/// </summary>
public string TableName { get; set; } #endregion /// <summary>
/// 获取参数
/// </summary>
/// <returns></returns>
public SqlParameter[] GetParameters()
{
return new SqlParameter[] {
new SqlParameter("@TableName", TableName),
new SqlParameter("@Fields", Fields),
new SqlParameter("@Filter", Filter),
new SqlParameter("@Group", Group),
new SqlParameter("@Order", Order),
new SqlParameter("@ID", ID)
};
} /// <summary>
/// 获取TOP分页数据
/// </summary>
/// <returns></returns>
public string GetTopSql()
{
StringBuilder sql = new StringBuilder(); if (PageIndex <= 1)
{
sql.Append(GetTop1Sql());
}
else
{
sql.Append("'SELECT TOP " + PageSize + " ' + @Fields + ' FROM ' + " + GetTableString() + (IsTable ? "AS TT1" : "") +
" + ' WHERE TT1.' + @ID + ' NOT IN (SELECT TOP " + PageSize * (PageIndex - 1));
sql.Append(" ' + @ID + ' FROM ' + " + GetTableString() + " + ' ' + @Order + ')' + @Order");
}
return "EXEC(" + sql.ToString() + ")";
} /// <summary>
/// 获取Count查询语句
/// </summary>
/// <returns></returns>
public string GetCountSql()
{
System.Text.StringBuilder sql = new StringBuilder();
if (!string.IsNullOrEmpty(Group))
{
sql.Append("'SELECT COUNT(0) FROM (SELECT 1 AS num FROM ' + " + GetTableString() + " + @Filter + @Group + ')T'");
}
else
{
sql.Append("'SELECT COUNT(0) FROM ' + " + GetTableString() + " + @Filter");
}
return "EXEC(" + sql.ToString() + ")";
} /// <summary>
/// 获取RowNum分页语句(sql2005)
/// </summary>
/// <returns></returns>
public string GetRowNumSql()
{
StringBuilder sql = new StringBuilder(); if (PageIndex <= 1)
{
sql.Append(GetTop1Sql());
}
else
{
sql.Append("' SELECT * FROM (SELECT ROW_NUMBER() OVER(' + @Order + ') AS rownum,' + @Fields + ' FROM ' + " + GetTableString() + " + @Filter + @Group");
sql.Append(" + ') AS T WHERE rownum BETWEEN ");
sql.Append(((PageIndex - 1) * PageSize + 1) + " AND " + (PageIndex * PageSize) + "'"); }
return "EXEC(" + sql.ToString() + ")";
} /// <summary>
/// 第一页时获取的分页语句
/// </summary>
/// <returns></returns>
private string GetTop1Sql()
{
StringBuilder sql = new StringBuilder();
sql.Append("'SELECT TOP " + PageSize + " ' + @Fields + ' FROM ' + " + GetTableString() + " + @Filter + @Group + @Order");
return sql.ToString();
} private string GetTableString()
{
if (IsTable)
{
return "@TableName";
}
else
{
return " '(' + @TableName + ')TT1'";
}
} private bool IsTable
{
get
{
return !string.IsNullOrEmpty(TableName) && TableName.ToLower().IndexOf("select ") == -1 && TableName.ToLower().IndexOf("from ") == -1;
}
}
}
解决方案 »
- IE8浏览器下使用Ajax的AutoCompleteExtention控件做模糊查询下拉菜单匹配,没有显示下拉菜单!
- ASP.NET发送邮件的问题,望各位达人指教
- Dreamwear中怎样配置运行.aspx文件?
- 以前问过的问题一直未有好的答案,就是用vs2005开发的c#Asp.net网站若.sln文件丢失了该如何恢复?
- 求一个页面引用问题
- 大家帮帮忙谁有visual sourcesafe 6.0c下载,在线等啊,急
- treeview怎么改变链接的颜色?
- 关于验证码中验证码刷新优化问题(以CSDN登录为例)的讨论!
- 我告诉你应该选择VB。NET还是C#!!!!!!!!!!!!!!!!!!
- asp.net core EFCode连接oracle数据库的问题。
- jqGrid如何添加时间
- 如何用js遍历gridview中的列
1、这样确实不能防注入,但是这里不考虑什么兼容性问题
2、动态sql语句除了拼接,不知道还有什么方法,存储过程也是拼接,请指教
3、多表连接的时候TableName属性可以设置成sql语句:
select 字段 from table1 inner join table2 on table1.id=table2.id
4、这里本来就是生成sql语句的。
5、排序可以写成这样:col1 desc,col2 asc用exec执行动态sql的真的不知道怎么样防注入了,除非在执行前去过滤掉关键字
System.Data.DataTable GetDataTable(ConditionParameter[] conditions, OrderParameter[] orderBy, int startRowIndex, int maximumRows);
就这么简单...我根本不关心将来是拼字符串还是调用存储过程,都可以...也不用考虑SELECT ROW_NUMBER() OVER是不是只有SQL Server 2005以上才支持...这些都在各个数据库访问方法类中去填写,它们只需要按上面的接口规格返回数据就行了(当然实际实现可能很复杂,上面这个接口方法之所以很简洁是因为应用了泛型和两个自定义参数类,相反应用Linq的话可能又会很简单)...最终在页面上只需要把<asp:DataPager />这个控件放到合适的位置绑定ObjectDataSource即可...即使不使用控件,接口分页方法返回的也是分页后的数据...例如,还可以有这样一个方法...
IList<T> GetData(ConditionParameter[] conditions, OrderParameter[] orderBy, int startRowIndex, int maximumRows);
因此它也适用于MVC、WebService、WinForm等...而这些都不依赖于某个分页类...
注入的问题大部分存在于有输入的地方,其他不输入的地方暂时还不知道,也请大家提出来,既然是输入的地方那么对于分页语句也只有where和order by后面的了,所以只要封装where和order by应该就可以了,当然也有人说,我的程序字段也是客户端输入的,甚至group by 分组字段也是可以输入的,我暂时还没有碰到这样的程序,只能碰到的时候在优化了。正在修改代码,修改后在发,请大家关注