Accessing query command

[John]

Hi

I have a strongly typed dataset in my winform app. I need to access a
specific command of a specific query of a specific table adapter of a
specific table in the dataset in my code in one of the forms? How can I
achieve this?

I need something like this;

x = MyDataset.MyTable.MyTableTableAdapter.MyQueryOne.CommandText

Thanks

Regards

 

[Cor Ligthert[MVP]]

John,

This is one of the main reasons why I don't like the TableAdapter.

Cor

 

[John]

There is no way to achieve this? How does one change the existing SQL?

 

[Cor Ligthert[MVP]]

John,'

I would use simple a DataAdapter with the DataTable, the table adapter is
AFAIK nothing more then an overloaded DataAdapter inside the DataSet.

(The latter is for the fill in fact a smart datareader, before William gives
a reply).

Cor

 

[miher]

There is no way to achieve this? How does one change the existing SQL?

By the designer You can change the sql.



The commands are hold in the TableAdapters CommandCollection array. This is
protected so You cant really get it from outside. If You really want to do
this, expose the commands by for example a public property, since the
tableadapter is generated as a partial class.
To do this:
- go to Your dataset design view
- select the tableadapter/right click -> view code
- in the generated skeleton place a property to expose the command texts,
for example
public string MyCommand
{
get { return CommandCollection[0].CommandText; }
}


-Zsolt

 

[Cor Ligthert[MVP]]

Miher,

A way that can be done, however the change that I forget this as I
regenerate the dataset is 100,
This happens of course not to you. However, I don't think I am not the only
one where this happens.

Cor

There is no way to achieve this? How does one change the existing SQL?

By the designer You can change the sql.



The commands are hold in the TableAdapters CommandCollection array. This
is protected so You cant really get it from outside. If You really want to
do this, expose the commands by for example a public property, since the
tableadapter is generated as a partial class.
To do this:
- go to Your dataset design view
- select the tableadapter/right click -> view code
- in the generated skeleton place a property to expose the command texts,
for example
public string MyCommand
{
get { return CommandCollection[0].CommandText; }
}


-Zsolt

 

[Cor Ligthert[MVP]]

change has to be chance, the times that I make this mistake is almost as
well 100%

Cor

Miher,

A way that can be done, however the change that I forget this as I
regenerate the dataset is 100,
This happens of course not to you. However, I don't think I am not the
only one where this happens.

Cor

There is no way to achieve this? How does one change the existing SQL?

By the designer You can change the sql.

I need something like this;

x = MyDataset.MyTable.MyTableTableAdapter.MyQueryOne.CommandText

The commands are hold in the TableAdapters CommandCollection array. This
is protected so You cant really get it from outside. If You really want
to do this, expose the commands by for example a public property, since
the tableadapter is generated as a partial class.
To do this:
- go to Your dataset design view
- select the tableadapter/right click -> view code
- in the generated skeleton place a property to expose the command texts,
for example
public string MyCommand
{
get { return CommandCollection[0].CommandText; }
}


-Zsolt

 

[William Vaughn \(MVP\)]

As Cor said (I think), even if you could change it, the results would be
less than desirable. Consider that the entire class is generated code based
on the CommandText. Unlike an untyped DataSet, the strongly typed
TableAdapter assumes that the SQL used to fetch the rowset is unchanged. If
you simply want to change the WHERE clause, then you need to create another
Fill method (using the TableAdapter UI) that returns a different set of rows
but with the same signature (column schema).

hth

--
__________________________________________________________________________
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)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[miher]

Hi,

I hope i understood Your post right. I suppose i might have been unclear,
but the code i posted should be put into <dataset>.cs file, so it wont be
overwritten when the dataset is regenerated. As i know when one selects the
tableadapter in the designer and selects "view code", it will open the
<dataset>.cs file, maybe i should have explicitly mentioned that. I would
like to point out that You are right, this code can be easily broken, if
someone wants to.

Also please note that what i posted here is only a tip, i never mentioned
that this is the best possible solution, as always every other solution is
welcomed.

Please let me know if i misunderstood Your post, or if i know something
wrong so i can learn from it.

-Zsolt

change has to be chance, the times that I make this mistake is almost as
well 100%

Cor

Miher,

A way that can be done, however the change that I forget this as I
regenerate the dataset is 100,
This happens of course not to you. However, I don't think I am not the
only one where this happens.

Cor

There is no way to achieve this? How does one change the existing SQL?
By the designer You can change the sql.


I need something like this;

x = MyDataset.MyTable.MyTableTableAdapter.MyQueryOne.CommandText



The commands are hold in the TableAdapters CommandCollection array. This
is protected so You cant really get it from outside. If You really want
to do this, expose the commands by for example a public property, since
the tableadapter is generated as a partial class.
To do this:
- go to Your dataset design view
- select the tableadapter/right click -> view code
- in the generated skeleton place a property to expose the command
texts, for example
public string MyCommand
{
get { return CommandCollection[0].CommandText; }
}


-Zsolt

 

[John]

Hi Bill

I only read one record at a time which has advantage of scalability among
other therefore can not use sorting on bound controls.

Thanks

Regards

 

[Cor Ligthert[MVP]]

John,

What is then the reason that you use a TableAdapter instead of a DataReader?

Cor

 

[Miha Markic]

Indeed, you are right. Or place the code in some other partial class. It
won't be overwritten.

 

[Miha Markic]

Strong typing?

 

[Miha Markic]

John,

I'd consider LINQ to SQL or Entity Framework or a 3rd party ORM (i.e.
LLBLGenPro) as an alternative.

 

[Cor Ligthert[MVP]]

Miha,

You can be right, I forgot the partial classes.

However, then in the way of a public property to the private subject.

Cor

Indeed, you are right. Or place the code in some other partial class. It
won't be overwritten.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I hope i understood Your post right. I suppose i might have been unclear,
but the code i posted should be put into <dataset>.cs file, so it wont be
overwritten when the dataset is regenerated. As i know when one selects
the tableadapter in the designer and selects "view code", it will open
the <dataset>.cs file, maybe i should have explicitly mentioned that. I
would like to point out that You are right, this code can be easily
broken, if someone wants to.

Also please note that what i posted here is only a tip, i never mentioned
that this is the best possible solution, as always every other solution
is welcomed.

Please let me know if i misunderstood Your post, or if i know something
wrong so i can learn from it.

 

[John]

Good question. The app has been developed peace meal and initially it
sounded like an OK idea when there were only handful of queries to a table
adapter...now it seems its easier if I can somehow make query on the fly and
assign to table adapter due to large number of permutation..

 

[John]

Silly question but can controls be bound to a data adapter somehow?

 

[William Vaughn \(MVP\)]

Not really, but if you load the data returned from the DataReader into a
table (as in:

MyDataTable.Load MyDataReader

The DataTable is binable.

Many folks have moved on beyond the DataAdapter but it's a perfectly okay to
use if you simply need a rowset and it's Fill method can return any number
of DataTables with one line of code. It's simple, fast and unless you're
trying to perform updates on complex resultsets, it can work fine. Being
untyped is not the kiss of death. IMHO typed datasets (TableAdapters) are
overblown as to their "security" and "type safety". They still don’t deal
with business rules or (any) data validation logic beyond simple type tests.
Yes, they can be extended but if you don't do it right, you're worse off
than before.

--
__________________________________________________________________________
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)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[William Vaughn \(MVP\)]

You're kidding I hope.

--
__________________________________________________________________________
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)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

John,

I'd consider LINQ to SQL or Entity Framework or a 3rd party ORM (i.e.
LLBLGenPro) as an alternative.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi Bill

I only read one record at a time which has advantage of scalability among
other therefore can not use sorting on bound controls.