Table Adapter ad hoc query

Say you have a search form, and it is detailed. The user fills out all kinds of possibilities, that when the query string is built, it has a load of if/else's etc.

Now you want to implement that behavior in a table adapter. Table adapters require a set query string or a stored procedure don't they? How would you implement the if/else behavior required to build the query string within a table adapter?

thanks.
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
 
David RobitailleConnect With a Mentor Analyst ProgrammerCommented:
Personaly, I build a general query using nullable parameter
Where ((fieldx = @paramX) or (@paramX is null) and (fieldy = @paramY) or (@paramY is null)
 
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
So if the nullable parameter is null, it won't run the first part at all and will return all results?
Is there any way to pass in a pre-created sql query string to use?
 
0
 
David RobitailleAnalyst ProgrammerCommented:
for your first question, yes that exacly the goal of this.
for the other well you could use sp_executesql
http://msdn.microsoft.com/en-us/library/ms175170.aspx
But I must Warn you! It`s not a good practice. Table adapters are there (along others reason) to make sure your SQL is "safe" (help avoid sql injection)  and encapsulate the data tier form your logic. You are not supposed to do that. It is really better to put parametrized query there.
Another solution is to wrtie multiple query, but i dont like that solution.
 
 
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Since I have a lot of existing code created for ado.net use that I want to pass into the table adapter as a query string, I'd like to try your second suggestion.
But I dont' understand how to implement it.
On the table adapter, I'm creating a new query, I'm selecting the "Select which rows to return" option.
I'm inputting:
EXECUTE sp_executesql @SQLString
And it errors with:
"Generated Select statement. The EXECUTE SQL Cosntruct or statement is not supported."
I'm using 2008 sql server.
How do I properly implement this?
Thanks.
 
0
 
David RobitailleAnalyst ProgrammerCommented:
I didn`t even try it myself, because it like bypassing the tableadapter macanism
 Anyway, you could try to  put that in a stored procedure, here a link.
http://forums.asp.net/p/1347311/2742422.aspx
But, again. Its not a good idea. I understand that you dont want to reprogram all your stuff, but ....
By the way, i noticed you are already engaged, (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24141412.html)
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
yes, I didn't know I'd get the same answer. And so far, I've not gotten one that answers how to make it work.
I'll check out the link. Thanks.
 
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Also, your first answer is much different and this question is about an ad hoc query, that one is about passing a query string.
 
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
okay, I'm gonna try it your way. I'll let you know if I run into trouble.
thanks.
 
0
 
David RobitailleAnalyst ProgrammerCommented:
ok, I wiil stay tuned on that one...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.