DEV383DEV383

The ADO.NET DataSet and You The ADO.NET DataSet and You

Jackie GoldsteinJackie GoldsteinGeneral ManagerGeneral ManagerRenaissance Computer SystemsRenaissance Computer Systemsjackie@renaissance.co.iljackie@renaissance.co.il

Jackie Goldstein…Jackie Goldstein…• General Manager of Renaissance Computer SystemsGeneral Manager of Renaissance Computer Systems

– Consulting, Training, & Development, with Microsoft Tools & Consulting, Training, & Development, with Microsoft Tools & TechnologiesTechnologies

• Author of “Database Access with Visual Basic.Net”Author of “Database Access with Visual Basic.Net”

(ISBN 0-67232-3435, Addison-Wesley, Q3 2002)(ISBN 0-67232-3435, Addison-Wesley, Q3 2002)• MSDN Regional Director for IsraelMSDN Regional Director for Israel• Founder and monthly host of IVBUG Founder and monthly host of IVBUG

(Israel Visual Basic User Group)(Israel Visual Basic User Group)• Speaker at local & international Speaker at local & international

developer conferences:developer conferences:– Microsoft Developer Days, TechEd, Microsoft Developer Days, TechEd, – VSLive!, VBITS, VB DevCon, SQL2TheMaxVSLive!, VBITS, VB DevCon, SQL2TheMax

• Selected as SME (Subject Matter Expert) to help Selected as SME (Subject Matter Expert) to help develop/review content for DevDays 2000 and DevDays develop/review content for DevDays 2000 and DevDays 2001 with Microsoft team in Redmond 2001 with Microsoft team in Redmond

Session TopicsSession Topics• ““Here You Come Again”Here You Come Again”– A Quick ReviewA Quick Review

• ““Alone Again (Naturally)”Alone Again (Naturally)” – The Basics: Tables, Relations, & ConstraintsThe Basics: Tables, Relations, & Constraints

• ““One, Twice, Three Times a Lady”One, Twice, Three Times a Lady”– 3 Ways to Configure the DataAdapter3 Ways to Configure the DataAdapter

• ““I Can See Clearly Now”I Can See Clearly Now”– Data Views and DataRowViewStateData Views and DataRowViewState

• ““Love Will Keep Us Together”Love Will Keep Us Together”– Multi-Table Reads and UpdatesMulti-Table Reads and Updates

• ““We Can Work it Out”We Can Work it Out”– Concurrency ConflictsConcurrency Conflicts

• Questions and SummaryQuestions and Summary

Data In The .NET PlatformData In The .NET Platform

.NET Data Provider.NET Data Provider

DataReaderDataReader

CommandCommandConnectionConnection

SyncSync

Controls,Controls,Designers,Designers,

Code-gen, etcCode-gen, etc

DataSetDataSet

XmlReaderXmlReader

XmlText-XmlText-ReaderReader

XmlNode-XmlNode-ReaderReader

XSL/T, X-Path,XSL/T, X-Path,Validation, etcValidation, etc

XmlData-XmlData-DocumentDocument

Data-Data-AdapterAdapter

Points to Note in ADO.NET vs. ADOPoints to Note in ADO.NET vs. ADO

• Separation between Separation between connected data accessconnected data access model and model and disconnected programmingdisconnected programming model model

• No CursorType, CursorLocation, or No CursorType, CursorLocation, or LockTypeLockType

• ADO Recordset functionality is split into ADO Recordset functionality is split into smaller, specific objects, e.g. DataReader, smaller, specific objects, e.g. DataReader, DataTable, CommandDataTable, Command

• ADO.NET allows manipulation of XML data, ADO.NET allows manipulation of XML data, not just XML as I/O formatnot just XML as I/O format

DataSet Object DataSet Object

DataSetDataSet

DataTableDataTable

DataTableDataTable

DataRowDataRow

DataColumnDataColumn

RelationsRelations

ConstraintsConstraintsXML SchemaXML Schema

DataAdapter ObjectDataAdapter Object

DataAdapterDataAdapter

SelectCommandSelectCommand

InsertCommandInsertCommand

UpdateCommandUpdateCommand

DeleteCommandDeleteCommand

TableMappingsTableMappings

DatabaseDatabase

DataSetDataSet

““Alone Again (Naturally)”Alone Again (Naturally)” The Basics: Tables, Relations, & ConstraintsThe Basics: Tables, Relations, & Constraints• Programmatic definition and access of a Programmatic definition and access of a

DataSet’s Tables, Relations, and DataSet’s Tables, Relations, and Constraints (without any database)Constraints (without any database)–Using only code Using only code –Using the DataSet ComponentUsing the DataSet Component

Demo!

““One, Twice, Three Times a Lady”One, Twice, Three Times a Lady”3 Ways to Configure the DataAdapter3 Ways to Configure the DataAdapter

• The CommandBuilder ObjectThe CommandBuilder Object• Explicit CodeExplicit Code• The DataAdapter Configuration The DataAdapter Configuration

WizardWizard

Demo!

““I Can See Clearly Now”I Can See Clearly Now”Data Views and DataRowViewStateData Views and DataRowViewState

• Multiple simultaneous views of Multiple simultaneous views of the same DataSet data, filtered the same DataSet data, filtered and sorted by and sorted by DataRowViewStateDataRowViewState Demo!

““Love Will Keep Us Together”Love Will Keep Us Together”Multi-Table Reads and UpdatesMulti-Table Reads and Updates

• The standard methods of generating update The standard methods of generating update command for the DataAdapter command for the DataAdapter (CommandBuilder and DataAdapter (CommandBuilder and DataAdapter Configuration Wizard) support only single-Configuration Wizard) support only single-table updatestable updates

• How do I do multi-table reads and updates ?How do I do multi-table reads and updates ?

““Love Will Keep Us Together”Love Will Keep Us Together”Multi-Table Reads and UpdatesMulti-Table Reads and Updates• Use Batch SQL / Stored Procedures to load Use Batch SQL / Stored Procedures to load

multiple DataSet tables in one server round-multiple DataSet tables in one server round-triptrip

• Use ExecuteXmlReader to fetch hierarchical Use ExecuteXmlReader to fetch hierarchical data and load into DataSetdata and load into DataSet

• Update multiple tables using a Stored Update multiple tables using a Stored ProcedureProcedure

• Issue multiple update commands by calling the Issue multiple update commands by calling the Update methods of the individual Update methods of the individual DataAdaptersDataAdapters

““Love Will Keep Us Together”Love Will Keep Us Together”Multi-Table UpdatesMulti-Table Updates

• Generally update related tables in Generally update related tables in the following order:the following order:

1.1. Child Table: Delete RecordsChild Table: Delete Records

2.2. Parent Table: Insert, Update, and Parent Table: Insert, Update, and Delete recordsDelete records

3.3. Child Table: Insert and Update Child Table: Insert and Update recordsrecords

““Love Will Keep Us Together”Love Will Keep Us Together”Multi-Table Reads and UpdatesMulti-Table Reads and Updates• Read multiple tables into the DataSet in Read multiple tables into the DataSet in

a single round-trip to the database servera single round-trip to the database server• Update the database with changes to Update the database with changes to

multiple tablesmultiple tables

Demo!

““We Can Work it Out”We Can Work it Out”Concurrency ConflictsConcurrency Conflicts

• Why Optimistic Locking ?Why Optimistic Locking ?• Conflict DetectionConflict Detection• Conflict ResolutionConflict Resolution–DataSet maintains 3 views of field value:DataSet maintains 3 views of field value:•Original, Current, and Proposed (during edit)Original, Current, and Proposed (during edit)

–Can force changes, reject changes, or reject changes and reload data from sourceCan force changes, reject changes, or reject changes and reload data from source

Sql.txt

What defines a conflict ?What defines a conflict ?• ADO 2.X – Dynamic Property “Update Criteria”ADO 2.X – Dynamic Property “Update Criteria”– adCriteriaUpdCols (default)adCriteriaUpdCols (default)– adCriteriaAllColsadCriteriaAllCols– adCriteriaTimeStampadCriteriaTimeStamp– adCriteriaKeyadCriteriaKey

• ADO.NET ADO.NET – Auto-generated commands include PK and all Auto-generated commands include PK and all

fields in UPDATE and DELETE statementsfields in UPDATE and DELETE statements– Developer can specify own custom SQL Developer can specify own custom SQL

statements (e.g. if 2 of the 5 columns were statements (e.g. if 2 of the 5 columns were modified).modified).

Differences in ADO.NETDifferences in ADO.NET

• Can be automatically generated (but Can be automatically generated (but with limitations)with limitations)

• Greater flexibility in defining what Greater flexibility in defining what constitutes a conflict (see previous constitutes a conflict (see previous slide)slide)

• A little more manual code requiredA little more manual code required– Passing different versions of columnsPassing different versions of columns– Retrieving current database valuesRetrieving current database values

• More flexibility in handling batches More flexibility in handling batches with one or more conflictswith one or more conflicts

““We Can Work it Out”We Can Work it Out”Concurrency ConflictsConcurrency Conflicts• Detecting and resolving Detecting and resolving

concurrency conflictsconcurrency conflictsDemo!

SummarySummary• The DataSet (and its associated objects) The DataSet (and its associated objects)

provides very flexible data manipulationprovides very flexible data manipulation• The DataAdapter provides flexible and The DataAdapter provides flexible and

powerful batch update supportpowerful batch update support• The DataView allows multiple The DataView allows multiple

simultaneous views of the same DataSet simultaneous views of the same DataSet datadata

• Dig into the details !Dig into the details !

Titles and ArtistsTitles and Artists

• ““Here You Come Again”Here You Come Again”– Dolly PartonDolly Parton

• ““Alone Again (Naturally)” Alone Again (Naturally)” – Gilbert O’ SullivanGilbert O’ Sullivan

• ““One, Twice, Three Times a Lady”One, Twice, Three Times a Lady”– The Commodores The Commodores

• ““I Can See Clearly Now”I Can See Clearly Now”– Johnny NashJohnny Nash

• ““Love Will Keep Us Together”Love Will Keep Us Together”– The Captain & Tennille The Captain & Tennille

• ““We Can Work it Out”We Can Work it Out”– The BeatlesThe Beatles

Questions?Questions?

Don’t forget to complete the Don’t forget to complete the on-line Session Feedback form on-line Session Feedback form on the Attendee Web siteon the Attendee Web site

https://web.mseventseurope.com/teched/https://web.mseventseurope.com/teched/