Warning! Serious design flaw dataset / table adapter designer

[Jim Rand]

VS 2005 provides for dragging tables from a server connection to the dataset
designer creating both data tables and related table adapters. For the most
part, the relationships between tables are automatically discovered and
rendered on the designer surface.

For my application, it is necessary to change each SELECT, UPDATE, INSERT
and DELETE statement to customize the handling of timestamps (TS --> CAST(TS
AS INT) AS TS), concurrency conflict handling and retrieving the
autoincrement keys. OK - that's fine but somewhat painful (i.e., labor
intensive [12 tables * 4 sql statements]).

Switching from SQL Server 2000 to SQL Server 2005, you go from a table owner
to schema name metaphor. For example, dbo.OfficeType becomes
AgentNET.OfficeType where dbo is the owner (in 2000) and AgentNET is the
schema (in 2005).

Making the change in the designer should be relatively simple by changing
the underlying SQL statements as in:

SELECT OfficeTypeID, OfficeType, Description, ListOrder, CAST(TS AS INT) AS
TS FROM dbo.OfficeType

SELECT OfficeTypeID, OfficeType, Description, ListOrder, CAST(TS AS INT) AS
TS FROM AgencyNET.OfficeType

However, the designer appears to have screwed up the relationships between
tables preventing the application from working. The only fix was to delete
the xsd file and start over from scratch!

The dataset is a disconnected container for holding a subset of the data on
the desktop. The table adapter is not part of the dataset - it is a wrapper
to the data adapter that handles CRUD (create, read, update, delete)
operations. Combining both dataset and adapters clouds the distinction.
Using the designer, changing where the data comes from breaks the container.

For my development, I'm going back to having one container (xsd) for the
dataset with no table adapters and a separate container for the data
adapters. That way, the backend can be changed without breaking the rest of
the application. So much for dragging tables to designer surface ever
again.

This was definitely one of those "You've got to be **#&$*#@ me!" moments in
life.

PS to Microsoft: Did you really try this stuff out prior to shipping?

 

[William \(Bill\) Vaughn]

I'm going to let MS comment on this...

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

VS 2005 provides for dragging tables from a server connection to the
dataset designer creating both data tables and related table adapters.
For the most part, the relationships between tables are automatically
discovered and rendered on the designer surface.

For my application, it is necessary to change each SELECT, UPDATE, INSERT
and DELETE statement to customize the handling of timestamps (TS -->
CAST(TS AS INT) AS TS), concurrency conflict handling and retrieving the
autoincrement keys. OK - that's fine but somewhat painful (i.e., labor
intensive [12 tables * 4 sql statements]).

Switching from SQL Server 2000 to SQL Server 2005, you go from a table
owner to schema name metaphor. For example, dbo.OfficeType becomes
AgentNET.OfficeType where dbo is the owner (in 2000) and AgentNET is the
schema (in 2005).

Making the change in the designer should be relatively simple by changing
the underlying SQL statements as in:

SELECT OfficeTypeID, OfficeType, Description, ListOrder, CAST(TS AS INT)
AS TS FROM dbo.OfficeType

SELECT OfficeTypeID, OfficeType, Description, ListOrder, CAST(TS AS INT)
AS TS FROM AgencyNET.OfficeType

However, the designer appears to have screwed up the relationships between
tables preventing the application from working. The only fix was to delete
the xsd file and start over from scratch!

The dataset is a disconnected container for holding a subset of the data
on the desktop. The table adapter is not part of the dataset - it is a
wrapper to the data adapter that handles CRUD (create, read, update,
delete) operations. Combining both dataset and adapters clouds the
distinction. Using the designer, changing where the data comes from breaks
the container.

For my development, I'm going back to having one container (xsd) for the
dataset with no table adapters and a separate container for the data
adapters. That way, the backend can be changed without breaking the rest
of the application. So much for dragging tables to designer surface ever
again.

This was definitely one of those "You've got to be **#&$*#@ me!" moments
in life.

PS to Microsoft: Did you really try this stuff out prior to shipping?

 

[Earl]

You've probably seen my rants on this subject. If not, Google them.
TableAdapters should come with the warning, "don't try this at home ..." If
I drag-drop a dataset, I always immediately delete the TableAdapters and use
the good ole' SqlDataAdapters in order to get what I consistently expect.
From what I have read, Microsoft is supposedly going to revisit the concept
of TableAdapters. Here's hoping. If I worked for a large company, I suppose
losing weeks of work time screwing around with the godforsaken things might
make some sense, but to an independent developer, it was a painful lesson to
learn. To MS: Loved the idea, hated the implementation.

VS 2005 provides for dragging tables from a server connection to the
dataset designer creating both data tables and related table adapters.
For the most part, the relationships between tables are automatically
discovered and rendered on the designer surface.

For my application, it is necessary to change each SELECT, UPDATE, INSERT
and DELETE statement to customize the handling of timestamps (TS -->
CAST(TS AS INT) AS TS), concurrency conflict handling and retrieving the
autoincrement keys. OK - that's fine but somewhat painful (i.e., labor
intensive [12 tables * 4 sql statements]).

Switching from SQL Server 2000 to SQL Server 2005, you go from a table
owner to schema name metaphor. For example, dbo.OfficeType becomes
AgentNET.OfficeType where dbo is the owner (in 2000) and AgentNET is the
schema (in 2005).

Making the change in the designer should be relatively simple by changing
the underlying SQL statements as in:

SELECT OfficeTypeID, OfficeType, Description, ListOrder, CAST(TS AS INT)
AS TS FROM dbo.OfficeType

SELECT OfficeTypeID, OfficeType, Description, ListOrder, CAST(TS AS INT)
AS TS FROM AgencyNET.OfficeType

However, the designer appears to have screwed up the relationships between
tables preventing the application from working. The only fix was to delete
the xsd file and start over from scratch!

The dataset is a disconnected container for holding a subset of the data
on the desktop. The table adapter is not part of the dataset - it is a
wrapper to the data adapter that handles CRUD (create, read, update,
delete) operations. Combining both dataset and adapters clouds the
distinction. Using the designer, changing where the data comes from breaks
the container.

For my development, I'm going back to having one container (xsd) for the
dataset with no table adapters and a separate container for the data
adapters. That way, the backend can be changed without breaking the rest
of the application. So much for dragging tables to designer surface ever
again.

This was definitely one of those "You've got to be **#&$*#@ me!" moments
in life.

PS to Microsoft: Did you really try this stuff out prior to shipping?

 

[Cor Ligthert [MVP]]

Jim,

The design of the Strongly Typed Dataset and all what is around it, is from
my point of view obviously not ready.

It is however already much better than in version 1.x.

If you don't like it at all, than don't use the designer as most regulars
from this newsgroup did in the 1.x version. However I start to appriciate
more and more the strongly typed dataset way.

Cor

 

[Jim Rand]

Cor,

The issue is not with the strongly typed dataset - that works great. It's
the table adapter where it get's ugly.

Jim

 

[Miha Markic [MVP C#]]

Hi Jim,

I would never use TableAdapters for non-demo or non-small project.
Instead I would take another road, i.e.:
- ORM (there is a ton of them out there, LLBLGenPro highly recommended)
- generate code using template based code generators (CodeSmith is a great
choice)
- do it manually

 

[Jim Rand]

That's pretty much the conclusion that I've come to. I'm thinking about
building a tool that:

- Is used interactively by the developer
- Picks off the tables / fields to populate a dataset
- Writes all SQL statements the way I want them - i.e., a smart command
builder
- Generates an XML file that includes SQL statements, table / field
mappings, and parameter information

A data adapter factory at runtime would then build a set of data adapters
using the XML file as input.

The goal is to simplify and reduce development time without sacrificing
runtime performance.

I just want to share my experience using table adapters and the unexpected
problems of moving to a different database so that others may benefit.

Jim

 

[Cor Ligthert [MVP]]

Jim,

I think that Microsoft has made a reaction on all those people who are
complaining that the DataAdapter was to complicated. They wanted the
recordset back. The tableadapter is in fact a recordset approach in new
clothes. In my idea not one of the best decisions from Microsoft.

Cor

 

[Earl]

That's pretty much what the DataAdapterConfiguration Wizard does.