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?
7 Answers, 1 is accepted
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
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
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
Dear dess,
thanks for reply
but i am really confused, what do you mean? could you sent me a snippet code, please.
thanks
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
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
Thank you for writing back.
The FilterChanged event is fired each time the data in RadGridView is filtered. Each entered 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