1 / 33

Overview of ADO.NET with the .NET Framework

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

albert
Download Presentation

Overview of ADO.NET with the .NET Framework

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Overview of ADO.NETwith the .NET Framework Scalable Development, Inc. Building systems today that perform tomorrow.

  2. .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.

  3. .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

  4. Agenda • Design Philosophy • Architectural Overview • Features • When to use What (Code Examples) • Summary

  5. 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

  6. Managed Data Overview

  7. 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

  8. 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

  9. 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.

  10. Oracle Managed Providers • Microsoft. No support for COM+. • Oracle. Requires Oracle 9iR2 client.

  11. 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.

  12. Oracle Support Through ODBC • Microsoft Driver for Oracle. • Oracle ODBC driver. • Thread challenged.

  13. 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!!!!!!

  14. DataSet Tables Table Columns Column Constraints Constraint Rows Row Relations Relation DataSet Common client data store • Relational View of Data • Tables, Columns, Rows, Constraints, Relations • Directly create metadata and insert data

  15. 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.

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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)

  23. 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")

  24. 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

  25. 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")

  26. 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”)

  27. Notes on the Client Cursor Update • Individual commands are sent. • Can be wrapped in a transaction. • CommandBuilder is “relatively” inefficient.

  28. 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

  29. Cleanup • Close your connection objects and return them to the connection pool. • Dispose all objects. Don't wait on the finalizer.

  30. 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, ……..

  31. ADO Classic in .NET • Works. • Can continue to use it. • Required for ADOX support.

  32. 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

  33. Questions? 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 END

More Related