Generating MySQLCommand Strings

Question

Is there a proper way to generate a valid MySQL string, and then pass values through a MySqlCommand?

I currently use a method like this one, and just stack more and more conditions on if I need more columns searched. It looks so gross I'd assume there's a better way of accomplishing the same thing.

private static string SelectUser(User u)
{
 bool and = false;
 string cmd = "SELECT * FROM `database`.`users` WHERE ";
 if (u.ID != null)
 {
 cmd += "`UserID` LIKE @0";
 and = true;
 }
 if (u.Username != null)
 {
 if (and) { cmd += " AND "; }
 cmd += "`Username` LIKE @1";
 and = true;
 }
 if (u.Role != null)
 {
 if (and) { cmd += " AND "; }
 cmd += "`Role` LIKE @1";
 and = true;
 }
 if (u.Department != null)
 {
 if (and) { cmd += " AND "; }
 cmd += "`Departments` LIKE @1";
 and = true;
 }
 if (u.Template != null)
 {
 if (and) { cmd += " AND "; }
 cmd += "`Template` LIKE @1";
 and = true;
 }
 return cmd;
}

I've found a few methods that almost work, like this answer, but none of them allow me to modularly pass the values through MySqlCommand.Parameters.

Answer 1

A couple of things to try.

First, do this:

 const string baseQuery = "SELECT * from database WHERE 1=1 AND ";

Then do

 var andClauses = new List<string>();
 if (U.id != null) andClauses.Add("UserID = @0");
 if (U.username != null) andClauses.Add("Username = @1");
 /* more like this */
 var query = baseQuery + string.Join (" AND ", andClauses);

That will get you a concatenated set of ANDs in your query. It's a little cleaner looking than what you have in your question.

Another trick to consider: Write your query like this

const string baseQuery = @"
 SELECT whatever
 FROM mytable
 WHERE (userid = @0 OR @0 <= 0)
 AND (username = @1 OR LENGTH(@1) <= 0)
 AND (department = @2 OR LENGTH(@department) <= 0) ";

These (column = @val OR @val <= 0) WHERE-clauses let you put in an empty string or a zero integer to effectively skip the clause.

The string = @"value"; construct lets you write a multiline string in c# -- incredibly handy for formatting your queries in a way where you have a chance of reading them.