Transcript Overview of ADO.NET Whidbey

Scalable Development, Inc.
Building systems today that perform tomorrow.
Overview of ADO.NET
with the .NET Framework
.NET Experiences
 PDC 2000 Build (July 2000).
 Visual Studio 1.0 Beta 1 (November 2000).
 Book began (January 2001).
 Visual Studio 1.0 Beta 2 (June 2001).
 First Production ASP.NET App (July 2001).
 Production Windows Service (November 2001).
Runs today.
 4 Production Applications by shipment.
 Multiple running applications.
.NET Resources
– www.asp.net
 AspAdvice – www.aspadvice.com
 Windows Forms – www.windowsforms.net
 Architecture –
msdn.microsoft.com/architecture
 .NET News – www.dotnetwire.com
 ASP.NET
Agenda
 Design
Philosophy
 Architectural Overview
 Features
 When to use What (Code Examples)
 Summary
Design Philosophy
 Unified
Data Story (data, xml, cache objects)
 Data
is Data; object model is choice
 XML objects feel like XML, Data objects feel like Data,
Typed Objects feel like objects
 Factored
components
 Explicit
model; no black boxes! (Customers/helper
code wires together components)
 Predictable behaviour, semantics
 Optimized performance
 Disconnected
Relational DataSet
 Get Data as fast as possible
Managed Data Overview
What Happened
to My RecordSet?
 RecordSet
 Updateable?
Scrollable? Bookmarks? Rowcount?
Holding server resources? Cache on client?
 DataReader
 Connected,
non-cached, FO/RO RecordSet
 DataSet
 Disconnected,
cached, scrollable data
 DataAdapter
 Logic
for populating the DataSet and propagating
changes back to the datasource
Managed Providers

Manages interaction to a data source





Managed equivalent of OLE DB layer
Directly exposes consumer interfaces
Not a full featured data store interface
Specific to (Optimized for) DataSource
Managed Provider Object Model

Connection
 Like ADODB.Connection

Command
 Like ADODB.Command

DataReader
 Similar

to FO/RO ADODB.RecordSet
Fields accessed through strongly typed, indexed accessors
Database Support
 System.Data.SQLClient
 SQL
Server 7
 SQL Server 2000
 Oracle.DataAccess
(ODP.NET).
 System.Data.Oracle (MS).
 DB2 (only supports .NET 1.0).
 MySql.
 System.Data.OleDb
 System.Data.Odbc ODBC drivers.
Oracle Managed Providers
 Microsoft.
 Oracle.
No support for COM+.
Requires Oracle 9iR2 client.
Oracle Support Through OleDb
 Microsoft
Driver for Oracle. (MsDaOra)
 7.x datatypes.
 No 8.x datatypes.
 Oracle OleDb Driver. (OraOleDb.Oracle)
 7.x datatypes.
 8.x datatypes.
 9.x datatypes.
 COM+ challenged.
Oracle Support Through ODBC
 Microsoft
 Oracle
Driver for Oracle.
ODBC driver.
 Thread
challenged.
Oracle Gotchas
 Use
the latest Oracle SQL Net.
 Don’t
use COM+ transactions, unless you
need them. Info - Oracle is working on an
OleDb driver that supports COM+
transactions better.
 Always
close connections!!!!!!
DataSet
Common client data store

Relational View
of Data


Tables, Columns,
Rows, Constraints,
Relations
DataSet
Tables
Table
Columns
Column
Constraints
Constraint
Rows
Row
Relations
Relation
Directly create metadata and insert data
DataSet
Common client data store
 Explicit
Disconnected Model
 Disconnected,
remotable object
 No knowledge of data source or properties
 Common Behaviour
 Predictable performance characteristics
 Strong Typing
 3-D Collection
 What to return from a DAL.
 Uses DataReader underneath the covers.
 Some column metadata is not available.
Data Adapter
 Loads
a table from a data store and
writes changes back.
 Exposes
two important methods:
 Fill(DataSet,DataTable)
 Update(DataSet,DataTable)
 Provides
mappings between tables & columns
 User provides insert/update/delete commands
 Allows
use of Stored Procedures
 CommandBuilder component available (not
used much in ASP.NET)
 Allows
single DataSet to be populated from multiple
different datasources
ADO.NET and XML

The DataSet

Loads/saves XML data into/out of DataSet
 Schema can be loaded/saved as XSD
 Schema can be inferred from XML Data

The DataSet can be associated with an XmlDataDocument

Exposes a relational view over structured XML
 According to the DataSet schema
 Allows strong typing, control binding, relational access of
XML data
 Allows XML tools (schema validation, XSL/T, XPath queries)
against relational data
 Preserves full fidelity of XML Document
When to use What

Connected Data Access


Disconnected Data Access


Code Examples:
 Retrieving Results
 Insert, Update, and
Delete.
Code Examples:
 Application Data
 Loading XML
 Client Cursor Updating
XML View of Relational Data

Code Example:
 SQLXML
 XML Manipulations
Connected Data Access

Connected Data Access - Managed Providers

Connection, Transaction
 Connecting to DataSource
 Starting/Ending
Transactions
 Command, Parameters
 Database Updates, Selects, DDL
 DataReader
 (FO/RO) Server Cursor
 DataAdapter
 Pushing data into Dataset
 Reading changes out of DataSet
Code: Retrieving Results
‘Create and open an OleDbConnection
Dim OleDbCn as new OleDbConnection(“……”)
OleDbCn.Open()
‘Create and execute OleDbCommand
Dim OleDbCmd as new OleDbCommand("Select * from authors where ...", OleDbCn)
Dim dr as DataReader
dr = OleDbCmd.ExecuteReader()
‘Retrieve Results
while(dr.Read())
Console.WriteLine("Name = " + dr("au_lname“))
End while
dr.Close()
OleDbCn.Close() ‘This is VERY IMPORTANT
Disconnected Data Access

Disconnected Data Access - DataSet

Application Data
 Remoting Results
 SOAP, WebMethods, Remoting
 Caching Results
 ASP.NET Cache
 Persisting results
 Save Data as XML, Schema as XSD
 User interaction
 Scrolling, sorting, filtering
 DataView, DataViewManager
 Binding Windows controls
Code: Application Data
‘ Create an "Inventory" Table
Dim ds as new DataSet()
Dim inventory as new DataTable("Inventory")
inventory.Columns.Add("TitleID",typeof(Int32))
inventory.Columns.Add("Quantity",typeof(Int32))
ds.Tables.Add(inventory)
‘ Add a record to the Inventory table
Dim row as inventory.NewRow()
Row("TitleID“)=1
Row("Quantity“)=25
inventory.Rows.Add(row)
Code: Loading XML
‘Load DataSet with XML
Dim ds as new DataSet()
ds.ReadXml("inventory.xml")
‘Add a record to the Inventory table
Dim inventory as new DataTable = ds.Tables("Inventory“)
Dim row as DataRow = inventory.NewRow()
Row("TitleID“)=1
Row("Quantity“)=25
inventory.Rows.Add(row)
‘Write out XML
ds.WriteXml("updatedinventory.xml")
Code: Insert/Update/Delete
Dim OleDbCn as new OleDbConnection(“…….”)
Dim OleDbCM as new OleDbCommand(
“Insert/Update/Delete Command“, OleDbCn)
OleDbCn.Open()
OleDbCm.ExecuteNonQuery()
If OleDbCn.State <> ConnectionState.StateClosed then
OleDbCn.Close()
End if
OleDbCn = Nothing ‘Old habits die hard
Code: Client Cursor Updating (easy)
‘Populate DataSet
Dim OleDbDA as new OleDbDataAdapter(
"Select * from customers“, OleDbCn);
Dim ds as new Dataset()
OleDbDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
OleDbDA.Fill(ds, "Customer")
‘Find customer w/PK value of 256
Dim dr as DataRow = ds.Tables("Customer“).Rows.Find(256)
‘Change LastName
Dr("LastName“) = "Smith"
‘Update DataBase
Dim OleDbCb as new OleDbCommandBuilder = OleDbCommandBuilder(da)
OleDbDa.Update(ds, "Customer")
Code: Client Cursor Operations (harder)
‘Populate DataSet
Dim OleDbDA as new OleDbDataAdapter(
"Select customerId, customerName from customers“, OleDbCn);
Dim ds as new Dataset()
Dim dr as DataRow
OleDbDA.Fill(ds, "Customer")
OleDbDA.InsertCommand = new OleDbCommand(“insert into customers
(CustomerName) values (@CustomerName)”, OleDbCn)
dr = ds.Tables(“Customer”).NewRow()
‘Add Customer Name
Dr("Customer Name“) = "Fred's Company"
‘Update DataBase
ds.Tables(“Customer”).Rows.Add(dr)
OleDbDA.Update(ds, “Customer”)
Notes on the Client Cursor Update
 Individual
 Can
commands are sent.
be wrapped in a transaction.
 CommandBuilder
is “relatively” inefficient.
Code: Manual Transaction
Dim OleDbCM as new OleDbCommand(
“Insert/Update/Delete Command“, OleDbCn)
Dim OleDbTx as OleDbTransaction
OleDbTx = OleDbCn.BeginTransaction(Isolation.ReadUncommitted)
OleDbCm.Transaction = OleDbTx
Try
OleDbCn.Open()
OleDbCm.ExecuteNonQuery()
OleDbTx.Commit()
Catch exc as Exception
OleDbTx.RollBack()
Throw( new Exception( “Error Occurred”, exc.InnerException )
Finally
If OleDbCn.State <> ConnectionState.StateClosed then
OleDbCn.Close()
End if
End Try
OleDbCn = Nothing
Cleanup
 Close
your connection objects and return
them to the connection pool.
 Dispose
finalizer.
all objects. Don't wait on the
Great, Now What’s Missing?
 Server-side
scrollable cursor support. (not an
issue for Oracle)
 Complete
column information.
 ADOX.Catalog
type of support. No support for
creating tables, columns, users, indexes, ……..
ADO Classic in .NET
 Works.
 Can
continue to use it.
 Required for ADOX support.
Summary
 ADO.NET
is…
 Optimized for Data Access.
 Managed Providers for connected
access
 DataSet for disconnected, user
interaction
 DataReader for connected RO use.
 Open Architecture
 No Black Boxes
 Tightly Integrated with XML
 DataSet reads/writes XML
 XmlDataDocument integrates relational
and XML views
Scalable Development, Inc.
Building systems today that perform tomorrow.
Questions?
 Scalable
Development, Inc.
 Consulting & Development Services.
 http://www.scalabledevelopment.com
 865-693-3004.
 [email protected]
END