搜索:
首页 >> 数据库 >> MsSQL教程 >> CPQuery 解决拼接SQL的新方法

CPQuery 解决拼接SQL的新方法

2012-11-30 来源:asp之家 网友评论投递文章

我一直都不喜欢在访问数据库时采用拼接SQL的方法,原因有以下几点:
1. 不安全:有被SQL注入的风险。
2. 可能会影响性能:每条SQL语句都需要数据库引擎执行[语句分析]之类的开销。
3. 影响代码的可维护性:SQL语句与C#混在一起,想修改SQL就得重新编译程序,而且二种代码混在一起,可读性也不好。
所以我通常会选择【参数化SQL】的方法去实现数据库的访问过程,而且会将SQL语句与项目代码(C#)分离开。

不过,有些人可能会说:我的业务逻辑很复杂,Where中的过虑条件不可能事先确定,因此不拼接SQL还不行。

看到这些缺点,ORM用户可能会认为:使用ORM工具就是终极的解决方案。
是的,的确ORM可以解决这些问题。
但是,解决方案并非只有ORM一种,还有些人就是喜欢写SQL呢。
所以,这篇博客不是写给ORM用户的,而是写给所有喜欢写SQL语句的朋友。

CPQuery是什么?
看到博客的标题,你会不会想:CPQuery是什么?

下面是我的回答:
1. CPQuery 是一个缩写:Concat Parameterized Query
2. CPQuery 可以让你继续使用熟悉的拼接方式来写参数化的SQL
3. CPQuery 是我设计的一种解决方案,它可以解决拼接SQL的前二个缺点。
4. CPQuery 也是这个解决方案中核心类型的名称。

希望大家能记住CPQuery这个名字。

CPQuery适合哪些人使用?
答:适合于喜欢手写SQL代码的人,尤其是当需要写动态查询时。

参数化的SQL语句
对于需要动态查询的场景,我认为:拼接SQL或许是必需的,但是,你不要将数值也拼接到SQL语句中嘛,或者说,你应该拼接参数化的SQL来解决你遇到的问题。

说到【拼接参数化SQL】,我想解释一下这个东西了。
这个方法的实现方式是:拼接SQL语句时,不要把参数值拼接到SQL语句中,在SQL语句中使用占位符参数,具体的参数值通过ADO.NET的command.Parameters.Add()传入。现在流行的ORM工具应该都会采用这个方法。

我认为参数化的SQL语句可以解决本文开头所说的那些问题,尤其是前二个。对于代码的维护问题,我的观点是:如果你硬是将SQL与C#混在一起,那么参数化的SQL语句也是没有办法的。如果想解决这个问题,你需要将SQL语句与项目代码分离,然后可以选择以配置文件或者存储过程做为保存那些SLQ语句的容器。

所以,参数化的SQL并不是万能的,代码的可维护性与技术的选择无关,与架构的设计有关。任何优秀的技术都可能写出难以维护的代码来,这就是我的观点。

改造现有的拼接语句
还是说动态查询,假设我有这样一个查询界面:

显然,在设计程序时,不可能知道用户会输入什么样的过滤条件。
因此,喜欢手写SQL的人们通常会这样写查询:

代码如下:


var query = "select ProductID, ProductName from Products where (1=1) ";
if( p.ProductID > 0 )
query = query + " and ProductID = " + p.ProductID.ToString();
if( string.IsNullOrEmpty(p.ProductName) == false )
query = query + " and ProductName like '" + p.ProductName + "'";
if( p.CategoryID > 0 )
query = query + " and CategoryID = " + p.CategoryID.ToString();
if( string.IsNullOrEmpty(p.Unit) == false )
query = query + " and Unit = '" + p.Unit + "'";
if( p.UnitPrice > 0 )
query = query + " and UnitPrice >= " + p.UnitPrice.ToString();
if( p.Quantity > 0 )
query = query + " and Quantity >= " + p.Quantity.ToString();


如果使用这种方式,本文开头所说的前二个缺点肯定是存在的。
我想很多人应该是知道参数化查询的,最终放弃或许有以下2个原因:
1. 这种拼接SQL语句的方式很简单,非常容易实现。
2. 便于包装自己的API,参数只需要一个(万能的)字符串!
如果你认为这2个原因很难解决的话,那我今天就给你 “一种改动极小却可以解决上面二个缺点”的解决方案,改造后的代码如下:

代码如下:


var query = "select ProductID, ProductName from Products where (1=1) ".AsCPQuery(true);
if( p.ProductID > 0 )
query = query + " and ProductID = " + p.ProductID.ToString();
if( string.IsNullOrEmpty(p.ProductName) == false )
query = query + " and ProductName like '" + p.ProductName + "'";
if( p.CategoryID > 0 )
query = query + " and CategoryID = " + p.CategoryID.ToString();
if( string.IsNullOrEmpty(p.Unit) == false )
query = query + " and Unit = '" + p.Unit + "'";
if( p.UnitPrice > 0 )
query = query + " and UnitPrice >= " + p.UnitPrice.ToString();
if( p.Quantity > 0 )
query = query + " and Quantity >= " + p.Quantity.ToString();


你看到差别了吗?
差别在于第一行代码,后面调用了一个扩展方法:AsCPQuery(true) ,这个方法的实现代码我后面再说。
这个示例的主要关键代码如下:

代码如下:


private static readonly string ConnectionString =
ConfigurationManager.ConnectionStrings["MyNorthwind_MSSQL"].ConnectionString;
private void btnQuery_Click(object sender, EventArgs e)
{
Product p = new Product();
p.ProductID = SafeParseInt(txtProductID.Text);
p.ProductName = txtProductName.Text.Trim();
p.CategoryID = SafeParseInt(txtCategoryID.Text);
p.Unit = txtUnit.Text.Trim();
p.UnitPrice = SafeParseDecimal(txtUnitPrice.Text);
p.Quantity = SafeParseInt(txtQuantity.Text);
var query = BuildDynamicQuery(p);
try {
txtOutput.Text = ExecuteQuery(query);
}
catch( Exception ex ) {
txtOutput.Text = ex.Message;
}
}
private CPQuery BuildDynamicQuery(Product p)
{
var query = "select ProductID, ProductName from Products where (1=1) ".AsCPQuery(true);
if( p.ProductID > 0 )
query = query + " and ProductID = " + p.ProductID.ToString();
if( string.IsNullOrEmpty(p.ProductName) == false )
query = query + " and ProductName like '" + p.ProductName + "'";
if( p.CategoryID > 0 )
query = query + " and CategoryID = " + p.CategoryID.ToString();
if( string.IsNullOrEmpty(p.Unit) == false )
query = query + " and Unit = '" + p.Unit + "'";
if( p.UnitPrice > 0 )
query = query + " and UnitPrice >= " + p.UnitPrice.ToString();
if( p.Quantity > 0 )
query = query + " and Quantity >= " + p.Quantity.ToString();
return query;
}
private string ExecuteQuery(CPQuery query)
{
StringBuilder sb = new StringBuilder();
using( SqlConnection connection = new SqlConnection(ConnectionString) ) {
SqlCommand command = connection.CreateCommand();
// 将前面的拼接结果绑定到命令对象。
query.BindToCommand(command);
// 输出调试信息。
sb.AppendLine("==================================================");
sb.AppendLine(command.CommandText);
foreach( SqlParameter p in command.Parameters )
sb.AppendFormat("{0} = {1}\r\n", p.ParameterName, p.Value);
sb.AppendLine("==================================================\r\n");
// 打开连接,执行查询
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while( reader.Read() )
sb.AppendFormat("{0}, {1}\r\n", reader[0], reader[1]);
}
return sb.ToString();
}
private int SafeParseInt(string s)
{
int result = 0;
int.TryParse(s, out result);
return result;
}
private decimal SafeParseDecimal(string s)
{
decimal result = 0m;
decimal.TryParse(s, out result);
return result;
}


我们来看一下程序运行的结果:

根据前面给出的调试代码:

代码如下:


// 输出调试信息。
sb.AppendLine("==================================================");
sb.AppendLine(command.CommandText);
foreach( SqlParameter p in command.Parameters )
sb.AppendFormat("{0} = {1}\r\n", p.ParameterName, p.Value);
sb.AppendLine("==================================================\r\n");


以及图片反映的事实,可以得出结论:改造后的查询已经是参数化的查询了!

站长工具
IP地址/域名归属地查询:
相关文章
  • 暂无相关文章!
loading 请稍等,评论加载中...

学习Asp到Asp之家(Aspxhome.com)

闽ICP备09044667号-4