Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jan 17, 2013 12:39 AM by Decker Dong - MSFT
Jan 15, 2013 08:29 PM|LINK
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
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.
Jan 15, 2013 11:26 PM|LINK
in sql query i use like this :
... WHERE 'apples,pears,bananas' like '%' + fruits + '%'
usualy we have query like this
select ... where fruits like '%' + @search + '%'
but, now i change the direction
select ... where @search like '%' + fruits + '%'
i am not sure if anyone use this technique, but it work for me.
Jan 16, 2013 10:14 AM|LINK
The question is how do you apply this method to the query string of an ObjectDataSource?
Jan 17, 2013 12:39 AM|LINK
how do you pass this list to the DAL?
Yes this is an interesting and until Microsoft hasn't such a control or function to directly do with that.
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.