update a table by using for loop with parameters in C#-CodePudding

I have a table with some columns like this.

now I want to use a for loop to set

out_0 = 0, 
out_1 = 1, 
out_2 = 2, 
out_3 = 3, 
out_4 = 4

so I update it with such code as

string sql = "update exchange_out set @column = @id where member_id = 6;";

SqlCommand cmd = new SqlCommand(sql, connet);
cmd.Parameters.Add("@column", SqlDbType.NVarChar);
cmd.Parameters.Add("@id", SqlDbType.Int);

int n = 0;

for (int i = 0; i < 5; i  )
{
    cmd.Parameters["@column"].Value = "out_"   i;
    cmd.Parameters["@gid"].Value = i;

    n = cmd.ExecuteNonQuery();                
    MessageBox.Show(""   n);
}

but it didn't write any data into the table while it literally did five times of updating, because the messagebox returns "1" five times.

finally I solve this by

    for (int i = 0; i < 5; i  ){
        sql = string.Format("update exchange_out set {0} = {1} where member_id = 6", "out_"   i, i);
}

but I'm still wondering why it didn't work by adding parameters?

any respond will be appreciated. :)

CodePudding user response：

I'm still wondering why it didn't work by adding parameters?

Identifiers such as table and column names cannot be parameterized in this way, only data. Your attempt effectively runs a query like this:

update exchange_out set 'out_1' = 1 where member_id = 6;

It's the same in any programming language:

var data1 = "hello";
var whichData = "1";

Console.WriteLine(data whichData); //it doesn't compile; you cannot programmatically build a variable name `data1` in this way

The way you found is reasonably the only way but you should still parameterize the data:

using var cmd = new SqlCommand(sql, connet);
cmd.Parameters.Add("@data", SqlDbType.NVarChar);
cmd.Parameters.Add("@id", SqlDbType.Int);

for (int i = 0; i < 5; i  ){
    sql = string.Format("update exchange_out set out_{0} = @data where member_id = @id", i);
    cmd.CommandText = sql;
    cmd.Parameters["@data"].Value = ...
    cmd.Parameters["@id].Value = 6;

    ...

You could also start with an SQL stub like "UPDATE t SET " and repeatedly concatenate on identifiers and parameters:

using var cmd = new SqlCommand(sql, connet);
cmd.Parameters.Add("@data", SqlDbType.NVarChar);
cmd.Parameters.Add("@id", SqlDbType.Int);
var sql = "UPDATE exchange_out SET ";
for (int i = 0; i < 5; i  ){
    sql  = string.Format("out_{0} = @data{0},", i);
    cmd.Parameters["@data" i].Value = ...
}
sql = sql.TrimEnd(',');
sql  =  " where member_id = @id";
cmd.Parameters["@id"].Value = 6;
cmd.CommandText = sql;

    ...

This does the update in one operation, running a query like UPDATE t SET out_1 = @data1, out_2 = @data2 ...

These are safe from SQL injection because your code controls the entire SQL; there isn't any capacity for a user to provide '; DROP TABLE Students;-- as the {0} going into the identifier in this case but take care that you don't arrange for it to be possible (don't let the user provide identifier text)..

Your non-parameter attempt is also safe from SQL injection in this case by virtue of inserting intergers that you control, rather than strings you don't, but be careful you don't universally apply the technique and one day include user-suppied strings. If you do find yourself in that suitable you should use something like a whitelist of user input - any string identifier provided by the user that isn't whitelisted should not be put in the SQL