What is the best way of filtering an ObjectDataSource, particulary when retrieving complex queries?
As an example imagine the following simplified example:
You have an ObjectDataSource connected to a gridview and it is displaying fruit (apples, pears, bananas, oranges, kiwi and clementines), you also have a CheckBoxList which allows multiple boxes to be selected and it is showing the type of fruit you want
to display.
In the CheckBoxList you select [apples], [pears] and [oranges].
To pass this as a list to the ObjectDataSource I would normally have a string input at the data access layer which would take this list (as "apples,pears,oranges") and then pass this as a parameter to the SQL server and in the select procedure it would create
an IN( ) predicate using this list.
My question is this: As you cannot pass a checkboxlist as a control parameter to the objectdatasource how do you pass this list to the DAL? Currently I have VB in the forms code behind that itterates through the checkboxlist, creates the list and then stuffs
that in a hidden control on the form, that hidden control on the form is then in the SelectParameters list of the ObjectDataSource, but now I have a security issue in that I am exposing my IN list and part of my WHERE clause to the client.
I am willing to pour as much time and effort as required into doing this 'properly' but I cannot find anywhere what the correct method is of making queries against ObjectDataSources when you may have a lot of parameters or when the parameters may be complicated
as shown above.
If you have managed to follow this as far as this then thank you very much for your patience, I am sorry if I am not explaining this very well. Any help or guidance would be appreciated.
UselessChimp
Member
210 Points
110 Posts
Filtering Data Source by Multiselect checkbox
Jan 15, 2013 08:29 PM|LINK
All,
What is the best way of filtering an ObjectDataSource, particulary when retrieving complex queries?
As an example imagine the following simplified example:
You have an ObjectDataSource connected to a gridview and it is displaying fruit (apples, pears, bananas, oranges, kiwi and clementines), you also have a CheckBoxList which allows multiple boxes to be selected and it is showing the type of fruit you want to display.
In the CheckBoxList you select [apples], [pears] and [oranges].
To pass this as a list to the ObjectDataSource I would normally have a string input at the data access layer which would take this list (as "apples,pears,oranges") and then pass this as a parameter to the SQL server and in the select procedure it would create an IN( ) predicate using this list.
My question is this: As you cannot pass a checkboxlist as a control parameter to the objectdatasource how do you pass this list to the DAL? Currently I have VB in the forms code behind that itterates through the checkboxlist, creates the list and then stuffs that in a hidden control on the form, that hidden control on the form is then in the SelectParameters list of the ObjectDataSource, but now I have a security issue in that I am exposing my IN list and part of my WHERE clause to the client.
I am willing to pour as much time and effort as required into doing this 'properly' but I cannot find anywhere what the correct method is of making queries against ObjectDataSources when you may have a lot of parameters or when the parameters may be complicated as shown above.
If you have managed to follow this as far as this then thank you very much for your patience, I am sorry if I am not explaining this very well. Any help or guidance would be appreciated.
oned_gk
All-Star
31407 Points
6419 Posts
Re: Filtering Data Source by Multiselect checkbox
Jan 15, 2013 11:26 PM|LINK
in sql query i use like this :
usualy we have query like this
but, now i change the direction
i am not sure if anyone use this technique, but it work for me.
UselessChimp
Member
210 Points
110 Posts
Re: Filtering Data Source by Multiselect checkbox
Jan 16, 2013 10:14 AM|LINK
The question is how do you apply this method to the query string of an ObjectDataSource?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Filtering Data Source by Multiselect checkbox
Jan 17, 2013 12:39 AM|LINK
Hi,
Yes this is an interesting and until Microsoft hasn't such a control or function to directly do with that.
So:
1) Please save the string into Session and carry that to another page.
2) Please directly pass the generated string by calling the method from DAL layer for the specific function.