Parameterized Query for MySQL with C#

Question

I have the code below (I've included what I believe are all relevant sections):

private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;";
public bool read(string id)
{
    level = -1;
    MySqlCommand m = new MySqlCommand(readCommand);
    m.Parameters.Add(new MySqlParameter("", val1));
    m.Parameters.Add(new MySqlParameter("", val2));
    MySqlDataReader r = m.ExecuteReader();
    if (r.HasRows)
        level = Convert.ToInt32(r.GetValue(0).ToString());
    r.Close();
    return true;
}

When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?

Answer 1

Try this instead:

private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = @param_val_1 AND VAL_2 = @param_val_2;";
public bool read(string id)
{
    level = -1;
    MySqlCommand m = new MySqlCommand(readCommand);
    m.Parameters.AddWithValue("@param_val_1", val1);
    m.Parameters.AddWithValue("@param_val_2", val2);
    level = Convert.ToInt32(m.ExecuteScalar());
    return true;
}

Answer 2

You need to use named parameters in your query. E.g.:

String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ?param1 AND VAL_2 = ?param2";

Then, pass the parameter names when you instantiate your MySqlParameter objects like so:

m.Parameters.Add(new MySqlParameter("param1", val1));

Answer 3

I don't think the MySql.Data classes support unnamed parameters. If you're keen to use them, you could access your MySql db via the Odbc drivers, they support this.

You'll need to name the parameters in your query:

"SELECT LEVEL FROM USERS WHERE VAL_1 = @val1 AND VAL_2 = @val2;"

I've chosen the param indicator "@", but recent versions of MySql.Data support both "@" and "?".

Then update your param constructor to pass in the correct param name (you don't need to include the param indicator here, although it doesn't make any difference if you do).

m.Parameters.Add(new MySqlParameter("val1", val1));

PS. You prob know this already, or it was just omitted in the snippet, but I think you forgot to call Read on your instance of ExecuteReader.