Multiple criteria search / filter with stored procedure C# ASP dot net 2010

Question

1.00/5 (1 vote)

See more:

Hi friends, I need help in writing c# code for...
Search / filter rows from sql server 2008 by giving multiple criteria in Textbox, combobox, Radiobutton using c# asp dot net 2010.
I create table "TBL_STUDENT"and stored procedure with the following columns/datatype

GENDER AS NVARCHAR(60),
FROMAGE AS INT,
TOAGE AS INT,
STANDARD AS NVARCHAR(256),
SECTION AS NVARCHAR(256),
SID AS INT,
STATUS INT

The gender,fromAge,ToAge,STANDARD is DropDownList,
"Section" is RadioButton (ex., A, B, C, D, E),
SID is TextBox (ie., Student Id if given Just Student ID and leaving other criteria empty)
STATUS being "PASS" or "FAIL"

How can I write c# code with Parameter Stored Procedure etc directly
Or
using SP in SQL Server and using it in C# with parameter

Please help,
Thanks
Ravi

What I have tried:

CREATE PROCEDURE [dbo].[STUDENT_SEARCH]

@GENDER AS NVARCHAR(60),
@FROMAGE AS INT,
@TOAGE AS INT,
@STANDARD AS NVARCHAR(256),
@SECTION AS NVARCHAR(256),
@SID AS INT

AS
BEGIN

IF @STANDARD = 'ALL' AND @SECTION ='ALL'
BEGIN
SELECT * FROM TBL_STUDENT WHERE GENDER=@GENDER AND AGE>=@FROMAGE AND AGE<=@TOAGE AND SID != @SID and status=1
END
ELSE IF @STANDARD !='ALL' AND @SECTION='ALL'
BEGIN
SELECT * FROM TBL_STUDENT WHERE GENDER=@GENDER AND AGE>=@FROMAGE AND AGE<=@TOAGE AND STANDARD =@STANDARD AND SID != @SID and status=1
END

ELSE IF @STANDARD !='ALL' AND @SECTION !='ALL'
BEGIN
SELECT * FROM TBL_STUDENT WHERE GENDER=@GENDER AND AGE>=@FROMAGE AND AGE<=@TOAGE AND STANDARD =@STANDARD AND CITY=@SECTION AND SID != @SID and status=1
END

ELSE IF @STANDARD ='ALL' AND @SECTION !='ALL'
BEGIN
SELECT * FROM TBL_STUDENT WHERE GENDER=@GENDER AND AGE>=@FROMAGE AND AGE<=@TOAGE AND SECTION=@SECTION AND SID != @SID and status=1
END
END

Posted 28-Jun-21 0:51am

ravitv

Updated 2-Jul-21 1:17am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-06-28T23:02:00

Assuming your question is "How can I call a stored procedure with parameters from C#", the answer is fairly straightforward:

C#

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:

SQL

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.

SQL

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.

ravitv · Accepted Answer · 2021-07-02T01:17:00

Amazing! Richard, Great, it works, Thanks a lot,

protected void btnstudentsearch_Click(object sender, EventArgs e)
{
string str = (@"Data Source=.\MSSQLSERVER2008;AttachDbFilename=C:\xxx.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
SqlConnection con = new SqlConnection(str);

SqlCommand sqlcomm = new SqlCommand("STUDENT_SEARCH", con);

sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Parameters.AddWithValue("@Gender", drpGender.Text);
sqlcomm.Parameters.AddWithValue("@AgeFrom", drpagefrom.Text);
sqlcomm.Parameters.AddWithValue("@AgeTo", drpageto.Text);
sqlcomm.Parameters.AddWithValue("@STANDARD ", txtStandard.Text);
sqlcomm.Parameters.AddWithValue("@SECTION", drpSection.Text);
sqlcomm.Parameters.AddWithValue("@SID", txtSearch.Text);
con.Open();
var da = new SqlDataAdapter(sqlcomm);
var ds = new DataSet();
da.Fill(ds);

if (ds.Tables.Count > 0)
{
gvStudent.DataSource = ds.Tables[0];
gvStudent.DataBind();

}
con.Close();
}

Multiple criteria search / filter with stored procedure C# ASP dot net 2010

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0