用于搜索的SQL返回错误的结果

Question

我在访问数据库中有3个表:Item,Recipe和ItemInRecipe-其中包含recipeId和itemId(配方中的项目)。

我在我的Dbf类中有这个方法，这意味着返回一个食谱id列表，其中包含itemList中的所有项目:

public static List<int> GetTheRecipesIdsWithAllItems(List<string> itemList)
{
List<int> recipeIdList = new List<int>();
string query = "SELECT recipeId FROM ItemInRecipe WHERE itemId IN " +
"(SELECT itemId FROM Item WHERE itemName IN (" +
string.Join(",", itemList.ConvertAll(i => "'" + i + "'")) +
")) GROUP BY recipeId HAVING COUNT(itemId) = " + itemList.Count;
DataTable dt = Dbf.GetInfo_fromTable(query); //returns datatable with the information
foreach (DataRow dr in dt.Rows)
{
int recipeId = int.Parse(dr["recipeId"].ToString());
recipeIdList.Add(recipeId);
}
return recipeIdList;
}

这是在我的服务器WebService1:

[WebMethod]
public List<int> GetTheRecipesIdsWithAllItems(List<string> itemList)
{
return Dbf.GetTheRecipesIdsWithAllItems(itemList);
}

我尝试在我的aspx页面中使用该方法:

public localhost.WebService1 w = new localhost.WebService1();
protected void selectItems_Click(object sender, EventArgs e)
{
List<string> l = GetList(); //gets a list of items names
int[] recipeIdList = w.GetTheRecipesIdsWithAllItems(l.ToArray());
Recipe[] recipes = w.GetRecipesByIds(recipeIdList.ToArray()); //returns a list of recipes from their ids
Session["recipeswithitems"] = recipes;
}

当试图从会话中使用列表时，它看起来是空的，我多次更改SQL，但找不到它不起作用的原因。

Answer 1

您的主要问题似乎是缺乏参数化，这导致您的查询返回不正确的结果。

首先创建一个表类型

CREATE TYPE dbo.ItemList (itemName varchar(100) NOT NULL PRIMARY KEY);

然后将其作为参数传递。表类型有点复杂:您需要创建包含数据的DataTable。

public static List<int> GetTheRecipesIdsWithAllItems(List<string> itemList)
{
List<int> recipeIdList = new List<int>();
const string query = @"
SELECT
ir.recipeId
FROM ItemInRecipe ir
JOIN Item i ON i.itemId = ir.itemId
JOIN @items it ON it.itemName = i.itemName
GROUP BY
ir.recipeId
HAVING COUNT(*) = @count;
";
var dt = new DataTable { Columns = { { "itemName", typeof(string) } } };
foreach (var item in itemList)
dt.Rows.Add(item);
using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(query, conn))
{
comm.Parameters.Add(new SqlParameter("@items", SqlDbType.Structured)
{
Value = dt,
TypeName = "dbo.ItemList",
});
comm.Parameters.Add("@count", SqlDbType.Int).Value = itemList.Count;
conn.Open();
using (var reader = comm.ExecuteReader())
{
while (reader.Read())
{
recipeIdList.Add((int)reader["recipeId"]);
}
}
}
return recipeIdList;
}