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

Answer 2

No, you do not need some special functions for all possible combinations. Moreover, it would be really big number of functions.

You can just check for some condition to be specified and add this condition.
Something like this:

public YourEntity[] Search(string name = "", int? age = null, bool? isActive = null)
{
    string query = "SELECT * FROM YourEntities";
    List<string> whereConditions = new List<string>();

    if (!string.IsNullOrWhiteSpace(name))
    {
        whereConditions.Add($"name LIKE '%{name}%'");
    }

    if (age.HasValue)
    {
        whereConditions.Add($"age = {age.Value}");
    }

    if (isActive.HasValue)
    {
        whereConditions.Add($"isActive = {isActive.Value:D}");
    }

    if (whereConditions.Any())
    {
       query += "WHERE " + string.Join(" AND ", whereConditions);
    }

    return someSqlExecutorAndProcessor(query);
}

Then, you can use this method like this:

var results = Search(); // all
var results = Search("name"); // name only
var results = Search(age: 17); // age only
var results = Search("name", isActive: true); // name and isActive

Important note: Note that this code uses string concatenation to build SQL query and it is not safe, it just a minimal example to provide the general idea. I just don't know what you use to work with database. Use parametrized queries or ORM instead.

For example, if you use Entity Framework, it would look like this:

public YourEntity[] Search(string name = "", int? age = null, bool? isActive = null)
{
    IQueryable<YourEntity> entities = dbContext.Set<YourEntity>();

    if (!string.IsNullOrWhiteSpace(name))
    {
        entities = entities.Where(x => x.Name.Contains(name));
    }

    if (age.HasValue)
    {
        entities = entities.Where(x => x.Age == age.Value);
    }

    if (isActive.HasValue)
    {
        entities = entities.Where(x => x.IsActive == isActive.Value);
    }

    return entities.ToArray();
}