Filter DataGridView with TextBox

Admin 1
Viewed 314

I've a DataGridView and a TextBox. I need filter the values from DB in SQL in DataGridView with the TextBox.

Something like this:

enter image description here

This is my code: 

public partial class Form1 : Form
    {
        private DataSet dataSet;
        private SqlDataAdapter adapter;
        private BindingSource bindingSource = new BindingSource();
        private DataView dataView = new DataView();

    public Form1()
    {
        InitializeComponent();
    }

    private void GetData(string valor)
    {
        try
        {
            // Initialize the DataSet.
            dataSet = new DataSet();
            dataSet.Tables.Add(new DataTable());
            dataSet.Tables[0].Columns.Add("numberAsString", typeof(string));
            dataSet.Locale = CultureInfo.InvariantCulture;

            // Create the connection string for the AdventureWorks sample database.
            string connectionString = "connection ";

            // Create the command strings for querying the Contact table.
            string contactSelectCommand = "SELECT Titulo as Título FROM V_CuetaWeb WHERE Titulo LIKE ('" + valor + "%') GROUP BY titulo ORDER BY titulo DESC";

            // Create the contacts data adapter.
            adapter = new SqlDataAdapter(
                contactSelectCommand,
                connectionString);

            // Create a command builder to generate SQL update, insert, and
            // delete commands based on the contacts select command. These are used to
            // update the database.
            SqlCommandBuilder contactsCommandBuilder = new SqlCommandBuilder(adapter);

            // Fill the data set with the contact information.
            adapter.Fill(dataSet, "V_CuetaWeb");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void Form1_Load(object sender, EventArgs e)
    {

        DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
        chk.HeaderText = "Seleccione";
        chk.Name = "check";
        dtgTitulo.Columns.Add(chk);
        dtgTitulo.AllowUserToAddRows = false;

        dataSet = new DataSet();

        GetData(txtFiltroTitulo.Text);

        dtgTitulo.DataSource = bindingSource;

        // Create a LinqDataView from a LINQ to DataSet query and bind it 
        // to the Windows forms control.
        EnumerableRowCollection<DataRow> contactQuery = from row in dataSet.Tables["V_CuetaWeb"].AsEnumerable()
                                                        orderby row.Field<double>("Título") descending
                                                        select row;

        dataView = contactQuery.AsDataView();

        // Bind the DataGridView to the BindingSource.
        bindingSource.DataSource = dataView;

        string value = "";
        dtgTitulo.DataSource = bindingSource;
        bindingSource.Filter = "Título LIKE('" + Convert.ToInt64(value) + "%')";
        dtgTitulo.AutoResizeColumns();
    }

    private void txtFiltroTitulo_TextChanged(object sender, EventArgs e)
    {
        GetData(txtFiltroTitulo.Text);
    }
}

But doesn't work 'cause "título" is a float data in SQL. So, any sugerence?

filter textbox datagridview c#
2 Answers

Cast Titulo to a varchar, something like this...

SELECT … WHERE CAST(Titulo AS varchar(50)) LIKE ('" + valor + "%') …

And the Filter may be something like this...

bindingSource.Filter = "CONVERT(Título, 'System.String') LIKE('" + Convert.ToInt64(value) + "%')";
user_10487990

The problem you describe is twofold. First, the code is “adding” the check box column to the DataGridView itself! This is a problem because the DataSource to the grid doesn’t “KNOW” about this “added” column. Therefore, when you apply a “filter” to the data source, this filter isn’t going to know about the check box column and will obviously NOT maintain/care about “which” check boxes have been checked or not checked. To maintain the check boxes in the filter, the check box column needs to be in the data source itself.

Second, it appears that when the user changes the text in the filter text box, that the code is -re-querying the data base. This is odd and I bet you may want re-think this. Example: every time the user types a character into the text box, the code re-queries the database. If the user wants to “type” 123, then… when the user types the “1”, the data base is queried, then again when the user types the “2” and again when the user types the “3.” This is obviously unnecessary and could be classified as clobbering the database.

Do not get me wrong, in a sense that we DO WANT to “filter” the data when the user types the “1”, then the “2” and finally the “3”… you just DON’T want to re-query the data base to do this. To avoid this data base clobbering, the code could make a “global” variable that holds the ORIGINAL data, then, when we want to filter the data, instead of re-querying the database, you can get a filtered copy FROM the ORIGINAL data. This will mean the code will only query the data base once.

Given this, the code below demonstrates adding the check box column to the grids DataSource DataTable… not to the grid itself. This will maintain the check boxes when filtered. Also, instead of re-querying the data base when each filter character is typed, the code filters the original data source. This is accomplished by creating a new DataView from the original data then filter that DataView for each character typed.

Hope this helps and makes sense.

The form’s load method may look like…

DataSet ds;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  ds = new DataSet();
  ds.Tables.Add(GetDataFromDB());
  ds.Tables[0].Columns.Add("Select", typeof(bool));
  dataGridView1.DataSource = ds.Tables[0];
}

The GetDataFromDB returns a DataTable with one (1) column named “Title” that is of type double. Then we add a column to the DataTable returned from the data base. This column is named “Select” and is of type bool… a check box column. Now the check boxes will be maintained when filtered.

Lastly, to “filter” the data when the user types text into the filter text box…

private void textBox1_TextChanged(object sender, EventArgs e) {
  DataView dv = new DataView(ds.Tables[0]);
  if (double.TryParse(textBox1.Text, out double value)) {
    dv.RowFilter = "CONVERT(Title, 'System.String') LIKE('" + value + "%')";
  }
  dataGridView1.DataSource = dv;
}

Here we create a “new” DataView from the original data, then filter that DataView using the text in the text box, then set the grid to this new DataView. It is noted that the double.TryParse method is used to filter out any text the user types that is NOT a number or is empty, in which case the filter will display ALL the data.

To complete this example, below is a method to get some test data for testing. 

private DataTable GetDataFromDB() {
  DataTable dt = new DataTable();
  dt.TableName = "Titles";
  Random rand = new Random();
  dt.Columns.Add("Title", typeof(double));
  for (int i = 0; i < 3000; i++) {
    dt.Rows.Add(rand.Next(0, 1000));
  }
  return dt;
}
user_68427160
Related