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