how to write a single sql query based on combobox selection?

Question

I have a winform in which user input values through a combobox. I am assigning combobox value to search db. If there is no selection then the SQL server query should not use that column to filter.

example -

if (string.IsNullOrEmpty(combobox1.text)) {
  .....Select * from country
}
else if (combobox1.selectedindex > -1) {
   ....Select * from country where city_name = combobox.text
}

Is there a way to write a single query instead of using this multiple 'IF' conditions in case where user selects or doesn't select a value from combobox.

T.S. · Accepted Answer · 2017-03-31 04:05:44Z

3

It is important to parameterize as well:

private const string _select = "select * from country";

void DoSomething()
{
     string sql = string.Empty;
     if (combobox1.SelectedIndex > -1)
     {
         command.Parameters.AddWithValue("@1",  (string)combobox1.SelectedValue);
         sql = " where city_name = @1";
     }
     sql = _select + sql;
     command.CommandText = sql;
     command.Execute...
}

@un-lucky asked me how would I deal with many conditions - here is one way

var conditions = new List<string>();     
if (/* condition 1*/)
{
    command.Parameters.AddWithValue("@2",  (string)cboN.SelectedItem);
    conditions.Add("col1 = @2");
}
if (/* condition 2*/)
{
    command.Parameters.AddWithValue("@3",  textBoxN.Text);
    conditions.Add("col2 = @3");
}

if (conditions.Count > 0)
    sql = _select + " where " + string.Join(" AND ", conditions.ToArray());

edited Mar 31, 2017 at 4:05

answered Mar 31, 2017 at 3:32

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sujith karivelil Over a year ago

What happens if you have few more conditions?

T.S. Over a year ago

@un-lucky Then it is different question. There are many ways to solve it. One - build each condition to field = @parameter and add to list. Check if list contains any items, then add WHERE and join your list items using " AND " as delimiter. This is one solution

T.S. Over a year ago

@un-lucky actually, a bit different technique though.

sujith karivelil Over a year ago

Looks fine now..!

mindOfAi · Accepted Answer · 2017-03-31 03:17:27Z

0

You can use the shorthand if only if you have two conditions:

 string query = string.Format("Select * from country{0}", string.IsNullOrEmpty(combobox1.text) ? "" : " where city_name = " + combobox1.text);

Hope it helps!

answered Mar 31, 2017 at 3:17

mindOfAi

4,7322 gold badges19 silver badges27 bronze badges

1 Comment

T.S. Over a year ago

" where city_name = " + combobox1.text <-- incorrect

sujith karivelil · Accepted Answer · 2017-03-31 03:28:33Z

0

I think you have to try something like this with parameterization:

StringBuilder queryBuilder = new StringBuilder("Select * from country Where 1=1 ");
SqlCommand cmdSql = new SqlCommand();

if (combobox1.selectedindex > -1) 
{
   queryBuilder.Append(" And city_name = @city_name "); 
   cmdSql.Parameters.Add("@city_name", SqlDbType.VarChar).Value = combobox.text;  
}
else if(Condition 2)
{
   queryBuilder.Append(" And column2 = @col2 "); 
   cmdSql.Parameters.Add("@col2", SqlDbType.VarChar).Value = "some Value here;  
}
// Build the query like this
cmdSql.CommandText= = queryBuilder.ToString();
cmdSql.Connection = conObject;
// Here you can execute the command

answered Mar 31, 2017 at 3:28

sujith karivelil

29.1k7 gold badges60 silver badges90 bronze badges

Comments

Nas · Accepted Answer · 2017-03-31 03:57:35Z

-1

I have a sample, try it

string select = this.combobox1.GetItemText(this.combobox1.SelectedItem); cm1 = new SqlCommand("Select * from country where city_name=@select or @select is null", con);
            cm1.Parameters.Add("@select", SqlDbType.NVarChar, 50);
            cm1.Parameters["@select"].Value = select;
            dap = new SqlDataAdapter(cm1);
            ds = new System.Data.DataSet();
            dap.Fill(ds, "DATABASE");
            //DataGridView1.DataSource = ds.Tables[0]; get data

answered Mar 31, 2017 at 3:57

Nas

14 bronze badges

Collectives™ on Stack Overflow

how to write a single sql query based on combobox selection?

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related