ADO.Net TableAdapters and TableAdapterManager

1. ADO.NetTableAdapters and TableAdapterManager Code Camp 2008 Emmet Gray http://home.hot.rr.com/graye

2. Introduction • Strongly-typed DataSets • Based upon DataSet and DataTable classes • Wizard based (no need to hand code) • Graphical view of the data • Benefits • Design-time type checking • Intellisense • Slight performance increase*

3. Strongly-Typed DataSet • Disadvantages • Severe code bloat* • Is often incorrectly used as a substitute for true n-tier “data access layer” • Use of wizard may cause you to overlook other options such as stored procedures * Performance increases may be overshadowed by code bloat

4. TableAdapter • Strongly-typed version of the DataAdapter class • The typical way to get data in/out of a strongly-typed DataSet • One “DataAdapter” per table • Has traditional Fill() and Update() methods • Can be expanded with additional methods • Concepts similar to that of stored procedures • All queries located in one place

5. TableAdapter Disadvantages • Tied to the current data provider (i.e. SQL Server) • To make a program database “agnostic” you would: • Build the DataSet as usual • Remove all TableAdapters • Use the “Data Provider Factory”

6. Schema Based • All queries in a TableAdapter must return rows based upon the schema associated with the strongly-typed DataTable • Joins are allows for filtering (but only columns in the base table are allowed) • Exception for scalar queries (that return a single value) • Supports parameterized queries

7. Queries • So how do I handle custom queries that return a different schema? • Create a new TableAdapter! • Also creates a new strongly-typed DataTable • Update() methods • Automatically creates the “CommandBuilder” equivalent Insert, Update, Delete commands

8. Database Integrity • Constraints upon the data • Unique / Primary key • Foreign Key • Relationships • Strongly-typed DataSets support relationships • Enforces relationships at run-time • Detects problems prior to saving to back-end database

9. Saving the data • TableAdapter.Update() method • Scans the rows for changes and sends the changes to the back-end database • How does the TableAdapter handle updates for queries based upon joins? • Poorly… • You’re are forced to write the update logic by hand

10. Database Integrity Revisited • Since strongly-typed DataSets support relationships, I’m good to go… right? • That’s true for your locally cached version of the data • But you must make sure you update the tables in the back-end database in the correct order! • Particularly important in foreign-key relationship

11. TableAdapterManager • TableAdapterManager • New to VS2008 • Automatically scans the relationships and builds an UpdateAll() method that updates the tables in the correct order • Not really based upon a “base class” • Not tied to newer .Net Framework v3.x

12. End Notes • TableAdapter • Based upon the associated schema of the strongly-typed DataTable • Handy place to keep all your queries • May not be suitable for all ocasions • TableAdapterManager • New component to solve the problem of updating the back-end database tables in the correct order