Hello,
I would like to know if there is a possibility to construct SQL compatible where clause from FieldLayout.RecordFilters of XamDataGrid. I have exposed Filter Record to the user to filter out rows. Once my user provide all the filter values, I would like to prepare a SQL statement with appropriate Where clause in order to run directly on the database. The FieldLayout property of XamDataGrid exposes a collection of RecordFilterCollection but I couldn't find anyway to prepare a compatible where clause from it.
Looking for a quick response from your side.
Best,
Allana
Hello Allana,
Thank you for your post!
From the RecordFilters collection in the XamDataGrid, you can obtain the RecordFilter objects in that collection. The RecordFilter objects have a collection of Conditions on them. This is a ComparisonCondition object. If you cast an entry in the RecordFilters collection to a ComparisonCondition object, then from the comparison condition you can get the Operator and the Value that the filter is using. Also, from the record filter collection, you can obtain the Logical Operator (and/or). From there, you can create a SQL query, based on the values that you obtain.
I have attached a sample application to include the code needed to obtain these values.
Please let me know if you have any other questions or concerns on this matter.
Sincerely,AndrewDeveloper Support Engineer IInfragistics Inc.www.infragistics.com/support
Hi Andrew,
In your sample, I couldn't see any usage of RecordFilter object in the query. Anyway, if I can twick the sample and use x, y and z variables there are two main concerns. The first is if user provide multiple criteria for any single column with OR operator between them and the second is SQL injection. I need to handle both of them manually. My original question was that is there any built-in mechanism available for XAMDataGrid or XAMGrid to take care of it?
For both the XamGrid and the XamDataGrid, there is a built in mechanism for each for providing multiple criteria for a single column with OR or AND operators between them. The XamGrid option is to set the AllowFiltering property in the XamGrid.FilteringSettings to “FilterMenu.” The XamDataGrid option is to set the FilterLabelIconDropDownType property in the XamDataGrid.FieldSettings to “MultiSelectExcelStyle” and set the FilterUIType property in the XamDataGrid.FieldLayoutSettings to “LabelIcons.” Both of these will enable an Excel-like filtering option for each grid.
As far as a built in feature for the SQL injection of the filter from the grid to the SQL database, nothing currently exists to do this via a property or built-in functionality of either of the grids. I believe your best option to achieve this would be to construct your SQL query using the RecordFilter operations that I had mentioned in my previous response.
Just checking in, did you have any other questions or concerns on this matter?