c# parameterized query returns no data/ null values

Question

Using mysql, I am trying to get/set data using parameterized queries, but some of them are returning null, though they working without using parameterized queries. Here are the two I have I had issues with:

This one returns nothing frm the database:

// csvFolder looks ridiculous but this is actually way it needs to look like to work
string csvFolder = "C:\\\\\\\\Users\\\\\\\\fakename\\\\\\\\Desktop\\\\\\\\csvScanner\\\\\\\\testeappfolder"
MySqlCommand deleteCheck = new MySqlCommand("SELECT * FROM email_list WHERE filepath LIKE '%@csvfolder%' AND expired IS NULL", conn);
deleteCheck.Parameters.AddWithValue("@csvfolder", csvFolder);
MySqlDataReader deleteRdr = deleteCheck.ExecuteReader();

But, concatenating the value in will work:

MySqlCommand deleteCheck = new MySqlCommand("SELECT * FROM email_list WHERE filepath LIKE '%" + csvFolder + "%' AND expired IS NULL", conn);
MySqlDataReader deleteRdr = deleteCheck.ExecuteReader();

This one returns null from the ExecuteScalar.

MySqlCommand getId = new MySqlCommand("SELECT * FROM email_list WHERE filepath = '@filepath' ORDER BY expired DESC LIMIT 1", conn);
getId.Parameters.AddWithValue("@filepath", deletedPath.Replace(@"\", "\\\\"));
int id = int.Parse(getId.ExecuteScalar().ToString());

But also works fine when concatenating.

I believe it has to be something with what I am passing (paths with \'s that cause various weirdness), but I cannot see exactly what it is. I have many other parameterized queries for paths that work correctly, but these two do not work. Anyone see something like this before

Answer 1

Because your parameter was within a quoted part of your query, it doesn't get treated like a parameter, It is treated as a literal string that you are searching for. You need to concatenate it like this...

MySqlCommand deleteCheck = new MySqlCommand(
    "SELECT * FROM email_list WHERE filepath LIKE '%'+@csvfolder+'%' AND expired IS NULL", conn);

Similar in the ExecuteScalar example:

MySqlCommand getId = new MySqlCommand(
    "SELECT * FROM email_list WHERE filepath = @filepath ORDER BY expired DESC LIMIT 1", conn);

Other tips:

1. MySqlCommand and MySqlDataReader are both IDisposable so each should be in a using block.
2. You can use literal string to make the string a little easier to follow: string csvFolder = @"C:\\\\Users\\\\fakename\\\\Desktop\\\\csvScanner\\\\testeappfolder" (this still doesn't look right, but it's equivalent to the value you've given).
3. You may want to read can we stop using AddWithValue.
4. SELECT * with ExecuteScalar (which only used the first value from the first row) is at risk of breaking in future: safer to select the particular column you are intending to work with.
5. You're using LIKE to look for a value containing your parameter when the value would appear to be a rooted path. If the value will start with that value, drop the initial wildcard.