ASP.Net Perform SQL (sub)-Query

I'm currently trying to create a "search engine" via a website created using ASP.net and MYSQL, I'm fairly new to both and I have this problem. I have created a page fo开发者_StackOverflowr input from the user so that they can search via typing in one or all the different search fields. (Make, model, color, ..etc.) With this information from the user what is the best way to create an SQL subquery to search based on what fields were typed in. I've looked around and I've seen many solutions to just write a UNION call where one param is NULL but I'd rather not do the 8! sql queries that would be required to get all of the possibilities. If someone has a suggestion that would be greatly appreciated.

---

Without seeing the schema we cannot say for sure, but I'm not convinced you need a subquery. The search could work by forcing the user to choose a make, model etc. via drop lists. In that case, what you need is a series of AND statements based on the criteria selected:

Select ...
From MyTable
Where Make = @Make
    And Model = @Model
    ...

You would build this SQL statement in C# code based on the parameters asked by the user excluding ones not specified. I.e., if the user did not choose a Model, you wouldn't include that And statement.

---

Your SQL assembly code might look like

private function Search( string make, string model, ... )
{
    var sql = new StringBuilder();
    sql.AppendLine( "Select .... " );
    sql.AppendLine( "From MyTable" );
    sql.AppendLine( "Where 1=1" ); //this is just to add the Where keyword

    var parameters = List<DbParameter>();
    if ( !string.IsNullOrEmpty( make ) )
    {
        sql.AppendLine( " And Make = @Make " );
        parameters.AddWithValue( "@Make", make );
    }

    if ( !string.IsNullOrEmpty( model ) )
    {
        sql.AppendLine( " And Model = @Model " );
        parameters.AddWithValue( "@Model", model );
    }
    ...
}

Another appoach is to simply build the entire SQL statement checking for nulls:

var sql = new StringBuilder();
sql.AppendLine( "Select .... " );
sql.AppendLine( "From MyTable" );
sql.AppendLine( "Where ( @Make Is Null Or Make = @Make" )
sql.AppendLine( "   And ( @Model Is Null Or Model = @Model" )
...