Thoughts on Datasets and TableAdapters?

[Guest]

In VS 2003, used to be able to drag a stored proc (that had multiple selects)
onto the designer and it would create a datatable for each select. In VS
2005 it only creates a datatable for the first select. I sometimes do
multiple selects to save round trips to the db.

Also, after going through a tutorial using TableAdapters for the data layer,
I discovered they don't allow you to enlist them in an ADO.NET transaction?
Most of my updates require multiple tables and a transaction.

Is it me or have things become less flexible? Maybe I'm not seeing the big
picture...

 

[BrendanC]

In VS 2003, used to be able to drag a stored proc (that had multiple selects)
onto the designer and it would create a datatable for each select. In VS
2005 it only creates a datatable for the first select. I sometimes do
multiple selects to save round trips to the db.

Also, after going through a tutorial using TableAdapters for the data layer,
I discovered they don't allow you to enlist them in an ADO.NET transaction?
Most of my updates require multiple tables and a transaction.

Is it me or have things become less flexible? Maybe I'm not seeing the big
picture...

Dave,
I've just been wrestling with the same issues (see my earlier post) -
so maybe this will ease your pain,

FWIW Table Adapters do not expose certain properties/events and it may
be better to use the old style DataAapter instead. AFAIK VS does not
provide a way to define RowUpdated events for the underlying Data
Adapter used by a Table adapter - you need to do this manually, but
the problem is that regenerating the Datasets etc can blow away any
changes you make.

(I too may be missing something here - but why are these nuances so
poorly documented (end of rant!) ).

David Sceppa describes the various alternatives (Data Adapters vs
Table Adapters etc) in his ADONET 2.0 book - highly recommended.

http://www.microsoft.com/mspress/books/5354.aspx

Here's another link that might shed some light:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=57791&SiteID=1

Hope this helps

Brendan

 

[PhilipDaniels]

Dave,
I've just been wrestling with the same issues (see my earlier post) -
so maybe this will ease your pain,

FWIW Table Adapters do not expose certain properties/events and it may
be better to use the old style DataAapter instead. AFAIK VS does not
provide a way to define RowUpdated events for the underlying Data
Adapter used by a Table adapter - you need to do this manually, but
the problem is that regenerating the Datasets etc can blow away any
changes you make.

(I too may be missing something here - but why are these nuances so
poorly documented (end of rant!) ).

David Sceppa describes the various alternatives (Data Adapters vs
Table Adapters etc) in his ADONET 2.0 book - highly recommended.

http://www.microsoft.com/mspress/books/5354.aspx

Here's another link that might shed some light:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=57791&SiteID=1

Hope this helps

Brendan

What do you mean "blow away my changes"? There is no need, the
strongly typed table adapter is a partial class so you can just add
your own .cs file and include any extensions you want. Then you can
regenerate the dataset at will, though you may have to fix any compile
errors of course. It's the nested-class structure of the DataSet which
can throw you.

public partial class MyDataSet {
////////////////////////////////////////////////
// DataSet level extensions.
////////////////////////////////////////////////
public void FillStuff() {
}



////////////////////////////////////////////////
// Table level extensions.
////////////////////////////////////////////////
public partial class YourTableDataTable {
// your code here
}

public partial class YourOtherTableDataTable {
// your code here
}


////////////////////////////////////////////////
// Row level extensions.
////////////////////////////////////////////////
[DebuggerDisplay("ID={YourTableId}, Name={Name}")]
public partial class YourTableRow {
// your code here
}

}

Of course you can split this out across multiple .cs files if you
want.

Note the use of the DebuggerDisplay attribute which I find helps a lot
during debugging.

Using the above technique you can create a public method to set the
connection used by a table adapter. You can also write public
transaction methods. Brian Noyes covers this in pages 61-65 of his
book "Data Binding with Windows Forms 2" by Brian Noyes.

Lastly, you can tame the nested types by using the "using alias"
feature liberally in your code, for example

using CountryTA =
Northwind.NorthwindTableAdapters.CountryTableAdapter;

using Countries = Northwind.NorthwindDataSet.CountryTable;
using Country = Northwind.NorthwindDataSet.CountryTableRow;


you can then write code like

foreach (Country c in myDS.Countries) {
...
}


I haven't really bothered with the events other than indirectly via
data binding so I can't comment on that. However you can drag "extra"
stuff out from the server explorer onto your data set. Try it and see.

Don't give up on typed data sets yet! I am finding them more and more
useful. It is well worth studying the generated code to find out what
it is doing. The above will make more sense then.

Hope this helps a bit,

 

[Jim Rand]

If you use SQL Server and want to get back the auto increment key, you must
have access to the underlying data adapter events as in:

static void da_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert) e.Status =
System.Data.UpdateStatus.SkipCurrentRow;
}

On the project I'm working on, I first started with table adapters exposing
the private data adapters via public properties as in:

public partial class AccessFlagLookupTableAdapter
{
public System.Data.SqlClient.SqlDataAdapter dataAdapter
{
get
{
this.Adapter.SelectCommand = this.CommandCollection[0];
return this.Adapter;
}
}
}

For various reasons, I then gave up on the table adapters and went back to
the VS 2003 style where you create a component and then drag the data
adapters onto the designer surface. That was better. However, the designer
was extremely difficult / impossible to work with in writing complex SQL
code as in:

SELECT R.RefUrlsID, R.OfficeIdxUrlID, R.RefIdxUrl, R.LastUpdated,
R.LastUpdatedBy, CAST(R.TS AS INT) AS TS
FROM AgencyNET.RefUrls AS R INNER JOIN AgencyNET.OfficeIdxUrl AS O ON
R.OfficeIdxUrlID = O.OfficeIdxUrlID
WHERE O.OfficeID = @OfficeID

INSERT INTO AgencyNET.RefUrls (LastUpdatedBy,OfficeIdxUrlID,RefIdxUrl)
VALUES (@LastUpdatedBy,@OfficeIdxUrlID,@RefIdxUrl);
SELECT RefUrlsID, CAST(TS AS INT) AS TS FROM AgencyNET.RefUrls
WHERE RefUrlsID = SCOPE_IDENTITY()

Finally, after analyzing the data schema of the data adapter designer, I
rolled my own designer in MS Access that then generates C# code for creating
and configuring each data adapter. This approach has worked great and now
allows me to configure a 10 table dataset / data adapter set in minutes
instead of hours and/or days.