Overview of ado net with the net framework
1 / 33

Overview of ADO.NET with the .NET Framework - PowerPoint PPT Presentation

  • Uploaded on

Overview of ADO.NET with the .NET Framework. Scalable Development, Inc. Building systems today that perform tomorrow. .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).

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Overview of ADO.NET with the .NET Framework' - albert

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Overview of ado net with the net framework l.jpg
Overview of ADO.NETwith the .NET Framework

Scalable Development, Inc.

Building systems today that perform tomorrow.

Net experiences l.jpg
.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 l.jpg
.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 l.jpg

  • Design Philosophy

  • Architectural Overview

  • Features

  • When to use What (Code Examples)

  • Summary

Design philosophy l.jpg
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

What happened to my recordset l.jpg
What Happenedto 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 l.jpg
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 l.jpg
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 l.jpg
Oracle Managed Providers

  • Microsoft. No support for COM+.

  • Oracle. Requires Oracle 9iR2 client.

Oracle support through oledb l.jpg
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 l.jpg
Oracle Support Through ODBC

  • Microsoft Driver for Oracle.

  • Oracle ODBC driver.

    • Thread challenged.

Oracle gotchas l.jpg
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 l.jpg












DataSet Common client data store

  • Relational View

    of Data

    • Tables, Columns,

      Rows, Constraints,


  • Directly create metadata and insert data

Dataset common client data store15 l.jpg
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 l.jpg
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 l.jpg

  • 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 l.jpg
When to use What

  • Connected Data Access

    • Code Examples:

      • Retrieving Results

      • Insert, Update, and Delete.

  • Disconnected Data Access

    • Code Examples:

      • Application Data

      • Loading XML

      • Client Cursor Updating

  • XML View of Relational Data

    • Code Example:

      • SQLXML

      • XML Manipulations

Connected data access l.jpg
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 l.jpg
Code: Retrieving Results

‘Create and open an OleDbConnection

Dim OleDbCn as new OleDbConnection(“……”)


‘Create and execute OleDbCommand

Dim OleDbCmd as new OleDbCommand("Select * from authors where ...", OleDbCn)

Dim dr as DataReader

dr = OleDbCmd.ExecuteReader()

‘Retrieve Results


Console.WriteLine("Name = " + dr("au_lname“))

End while


OleDbCn.Close() ‘This is VERY IMPORTANT

Disconnected data access l.jpg
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 l.jpg
Code: Application Data

‘ Create an "Inventory" Table

Dim ds as new DataSet()

Dim inventory as new DataTable("Inventory")




‘ Add a record to the Inventory table

Dim row as inventory.NewRow()




Code loading xml l.jpg
Code: Loading XML

‘Load DataSet with XML

Dim ds as new DataSet()


‘Add a record to the Inventory table

Dim inventory as new DataTable = ds.Tables("Inventory“)

Dim row as DataRow = inventory.NewRow()




‘Write out XML


Code insert update delete l.jpg
Code: Insert/Update/Delete

Dim OleDbCn as new OleDbConnection(“…….”)

Dim OleDbCM as new OleDbCommand(

“Insert/Update/Delete Command“, OleDbCn)



If OleDbCn.State <> ConnectionState.StateClosed then


End if

OleDbCn = Nothing ‘Old habits die hard

Code client cursor updating easy l.jpg
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 l.jpg
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


OleDbDA.Update(ds, “Customer”)

Notes on the client cursor update l.jpg
Notes on the Client Cursor Update

  • Individual commands are sent.

  • Can be wrapped in a transaction.

  • CommandBuilder is “relatively” inefficient.

Code manual transaction l.jpg
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





Catch exc as Exception


Throw( new Exception( “Error Occurred”, exc.InnerException )


If OleDbCn.State <> ConnectionState.StateClosed then


End if

End Try

OleDbCn = Nothing

Cleanup l.jpg

  • Close your connection objects and return them to the connection pool.

  • Dispose all objects. Don't wait on the finalizer.

Great now what s missing l.jpg
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 l.jpg
ADO Classic in .NET

  • Works.

  • Can continue to use it.

  • Required for ADOX support.

Summary l.jpg

  • 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

Questions l.jpg

Scalable Development, Inc.

Building systems today that perform tomorrow.

  • Scalable Development, Inc.

  • Consulting & Development Services.

  • http://www.scalabledevelopment.com

  • 865-693-3004.

  • wallym@scalabledevelopment.com