How to use LIKE operator in Dataview.RowFilter for Date Time or Numaric Fields using CONVERT

2012-01-16

The RowFilter property of the DataView allows to use flexible string similar to SQL or LINQ to easily filter rows wihtout having to iterate through them.

I had to implement a fully flexible search module for a project I’m working on. The user had to be able to perform a string search on any field displayed on the gridview. The above mentioned RowFilter method is really handy to provide such a functionality due to increased efficiency.

Assume following example:

The data table (in the database)

Field Name         Data Type  

Name                String

DateOfBirth        DateTime

Data

Name                     DOB

John                      1976-10-12

Sophie                   1990-12-30

If you want to use the RowFilter to enable flexible searching (i.e. if the user type “j” in the search textbox the search grid view would only show the record for John) you can use following code:

string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("Name LIKE '%", SearchFor, "%'");

This will allow the above explained behaviour so if the user now enter “h” in the textbox it will show both records because both John and Sophie have the letter “h” in their names.

So what if we repeat the same and use the following code for the date of birth field?

// WRONG CODE
string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("DateOfBirth LIKE '%", SearchFor, "%'");

We would expect the program to filter records similarly. However, if you enter “1990” in the search textbox hoping it would filter Sophie, it would give you an error instead!

This is because the LIKE operator cannot work with non-charactor types.

The Solution!

We have to use a converter to convert the datetime field into a string just before the RowFilter is applied.

Here’s the code:

string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("CONVERT(DateOfBirth, System.String) LIKE '%", SearchFor, "%'");

The Convert function will cast the datetime value into string just before the LIKE operation takes place. And since the datetime value is only temperory converted, the original data are not affected as well.

So if you add this code to the previously worked code for the Name column as described below:

string SearchFor = SearchTextBox.Text;
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("Name LIKE '%", SearchFor, "%'");
((DataView)SearchGrid.DataContext).RowFilter = string.Concat("CONVERT(DateOfBirth, System.String) LIKE '%", SearchFor, "%'");

The Result

Now if you type “John” the grid will only show records that match that value and if you type a digit (e.g. 30) it will show the record that has 30 in the dob field (i.e. the record for Sophie)

Note
You can use the same method for any other field type which doesn’t support use of LIKE directly.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

This entry was posted in C#.Net and tagged , , , , , , , , , , , , , , . Bookmark the permalink.

7 Responses to How to use LIKE operator in Dataview.RowFilter for Date Time or Numaric Fields using CONVERT

  1. Moeed says:

    Hi,
    It works, but sort function will not work with this technique.

    when ever you click on any column. it will through an error “Cannot perform ‘Like’ operation on System.DateTime and System.String.”.

    Any solution ?

    • Menol says:

      Hi,

      Non string data columns are converted Only in the filtering process. This filtering doesn’t affect the actual data column so your datetime data column will remain date time. Therefore this doesn’t affect (tested in my application) your sorting or any other grid operation.

      This solution was introduced to handle “Cannot perform ‘Like’ operation on System.DateTime and System.String.”. problem so are you sure you did following:

      1) added a different row filter for each column that you want to search in?
      2) added a convert function for each non-string column?

      if it helps, following is the actual code I used in my application which runs perfectly at the moment.

                  StringBuilder _sb = new StringBuilder();
      
                  foreach (GridColumn _col in DxGrid.Columns)
                  {
                      if (_col.Visible && _col.Width != 5.0)
                      {
                          if (_sb.Length > 0)
                          {
                              _sb.Append(" OR ");
                          }
      
                          // filter out non string columns and convert them to string before applying the filter cos' those columns don't support LIKE statements
                          if (((DataView)DxGrid.DataSource).Table.Columns[_col.FieldName].DataType != typeof(String))
                          {
                              _sb.Append(string.Concat("CONVERT(", _col.FieldName, ", System.String)"));
                              _sb.Append(string.Concat(" LIKE '%", SearchFor, "%'"));
                          }
                          else
                          {
                              _sb.Append(string.Concat(_col.FieldName, " LIKE '%", SearchFor, "%'"));
                          }
                      }
                  }
                 
                  
                  ((DataView)DxGrid.DataSource).RowFilter = _sb.ToString();
      
  2. Moeed says:

    Hi Menol,

    i am using VS 2008 , using C# / SQL with desktop application. my code is here under. can you help with this.

    string colName = “”;
    switch(cboFilter.Text)
    {
    case “Our Ref”:
    colName = “OurCaseRef”;
    break;
    ….
    .
    .
    .
    .
    Note : Many cases are there
    }

    if (colName == “CDOB”)
    {

    dtCases.DefaultView.RowFilter = string.Concat(“CONVERT(CDOB, System.String) LIKE ‘%”, txtFilter.Text.Trim(), “%'”);

  3. Menol says:

    Hi Moeed,

    I don’t see anything wrong in setting the RowFilter value.

    Can you debug/trace the click event that causes to throw the exception and see if the value you set has not changed by the time you click on the grid?

    Menol

  4. .net performance says:

    Hey thank you so much it works brilliantly.

  5. daniela says:

    Thanks , I’ve just been searching for information about this subject for a while and yours is the greatest I have came upon till now.

  6. sandeep says:

    Thanks it works and saved my life……

Leave a Reply

Your email address will not be published. Required fields are marked *