KerDam KerDam - 4 months ago 11
SQL Question

MySQLConnector c# update MySqlCommand parameters

I have a foreach loop in C# wich execute a MySql command to check if the result exist, the problem is I dont know how to update the value in the parameters corresponding to

@id_line
an error is thrown at the second iteration

foreach (String[] infos in ReferenceLine.getList())
{
cmd.Parameters.AddWithValue("@id_line", infos[0].ToString());
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows) { return true; }
}
}



I thought of checking if the value exist and deleting it but I dont find this solution clean


Thanks

Answer

Move the declaration of the parameter outside the loop without giving an explicit value, then inside the loop just update the Value property without redeclaring the parameter

cmd.Parameters.AddWithValue("@id_line", "");
foreach (String[] infos in ReferenceLine.getList())
{

    cmd.Parameters["@id_line"].Value = infos[0].ToString();
    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows) { return true; }
    }
}

However, it seems possible to completely avoid this loop with a more precise query with a WHERE condition.

Consider also to use

 cmd.Parameters.Add("@id_line", MySqlDbType.VarChar);

instead of AddWithValue. AddWithValue has some drawbacks that suggest to limit its use.

Comments