C# MySql statement. Spot my error!

Question

Trying to do a select statement from a MySql database. The following works when i plug variables in directly:

MySqlCommand playerSearch = conn.CreateCommand();
playerSearch.CommandText = @"select username from players where username like '%" + username + "%'";
playerSearch.Prepare();
// Execute the command, get the restuls, etc.

However if i try to do it the prefered way with paramater adding, such as:

MySqlCommand playerSearch = conn.CreateCommand();
playerSearch.CommandText = @"select username from players where username like @username";
playerSearch.Prepare();
playerSearch.Parameters.AddWtihValue("@username", "'%" + username + "%'");
// Execute the command, get the restuls, etc.

I get no results back from the query. Thus far i haven't figured out why this isn't working. Any advice?

Answer 1

Build the wildcards into the query, rather than the data:

MySqlCommand playerSearch = conn.CreateCommand();
playerSearch.CommandText = @"select username from players where username like '%' + @username + '%'";
playerSearch.Prepare();
playerSearch.Parameters.AddWtihValue("@username", username);
// Execute the command, get the restuls, etc.

Even better, avoid writing this kind of LIKE query entirely. Initial wildcards in your query prevent the database from using any indexes. Use a real full-text search mechanism. LIKE is a poor substitute.

Answer 2

Well, just run Mysql and enter query directly, than mysql server will give you information where your syntax error is, if there is one. The best way is, let program to show your query as plain text (I mean at runtime) and then put it into mysql command line.

Answer 3

Drop the inner quotes.

playerSearch.Parameters.AddWtihValue("@username", "%" + username + "%");