Ok, this is a fantastic question. One we should keep as an interview question!
I have since Foxpro, VB, Access, SQL server and more systems have ALWAYS had to provide some kind of filter system.
This is really a business "101" type of question! Anyone writing software for a company? You will encounter this type of problem!
And of course, this issue looms:
In order to address all combinations of 8 columns, I need to have 8! (8*7*6*5*4*3*2*1) i.e. 40,000+ if-else scenarios.
yes, sort of!!!
And there is a great way to deal with this issue.
First up, one can write SQL or even a stored procedure, and setup to have "optional" parameters. It is however still messy.
That approach involves setting a default for the paramter - like say -1, and then where (@City = '-1' OR City = @City).
But, there is a better way.
As you note, if the user does NOT enter a value/select a check box/set a dropdown, then we are to "ignore" those choices.
In other words, we are "ADD UP" the filter choices so to speak.
So, say I have this filter - just plain jane markup:
Now, if you look closely, I have 6 possible choices (6! = 720 possible combinations).
(and love that you introduced n! (factorial) here - reminds me of high school math)
However, the trick is to keep in mind that the choices are cumulative!
Thus, all we have to do is write our filter code to "add" each control if filter, or ignore.
So, we can rather easy build up a filter (sql where clause).
the 2nd VERY important issue is we STILL need to use SQL parameters (no sql injection allowed - right!!!).
so, the way the filter code looks is like this:
Now, for the check boxes, we actually don't need SQL paramters. And I don't have any input text box, but for safety, the follow code shows I can BOTH add SQL parmaters on the fly and also direct sql condiations (for when I don't need a paramter).
Most don't realize that the Sqlcommnd allows you to add parmaters, and it DOES NOT care about the SQL it has (yet!!!).
So, the code looks like this. We can have 2, or 20 filters. All you do is just keep on appending/adding to the where clause string.
And for any case to avoid sql injeciton then simple add/use a @ paramter.
So, the code looks like this:
protected void cmdSearch_ServerClick(object sender, EventArgs e)
{
string strSQL = "SELECT * FROM tblHotels ";
string strOrderBy = " ORDER BY HotelName";
string strWhere = "";
SqlCommand cmdSQL = new SqlCommand();
if (chkDescripiton.Checked)
{
if (strWhere != "") strWhere += " AND ";
strWhere += "(Description is not null)";
}
if (chkActiveOnly.Checked)
{
if (strWhere != "") strWhere += " AND ";
strWhere += "(Active = 1)";
}
if (chkBalconyOnly.Checked)
{
if (strWhere != "") strWhere += " AND ";
strWhere += "(Balcony = 1)";
}
if (chkWaterOnly.Checked)
{
if (strWhere != "") strWhere += " AND ";
strWhere += "(LakeView = 1)";
}
if (cboCity.SelectedIndex >0)
{
if (strWhere != "") strWhere += " AND ";
strWhere += "(City = @City)";
cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = cboCity.Text;
}
if (cboProvince.SelectedIndex >0)
{
if (strWhere != "") strWhere += " AND ";
strWhere += "(Province = @Province)";
cmdSQL.Parameters.Add("@Province", SqlDbType.NVarChar).Value = cboProvince.Text;
}
cmdSQL.CommandText = strSQL;
if (strWhere != "")
cmdSQL.CommandText += " WHERE " + strWhere;
cmdSQL.CommandText += strOrderBy;
LoadGrid(cmdSQL);
}
Note how we written the code. We just add/have/write a code stub for each filter control. We just keep on appending (building up) the where clause.
And the code works if NO filter is selected.
And note how we handle the optional @ parmaters. In those cases, you add BOTH to the "where clause" we are building up, and also add the paramter to the sql where clause!!
So, after the above code runs, we call loadgrid, and that is this simple code:
void LoadGrid(SqlCommand cmdSQL)
{
DataTable rstData = MyRstP(cmdSQL);
GridView1.DataSource = rstData;
GridView1.DataBind();
}
And of course, our little helper routine MyRstP (it just returns a table on passed SqlCommand object)
public DataTable MyRstP(SqlCommand cmdSQL)
{
DataTable rstData = new DataTable();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (cmdSQL)
{
cmdSQL.Connection = conn;
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
return rstData;
}
And that is quite much it.
The results thus look like this: