Assuming your question is
"How can I call a stored procedure with parameters from C#", the answer is fairly straightforward:
public DataSet StudentSearch(string gender, int fromAge, int toAge, string standard, string section, int sid)
{
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("dbo.STUDENT_SEARCH", connection))
{
command.Parameters.AddWithValue("@Gender", gender);
command.Parameters.AddWithValue("@FromAge", fromAge);
command.Parameters.AddWithValue("@ToAge", toAge);
command.Parameters.AddWithValue("@Standard", standard);
command.Parameters.AddWithValue("@Section", section);
command.Parameters.AddWithValue("@SID", sid);
var result = new DataSet();
var adapter = new SqlDataAdapter(command);
adapter.Fill(result);
return result;
}
}
If your question is how to apply the conditions in your stored procedure, you have two options.
Using conditional filters in the
WHERE
clause could cause performance problems:
SELECT
... COLUMN NAMES ...
FROM
TBL_STUDENT
WHERE
status = 1
And
GENDER = @GENDER
And
AGE Between @FromAge And @ToAge
And
SID != @SID
And
(@STANDARD = N'All' Or STANDARD = @STANDARD)
And
(@SECTION = N'All' Or SECTION = @SECTION)
;
The alternative is to use dynamic SQL. However, you need to ensure the parameters are passed correctly to avoid
SQL Injection[
^] vulnerabilities.
DECLARE @command nvarchar(max), @params nvarchar(max);
SET @command = N'SELECT
... COLUMN NAMES ...
FROM
TBL_STUDENT
WHERE
status = 1
And
GENDER = @GENDER
And
AGE Between @FromAge And @ToAge
And
SID != @SID
';
If @STANDARD != N'All' SET @command = @command + N' And STANDARD = @STANDARD';
If @SECTION != N'All' SET @command = @command + N' And SECTION = @SECTION';
SET @params = N'@GENDER nvarchar(60), @FROMAGE int, @TOAGE int, @STANDARD nvarchar(256), @SECTION nvarchar(256), @SID int';
EXEC sp_executesql @command, @params,
@GENDER = @GENDER,
@FROMAGE = @FROMAGE,
@TOAGE = @TOAGE,
@STANDARD = @STANDARD,
@SECTION = @SECTION,
@SID = @SID;
sp_executesql (Transact-SQL) - SQL Server | Microsoft Docs[
^]
In either case, list the column names explicitly rather than using
SELECT * FROM
.