MySQL Parameters.AddWithValue not working C#

Question

I have been trying my hand at writing the connecting code to connect with the database. It's been going pretty well, but I ran into a strange problem that I cant seem to fix.

When I try to add a parameter to my query it doesn't retrieve the data. But when I switch out the parameter it does retrieve the correct data.

The data access layer looks like this. (I know it is very insecure but I'm just getting started):

public static class UserDA
{
    private static MySqlCommand cmd = null;
    private static DataTable dt;
    private static MySqlDataAdapter da;

    public static User RetrieveUser(String username)
    {
        // Create the query
        const String query = "SELECT `Username`, `Password` FROM login WHERE `Username` = '@Username';";
        cmd = DbHelper.Select(
            query, 
            new Dictionary<String, String> 
            {
                {"@Username", username}
            });

        //Setup model
        User user = null;
        if (cmd == null) return null;

        // Get the data and add it to the user model
        dt = new DataTable();
        da = new MySqlDataAdapter(cmd);
        da.Fill(dt);
        foreach (DataRow dataRow in dt.Rows)
        {
            user = new Models.User(
                dataRow["Username"].ToString(),
                dataRow["Password"].ToString());
        }

        return user;
    }
}

And the Select method looks like this:

public static class DbHelper
{
    private static MySqlConnection _connection;
    private static MySqlCommand _cmd;
    private static DataTable _dataTable;
    private static MySqlDataAdapter _adapter;

    public static void EstablishConnection()
    {
        // Gets a connection to the database. Code works.
    }

    /// <summary>
    /// Give the query and a dict with the param name and value
    /// </summary>
    public static MySqlCommand Select(String query, Dictionary<String , String> param)
    {
        try
        {
            if (_connection != null)
            {
                _connection.Open();
                _cmd = _connection.CreateCommand();
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = query;
                foreach (KeyValuePair<String, String> keyValuePair in param)
                {
                    _cmd.Parameters.AddWithValue(keyValuePair.Key, keyValuePair.Value);
                }

                _cmd.ExecuteScalar();
                _connection.Close();
            }
        }
        catch (Exception e)
        {
            _connection?.Close();
        }
        return _cmd;
    }
}

And then finally the user model:

public class User
{
    public String Username;
    public String Password;

    public User(String username = "", String password = "")
    {
        Username = username;
        Password = password;
    }
}

The problem happens when I try to get the data from it. If I use the select query with the parameter it does not work, but as soon as I change the parameter to the actual value it retrieves it just fine. I am really scratching my head at this point. Did I forget a step somewhere?

Thanks!

Answer 1

Remove this from your code

  _cmd.ExecuteScalar();
                _connection.Close();

and just for the better coding style I higly recomend to change class User to

public class User
{
    public string Username {get; set;}
    public string Password {get; set;}

 public User()
{
}
    public User(string username, string password)
    {
        Username = username;
        Password = password;
    }
}
```

Answer 2

Try removing the quotation marks around @Username in your SQL code.

Before

SELECT `Username`, `Password` FROM login WHERE `Username` = '@Username';

After

SELECT `Username`, `Password` FROM login WHERE `Username` = @Username;

If that doesn't work, try changing your dictionary from <String, String> to <string, string>.