Question A combination of two search methods

[Mimi]

In the application, I have two ways to search, one using the btnSearch button and the other using inserting values into textBox1. Here are both types of searches:

 DataTable dt = new DataTable();

private void btnHladaj_Click(object sender, EventArgs e)
{

     SqlCommand cmd = new SqlCommand("SELECT * FROM Databaza", con);

     DataTable dt = new DataTable();

     con.Open();
     SqlDataReader dr = cmd.ExecuteReader();
     dt.Load(dr);
     con.Close();

     dataGridView1.DataSource = dt;


     BindingSource bs = new BindingSource();
     bs.DataSource = dataGridView1.DataSource;

     string filter = "";

     // Check if text fields are not null before adding to filter.
     if (!string.IsNullOrEmpty(comboDruh.Text))
     {
         filter += dataGridView1.Columns["Druh"].HeaderText.ToString() + " LIKE '%" + comboDruh.Text + "%' ";
     }
     if (!string.IsNullOrEmpty(comboKategoria.Text))
     {
         if (filter.Length > 0) filter += "AND ";
         filter += dataGridView1.Columns["Kategoria"].HeaderText.ToString() + " LIKE '%" + comboKategoria.Text + "%' ";
     }
     if (!string.IsNullOrEmpty(comboMeno.Text))
     {
         if (filter.Length > 0) filter += "AND ";
         filter += dataGridView1.Columns["Meno"].HeaderText.ToString() + " LIKE '%" + comboMeno.Text.Substring(0, comboMeno.Text.Length - 5) + "%' ";
     }

     bs.Filter = filter;
     dataGridView1.DataSource = bs;
private void textBox1_TextChanged(object sender, EventArgs e)
{
    DataView dv = dt.DefaultView;
    dv.RowFilter = string.Format("Poznamka LIKE '%{0}%'", textBox1.Text);
    dataGridView1.DataSource = dv.ToTable();
}

1. I need it to show it to me in the datagride of data after searching with the btnSearch button (this works for me).
2. After inserting a value into texBox1, it looks in those records that were searched by the btnSearch branch. (now looking for me in the whole database) I also need to avoid this in the datagride.

I thought to put the first filtered data in a temporary table and search that table using textBox1_TextChanged. I tried it, so far I can't write it correctly so that it works for me.

 

[Skydiver]

Show us your temporary table attempt.

 

[jmcilhinney]

Firstly, that second option is bad. Creating new DataViews all the time is pointless. That's because, when you bind a DataTable, the data comes from its DefaultView anyway. You should simply bind the DataTable in the first place and then any changes you make to the RowFilter of its DefaultView will be reflected in the UI.

The reason for all this is that complex data-binding in Windows Forms uses the IList or IListSource interfaces. If you bind an IList object then the data it contains is what you see. If you bind an IListSource object, its GetList method is called and that returns an IList object and the data that contains is what you see. As you may have guess by now, DataView implements IList while DataTable implements IListSource and its GetList method returns its DefaultView.

Better still, bind the DataTable to a BindingSource and the BindingSource to your UI, then set the Filter property of your BindingSource. The BindingSource would be added in the designer.

 

[jmcilhinney]

I'm not clear on why you need a temporary table. If you search the database and get the results into a DataTable and bind that, then filter that DataTable locally, what exactly is the issue? I'm not sure why you would want that secondary filter to go back to the database when it can all happen in the application. Is it because you think the initial query produces too much data to pull down into the ap0plication?

 

[jmcilhinney]

I just looked more closely at your code and realised that you do have a BindingSource. I'm now even more confused. Query the database in the Click event handler of the Button and populate a DataTable and bind that to the UI via the BindingSource. Set the Filter property of the BindingSource to filter locally. That seems to be all you need as far as I can tell. Am I missing something?

Also, it's not a good idea to filter on the TextChanged event of a TextBox. If the user wants to type in multiple characters then you're going to filter multiple times for no reason. I suggest that you start/restart a Timer on the TextChanged event and then filter on the Tick event. You can make the Interval fairly small, e.g. 250 milliseconds, so it gives the user time to type multiple characters without filtering in between but doesn't make them wait a long time for the filtering to happen once they stop typing.