Search Function Concept

Question

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

Question: I need to do all 7 possible search functions, which are:

Connects directly to the database (SQL)

• The search result is based only on the condition
• Condition a + Condition b
• Condition a + Condition b + Condition c
• Condition a + Condition c
• Condition b only
• Condition b + Condition c
• Condition only c

or can I create a table list from a database (SQL) and make a condition inside C # aspx.cs?

I will not ask about code examples, I just ask that the concept be available to simplify my coding for the search function, since I have at least 5 conditions that will make me at least 25 different search functions for all search possibilities. Thanks.

Answer 1

This can be done from a database (SQL), and it will be the best solution. You must create one Stored Procedurefor this as shown 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

SQL. param @A, @B @C. @A 0, @A = Null. (A=@A Or @A Is Null) . @A , , null, . .

• Exec SP_Search 1,'',0
• a + b Exec SP_Search 1,'A',0
• a + b + c Exec SP_Search 1,'A',1
• a + c Exec SP_Search 1,'',1
• b Exec SP_Search 0,'A',0
• b + c Exec SP_Search 0,'A',1
• c Exec SP_Search 0,'',1

# 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

, . , .

, - .
- :

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

:

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

: , SQL-, , . , . ORM.

, Entity Framework, :

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();
}