Why ADO'NET

1
Why ADO.NET

• Not your fathers Data Access

2
Who I am

• Shawn Wildermuth
• swildermuth_at_adoguy.com
• Author of Pragmatic ADO.NET
• Editor of http//OnDotNet.com
• For More Info http//adoguy.com
• This Presentation can be found at
• http//adoguy.com/presentations

3
Short History of Data Access

• VT Objects
• Data Access Objects (DAO/Jet)
• Open Database Connectivity (ODBC)
• OLE for Databases (OLE/DB)
• ActiveX Data Objects (ADO)

4
Why is ADO.NET Better?

• Disconnected by Design
• Relational by Nature
• Integration with XML
• Framework Supports Real Database Schema

5
Connected vs. Disconnected

• The Pizza Delivery Guy
• Connections are expensive
• Keeping connections alive longer than necessary
  is extremely wasteful
• Long lived connections impede load balancing
• Connections unnecessary while manipulating
  database results

6
Introducing ADO.NET

• Managed Providers
• DataSet
• DataBinding in ASP.NET
• DataBinding in WinForms

7
Managed Providers

• ADO.NETs Version of Providers (ADO, OLE/DB) and
  Drivers (ODBC)
• Not the only way to access data in .NET (most
  notably the Xml classes)
• Made up of a number of managed classes that
  implement known interfaces

8
System.Data Namespace
9
Managed Provider Abstraction
10
using System using System.Data using
System.Data.OleDb class HelloADONET static
void Main()
OleDbConnection conn new OleDbConnection("..
.") conn.Open()
OleDbCommand cmd conn.CreateCommand()
cmd.CommandText "SELECT FROM AUTHORS"
OleDbDataReader rdr cmd.ExecuteReader()
while (rdr.Read()) Console.WriteLine(rdr"a
u_id") conn.Close()
11
What are DataSets?

• Disconnected set of Database Data?
• In-Memory Database (IMDB)?
• A complex, relational data structure with
  built-in support for XML serialization?
• All three?

12
The DataSet
13
using System using System.Data using
System.Data.SqlClient class HelloADONET
static void Main()
SqlConnection conn new SqlConnection("...")

SqlDataAdapter da new SqlDataAdapter()
da.SelectCommand conn.CreateCommand()
da.SelectCommand.CommandText "SELECT FROM
AUTHORS"
DataSet dataSet new DataSet()
da.Fill(dataSet)
14
Typed DataSets

• Strong Typing
• XSD Based Schema
• Simple to Setup relationships, constraints, etc.
• Not very much use if you have amorphous data

15
Type DataSet Demo
16
Using DataSets

• Bulk Data Loading is supported
• Two possible versions of all data,
  RowState.Original and RowState.Modified
• Relationship Navigation
• DataViews

17
The Hard Part

• Disconnected Concurrency
• Optimistic Concurrency Supported by
  CommandBuilders
• Optimistic Concurrency could be more efficient
• Pessimistic Concurrency can be achieved with
  Check-out, Check-in

18
XML Integration

• DataSets XML Serialization
• You can control the Serialization
• Full XSD Support
• Supports DiffGrams
• XmlDataDocument
• View DataSets as XmlDocuments
• SqlCommands and XmlReader

19
Practical Applications of DataSets

• Stop Writing Business Objects!
• Derive from Typed DataSets
• Let ADO.NET do the Database work
• Write Just Your Business Logic
• Deal with data as Relationally
• or Hierarchically by using XmlDataDocument

20
Scalability in ADO.NET

• Scale Out the Database as DataSets in the Middle
  Tier
• Use DiffGrams to Keep DataSets in Sync
• With SQL Server 2K
• Can use DiffGrams to update the database
• Caveats about different DiffGram format

21
DataBinding in ASP.NET

• ASP.NET DataBinding is read-only
• Can bind with DataReaders (but troublesome)

22
DataBinding in ASP.NET (2)
using System.Data using System.Web.UI using
System.Web.UI.WebControls protected ListBox
ListBox1 DataSet dataSet new DataSet() //
... // DataBind ListBox1.DataSource
dataSet ListBox1.DataMember "Customers" ListBo
x1.DataTextField "CompanyName" ListBox1.DataVal
ueField "CustomerID" ListBox1.DataBind()
23
DataBinding in WinForms

• Different from ASP.NET
• Fully bidirectional
• No need to call DataBind()
• Allows binding to any property

using System.Data using System.Windows.Forms Da
taSet dataSet new DataSet() //
... listBox1.DataSource dataSet.Tables0 list
Box1.DisplayMember "CompanyName" listBox1.Value
Member "CustomerID"
24
DataBinding in WinForms (2)

• Master-Detail Binding
• Set multiple object to same DataSource
• Set DataMember to name of Relationship
• Use CurrencyManager to move the cursor if you do
  not want to use the master control

25
Quick Rants

• Classic ADO has a role in .NET
• Batch Queries are cool!
• Caveats for SqlClients Connection Pooling
• Trust your DBAs and strive to be like them )

26
Questions?