TableAdapter vs. DataAdapter: Ad Hoc Queries

[msnews.microsoft.com]

Dear group,

Am I right in my understanding that when using strongly typed table adapters
I am limited to the (select) statements defined in the various .FillBy...
and .GetDataBy... functions?

I'd like to build a flexible select statement on a table based on a filter
set by the user. He should be able to restrict the data by setting the
conditions on different table columns (WHERE clause), where the filter can
be any comparison operator like =, <, <=, etc, or a predicate like BETWEEN,
LIKE, etc.

As I understand, a select statement built this way can not be passed to a
strongly typed table adapter.

Many thanks for your suggestions,
Etienne

 

[Cor Ligthert[MVP]]

Etienne,

Dear group,

Am I right in my understanding that when using strongly typed table
adapters I am limited to the (select) statements defined in the various
.FillBy... and .GetDataBy... functions?

I'd like to build a flexible select statement on a table based on a filter
set by the user. He should be able to restrict the data by setting the
conditions on different table columns (WHERE clause), where the filter can
be any comparison operator like =, <, <=, etc, or a predicate like
BETWEEN, LIKE, etc.

I am curious what is your definition of strongly typed in the latter?

Cor

 

[Etienne-Louis Nicolet]

Etienne,
I am curious what is your definition of strongly typed in the latter?

Cor

Dear Cor,

Sorry, I'm quite new to these subjects, so my terminology might be wrong.
- When talking about strongly typed table adapters I mean the ones created
when using the "Add New Data Source..." wizzard from the Data menu in
VS2008.
- By weakly typed data adapter I mean an instance of a
System.Data.SqlClient.SqlDataAdapter.

Kind regards,
Etienne

 

[Miha Markic]

Hi Eitenne,

I think you have at least two options to do it.
a) derive a class from that table adapter and add a method that uses your
select statement.
b) create a partial class and add that method
Either way look at xxx.Designer.cs auto generated code how the auto
generated methods are done.

 

[Cor Ligthert [MVP]]

Etienne

You can use as the code for your selects is not implemented in your DataSet
use everything that is possible as regular T-SQL and can be given in a
parameter. Problem is that there are only few things that can be used in
T-SQL in a parameter.

Cor

 

[William Vaughn \(MVP\)]

First, I suggest you enter your name (even a pseudonym) instead of
"msnews.microsoft.com".
It is entirely possible (and a recommended practice) to construct either
parameter-driven stored procedures or parameter-driven ad hoc queries to
populate the TableAdapter rowsets. I've written a few articles on this for
SQL Server magazine as well as for Developer.Com
http://www.developer.com/db/article.php/10920_3693236_1 that can walk you
through the process. Look for the section of the article that shows how to
add new TableAdapter queries.

Of course, I discuss this in depth in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Hi Bill,

How does this one go or on what page have you describen this on your book.

Select @pA from @pB Where @pC @pD @pE

@pA = *
@pB = Whatever
@pC = DateNow
@pD = >
@pE = "2-4=2047"

I am curious because although I use a lot of Linq now am I curious because I
was never able to do this

Cor

 

[Etienne-Louis Nicolet]

Cor, Miha, William,

First of all: Sorry for having used a wrong name, I realized it after having
sent my first two posts and corrected it.

Again, I'm fairly new to all these subjects, so your answers are rather
nourishing and it takes me some time to understand, so please apoligize for
not reacting that fast to your suggestions. I just bought William's "Guide
to Visual Studio and SQL Server" yesterday, but getting through 1'000 pages
is not a matter of minutes ;-)

Your help is most valuable to me, I'd like to take the opportunity to thank
you all for it. Unfortunately I do not really come along with the help
provided by Visual Studio, I have the feeling that mostly every information
is there, but it's hardly possible to find it.

Kind regards,
Etienne

 

[Cor Ligthert [MVP]]

Ettiene,

In my idea as Bill talks about his book, then it is the Hitchhiker's Guide.

Although I not always agree what with Bill writes, it is for sure a very
practical book for those who are new to .Net and SQL server.



Cor

 

[Miha Markic]

Hi Etienne,

Cor, Miha, William,

First of all: Sorry for having used a wrong name, I realized it after
having sent my first two posts and corrected it.

I don't care and Bill probably doesn't read to the end of the post (where
your name is )

Again, I'm fairly new to all these subjects, so your answers are rather
nourishing and it takes me some time to understand, so please apoligize
for not reacting that fast to your suggestions.

Indeed. If you are new it will take some time. ado.net isn't trivial. I
suggest you to read a book (perhaps Sahil Malik's one) on the topic and
start experimenting.

I just bought William's "Guide

to Visual Studio and SQL Server" yesterday, but getting through 1'000
pages is not a matter of minutes ;-)

Your help is most valuable to me, I'd like to take the opportunity to
thank you all for it. Unfortunately I do not really come along with the
help provided by Visual Studio, I have the feeling that mostly every
information is there, but it's hardly possible to find it.

In time you will. You have to understand the basics first.
Anyway, if you have questions go ahead, ask them.

 

[mail]

I'd like to build a flexible select statement on a table based on a filter

set by the user. He should be able to restrict the data by setting the
conditions on different table columns (WHERE clause), where the filter can
be any comparison operator like =, <, <=, etc, or a predicate like BETWEEN,
LIKE, etc.

You can do this by creating a partial class. Assume your dataset is
called MyDataSet. When you created your dataset a MyDataSet.vb file
was created automatically but you probably ignored it.

Assuming your MyDataSet dataset contains a Person table you can add
the partial class as below, then call the GetPersons function from
elsewhere in your code using totally (so long as your select returns
the fields the datatable is expecting) adhoc SQL command string

eg. GetPersons("select * from Persons where Age<18")




*** Here's the partial class ***

Namespace MyDataSetTableAdapters

Partial Public Class PersonTableAdapter

Public Overridable Overloads Function GetPersons(ByVal
sqlCommand As String) As MyDataSet.PersonDataTable

Dim cmd As New OleDb.OleDbCommand(sqlCommand)
Return GetPersons(cmd)

End Function

Private Function GetPersons(ByVal cmd As OleDbCommand) As
MyDataSet.PersonDataTable

cmd.Connection = Me.Connection
Me.Adapter.SelectCommand = cmd

Dim dataTable As MyDataSet.PersonDataTable = New
MyDataSet.PersonDataTable
Me.Adapter.Fill(dataTable)
Return dataTable
End Function

End Class

End Namespace





Regards,
Hawbsl

 

[William Vaughn \(MVP\)]

Cor, one cannot pass anything except WHERE-clause constraints to a SP or
arguments to operators that accept them (like TOP). The list of columns and
the list of tables in a SELECT cannot be passed as parameters unless the
query is generated on the fly as a string and this is not recommended as it
leads to SQL injection attacks. This is how TSQL works.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Bill,

There was my answer based on.



Cor

Cor, one cannot pass anything except WHERE-clause constraints to a SP or
arguments to operators that accept them (like TOP). The list of columns
and the list of tables in a SELECT cannot be passed as parameters unless
the query is generated on the fly as a string and this is not recommended
as it leads to SQL injection attacks. This is how TSQL works.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________