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));
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?
C programming language is a machine-independent programming language that is mainly used to create many types of applications and operating systems such as Windows, and other complicated programs such as the Oracle database, Git, Python interpreter, and games and is considered a programming foundation in the process of ...
C is a general-purpose language that most programmers learn before moving on to more complex languages. From Unix and Windows to Tic Tac Toe and Photoshop, several of the most commonly used applications today have been built on C. It is easy to learn because: A simple syntax with only 32 keywords.
In the real sense it has no meaning or full form. It was developed by Dennis Ritchie and Ken Thompson at AT&T bell Lab. First, they used to call it as B language then later they made some improvement into it and renamed it as C and its superscript as C++ which was invented by Dr. Stroustroupe.
C is more difficult to learn than JavaScript, but it's a valuable skill to have because most programming languages are actually implemented in C. This is because C is a “machine-level” language. So learning it will teach you how a computer works and will actually make learning new languages in the future easier.
This is not possible in MySQL. You can create a required number of parameters and do UPDATE ... IN (?,?,?,?). This prevents injection attacks (but still requires you to rebuild the query for each parameter count).
Other way is to pass a comma-separated string and parse it.
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.
So, something like:
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(...));
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With