Table Adapter for multiple tables?

  • Thread starter Thread starter offwhite
  • Start date Start date
O

offwhite

I would like to create a Table Adapter which can work with multiple
tables. Ideally it would still work with the configuration wizard. I
have tried to add a query which joins 2 tables, but then it does not
create the insert, update and delete methods.

Is there a way to do this? Can I do it if I create a View?

Brennan Stehling
http://brennan.offwhite.net/blog/
 
This question has been asked a dozen dozen times. That's why I wrote an
entire chapter in my book about the answer. Basically, a TableAdapter is
designed to handle a rowset. If it's returned from a SELECT against a single
table the CommandBuilder (behind the scenes) can generate simple CRUD
commands. When you execute a JOIN, the simplistic CommandBuilder and
TableAdapter cannot figure out how to build correlated UPDATE or other
action commands. As I said, I wrote a whole chapter on this to walk you
through the process of working with more complex products.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Chapter 6. Yes, it's a big chapter, but the mechanics are there. In several
other chapters (esp. Chapter 8) I show how to use ADO.NET to build and
manage the rowsets, chapter 12 discusses updates and concurrency.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Bill,

What I did yesterday start with a TableAdapter with single table. I
had it generate the stored procedures. I then updated it to join 2
tables for the query which brought in more fields. I then adjusted the
Insert, Update and Delete routines accordingly. The output parameters
work just fine and I can bind databound controls to the
ObjectDataSource which uses this new TableAdapter.

It all works, but what I want to know is if a View or another approach
is supported by the configuration wizard. As best as I can tell the
approach I took is the only way to go.

As I browsed your chapter (actually PDF download from Safari Books) I
only saw that multiple tables was just referenced as a limitation but I
did not see an example to make it work later in the chapter.

What I did see is some examples with the DataRelation. Is that what
you suggest as the way to go?

Brennan
 
I'm no fan of the TableAdapter for a number of reasons. It's "brittle" in
that once you use the VS IDE to set it up, the schema can't change. If
(when) it does, your hard-coded TableAdapter is busted and has to be
rebuilt. The CommandBuilder (used at design time by the wizards)-generated
CRUD commands are simplistic and don't work with multiple tables or complex
views. Yes, I talk about stored procedures as well quite a bit (another
whole chapter). Frankly, I think untyped DataSets are fine for more complex
rowsets and since you can't really expect a database to have updatable
tables (the DBAs don't permit it and the DBMS architecture requires complex
update schemes), the CommandBuilder runs out of headroom rather quickly.

I expect that you need to do more than skim to get the meat out of the
book...
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Offwhite,

In my opinion can you use the wizards, to create an update command for each
table.

However be aware that you do the update using the rowstate

http://msdn2.microsoft.com/en-us/library/system.data.datarowview.aspx

In common is the routine,
first the added parent rows
secondly the added child rows
thirth update the parent rows
fourth update the child rows
fifth delete the child rows
sixt delete the parent rows

Than do an acceptchanges on the dataset because you are using copies.

I hope this helps,

Cor
 
Cor,

Why the add, update and delete?

What I have done is create the Select, Insert, Update and Delete
methods on the TableAdapter with input parameters against a stored
procedure which works with the multiple tables. They still appear as a
single table to the TableAdapter, but I handle all of the details like
child tables with foreign key references.

It is working quite well, but it would be great if the wizard would be
smart enough to realize that I am joining tables in the Select method
and then generate the Insert, Update and Delete methods. Perhaps that
level of complexity is something the wizard is not meant to attempt.

At least I can back them with my own sprocs.

Brennan
 
Brennan,

The (Microsoft) wizards are not able to do what you ask, we don't know what
the future will bring, but I think that all real .Net developers would need
this more than every other thing.

Cor
 
Cor,

I think DLinq will make these sorts of concerns go away, hopefully. I
have yet to dig into that with this sort of scenario. However, that
does appear it will encourage developers to glue the BLL directly to
the database server instead of planning and implementing a DAL. So it
could introduce as many problems as it solves. I hope this concern is
being addressed.

Brennan
 
If you had read my books you would know that pessimistic locks make sense in
some cases, but the discussion here has little to do with that approach.
What I am suggesting is that the query strategy that requires 5-minute
locking be reevaluated. ADO.NET is not JUST for disconnected data
architectures. It can (and is) used for Connected architectures as well.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
This thread has been entirely unhelpful. The responses do not appear
to relate directly to the problem I proposed and instead raise points
which are not explained in relation to the original problem.

Please, if you do respond to a question do so by making a point which
is related to the original post and then explaining your point instead
of relying on innuendo or references to a book most people reading the
newsgroup do not have on hand.

Brennan
 
But they *did* answer your question, several times, and the
answer is: No.

The only way to accomplish this is the way you did.

There are too many possibilities for this kind of thing to be
auto-generated. I'd be surprised if DLinq resolves this.

As for Mr. Vaughn, he is just trying to help you by selling
you a copy of his fine book. (Not that there's anything wrong
with that.)

Good luck.

Robin S.
 
Robin,

I would not say that their response was simply no. It looked like that
Vaughn was trying to imply that the answer was buried somewhere in his
book. Since I have access to that book through the Safari bookshelf I
was able to skim that chapter and it did not cover my question. It
instead covered a single table to adapter example.

A simple no was the response would have prevented me from search for
the "right" solution.

I often answer questions when I have the right information and do so by
responding with a short answer and a link or two to MSDN docs which
support my answer so that readers can get right to solving their issue.
There is no need to rant about how a TableAdapter or DataSet does not
solve every problem in ADO.NET or to suggest vaguely that my approach
is somehow flawed. Doing so is just a distraction.

Brennan
 
I understand what you're saying. It must have just hit a button.
It's a question that gets asked a lot.

Robin S.
 
Back
Top