Transcript Why ADO.NET - Wildermuth

Why ADO.NET
Not your father’s Data Access
http://adoguy.com
Who I am
Shawn Wildermuth
 [email protected]
 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
http://adoguy.com
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)

http://adoguy.com
Why is ADO.NET Better?
Disconnected by Design
 Relational by Nature
 Integration with XML
 Framework Supports Real Database
Schema

http://adoguy.com
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

http://adoguy.com
Introducing ADO.NET
Managed Providers
 DataSet
 DataBinding in ASP.NET
 DataBinding in WinForms

http://adoguy.com
Managed Providers
ADO.NET’s 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

http://adoguy.com
System.Data Namespace
Dataset
DataRow
Data Relation
DataTable
DataColumn
ForeignKeyConstraint
etc.
System.Data.Common Namespace
DataAdapter
DataTableMapping
DbDataRecord
etc.
System.Data.SqlClient
Namespace
System.Data.OleDb
Namespace
Your Provider
SqlConnection
OleDbConnection
YourConnection
SqlCommand
OleDbCommand
YourCommand
SqlDataReader
OleDbDataReader
YourDataReader
etc.
etc.
http://adoguy.com
etc.
Managed Provider Abstraction
System.Data.SqlClient
System.Data
SqlConnection
SqlDataAdapter
SqlCommand
DataSet
SqlDataReader
SqlParameters
SqlParameter
SqlErrors
SqlError
http://adoguy.com
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["au_id"]);
conn.Close();
}
}
http://adoguy.com
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?

http://adoguy.com
The DataSet
DataSet
Tables
DataTable
DataTable
Rows
Rows
DataRow
DataRow
DataRelation
Constraint
ForeignKeyConstraint
http://adoguy.com
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);
}
}
http://adoguy.com
Typed DataSets
Strong Typing
 XSD Based Schema
 Simple to Setup relationships, constraints,
etc.
 Not very much use if you have amorphous
data

http://adoguy.com
Type DataSet Demo
http://adoguy.com
Using DataSets
Bulk Data Loading is supported
 Two possible versions of all data,
RowState.Original and RowState.Modified
 Relationship Navigation
 DataViews

http://adoguy.com
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
http://adoguy.com
XML Integration

DataSets XML Serialization
– You can control the Serialization
– Full XSD Support
– Supports DiffGrams

XmlDataDocument
– View DataSets as XmlDocuments

SqlCommands and XmlReader
http://adoguy.com
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
http://adoguy.com
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
http://adoguy.com
DataBinding in ASP.NET

ASP.NET DataBinding is read-only
– Can bind with DataReaders (but troublesome)
http://adoguy.com
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";
ListBox1.DataTextField = "CompanyName";
ListBox1.DataValueField = "CustomerID";
ListBox1.DataBind();
http://adoguy.com
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;
DataSet dataSet = new DataSet();
// ...
listBox1.DataSource = dataSet.Tables[0];
listBox1.DisplayMember = "CompanyName";
listBox1.ValueMember = "CustomerID";
http://adoguy.com
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
http://adoguy.com
Quick Rants
Classic ADO has a role in .NET
 Batch Queries are cool!
 Caveats for SqlClient’s Connection
Pooling
 Trust your DBAs and strive to be like them
:)

http://adoguy.com
Questions?
http://adoguy.com