Click here to Skip to main content
15,038,237 members
Please Sign up or sign in to vote.
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
Updated 2-Jul-21 1:17am

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.
   
Comments
ravitv 1-Jul-21 9:40am
   
Hi Richard,
Thanks for quick reply and excellent solution, sorry for late reply.
But, how can use your code in a button click event if I already have c# code structure as below and display it in gridview?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
...
.....
......etc.,

public partial class Search_Student: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
some login validation goes here
}
}

protected void btnSearchStudent_Click(object sender, EventArgs e)
{
HERE I need to your code to be executed
}

}

Thanks
Ravi
Richard Deeming 1-Jul-21 9:43am
   
Gather the parameter values from your controls. Pass them to the StudentSearch method. Assign the returned DataSet to the grid's DataSource property, and call the grid's DataBind method.
ravitv 1-Jul-21 10:23am
   
Thanks Richard, I will try this.
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();
}
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900