Concept on Search Function

asp.net, c#, visual-studio

Solution

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.

- Search result based of condition a only `Exec SP_Search 1,'',0`

- Condition a + condition b `Exec SP_Search 1,'A',0`

- Condition a + condition b + condition c `Exec SP_Search 1,'A',1`

- Condition a + condition c `Exec SP_Search 1,'',1`

- Condition b only `Exec SP_Search 0,'A',0`

- Condition b + condition c `Exec SP_Search 0,'A',1`

- 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
        }
    }
}

Problem

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) - Search result based of condition a only - Condition a + condition b - Condition a + condition b + condition c - Condition a + condition c - Condition b only - Condition b + condition c - 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.

Original source