Geek asks and answers

Find a row in all columns of a DataTable

I am trying to find a quick way to find a row in all datatable columns! Following this does not work, because I want to search all the column values.

string str = "%whatever%";
foreach (DataRow row in dataTable.Rows)
    foreach (DataColumn col in row.ItemArray)
        if (row[col].ToString() == str) return true;
+3
source share
5 answers

This can be achieved by filtering. Create a (reusable) filter string based on all columns:

        bool UseContains = false;
        int colCount = MyDataTable.Columns.Count;


        string likeStatement = (UseContains) ? " Like '%{0}%'" : " Like '{0}%'"; 
        for (int i = 0; i < colCount; i++)
        {
            string colName = MyDataTable.Columns[i].ColumnName;
            query.Append(string.Concat("Convert(", colName, ", 'System.String')", likeStatement));


            if (i != colCount - 1)
                query.Append(" OR ");
        }

        filterString = query.ToString();

Now you can get rows in which one of the columns matches your search string:

 string currFilter = string.Format(filterString, searchText);
 DataRow[] tmpRows = MyDataTable.Select(currFilter, somethingToOrderBy);
+1
source

You can use LINQ. It will not be faster, because you still need to look at each cell if the value does not exist, but it will be on the same line:

return dataTable
    .Rows
    .Cast<DataRow>()
    .Any(r => r.ItemArray.Any(c => c.ToString().Contains("whatever")));

, , :

var text = "whatever";
return dataTable
    .Rows
    .Cast<DataRow>()
    .Where(r => r.ItemArray.Any(
        c => c.ToString().IndexOf(text, StringComparison.OrdinalIgnoreCase) > 0
    )).ToArray();
+5

If you want to check every row of every column in a datatable, try this (this works for me!).

DataTable YourTable = new DataTable();
// Fill your DataTable here with whatever you've got.

foreach (DataRow row in YourTable.Rows)
{
    foreach (object item in row.ItemArray)
    {
        //Do what ya gotta do with that information here!
    }
}

Do not forget the type object itemfor what you need (string, int, etc.).

I went through the debugger and it works in charm. Hope this helps, and good luck!

+2
source

You can create a search routine with an array of strings with column names:

string[] elems = {"GUID", "CODE", "NAME", "DESCRIPTION"};//Names of the columns             
foreach(string column in elems)
{
    string expression = string.Format("{0} like '%{1}%'",column, 
    txtSearch.Text.Trim());//Search Expression
    DataRow[] row = data.Select(expression);
    if(row.Length > 0) {
        // Some code here
    } else {
        // Other code here
    }
 }
0
source

You can also create a filter expression in a datatable. See MSDN Article. Use as in a filter expression.

 string filterExp = "Status = 'Active'";
 string sortExp = "City";
 DataRow[] drarray;
 drarray = dataSet1.Customers.Select(filterExp, sortExp, DataViewRowState.CurrentRows);
 for (int i=0; i < drarray.Length; i++)
 {
    listBox1.Items.Add(drarray[i]["City"].ToString());
 }
-1
source

More articles:


All Articles