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

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
    MySqlConnection con = new MySqlConnection("server=localhost;User Id=root;database=result;password=1234");
    con.Open();

    MySqlCommand cmd = new MySqlCommand("Select * from users where username=?username and password=?password", con);
    cmd.Parameters.Add(new MySqlParameter("username", this.Login1.UserName));
    cmd.Parameters.Add(new MySqlParameter("password", this.Login1.Password)); 

    MySqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows ==true)
    {
        e.Authenticated = true;
    }
}

Answer 3

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 4

m.Parameters.AddWithValue("parameter",value) 

will be better option for parametrized query.

Answer 5

If you want to execute the sql many times, then you should use this way:

conn.Open();
cmd.Connection = conn;

cmd.CommandText = "INSERT INTO myTable VALUES(NULL, @number, @text)";
cmd.Prepare();

cmd.Parameters.AddWithValue("@number", 1);
cmd.Parameters.AddWithValue("@text", "One");

for (int i=1; i <= 1000; i++)
{
    cmd.Parameters["@number"].Value = i;
    cmd.Parameters["@text"].Value = "A string value";

    cmd.ExecuteNonQuery();
}

First time is without "ExecuteNonQuery" just adding the parameters with faked values, then inside the loop you add the real values.

See this link: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html

Answer 6

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.