Ado

  • November 2019
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Ado as PDF for free.

More details

  • Words: 1,267
  • Pages: 33
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

Related Documents

Ado
November 2019 63
Ado
November 2019 61
Ado
November 2019 59
Ado
November 2019 52
Ado
June 2020 48
Ado
November 2019 24