Gridview filtering using textbox in asp.net

Question

i want to know as how to search or filter records in a gridview dynamically based on the character(s) entered on a textbox. What is the best way to achieve this? Any sample codes or examples will be really helpful.

Answer 1

The trick here is to make databind only when the text change on the search box, but you must always set the datasource select command code. So you add a text box, and a button that say, submit, and you have the following:

    OnPageLoad -> 
if(SearchContron.Text.Length > 0)
  SqlDataSource1.SelectCommand = "SELECT * FROM TABLE WHERE Desc LIKE N'%" + SearchContron.Text +"%'"
else
  SqlDataSource1.SelectCommand = "SELECT * FROM TABLE "

and

OnSubmitButtonClick -> GridView.DataBind()

If you do it other way, the paging and editing and other commands will fail. You can also make it more advanced if you get the text from the text box and break it in many words and search each one as separate on the same sql command.

Answer 2

Its simple,

Look here for a basic tutorial on adding Ajax control to page. 1) Add the text box as well as the grid view into same update panel 2) In the text box's key press event, you can set the data source of gird and invoke databind command.

Note that when the key press will be fired, it will cause the complete page life cycle to be executed at server side. Hence, you will have to check whether the post back is async or not in your Page Load even handler.

A trick to reduce the number of database queries being fired is to set a timer when the user presses a key with a timeout of say...500ms and do the databinding of gridview in timer's tick event. If you do this, database will be queried only when the user has stopped typing something.

Thanks,
Vamyip

Answer 3

To bind gridview data write the following code

 private void GridData()
    {
        string conString = ConfigurationManager.ConnectionStrings["MyCon"].ToString();
        SqlConnection sqlcon = new SqlConnection(conString);
        SqlCommand sqlcmd;
        SqlDataAdapter da;
        DataTable dt = new DataTable();
        String query;

        if (txtsearch.Text == "")
       {
           query = "select  PersonID,LastName,FirstName from Person";
        }
        else
        {
            query = "select  PersonID,LastName,FirstName from Person where PersonID like '" + txtsearch.Text + "%' or LastName like '" + txtsearch.Text + "%' or FirstName like '" + txtsearch.Text + "%'";
        }
        sqlcmd = new SqlCommand(query, sqlcon);
        sqlcon.Open();
        da = new SqlDataAdapter(sqlcmd);
        dt.Clear();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            grdsearch.DataSource = dt;
            grdsearch.DataBind();
        }
        else
        {
            grdsearch.DataBind();
            Label1.Text = "No Records Found";
        }
        sqlcon.Close();
    }

In page load event

if (!IsPostBack)
        {
            GridData();
        }

for search button click event call GridData() method and for clear button click event write following code

        txtsearch.Text = "";
        GridData();
        Label1.Text = "";

Answer 4

implement the onclick of search button like this:

protected void searchButton_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(searchTextBox.Text))
        {
            SqlDataSource1.SelectCommand = "SELECT id,name,address, datetime FROM nirmaan.[seller] where id <>0" +
                " ORDER BY [name], [id]";
        }
        else
        {
            SqlDataSource1.SelectCommand = "SELECT id,name,address, datetime FROM nirmaan.[seller] where id <>0" +
                "and "+DropDownList1.SelectedValue+" LIKE '%" + searchTextBox.Text + "%' ORDER BY [name], [id]";
        }
        GridView1.DataBind();
    }

Answer 5

If you do other way to working search filtering condition for grid view header part. it is easy to use implement in your code. This is concepts used without database but i was using data table in linq. i hope to this code use full.

DataTable dt = (DataTable)Session["ProductTable"];
var query = from t in dt.AsEnumerable()
            where t.Field<string>("ProducId").StartsWith(txtProductId.Text.ToString().Trim())
            || t.Field<string>("ProducId").Contains(txtProductId.Text.ToString().Trim())
            select t;

Here is a sample program.

Answer 6

Unless you have a specific need to do this on the server, why not perform the filtering on the client? A solution like DataTables is fast and user-friendly.