C# database search (how to create query string)

Question

I am trying to make a MySQL database search in my C# WindowsForms app. I have 16 search values (for example: age, status, height, weight, ... etc.). The thing is, that I do not need to fill all values to perform the search and I do not know how to set up the query string. If I will fill fields only for "age" and "status" It should look something like:

 string querySearch = " SELECT * FROM table WHERE age=" + value1 +"status="+value2;
 MySqlCommand cmdSearch = new MySqlCommand(querySearch, conn);
 readerSearch = cmdSearch.ExecuteReader();
 readerSearch.Read();

but I dont know how many fields would the user like to fill, so I have to write the query (WHERE part) more dynamically. Can anyone please suggest how to approach this? Thank you.

You should never concatenate inputs into SQL; if possible *always* use parameters - the above looks ripe for SQL injection — Marc Gravell, Mar 24 '15 at 07:58

CompuChip · Answer 1 · 2015-03-24T08:00:12.063

A trick I often use is ending the query string with a trivial condition, and then adding the fields as I need them. For example

// Note the trivial condition
string query = "SELECT * FROM table WHERE 1 = 1";

// Add any number of "AND ..." clauses, as needed.
if( age.HasValue ) query += " AND age = " + age.Value;
if( name.HasValue) query += " AND name LIKE \"%" + name.Value + "\"";
if( id.HasValue) query += " AND id = " + id.value;
// ...

MySqlCommand cmdSearch = new MySqlCommand(querySearch, conn);
readerSearch = cmdSearch.ExecuteReader();
readerSearch.Read();

Note that is is a very simple example meant just to illustrate the "WHERE 1 = 1" trick. In reality you should always use query parameters, or at the very least validate your user input. Also the string concatenation may be replaced by a StringBuilder for better performance.

No problem, you had a good point so I added some bold italics ;) — CompuChip, Mar 24 '15 at 08:01

score 1 · Answer 2 · edited May 23 '17 at 11:54

You could try to start with a basic query string and keep the conditions to add in a StringBuilder. If you find a condition to add you insert the condition in the stringbuilder and add a matching parameter in the list. At the end of your checks you could easily add the WHERE condition if needed and add the parameters to the command

List<MySqlParameter> prms = new List<MySqlParameter>();
StringBuilder sb = new StringBuilder();
string query = "SELECT * FROM table";

if(txtBoxStatus.Text.Trim().Length > 0)
{
   sb.Append(" status = @status AND ");
   prms.Add("@status", MySqlDbType.VarChar).Value = txtBoxStatus.Text.Trim();
}
if(txtBoxAge.Text.Trim().Length > 0)
{
   int age;
   if(Int32.TryParse(txtBoxAge.Text, out age))
   {
       sb.Append(" age = @age AND ");
       prms.Add("@age", MySqlDbType.Int).Value = age;
   }
}
.... so on for other parameters
....
.... and at the end 
MySqlCommand cmdSearch = new MySqlCommand(query + sb.ToString(), conn);
if(sb.Length > 0)
{
    // If you enter here you have one or more WHERE conditions 
    // AND a list of parameters to add to the query
    sb.Insert(0, " WHERE ");
    sb.Length -= 5; // remove the last ' AND '
    cmdSearch.Parameters.AddRange(prms.ToArray());
}
readerSearch = cmdSearch.ExecuteReader();
....

You should always use a parameterized query and not a string concatenation because that would lead to Sql Injection

score 0 · Answer 3 · answered Mar 24 '15 at 08:01

What about something like this:

string querySearch = " SELECT * FROM table "
addConditionToQuery(querySearch, "age", textboxAge.text);
addConditionToQuery(querySearch, "status", textboxStatus.text);
...

void addConditionToQuery(string Query, string ColumnName, string Value) {
    if(!String.IsNullOrWhiteSpace(Value)) {
      if(Query.IndexOf("WHERE")>-1) {
        Query += "WHERE ";
      } else {
        Query += "AND ";
      }
        Query += ColumnName +" = '" + Value + "' ";
    }
}

Note: this is only an example. you will need to implement a slightly different method for comparing different sql data types (such as integers).

score 0 · Answer 4 · answered Mar 24 '15 at 08:02

I think this may help:

create a bulid_where method

private string build_where(string where, string clause)
{
    if (clause == "")
    {
        return where;
    }

    string sql;

    if (where == "")
    {
        sql = " where ";
        sql += clause;
    }
    else
    {
        sql = where;
        sql += "and ";
        sql += clause;
    }

    return sql;
}

Call the method when building the sql sentence

     string where = "";


    where = build_where(where, age);
    where = build_where(where, status);

...

score 0 · Answer 5 · answered Mar 24 '15 at 08:06

To follow best practice etc, you should use parameters; something like this should work:

bool first = true;
var sb = new StringBuilder("SELECT * FROM table");
if(age != null) { // or >= 0, or whatever
    sb.Append(first ? " WHERE " : " AND ").Append("age=@age");
    first = false;
    cmd.Parameters.AddWithValue("@age", age);
}
if(status != null) { // or != "", or whatever
    sb.Append(first ? " WHERE " : " AND ").Append("status=@status");
    first = false;
    cmd.Parameters.AddWithValue("@status", status);
}
cmd.CommandText = sb.ToString();
using(var reader = cmd.ExecuteReader()) {
    // etc
}

maam27 · Answer 6 · 2015-03-24T08:30:54.963

I had something like that in a program once, the way I went about doing it is like this:

//viewselected is list of strings
//combobox1 has all items from the viewselected as options

//start building the select query
        string command = "SELECT ";
        for (int i = 0; i < viewselected.Count; i++)
        {//add each selected database item
            if (i == viewselected.Count - 1)
            {//if it's the last item in the list don't add a comma
                command = command + viewselected[i];
                Console.WriteLine("Showing " + AcessStuff.viewselected[i]);
            }
            else
            {
                command = command + viewselected[i] + ",";
                Console.WriteLine("Showing " + AcessStuff.viewselected[i]);
            }
        }//add last pice of query command - the combobox has all the columns that are
         // in the list added so the user can select a column to sort it on
        command = command + " FROM ART ORDER BY " + comboBox1.Text.ToString() + "";

and then you would be able to use the string "command" as the sql command string. as long as you make sure to validate that there is at least 1 item in the list it should work fine.

NOTE: this one took all rows and not any specific ones but instead of using

command = command + viewselected[i] + ",";

you could use something like

command = command + viewselected[i] + "=@" + viewselected[i] + ",";

however this does expect you to have a variable of the same name as the database column (example column "ID" would have variable "@ID".

score 0 · Answer 7 · answered Mar 24 '15 at 08:29

0

I prefer this approach. I hate building SQL query in app

select * from [Table] where (filter1 is not null and Column1 = filter1) or     (filter2 is not null and Column2 = filter2) or (filter3 is not null and Column3 LIKE '%' + filter3) .. or ()

answered Mar 24 '15 at 08:29

Alex Lebedev

601
5
14

C# database search (how to create query string)

7 Answers7