Filtering GridViewDateTimeColumn

8 posts, 0 answers
  1. ALED
    ALED avatar
    18 posts
    Member since:
    Nov 2014

    Posted 11 Jul Link to this post

    Hello All,

    I have problem when catch filtering datetime in radgrid, in my case i want get a result of filter ,

    this is my snippet result of Expression Filter :

    ModifiedAt = #07/14/2016 00:00:00#

    a problem come from character "#" , i used an expression to my SQl query , show i want to remove # and replace with single quote " ' ", like this

    ModifiedAt = '07/14/2016 00:00:00' 

    so how its work?

  2. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 13 Jul Link to this post

    Hello Aled,

    Thank you for writing. 
     
    When you have a DateTime value in the filter cell, the FilterDescriptor.FilterExpression uses a specific format for the DateTime value: "#{0}#". This is by design. In order to use the expression in the SQL query, you have two options: the first one is to pass to the query the expression by replacing the " " symbol with " ". An alternative solution is to handle the RadGridView.FilterChanged event and construct the expression manually in the desired format considering the FilterDescriptor's properties.
     
    I hope this information helps. Should you have further questions I would be glad to help.

    Regards,
    Dess
    Telerik by Progress
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
  3. UI for WinForms is Visual Studio 2017 Ready
  4. ALED
    ALED avatar
    18 posts
    Member since:
    Nov 2014

    Posted 13 Jul Link to this post

    Dear Dess, 

    thanks for reply, i think replacing " # " with " ' " symbol is not a good solution, because in my mind, a time a symbol contains in my string because the grid design have a field description, event description string have a filter string contains " # ", its will be replacing too, and this not good, so any another way for this case, really i was spent 2 day tou out from this problem, please give me another way. thanks

  5. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 13 Jul Link to this post

    Hello Aled,

    Thank you for writing back. 

    Feel free to use the suggested alternative solution from my previous post: handle the RadGridView.FilterChanged event and construct the expression manually in the desired format considering the FilterDescriptor's properties (Value, Operator, PropertyName).

    I hope this information helps. If you have any additional questions, please let me know.

    Regards,
    Dess
    Telerik by Progress
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
  6. ALED
    ALED avatar
    18 posts
    Member since:
    Nov 2014

    Posted 13 Jul Link to this post

    Dear dess,

    thanks for reply

    but i am really confused, what do you mean? could you sent me a snippet code, please.

    thanks

  7. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 13 Jul Link to this post

    Hello Aled,

    Thank you for writing back. 

    You can find below a sample approach how you can manually build the filter expression:
    string filterExpression = string.Empty;
     
    private void radGridView1_FilterChanged(object sender, GridViewCollectionChangedEventArgs e)
    {
        if (e.Action != NotifyCollectionChangedAction.Remove)
        {
            foreach (FilterDescriptor fd in e.NewItems)
            {
                DateFilterDescriptor dateTimeFilterDescriptor = fd as DateFilterDescriptor;
                if (dateTimeFilterDescriptor != null)
                {
                    filterExpression = BuildFilterExpression(dateTimeFilterDescriptor);
                    Console.WriteLine(filterExpression);
                }
            }
        }
    }
     
    private string BuildFilterExpression(DateFilterDescriptor fd)
    {
        StringBuilder sb = new StringBuilder();
        string dateBegin = null;
        string dateEnd = null;
        if (fd.Value != null)
        {
            dateBegin = String.Format(CultureInfo.InvariantCulture, "'{0}'", fd.Value.Value.Date);
            dateEnd = String.Format(CultureInfo.InvariantCulture, "'{0}'", fd.Value.Value.Date.AddDays(1));
        }
        switch (fd.Operator)
        {
            case FilterOperator.None:
                return String.Empty;
     
            case FilterOperator.IsNull:
                sb.Append(string.Format("{0} IS NULL", fd.PropertyName));
                break;
            case FilterOperator.IsNotNull:
                sb.Append(string.Format("NOT ({0} IS NULL)", fd.PropertyName));
                break;
            case FilterOperator.IsLessThan:
                sb.Append(string.Format("{0} < {1}", fd.PropertyName, dateBegin));
                break;
            case FilterOperator.IsLessThanOrEqualTo:
                sb.Append(string.Format("{0} < {1}", fd.PropertyName, dateEnd));
                break;
            case FilterOperator.IsLike:
            case FilterOperator.IsEqualTo:
                sb.Append(string.Format("{0} = {1}", fd.PropertyName, dateBegin));
                break;
            case FilterOperator.IsNotLike:
            case FilterOperator.IsNotEqualTo:
                sb.Append(string.Format("{0} < {1} OR {0} >= {2}", fd.PropertyName, dateBegin, dateEnd));
                break;
            case FilterOperator.IsGreaterThanOrEqualTo:
                sb.Append(string.Format("{0} >= {1}", fd.PropertyName, dateBegin));
                break;
            case FilterOperator.IsGreaterThan:
                sb.Append(string.Format("{0} >= {1}", fd.PropertyName, dateEnd));
                break;
            case FilterOperator.StartsWith:
            case FilterOperator.EndsWith:
            case FilterOperator.Contains:
            case FilterOperator.NotContains:
            case FilterOperator.IsContainedIn:
            case FilterOperator.IsNotContainedIn:
            default:
                return String.Empty;
        }
        return sb.ToString();
    }

    Note that this is just a sample approach and it may not cover all possible cases. Feel free to modify it in a way which suits your requirement best.

    I hope this information helps. If you have any additional questions, please let me know.

    Regards,
    Dess
    Telerik by Progress
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
  8. ALED
    ALED avatar
    18 posts
    Member since:
    Nov 2014

    Posted 13 Jul in reply to Dess Link to this post

    Dear Dess,

    thanks so much,

    but actually i not want used FilterChanged Event, cause every user type is enter to event, in another choice i used FilterExpressionChanged, could you tell me if u sed FilterChangedEvent to reformat Filter like ur code before? thanks

  9. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 14 Jul Link to this post

    Hello Aled,

    Thank you for writing back. 

    The FilterChanged event is fired each time the data in RadGridView is filtered. Each entered character in the filter cell changes the filter. Hence, the FilterChanged event is fired each time. However, the FilterExpressionChanged event is fired as well every time the filter value is modified. I can suggest you to handle the CellEndEdit event for the filter cell and construct the filter expression when the filter cell is not in edit mode yet. But have in mind that the grid data will be already filtered: 
    private void radGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
     {
         if (e.Row is GridViewFilteringRowInfo)
         {
             foreach (FilterDescriptor fd in this.radGridView1.FilterDescriptors)
             {
                 DateFilterDescriptor dateTimeFilterDescriptor = fd as DateFilterDescriptor;
                 if (dateTimeFilterDescriptor != null)
                 {
                     filterExpression = BuildFilterExpression(dateTimeFilterDescriptor);
                     Console.WriteLine(filterExpression);
                 }
             }
         }
     }
     
    //... the next code part

    I hope this information helps. If you have any additional questions, please let me know.

    Regards,
    Dess
    Telerik by Progress
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
Back to Top
UI for WinForms is Visual Studio 2017 Ready