Transcript Love Will Keep Us Together

DEV383
The ADO.NET DataSet and You
Jackie Goldstein
General Manager
Renaissance Computer Systems
[email protected]
Jackie Goldstein…
•
General Manager of Renaissance Computer Systems
– Consulting, Training, & Development, with Microsoft Tools &
Technologies
•
•
•
•
Author of “Database Access with Visual Basic.Net”
(ISBN 0-67232-3435, Addison-Wesley, Q3 2002)
MSDN Regional Director for Israel
Founder and monthly host of IVBUG
(Israel Visual Basic User Group)
Speaker at local & international
developer conferences:
– Microsoft Developer Days, TechEd,
– VSLive!, VBITS, VB DevCon, SQL2TheMax
•
Selected as SME (Subject Matter Expert) to help
develop/review content for DevDays 2000 and DevDays
2001 with Microsoft team in Redmond
Session Topics
• “Here You Come Again”
– A Quick Review
• “Alone Again (Naturally)”
– The Basics: Tables, Relations, & Constraints
• “One, Twice, Three Times a Lady”
– 3 Ways to Configure the DataAdapter
• “I Can See Clearly Now”
– Data Views and DataRowViewState
• “Love Will Keep Us Together”
– Multi-Table Reads and Updates
• “We Can Work it Out”
– Concurrency Conflicts
• Questions and Summary
Data In The .NET Platform
Controls,
Designers,
Code-gen, etc
XSL/T, X-Path,
Validation, etc
DataSet
Sync
DataAdapter
DataReader
Command
Connection
.NET Data Provider
XmlDataDocument
XmlReader
XmlText- XmlNodeReader
Reader
Points to Note in ADO.NET vs. ADO
• Separation between connected data access
model and disconnected programming model
• No CursorType, CursorLocation, or LockType
• ADO Recordset functionality is split into
smaller, specific objects, e.g. DataReader,
DataTable, Command
• ADO.NET allows manipulation of XML data,
not just XML as I/O format
DataSet Object
DataSet
DataTable
DataColumn
DataTable
DataRow
Relations
XML Schema
Constraints
DataAdapter Object
DataAdapter
Database
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
TableMappings
DataSet
“Alone Again (Naturally)”
The Basics: Tables, Relations, & Constraints
• Programmatic definition and
access of a DataSet’s Tables,
Relations, and Constraints
(without any database)
– Using only code
– Using the DataSet Component
Demo!
“One, Twice, Three Times a Lady”
3 Ways to Configure the DataAdapter
• The CommandBuilder Object
Demo!
• Explicit Code
• The DataAdapter Configuration
Wizard
“I Can See Clearly Now”
Data Views and DataRowViewState
• Multiple simultaneous views of
the same DataSet data, filtered
and sorted by
Demo!
DataRowViewState
“Love Will Keep Us Together”
Multi-Table Reads and Updates
• The standard methods of generating
update command for the DataAdapter
(CommandBuilder and DataAdapter
Configuration Wizard) support only
single-table updates
• How do I do multi-table reads and
updates ?
“Love Will Keep Us Together”
Multi-Table Reads and Updates
• Use Batch SQL / Stored Procedures to
load multiple DataSet tables in one server
round-trip
• Use ExecuteXmlReader to fetch
hierarchical data and load into DataSet
• Update multiple tables using a Stored
Procedure
• Issue multiple update commands by
calling the Update methods of the
individual DataAdapters
“Love Will Keep Us Together”
Multi-Table Updates
• Generally update related tables in
the following order:
1. Child Table: Delete Records
2. Parent Table: Insert, Update, and
Delete records
3. Child Table: Insert and Update
records
“Love Will Keep Us Together”
Multi-Table Reads and Updates
• Read multiple tables into the
DataSet in a single round-trip to
the database server
• Update the database with changes
to multiple tables
Demo!
“We Can Work it Out”
Concurrency Conflicts
• Why Optimistic Locking ?
• Conflict Detection
• Conflict Resolution
–DataSet maintains 3 views of field value:
•Original, Current, and Proposed (during edit)
–Can force changes, reject changes, or
reject changes and reload data from
source
What defines a conflict ?
• ADO 2.X – Dynamic Property “Update Criteria”
–
–
–
–
adCriteriaUpdCols (default)
adCriteriaAllCols
adCriteriaTimeStamp
adCriteriaKey
• ADO.NET
– Auto-generated commands include PK and all
fields in UPDATE and DELETE statements
– Developer can specify own custom SQL
statements (e.g. if 2 of the 5 columns were
modified).
Differences in ADO.NET
• Can be automatically generated (but
with limitations)
• Greater flexibility in defining what
constitutes a conflict (see previous
slide)
• A little more manual code required
– Passing different versions of columns
– Retrieving current database values
• More flexibility in handling batches
with one or more conflicts
“We Can Work it Out”
Concurrency Conflicts
• Detecting and resolving
concurrency conflicts
Demo!
Summary
• The DataSet (and its associated
objects) provides very flexible data
manipulation
• The DataAdapter provides flexible and
powerful batch update support
• The DataView allows multiple
simultaneous views of the same
DataSet data
• Dig into the details !
Titles and Artists
•
“Here You Come Again”
– Dolly Parton
•
“Alone Again (Naturally)”
– Gilbert O’ Sullivan
•
“One, Twice, Three Times a Lady”
– The Commodores
•
“I Can See Clearly Now”
– Johnny Nash
• “Love Will Keep Us Together”
– The Captain & Tennille
• “We Can Work it Out”
– The Beatles
Questions?
Don’t forget to complete the
on-line Session Feedback form
on the Attendee Web site
https://web.mseventseurope.com/teched/