Too many Conditional Statements... How to Optimize the code?

[BilalAKhan]

Hi,

I have two DropdownLists. One is for Category and another is for Product. The concept is that with each Category and Product selected, the website will display the result on another page and show all the results present in the database.

With more than 25 Categories with me and approx 370 products in each category, I am putting conditional statement for each Category and Product so that the results should be taken from the database. This makes approx 9250 conditional statements for searching of product in database

I believe this kind of searching where I am using so many lines of codes and conditional statements makes my program extremely slow and inefficient.

May be I am not using the right approach for the searching.

Can anyone please tell me in this matter and let me know a technique where I can optimize my code? Any help will be appreciated. I am putting the sample code below:

The page with DropDownList has the code as below (I am putting a few lines):

If Category.SelectedItem.Value = "Plastic" And ProductList.SelectedItem.Value = "Bottles" Then

            'Redirect to the search result page

            Response.Redirect("Search Page.aspx?Category=Plastic&Product=Bottles")

        ElseIf Category.SelectedItem.Value = "Plastic" And ProductList.SelectedItem.Value = "Calculators" Then

            Response.Redirect("Search Page.aspx?Category=Plastic&Product=Calculators")

        ElseIf Category.SelectedItem.Value = "Plastic" And ProductList.SelectedItem.Value = "Dryer" Then

            Response.Redirect("Search Page.aspx?Category=Plastic&Product=Dryer")

The result page where the results are shown in the GridView is given below:

If Category = "Plastic" And Product = "Bottles" Then

            Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Product_Details").ConnectionString

            Dim con As New SqlConnection(connectionstring)

            Dim selectsql As String = "SELECT Product_Name,Size,Price FROM Prod_Det WHERE Products='Bottles' ORDER BY Prod_Det"
            Dim cmd As New SqlCommand(selectsql, con)
            Dim adapter As New SqlDataAdapter(cmd)

            'Fill the dataset

            Dim ds As New DataSet()

            adapter.Fill(ds, "Prod_Det")

            'Perform Binding

            SearchResultGrid.DataSource = ds
            SearchResultGrid.DataBind()

        ElseIf Category = "Plastic" And Product = "Calculator" Then

            Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Product_Details").ConnectionString

            Dim con As New SqlConnection(connectionstring)

            Dim selectsql As String = "SELECT Product_Name,Size,Price FROM Prod_Det WHERE Products='Calculator' ORDER BY Product_Name"
            Dim cmd As New SqlCommand(selectsql, con)
            Dim adapter As New SqlDataAdapter(cmd)

            'Fill the dataset

            Dim ds As New DataSet()

            adapter.Fill(ds, "Prod_Det")

            'Perform Binding

            SearchResultGrid.DataSource = ds
            SearchResultGrid.DataBind()

The code given above is just a sample. Kindly provide me the suggestion about how to optimize this code.

Regards,

Bilal A. Khan

[urtrivedi]

I think you have bunked your class in your college when your professor was teaching you variables.

No condition required at all, remove all if condition and keep following link in first page.

Response.Redirect("Search Page.aspx?Category=" & Category.SelectedItem.Value & "&Product=" &  ProductList.SelectedItem.Value )

Now in second page, same way remove all if conditions keep only following logic

Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Product_Details").ConnectionString

            Dim con As New SqlConnection(connectionstring)

            Dim selectsql As String = "SELECT Product_Name,Size,Price FROM Prod_Det WHERE Products='" & Product &"' ORDER BY Prod_Det"
            Dim cmd As New SqlCommand(selectsql, con)
            Dim adapter As New SqlDataAdapter(cmd)

            'Fill the dataset

            Dim ds As New DataSet()

            adapter.Fill(ds, "Prod_Det")

            'Perform Binding

            SearchResultGrid.DataSource = ds
            SearchResultGrid.DataBind()

Also I found that you design is not propert you are searching on base of product name. Actually you show use numeric id columns, because name may be same but ID will remain unique.
Remember one thing, YOUR CODE SHOULD NEVER ASSUME WHAT WOULD BE THE DATA IN DATABASE.

[BilalAKhan]

Thanks a lot for the help. Hadn't been bunking but its been a while I have worked on web development after I graduated in 2002. Working on ASP.NET for the first time for a project.

Thanks again for the help. I will try the code like this.

Regards,

Bilal A. Khan

I think you have bunked your class in your college when your professor was teaching you variables.

No condition required at all, remove all if condition and keep following link in first page.

Response.Redirect("Search Page.aspx?Category=" & Category.SelectedItem.Value & "&Product=" &  ProductList.SelectedItem.Value )

Now in second page, same way remove all if conditions keep only following logic

Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Product_Details").ConnectionString

            Dim con As New SqlConnection(connectionstring)

            Dim selectsql As String = "SELECT Product_Name,Size,Price FROM Prod_Det WHERE Products='" & Product &"' ORDER BY Prod_Det"
            Dim cmd As New SqlCommand(selectsql, con)
            Dim adapter As New SqlDataAdapter(cmd)

            'Fill the dataset

            Dim ds As New DataSet()

            adapter.Fill(ds, "Prod_Det")

            'Perform Binding

            SearchResultGrid.DataSource = ds
            SearchResultGrid.DataBind()

Also I found that you design is not propert you are searching on base of product name. Actually you show use numeric id columns, because name may be same but ID will remain unique.
Remember one thing, YOUR CODE SHOULD NEVER ASSUME WHAT WOULD BE THE DATA IN DATABASE.

[crishlay]

string searchvalue = "product"
Response.Redirect("Search Page.aspx?Category="+searchvalue)

[khadakbist]

Chrislays solution is better.