CODEWITHSUNDEEP
c#sql
GenericEric
GenericEric

Reputation: 31

Use * as a wildcard when filtering data

I am looking to perform a search on multiple columns from an access database in C#. The data is built in rows with each column either holding relevant data or "*" as a wildcard.

So as a rough example:

If i had data that was (, indicates new cell) Ford, Fiesta, *, 1998 then if i had a value...

Ford, Fiesta, Petrol, 1998

it would find and display the row of data.

Currently I am trying:

string sql = "SELECT * FROM [mydatabase]  
WHERE Manufacturer ='" + textBox1.Text +  
"' OR Manufacturer='*' AND Model ='" + textBox2.Text +  
"' OR Model='*' AND Fuel ='" + textBox3.Text +  
"' OR Fuel='*' AND Year='" + textBox4.Text + "' OR Year='*'";

But this is bringing up all values rather than filtering them down. Is there a way of using and if/else within the query instead of OR?

Upvotes: 0

Views: 342

Answers (2)

WraithNath
WraithNath

Reputation: 18013

If you want to use a wild card, I would just exclude it from the where clauses.

Alternateively, if you want to search all columns as one string you could add them all to a new column in the select list.

for example:

        public void GetCars(string manufacturer, string model, string fuel, DateTime? year, string searchString)
        {
            string query = @"
                           SELECT *,
                           ISNULL([Manufacturer],'') + ' ' + ISNULL([Model],'') + ' ' ISNULL([Fuel],'') + ' ' ISNULL('Year', '') AS [SearchString]
                           FROM [MyDatabase]
                           WHERE [Manufacturer]=@Manufacturer ";

            if (!String.IsNullOrEmpty(model))
                query += @"AND [Model]=@Model ";

            if (!String.IsNullOrEmpty(fuel))
                query += "AND [Fuel]=@Fuel ";

            if (year.HasValue)
                query += "AND [Year]=@Year ";

            if (!String.IsNullOrEmpty(searchString))
                query += @"AND [SearchString] Like '%@SearchString%' ";

            using (SqlCommand sqlCommand = new SqlCommand(query))
            {
                sqlCommand.Parameters.AddWithValue("@Manufacturer", manufacturer);

                if (!String.IsNullOrEmpty(model))
                    sqlCommand.Parameters.AddWithValue("@Model", model);

                if (!String.IsNullOrEmpty(fuel))
                    sqlCommand.Parameters.AddWithValue("@Fuel", fuel);

                if (year.HasValue)
                    sqlCommand.Parameters.AddWithValue("@Year", year.Value);

                if (!String.IsNullOrEmpty(searchString))
                    sqlCommand.Parameters.AddWithValue("@SearchString", searchString);

                //Execute to data table etc
            }
        }

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156938

Instead of Manufacturer ='" + textBox1.Text + "' OR Manufacturer='*', you can use coalesce, which is sort of an if/else:

string sql = "... Manufacturer = coalesce('" + textBox1.Text + "', '*') ...";

In that way, you only need ands, and not mixed with or. This is probably giving the problem now, since the ors cause the and not to be evaluated.

You can also add parenthesis around the and, so the or will be applied only inside the parenthesis:

 string sql = "... where (Manufacturer ='" + textBox1.Text + "' OR Manufacturer='*') and ...";

Note you should use parameterized queries, so you would get something like this:

command.CommandText = "select * from ... where Manufacturer = coalesce(@mgr, '*') and ...";
command.Parameters.Add(new SqlParameter("mgr", textBox1.Text));

Upvotes: 1

Related Questions