Run search in entire table

Question

My program runs, client enters what he is looking for and it searches it in the SQL database. When he clicks on search, it appears in a datagridview.

I want the client to enter any word, beginning or end of a word and instead of looking in one column it will look in the entire table.

How do I program that?

clsdatasource.search gets what's in the textbox and stores it in a variable.

DataView myview = new DataView(mytable);
myview.RowFilter = "CUSTOMER='" + clsDataSource.search + "'";
dataGridView1.DataSource = myview;

What I tried:

for (int i = 0; i < myset.Tables[0].Columns.Count; i++) {
  DataRow[] MyDR = myset.Tables[0].Select(myset.Tables[0].Columns[i].ToString() + "='" +   clsDataSource.search + "'");
  if (MyDR.Length > 0) {
    dataGridView1.DataSource = myview;
  }
}

Answer 1

Try this:

public partial class Form1 : Form
{
    DataGridView dataGridView;
    DataTable dataTable;
    DataView dataView;
    TextBox textBoxSearch;

    public Form1()
    {
        //InitializeComponent();

        Width = 400;
        dataGridView = new DataGridView { Parent = this, Dock = DockStyle.Top };
        textBoxSearch = new TextBox { Parent = this, Top = 200 };
        textBoxSearch.TextChanged += TextBoxSearch_TextChanged;

        dataTable = new DataTable();

        dataTable.Columns.Add("Id", typeof(int));
        dataTable.Columns.Add("Name", typeof(string));
        dataTable.Columns.Add("BirthDate", typeof(DateTime));

        dataTable.Rows.Add(1, "Bob", new DateTime(2001, 01, 01));
        dataTable.Rows.Add(2, "John", new DateTime(1999, 09, 09));
        dataTable.Rows.Add(3, "Alice", new DateTime(2002, 02, 02));

        dataView = new DataView(dataTable);
        dataGridView.DataSource = dataView;
    }

    private void TextBoxSearch_TextChanged(object sender, EventArgs e)
    {
        var text = textBoxSearch.Text;

        var values = dataTable.Columns
            .OfType<DataColumn>()
            .Select(c => "Convert([" + c.ColumnName + "], System.String)")
            .Select(c => c + " like '%" + text + "%'");

        var filter = string.Join(" or ", values);

        dataView.RowFilter = filter;
    }
}

To search in all columns of different types, I convert them to strings. Then use the like operator. And, finally, combine with or.