Transcript Love Will Keep Us Together

DAT 390
Advanced ADO.NET
Programming Techniques
Jackie Goldstein
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)
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
“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
demo
“One, Twice, Three Times a Lady”
3 Ways to Configure the DataAdapter
The CommandBuilder Object
Explicit Code
The DataAdapter Configuration Wizard
demo
“I Can See Clearly Now”
DataViews and
DataRowViewState
Multiple simultaneous views of
the same DataSet data, filtered
and sorted byDataRowViewState
“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.
2.
3.
Child Table: Delete Records
Parent Table: Insert, Update, and
Delete records
Child Table: Insert and Update
records
demo
“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
“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
demo
“We Can Work it Out”
Concurrency Conflicts
Detecting and resolving
concurrency conflicts
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 !
Community Resources
Community Resources
http://www.microsoft.com/communities/default.mspx
Most Valuable Professional (MVP)
http://www.mvp.support.microsoft.com/
Newsgroups
Converse online with Microsoft Newsgroups, including Worldwide
http://www.microsoft.com/communities/newsgroups/default.mspx
User Groups
Meet and learn with your peers
http://www.microsoft.com/communities/usergroups/default.mspx
Ask The Experts
Get Your Questions Answered
I will be in the Microsoft Ask The Experts
area during the following hours:
Wednesday
Thursday
13:00 – 14:00
13:00 – 14:00
evaluations
Questions?
Titles and Artists
“Here You Come Again”
Dolly Parton
“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
© 2003 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.