This is a migrated thread and some comments may be shown as answers.

Using GetEntitySqlFilterExpression to get the current filter

7 Answers 213 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Terry
Top achievements
Rank 1
Terry asked on 28 Feb 2019, 07:02 AM

I have a textbox filter on a radgrid and need to get all results between two integers. So I enter "1974 2000" and use the 'between' selection to get all values between 1974 and 2000. I now need to retrieve the current filter expression in order to pass to a stored procedure. I tried using FilterExpression like so:

string EntitySQL = grid.MasterTableView.FilterExpression;

and get: "((Convert.ToInt32(iif(it[InstallationYear\"]==Convert.DBNull,null,it[\"InstallationYear\"])) >= 1974) AND ( Convert.ToInt32(iif(it[\"InstallationYear\"]==Convert.DBNull,null,it[\"InstallationYear\"])) <= 2000))"

But when I use GetEntitySqlFilterExpression like so:

string EntitySQL = grid.MasterTableView.GetEntitySqlFilterExpression();

The result is blank. For some reason GetEntitySqlFilterExpression() does not process the "between" filtering command. I'd like to use this expression because it is much easier to understand and use. Does anyone know why the "between" filtering command is not recognized?

7 Answers, 1 is accepted

Sort by
0
Marin Bratanov
Telerik team
answered on 04 Mar 2019, 05:25 PM
Hello Terry,

I just answered your support ticket with this question and I am pasting my answer here for anyone else with a similar situation. I'd suggest we continue there if you have outstanding questions and then post the final information here for brevity.

The built-in Between filter is available for DateTime columns and this does not look like values for a date field. I am attaching here an example I built for you that shows how this is supposed to work and it returns the expected expressions for me. If I am missing something, please modify this sample to showcase the issue so I can examine it and offer a more concrete answer.

The built-in filtering of the grid is done in-memory and is not an actual SQL operation, and there is no built-in feature that provides ready-made commands or the filtered data set. For example, in actual SQL queries, you can use the "between" keyword/command, while RadGrid does not use it. Thus, you may want to create your own queries that are better optimized and suited to the particular task and setup.

If you want to implement a custom form of filtering for the grid or other purposes, you can try capturing the filter operation in the ItemCommand event of the grid (see here). In case using the .FindControl() approach from the article does not suit your needs for obtaining the values and command, you can parse the __EVENTARGUMENT field from the POST data - it contains the grid name, command, column, values and filter operation delimited by pipe symbols.


Regards,
Marin Bratanov
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Terry
Top achievements
Rank 1
answered on 05 Mar 2019, 05:41 AM

Hi Marin,

I'm aware of the Between filter on the DateTime column - I use it extensively in many of my projects. What I need here though is to use the Between filter on a numeric column or a GridBoundColumn with DataType set to integer. You say that the Between filter is only available on the DateTime columns but I am using it on an integer column and it filters fine (See attached image). What I need is to grab the partial query through the GetEntitySqlFilterExpression. Right now this expression brings back an empty string. As I pointed out above, I can get it through the FilterExpression but there is no easy way to remove all the unwanted jargon around the actual query. From my example above, what I really want is " AND InstallationYear >= 1974 AND InstallationYear <= 2000 ".

If you maintain that the Between filter only works correctly on the DateTime columns then why is it filtering in my case? Is there another way to grab the partial query?

 

Thank you for your help Marin,

Terry

0
Marin Bratanov
Telerik team
answered on 06 Mar 2019, 11:01 AM
Hello Terry,

Maybe I did not explain this very well, I was put off the scent by the field name, I assumed it had to be a date column because it said "installation year". I'm sorry about that.

The Between option is available for the DateTime column, but not for a NumericColumn. You can also use the Between filter with a BoundColumn, but its behavior will depend on the data type. Using an integer should do the trick.

I am attaching a basic example that works fine for me on the latest version of the controls and I hope comparing against it and ensuring that the latest version is used will help you get this working. If not, please modify this sample to showcase the problem and send it back to me so I can take a look.


Regards,
Marin Bratanov
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Terry
Top achievements
Rank 1
answered on 08 Mar 2019, 02:03 AM

Hi Marin,

Thank you for the example you posted. I can now see how this works. I've realized my issue is that I'm generating the grid dynamically and therein I'm having the issue. So here is an excerpt from  my code, at the point that I'm generating a GridBoundColumn:

GridBoundColumn col = new GridBoundColumn();
col.DataField = item.ColumnName;
col.UniqueName = item.ColumnName;
col.SortExpression = item.ColumnName;
col.HeaderText = item.HeaderName;
col.HeaderStyle.Width = Unit.Pixel(item.ColumnWidth);
col.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
col.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
col.DataType = typeof(int);

grid.MasterTableView.Columns.Add(col);

 

As you can see from the second-to-the-last line, I'm setting a datatype of int. This is not working. Somehow, I need to set a datatype of System.Int32. I've tested this on another file where the grid is declared normally and I set the DataType to System.64, like so:

                        <telerik:GridBoundColumn DataField="WorkOrderID" HeaderText="WO ID"
                            HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="80"
                            UniqueName="WorkOrderID" SortExpression="WorkOrderID" FilterControlWidth="40px" DataType="System.Int64" />

This will  not work. When I switch to System.Int32 though, it does work. IN my situation above though, I'm generating the grid dynamically and I can't seem to figure out how to do this. The best answer I've found so far is to use: col.DataType = typeof(int);

This does not work.How do I do this? And please do not point me to the following link. There is nothing there about DataType.

https://docs.telerik.com/devtools/aspnet-ajax/controls/grid/defining-structure/creating-a-radgrid-programmatically

 

Thank you in advance Marin.

0
Terry
Top achievements
Rank 1
answered on 08 Mar 2019, 06:39 AM

Hi Marin,

I did a little more digging and found how to specify the GridBoundColumn DataType from the following link: https://www.telerik.com/forums/problem-with-server-side-set-dataformatstring-on-gridboundcolumn

I was hoping that would fix my issue but it didn't. So back to square one. I'm attaching the file that I am having trouble with along with an image of what the output is. As you can see from the image, I've tried to implement some of the code you sent me. Unfortunately, there's too much info coming from the database for me to get the file working with actual data. The column names are stored in a table. You can see their type from the SettingBO.cs file.

Can you look at this and see why it's not pulling the correct filter string. If you can't find anything, it would really be helpful if you could put together a file where the grid is put together from code-behind, in a similar fashion as I have it, and get it pulling correctly.

Thank you so much Marin.

It seems that I cannot upload a zip file. You can download it though from http://dev2.aimsteam.net/CWDNET/CommonGrid.zip
0
Marin Bratanov
Telerik team
answered on 10 Mar 2019, 09:04 AM
Hi Terry,

I am attaching a modified version of my sample that keeps working correctly so you can compare against it.

I can also suggest that you try simplifying the code - remove most of the columns, just keep the bound column, for example. Also, make sure that the type of the column in the DataTable given to the grid as its data source is properly declared and is also an int32. Basically, get something as simple as my page to run. Once that works, start adding the complexity back to see what breaks the logic. It may be some control tree change done in custom template columns, or there may even be some irregularity in the data.


Regards,
Marin Bratanov
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Marin Bratanov
Telerik team
answered on 10 Mar 2019, 09:07 AM
I forgot to mention this explicitly, but still - remove the AJAX setup (both the RadAjaxManager and the RadAjaxPanel).

The partial rendering may be hiding some errors thrown on the server and also using both of the control to ajax-enable the same part of the page can lead to issues (see here).

On uploading archives - this is allowed in private suppor tickets, and this is a pubic forum thread. If you like, we can move back to the private ticket.

--Marin

Tags
Grid
Asked by
Terry
Top achievements
Rank 1
Answers by
Marin Bratanov
Telerik team
Terry
Top achievements
Rank 1
Share this question
or