在ASP.NET中使用查询字符串参数形成动态SQL语句 [英] Forming a dynamic SQL statement using query string parameters in ASP.NET

问题描述

我正在尝试根据查询字符串参数动态创建MS SQL查询.

I'm trying to dynamically create an MS SQL query based on query string parameters.

我当前正在使用要求任何有效查询字符串参数值的变量:如果参数不为空，则该变量构成SQL语句的一部分.如果为空，则变量保持空白.

I'm currently using variables that request the values of any valid query string parameters: if the parameter isn't empty, then that variable forms part of the SQL statement. If it is empty, the variable remains blank.

例如:

 var search = Request["search"].IsEmpty() ? "" : Request["search"];
 var price = Request["price"].IsEmpty() ? "" : Request["price"];

 string searchSQL = "WHERE item_name LIKE '%" + search + "%'";
 string priceSQL = " AND item_price < " + price;

 string sql = "SELECT * from Items " + searchSQL + priceSQL;

如果两个查询参数均有效，则此sql查询运行正常.但是，如果搜索参数为空且价格不是，则sql查询将变为无效:

If both query parameters are valid, then this sql query runs okay. But if the search parameter is empty and the price isn't, the sql query then becomes invalid:

SELECT * from Items AND item_price < 100

我的计划是使用多达6个不同的查询字符串参数，因此无法真正使用此代码段.这种方法的最佳做法是什么?这样可以创建有效的动态sql语句吗?

My plan is to use upto 6 different query string parameters, so this snippet of code can't really be used. What are the best practices for this type of method? Is it possible to create valid, dynamic sql statements this way?

推荐答案

您可以使用 null 参数:

select *
from items
where (@name is null or item_name like '%' + @name + '%')
  and (@price is null or item_price < @price)";

然后，您将添加参数，并为未给定的参数指定一个 null 值，这将使WHERE语句的这一部分不过滤任何内容，并且您没有每次都建立一个不同的SQL.

Then you would add the parameters, and specify a null value for those that haven't been given, this will make that part of the WHERE statement not filter anything, and you don't have to build up a different SQL every time.

这篇关于在ASP.NET中使用查询字符串参数形成动态SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！