Concept on Search Function

Question

I am using C# ASP.NET and planning to make a search function that requires 5 parameters as the search condition Let's make the example using 3 conditions: a, b, and c

Question: Do I need to make all 7 possible search function that:

Connects directly to database (SQL)

1. Search result based of condition a only
2. Condition a + condition b
3. Condition a + condition b + condition c
4. Condition a + condition c
5. Condition b only
6. Condition b + condition c
7. Condition c only

or is it possible to, create a list of the table from database (SQL) and make a condition inside the C# aspx.cs?

I'm not going to ask about example codes, I am just asking for the concept available to simplify my coding for the search function, as I have at the very least 5 conditions, which will make me at least do 25 different search functions for all the possibilities of the search. Thanks.

Answer 1

It is possible to do from database (SQL) and it will be the best solution. You have to create Stored Procedure for this like below.

SQL:

Create Proc SP_Search(@A Int, @B NVarChar(20), @C Int)
    As
Begin
    If @A = 0 Set @A = Null
    If @B = '' Set @B = Null
    If @C = 0 Set @C = Null

    Select * From Table Where (A=@A Or @A Is Null) And (B=@B Or @B Is Null) And (C=@C Or @C Is Null)
End

Let me explain above SQL. It will take input in param @A, @B And @C. If @A is 0 then set @A = Null. In (A=@A Or @A Is Null) condition work like a optional param. If @A has some value the condition will apply, if it has null the condition will ignore. You can add more param like this.

1. Search result based of condition a only Exec SP_Search 1,'',0
2. Condition a + condition b Exec SP_Search 1,'A',0
3. Condition a + condition b + condition c Exec SP_Search 1,'A',1
4. Condition a + condition c Exec SP_Search 1,'',1
5. Condition b only Exec SP_Search 0,'A',0
6. Condition b + condition c Exec SP_Search 0,'A',1
7. Condition c only Exec SP_Search 0,'',1

C# Code to call the Stored Procedure:

int A = 1;
string B = "A";
int C = 1;
using (SqlConnection conn = new SqlConnection("Connection String")) {
    conn.Open();
    SqlCommand cmd  = new SqlCommand("SP_Search", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@A", A));
    cmd.Parameters.Add(new SqlParameter("@B", B));
    cmd.Parameters.Add(new SqlParameter("@C", C));
    using (SqlDataReader reader = cmd.ExecuteReader()) {
        while (reader.Read())
        {
            //Read Your Data Here
        }
    }
}