?
Solved

Table Adapter ad hoc query

Posted on 2009-02-18
9
Medium Priority
?
326 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:Starr Duskk
  • 5
  • 4
9 Comments
 
LVL 18

Accepted Solution

by:
David Robitaille earned 2000 total points
ID: 23673459
Personaly, I build a general query using nullable parameter
Where ((fieldx = @paramX) or (@paramX is null) and (fieldy = @paramY) or (@paramY is null)
 
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 23674706
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
 
LVL 18

Expert Comment

by:David Robitaille
ID: 23674864
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:Starr Duskk
ID: 23675091
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
 
LVL 18

Expert Comment

by:David Robitaille
ID: 23675200
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
 
LVL 2

Author Comment

by:Starr Duskk
ID: 23676164
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
 
LVL 2

Author Comment

by:Starr Duskk
ID: 23676189
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
 
LVL 2

Author Comment

by:Starr Duskk
ID: 23676231
okay, I'm gonna try it your way. I'll let you know if I run into trouble.
thanks.
 
0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 23676337
ok, I wiil stay tuned on that one...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question