C＃参数化查询MySQL的用`in`条款 [英] C# Parameterized Query MySQL with `in` clause

问题描述

我在其中转换为硬coded到应用程序，并建立在飞到参数​​化查询几个查询的过程。我有一个特定的查询，其中有一个在条款的麻烦：

I am in the process of converting several queries which were hard-coded into the application and built on the fly to parameterized queries. I'm having trouble with one particular query, which has an in clause:

UPDATE TABLE_1 SET STATUS = 4 WHERE ID IN (1, 14, 145, 43);

第一个参数是很容易的，因为它只是一个正常的参数：

The first parameter is easy, as it's just a normal parameter:

MySqlCommand m = new MySqlCommand("UPDATE TABLE_1 SET STATUS = ? WHERE ID IN (?);");
m.Parameters.Add(new MySqlParameter("", 2));

然而，第二个参数是一个整数列表重新presenting需要更新的行的ID。如何在一列整数通为一个单一的参数？或者，你将如何去建立这个查询，这样你就不必每调用它完全建立它，并可以prevent SQL注入攻击？

However, the second parameter is a list of integers representing the ids of the rows that need updating. How do I pass in a list of integers for a single parameter? Alternatively, how would you go about setting up this query so that you don't have to completely build it each and every time you call it, and can prevent SQL injection attacks?

推荐答案

您可以建立对飞的基础上（presumably）的参数变量数量parametrised查询，并遍历，要通过他们英寸

You could build up the parametrised query "on the fly" based on the (presumably) variable number of parameters, and iterate over that to pass them in.

那么，是这样的：

List foo; // assuming you have a List of items, in reality, it may be a List<int> or a List<myObject> with an id property, etc.

StringBuilder query = new StringBuilder( "UPDATE TABLE_1 SET STATUS = ? WHERE ID IN ( ?")
for( int i = 1; i++; i < foo.Count )
{   // Bit naive 
    query.Append( ", ?" );
}

query.Append( " );" );

MySqlCommand m = new MySqlCommand(query.ToString());
for( int i = 1; i++; i < foo.Count )
{
    m.Parameters.Add(new MySqlParameter(...));
}

这篇关于C＃参数化查询MySQL的用`in`条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！