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)
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.
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.
Exec SP_Search 1,'',0
Exec SP_Search 1,'A',0
Exec SP_Search 1,'A',1
Exec SP_Search 1,'',1
Exec SP_Search 0,'A',0
Exec SP_Search 0,'A',1
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
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With