DataGridView - Search and Load by multiple columns and only 1 Search button

Question

For example I have a Table with 2 columns:

• Person Age
• Person Name

Right now, in the Query screen, I have 2 textboxes and 2 different search buttons:

• Textbox 1: SearchByAge
• Textbox 2: SearchbyName

I have a different code for each Search button:

• Button 1:

  Int32 Age; int.TryParse(this.SearchByAge.Text, out Age); this.personsTableAdapter.FillBy_queryAge(this.persons.personsTable, Age);

• Button 2:

this.personsTableAdapter.FillBy_queryName(this.persons.personsTable,this.SearchByName.Text);

As you can see, I have 2 different queries added in the Table Adapter: FillBy_queryAge and FillBy_queryName.

FillBy_queryAge: SELECT NAME, AGE FROM PERSONS WHERE (AGE = @AGE) FillBy_queryName: SELECT NAME, AGE FROM PERSONS WHERE (NAME =@NAME)

I would like to use only 1 Search button which should look in both Textboxes and Load the DataGridView.

So it would be like: Textbox 1: 20 Textbox 2: Anna

Click [Search] -> DataGridView is loaded with all the records where Age is 20 and Name is Anna.

Could you help, please?

Answer 1

I think that my main issue was how to handle a null value in one of those fields. I used a query as below which solved the issue:

SELECT * 
  FROM PERSONS
 WHERE ((AGE LIKE '%' + ISNULL(@AGE, N'') + '%') OR(ISNULL(@AGE, '') = '')) 
       AND((NAME LIKE '%' + ISNULL(@NAME , N'') + '%') OR(ISNULL(@NAME , '') = ''))

The query builder automatically generates a bigger query with all possible scenarios. It may be too big if I use more fields but it works for now. Open to suggestions if there are better ways to do that avoiding performance issues.