How to Implement Multiple search in Asp.Net

Question

I want to Implement multiple search query system in Asp.Net where search input are in form of TEXTBOX and DROPDOWN LIST. Query should work in combination or indivisually to filter the data from SQL Server and show in Gridview.

This Code Snippet is for filtering two Dropdown values:

if (Agree_type_srch.SelectedValue != null || Status_srch.SelectedValue != null)
{
    if (Agree_type_srch.SelectedValue != null)
    {
        string connString = @"data source=ABC; database=XYZ; user id=sa; password=1234;";
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand com = new SqlCommand("Select *from EntryDatabase where Agree_type ='" + Agree_type_srch.SelectedItem.Text + "'", conn);
        SqlDataAdapter sqldatad = new SqlDataAdapter();
        DataSet ds = new DataSet();

        com.Connection = conn;
        sqldatad.SelectCommand = com;

        using (DataTable dt = new DataTable())
        {
            sqldatad.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

    }
    else if (Status_srch.SelectedValue != null)
    {
        string connString = @"data source=ABC; database=XYZ; user id=sa; password=1234;";
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand com = new SqlCommand("Select *from EntryDatabase where Curnt_St ='" + Status_srch.SelectedItem.Text + "'", conn);
        SqlDataAdapter sqldatad = new SqlDataAdapter();
        DataSet ds = new DataSet();

        com.Connection = conn;
        sqldatad.SelectCommand = com;

        using (DataTable dt = new DataTable())
        {
            sqldatad.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }

    if (Agree_type_srch.SelectedItem.Text != null && Status_srch.SelectedItem.Text != null)
    {
        string connString = @"data source=ABC; database=XYZ; user id=sa; password=1234;";
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand com = new SqlCommand("Select * from EntryDatabase where Agree_type ='" + Agree_type_srch.SelectedItem.Text + "'and Curnt_St ='" + Status_srch.SelectedItem.Text + "'", conn);
        SqlDataAdapter sqldatad = new SqlDataAdapter();
        DataSet ds = new DataSet();

        com.Connection = conn;
        sqldatad.SelectCommand = com;

        using (DataTable dt = new DataTable())
        {
            sqldatad.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
...

Why not create stored procedure?

huMpty duMpty
– huMpty duMpty

2019-09-19 11:42:43 +00:00
Commented Sep 19, 2019 at 11:42 — huMpty duMpty
– huMpty duMpty, Commented Sep 19, 2019 at 11:42

Circle Hsiao · Accepted Answer · 2019-09-20 02:51:38Z

First, using string concatenation to provide parameters can result in SQL injection, use SqlParameter to pass parameters would be better.

Second, consider to warp all SqlClient classes by using scope so you don't have to worry close/dispose.

Lastly, For your question, you can use WHERE 1=1 then append any conditions you need. Take your code as instance.

string connString = @"data source=ABC; database=XYZ; user id=sa; password=1234;";
using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    string query = "SELECT * FROM EntryDatabase WHERE 1=1";
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        if (Agree_type_srch.SelectedValue != null)
        {
            query += " AND Agree_type = @agree_type";
            cmd.Parameters.AddWithValue("agree_type", Agree_type_srch.SelectedValue);
        }
        if (Status_srch.SelectedValue != null)
        {
            query += " AND Curnt_St = @curnt_st";
            cmd.Parameters.AddWithValue("curnt_st", Status_srch.SelectedValue);
        }
        cmd.CommandText = query;
        using (SqlDataAdapter sqldatad = new SqlDataAdapter())
        {
            DataSet ds = new DataSet();
            sqldatad.SelectCommand = cmd;

            using (DataTable dt = new DataTable())
            {
                sqldatad.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
    }
}

Collectives™ on Stack Overflow

How to Implement Multiple search in Asp.Net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related