Transcript Session Code: ARC404 Data Access Design Patterns: Navigating the Data Access Maze Michael Pizzo Software Architect Microsoft Corporation.

Session Code: ARC404
Data Access Design Patterns:
Navigating the Data Access
Maze
Michael Pizzo
Software Architect
Microsoft Corporation
Agenda
Whidbey Data Access
System.Data Namespace
Data Access Solutions
Sample Application
Whidbey Data Access Solutions
DataSets and Data Providers
ObjectSpaces
SQLXML
Related Technologies
WinFS, MBF, and ObjectSpaces
ObjectSpaces and Typed DataSets
Summary
Tools
Client Application Model
Avalon
Windows Forms
System.Windows
System.Windows.Forms
Web & Service Application Model
Data Systems Application Model
Win FS
ASP.NET / Indigo
Yukon
System.Data.SqlServer
System.Storage
System.Web
Mobile PC & Devices Application Model
Compact
Framework
Command Line
System.Console
Mobile PC Optimized
System.Windows.Forms
NT Service
System.Windows
System.ServiceProcess
Communication
Data
Presentation
System.Search
System.Windows
UI Element
Explorer
Documents
Media
Controls
Text Element
Annotations
Animation
Dialogs
System.Data
Monitoring
Controls
System.Messaging
SqlClient
SqlClient
DataSet
DataSet
SqlTypes
Mapping
SqlXML
ObjectSpaces
ObjectSpaces
Logging
Shapes
SideBar
Shape
Control
Notification
Navigation
Ink
System.Windows.Forms
Relevance
Panel
Design
Forms
Page
WebControls
Control
Control
Adaptors
Print Dialog
HtmlControls
Design
Design
System.Help
MobileControls
Recognition
Synthesis
System.NaturalLanguageServices
System.Runtime.Remoting
Query
Activities
OracleClient
Schema
System.Web.Services
Description
Discovery
Contact
Media
Location
Audio
Message
Video
Document
Images
Event
Protocols
Transport
Queue
Port
PubSub
Channel
Router
Service
Policy
System.Net
System.Web
Personalization
Query
System.MessageBus
Peer Group
System.Xml
Serialization
Uddi
OleDbClient
Relationship
Xpath
TransientDataSession
SignalingSession
Media
Core
Schema
Active
Directory
ObjectSpace
System.Speech
System.Drawing
System.Remoting
Web.Service
Item
RealTimeEndpoint
System.DirectoryServices
OdbcClient
System.Storage
System.Web.UI
System.Collaboration
System.
Discovery
Caching
SessionState
HttpWebRequest
NetworkInformation
FtpWebListener
Sockets
SslClientStream
Cache
WebClient
Fundamentals
Security
Base & Application Services
System.Timers
System.Globalization
System.Serialization
System.Threading
System.Text
System.Design
System.IO
System.Collections
System.ComponentModel
System.CodeDom
CompilerServices
System.Reflection
InteropServices
Configuration
System.Web.Configuration
System.Security
Generic
System.Web.
Security
Ports
System.Runtime
Serialization
System.Windows.
TrustManagement
System.EnterpriseServices
System.Transactions
System.Message
Bus.Security
Authorization
AccessControl
Administration
System.Configuration
Management
Policy
Principal
Cryptography
Token
System.Web
System.MessageBus.Configuration
Permissions
Credentials
Deployment/Management
System.Resources
System.Management
System.Deployment
System.Diagnostics
What’s New In System.Data
Enhancements to System.Data
Features added to relational APIs
ADO.NET with ObjectSpaces
Extending the programming model to support
objects over SQL Server
ADO.NET with SQLXML
Extending the programming model to support
XML
Ability to query and update SQL Server as
XML documents via XML Views
Selecting a Solution
You are part of the answer…
…your skills
…your background
…your familiarity
What are you doing with the data?
Does it contain business logic?
What tools are you using with it?
What is the destination of the data?
Are you exchanging it with other partners?
What type of project are you working on?
Self-contained app versus enterprise
framework
ADO.NET Data Access Support
Data is relational is objects is XML is Data
Technology
Strengths
Use if…
DataSet and
DataReader
in ADO.NET
•Relational (tabular) model
•Highest performance
•Explicit control
•Fully exposes database
functionality
•You are comfortable with the
relational model
•You require maximum
control/performance/functionality
•You are using UI bound controls
ObjectSpaces
in ADO.NET
•Business level objects
•Relational mapping via metadata
•Decoupled from database schema
•Smaller working set than other
object abstractions
•You need a strong business object
layer
•You know the shape of the results
you want to work with
SQLXML in
ADO.NET
•Interoperability. Format for the
Web – B2B, A2A
•Sparse (semi-structured) data
•XML Services e.g. XQuery, XSD
•Relational mapping via metadata
•Decoupled from database schema
•You need to query data from XML data
sources e.g. XML Web Services
•You use vertical industry XML schemas
for content publishing e.g. XBRL, RIXML,
FinXML
•You need to load XML documents into
database tables
•You are using UI bound controls for XML
Sample Application
XML
News
Loader
XML
(Windows
Service)
HTML
Stock
Information
Reference
News
Trades
Positions
Customers
Accounts
XML
(Web App)
ObjectSpaces
DataSet
Portfolio
Manager
(Web App)
DataReader
Customer
Reporting
Trade
Resolution/
History
(Web App)
(Windows App)
Agenda
 Whidbey Data Access
System.Data Namespace
Data Access Solutions
Sample Application
Whidbey Data Access Solutions
DataSets and Data Providers
ObjectSpaces
SQLXML
Related Technologies
WinFS, MBF, and ObjectSpaces
ObjectSpaces and Typed DataSets
Summary
DataSets And Data Providers
Overview
Architecture
Scenarios
Demo
DataSets And Data Providers:
Overview
Goal: Integrate manipulation of relational data
with the .NET Framework
Accessing a Relational Store (.NET Data Providers)
Connecting, Executing Commands, Retrieving Results
Common in-memory representation for tabular data
(DataSet)
Container of tables, columns, relations, constraints
May be populated from any store, application, XML
Connectionless, provider-independent
Explicit Architecture
Predictable behavior, semantics
Optimized performance
DataSet And Data Providers:
Architecture
DataSet
DataAdapter
DataReader
Command
Connection
.NET Data Provider
DataSets And Data Providers:
Scenarios
Work with data as relational tables
Retrieve a single value from the database
Execute a command and process the results
as a stream
Bind tabular results to a Windows Client
Cache a set of results in memory
Remote a collection of records
Combine results from multiple stores
Bulk load query results into SqlServer
DataSets And Data
Providers
Carl Perry
Program Manager
WebData
System.Data Demo Architecture
Customer Information
Trade Resolution/History
Reader (Paging)
Data Set
Command
Data Adapter
Connection
Connection
ASP.Net Page
Windows Form App
Positions Table
Account Table
Customers Table
Customer Information
Account Number
ObjectSpaces
Overview
Architecture
Scenarios
Demo
ObjectSpaces: Overview
Access and manipulate data as domain objects:
Customer, Order, Address
Not as tables, columns, and rows
Any CLR object of your choice can be persisted
No need to inherit from our classes
Provides a separation between business logic
and data access logic
We handle the CRUD operations
Declarative mapping between objects and
relational tables
Natural, easy-to-use query language: OPath
Single method call to persist a graph
ObjectSpaces: Architecture
ObjectSet
Business
Object
Business
Object
Business
Object
Business
Object
Business
Object
ObjectReader
ObjectSpace/ObjectEngine
.NET Framework Data Provider
DataSource
ObjectSpaces: Scenarios
Work with a common set of data classes
across development organization
Query results in terms of data classes
Retrieve results as data classes
Work with a set of data classes
Add new objects
Make changes to existing objects
Delete Objects
Submit changes back to database
ObjectSpaces
Dinesh Kulkarni
Program Manager
WebData
System.Data.ObjectSpaces
Demo Architecture
Business Objects
Query
Trade
ObjectReader
Update
ObjectSpace
Managed Provider
ASP.Net Page
Trades Table
Positions Table
Accounts Table
Customers Table
Customer Information
SQLXML
Overview
Architecture
Scenarios
Demo
SQLXML: Overview
Growing number of applications unifying under
XML based interfaces
Loose coupling
Coping with decentralized data
Managing sparse (semi-structured) data
SQLXML supports XML over SQLServer
XML programming model for queries and updates
XQuery - Full fledged XML query language
XML Views
Mapping technology to expose logical XML views of SQL
Server relational tables
XML Views provide XQuery support over SQL Server.
ADO.NET With SQLXML
DataSet
XPathDocument
DataAdapter
SqlCommand
XmlAdapter
Data Reader
XQueryProcessor (Command)
XmlViewSchema XmlReader
SQL Server database
XML data source
SQLXML: Scenarios
Represent data as XML
Source and sink for XML
Content management systems, B2B scenarios
Queries and updates against XML Views
Loading large XML data sets
Bulk load data into existing relational table structure
E.g. Catalog information
Transfer data as XML between databases
Agree on transfer format
Apply mappings at either end
SQLXML
Alex Laskos
Program Manager
WebData
SQLXML: Demo Architecture
News Query ASP
News XML Files
XQueryProcessor
Loader Application
XmlViewSchema
XmlBulkLoad
XmlViewSchema
Reference Tables
News Table
StockSymbol
ArticleDate
NewsXML
Agenda
 Whidbey Data Access
System.Data Namespace
Data Access Solutions
Sample Application
 Whidbey Data Access Solutions
DataSets and Data Providers
ObjectSpaces
SQLXML
Related Technologies
WinFS, MBF, and ObjectSpaces
ObjectSpaces and Typed DataSets
Summary
WinFS, MBF, And ObjectSpaces
Programming Model
Prescriptive Object Model / Prescriptive Storage Model
MBF
Prescriptive Object model / Existing Storage
ObjectSpaces
Plain Old CLR Objects
Greater Functionality
Greater Flexibility
WinFS
ObjectSpaces And Typed DataSets
Typed DataSets: Strongly typed Relational objects
Code-Gen classes for strongly typed access/navigation
ObjectSpaces: Persistence of your objects
Mapping and Query Services
Typed DataSets
ObjectSpaces
Work with relational data in a
strongly typed manner
Types based on relational classes
Access to underlying datarows
Populate using SQL
Generated mapping in code
Individual application classes
Visual Studio designer experience
General object persistence
Define your own business classes
Encapsulates underlying data
Query in terms of Objects
Declarative mapping in file
Shared classes across an enterprise
Explicit object modeling experience
Summary
It’s all about Data…
Understand the strengths of each technology
DataSet and Data Providers
Relational Model
Ultimate Performance, Control
ObjectSpaces
Object Model
Strong Business Objects decoupled from storage
SQLXML
XML Model
Interoperable, self-describing format
Pick the technology appropriate to your scenario
How results will be used
Tools to be applied
Your knowledge/comfort level
Related Sessions
Wednesday
10:00AM DAT402
Building Database Applications with
SQL Server "Yukon": XQuery, XML Datatype
2:00PM ARC380
.NET Framework: What's New in System.Xml for
"Whidbey“
5:00PM DAT410
.NET Framework: Developing Applications Using
the New Object-Relational Technologies in
"Whidbey“
Caching Techniques for Scalable Enterprise
Applications
Other Resources
“A First Look at ADO.NET and System.Xml
V2” by Addison Wesley
Questions?
SQL Server Resources
Week long SQL Server “Ask the Experts” lounge in:
Foyer outside Room 309
Support for SQL Server “Yukon” PDC Preview at SQLJunkies
http://www.sqljunkies.com/forums
SQL Server “Yukon” FAQ Blog at SQLTeam
http://yukonblog.sqlteam.com/
Other Key Resources
http://www.microsoft.com/sql/community
http://msdn.microsoft.com/sqlserver/
34 world wide user groups,
http://msdn.microsoft.com/usergroups/find.asp
Our most active SQL Server newsgroups,
Microsoft.public.sqlserver.programming
Microsoft.public.sqlserver.server
Microsoft.public.sqlserver.dts
Microsoft.public.sqlserver.olap
Microsoft.public.sqlserver.setup
Microsoft.public.sqlserver.replication
Microsoft.public.sqlserver.msde
© 2003-2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.