Creating an advance search in WinForms

Question

I am trying to create an advanced search page to search up data. I know I can create one SQL statement, but that will require me to have all fields filled. I am trying to implement where the user can input into any field or multiple fields in order to get results. I am also using SQLite. Any ideas?

Keep in mind that the image shown below is what I am trying to implement, and the black square is the advance search section.

The quick search above works.

Answer 1

Build your query conditional on the contents of each UI element, ignoring ones left blank by the user.

var filters = new List<string>();

if ( !String.IsNullOrWhiteSpace( firstNameTextBox.Text ) ) 
    filters.Add( "firstName like '%" + firstNameTextBox.Text + "%'";

// ... repeat for each advanced search element ...

var query = "select * from sometable";
if ( filters.Count > 0)
    query += " where " + String.Join( " and ", filters );

Answer 2

You can just use optional parameters for your stored procedure and check if they are NULL in your WHERE clause. If the parameter is NULL then that part of the WHERE clause evaluates to true and moves on to the next field. If a value is supplied for that parameter, then it tries to evaluate it against the field.

SELECT Foo, Bar
FROM SomeTable
WHERE (@FirstName IS NULL OR FirstName LIKE '%' + @FirstName + '%')
    AND (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
    AND (@Advisor IS NULL OR Advisor LIKE '%' + @Advisor + '%')
etc.