Ado

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  ASP.NET

– www.asp.net  AspAdvice – www.aspadvice.com  Windows Forms – www.windowsforms.net  Architecture – msdn.microsoft.com/architecture  .NET News – www.dotnetwire.com

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



Command  Like



ADODB.Connection 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",type of(Int32)) inventory.Columns.Add("Quantity",ty peof(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)

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

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.ReadU ncommitted) 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

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