Transcript Dim cn As New OleDbConnection(strConn)

DEV 202:
Introduction to ADO.NET
David Sceppa
Author, Technical Lead
Microsoft
Agenda
•
•
•
•
Overview of ADO.NET Architecture
What’s a .NET Data Provider?
Working with connected objects
Working with disconnected objects
ADO Architecture
Your code
ADO
ODBC
OLE DB
OLE DB
Provider
Data
Store
MSDASQL
/ Kagera
ODBC
Driver
Data
Store
ADO.NET Architecture
Your code
OLE DB .NET
Data Provider
SQL Client .NET
Data Provider
Oracle Client .NET
Data Provider
ODBC
Driver
OLE DB
Provider
Data
Store
ODBC .NET
Data Provider
SQL Server
/ MSDE
Data
Store
Oracle
8i / 9i
.NET Data Provider
• Designed for a specific back-end or API
– OLE DB, SQL Server, ODBC, Oracle
• Implements base interfaces
– IDbConnection, IDbCommand, IDbDataAdapter
• Implements back-end specific functionality
– SqlCommand.ExecuteXmlReader
– OracleDataReader.GetOracleBFile
ADO.NET Object Model
Connection
Command
DataSet
DataTable
Parameter
DataColumn
DataReader
DataRow
Transaction
DataAdapter
.NET Data Provider
Constraint
DataRelation
Highly Factored Object Model
• Objects support the same functionality
regardless of back end, property settings
• DataReader
– Designed for read-only, forward-only data
• DataSet
–
–
–
–
–
Designed for disconnected data scenarios
Supports scrolling, filtering, searching, sorting
Hierarchical data
Caching updates
Tight XML integration
Connection Object
• Manages connection to database
• Starting point for queries and transactions
Dim strConn As String
strConn = "Provider=SQLOLEDB;Data Source=(local)" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
...
cn.Close()
Connection
Command
Parameters
DataReader
Transaction
DataAdapter
Command Object
• Manages row returning and action queries
• Can be parameterized
• Returns results via a DataReader
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local);..."
Dim cn As New OleDbConnection(strConn)
cn.Open()
strSQL = "SELECT OrderID, OrderDate FROM Orders " & _
"WHERE CustomerID = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5)
cmd.Parameters("@CustomerID").Value = "ALFKI"
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
...
rdr.Close()
cn.Close()
Connection
Command
Parameters
DataReader
Transaction
DataAdapter
Command object
• Use ExecuteNonQuery for action queries
Dim cn As New OleDbConnection(strConn)
cn.Open()
strSQL = "DELETE FROM Products WHERE ProductID = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
cmd.Parameters.Add("@ProductID", OleDbType.Integer)
cmd.Parameters("@ProductID").Value = 7
Dim intRowsAffected As Integer = cmd.ExecuteNonQuery()
• Use ExecuteScalar for single-value queries
Dim cn As New OleDbConnection(strConn)
cn.Open()
strSQL = "SELECT COUNT(OrderID) FROM Orders WHERE CustomerID = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5)
cmd.Parameters("@CustomerID").Value = "ALFKI"
Dim intNumOrders As Integer = cmd.ExecuteScalar()
DataReader Object
• Forward-only, read-only stream of data
• Returns data using generic Object data type
• Also exposes type-specific Get methods
Dim strConn, strSQL As String
Dim cn As New OleDbConnection(strConn)
Dim cmd As New OleDbCommand(strSQL, cn)
cn.Open()
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Do While rdr.Read()
Console.WriteLine(rdr("OrderID"))
Console.WriteLine(rdr(1))
Console.WriteLine(rdr.GetDateTime(2))
Loop
rdr.Close()
cn.Close()
Connection
Command
Parameters
DataReader
Transaction
DataAdapter
DataReader Object
• DataReader does not initially point to first row
• To move to next row, call the Read method
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Do While rdr.Read()
Console.WriteLine(rdr("OrderID"))
Console.WriteLine(rdr(1))
Console.WriteLine(rdr.GetDateTime(2))
Loop
rdr.Close()
• Ordinal-based lookups faster than string-based
– Use GetOrdinal to determine ordinal for a column
• NextResult method moves to next resultset
• Connection is blocked until DataReader is closed
Transaction Object
• Manages transactions
• To execute a Command on a Transaction,
associate Command with the Transaction
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim txn As OleDbTransaction = cn.BeginTransaction()
Dim cmd As New OleDbCommand(strSQL, cn, txn)
intRowsAffected = cmd.ExecuteNonQuery()
If intRowsAffected = 1 Then
'Success!
txn.Commit()
Else
'Failure!
txn.Rollback()
End If
cn.Close()
Connection
Command
Parameters
DataReader
Transaction
DataAdapter
DataAdapter Object
• Bridge between database and
disconnected objects
• Retrieves data from database
• Submits pending changes to database
Dim strConn, strSQL As String
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim tbl As New DataTable()
da.Fill(tbl)
...
Dim cb As New OleDbCommandBuilder(da)
da.Update(tbl)
Connection
Command
Parameters
DataReader
Transaction
DataAdapter
DataAdapter object
• DataAdapter uses Command and
DataReader objects to execute query,
fetch the results, and store the data in a
DataTable
• DataAdapter uses Commands and
Parameters to submit pending changes
to the data store
Disconnected objects
•
•
•
•
•
•
Relational, robust, disconnected data cache
Similar to disconnected ADO Recordset
Supports scrolling, filtering, searching
Supports cached changes
DataSet
No knowledge of back-end
DataTable
Features do not rely on
DataColumn
data provider
DataRow
Constraint
DataRelation
DataTable Object
•
•
•
•
•
Designed to manage data off-line
Collection of rows and columns of data
Supports scrolling, filtering and searching
Supports caching changes
DataSet
Enforces constraints
DataTable
DataColumn
DataRow
Constraint
DataRelation
Using DataTables
• Store results of a query via DataAdapter.Fill
Dim strConn, strSQL As String
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim tbl As New DataTable()
da.Fill(tbl)
– Automatically populates the DataTable's
Columns collection
• Can manually populate the collection
Dim strConn, strSQL As String
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim tbl As New DataTable()
tbl.Columns.Add("CustomerID", GetType(String))
tbl.Columns.Add("CompanyName", GetType(String))
…
da.Fill(tbl)
Using DataTables
• All rows available at all times
Dim row As DataRow
For Each row In tbl.Rows
Console.WriteLine(row("CustomerID") + " – " + row("CompanyName"))
Next row
• Cache changes – submit via DataAdapter.Update
row("CompanyName") = "Modified Value"
Dim cb As New OleDbCommandBuilder(da)
da.Update(tbl)
– AcceptChanges does not submit changes.
It marks them as submitted.
DataSet Object
• Collection of DataTables
• DataRelations manage related DataTables
– Navigate parent to child
– Cascade changes
– Enforce constraint
• XML support
– Load and save data as XML
– Load and save schema as XML
DataSet
DataTable
DataColumn
DataRow
Constraint
DataRelation
Working with related data
• Fill DataSet with data from separate queries
• Add DataRelation
• Use DataRelation to display related data
Dim daCustomers As New SqlDataAdapter(strSQLCustomers, strConnSQLServer)
Dim daOrders As New OracleDataAdapter(strSQLOrders, strConnOracle)
Dim ds As New DataSet()
daCustomers.Fill(ds, "Customers")
daOrders.Fill(ds, "Orders")
ds.Relations.Add("CustomersOrders", ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
Dim rowCustomer, rowOrder As DataRow
For Each rowCustomer In ds.Tables("Customers").Rows
Console.WriteLine(rowCustomer("CompanyName"))
For Each rowOrder In rowCustomer.GetChildRows("CustomersOrders")
Console.WriteLine(vbTab & rowOrder("OrderID") & " - " & rowOrder("OrderDate"))
Next rowOrder
Next rowCustomer
Constraints
• DataTable supports unique and foreign key
constraints
• PrimaryKey property of DataTable is a
special case used for quick searches
– DataTable.Rows.Find(KeyValue)
• Constraints enforced based on local data
DataView object
• Represents a view of data in DataTable
• Supports filtering and sorting
• Can filter rows based on RowState
Dim tbl As New DataTable()
…
Dim vue As New DataView(tbl)
vue.RowFilter = "Country = 'Spain'"
vue.RowStateFilter = DataViewRowState.ModifiedCurrent
vue.Sort = "CustomerID DESC"
For intCounter = 0 To vue.Count - 1
Console.WriteLine(vue(intCounter)("CustomerID") + " – " +
vue(intCounter)("CompanyName"))
Next row
– A DataView is not the same as a database view
Writing provider portable code
• Separate code into different layers
– Data access layer vs. business object layer
• Use generic interfaces between layers
DataSet / DataTable
Data
Store
Data Access
Layer
Business
Object Layer
IDbDataAdapter
/ IDataReader
Porting ADO code to .NET
• COM Interop leverages existing ADO code
• OleDbDataAdapter accepts ADO Recordsets
• Not all ADO features have direct counterparts in
ADO.NET
–
–
–
–
–
ADOX / ADOMD
Recordset.GetRows / GetString
Recordset.Resync
Server-side cursors
Parameters.Refresh
Agenda for DEV329 (Fri 08:30 Rm 6)
Advanced Data Access in Visual Studio .NET
• Key ADO.NET features
• Visual Studio .NET: The best ADO.NET
experience at design-time and run-time
• Submitting updates in ADO.NET
• Advanced updating scenarios
• Paging through a large resultset
More ADO.NET Presentations
• Moving from ADO 2.x to ADO.NET – Today 11:45
• The Microsoft ADO.NET DataSet and You – Wed 11:45
• Extending ADO.NET:
Building a Custom Data Factory API – Thu 08:30
• Microsoft ADO.NET:
Integrating XML into Your Data Access Layer – Thu 16:45
• Advanced Data Access in Visual Studio .NET – Fri 08:30
• Microsoft ADO.NET: Blackbelt – Fri 10:00
Resources from Microsoft Press
ADO.NET / ADO
For more information please visit the TechEd Bookshop.
www.microsoft.com/mspress
Questions?
Don’t forget to complete the
on-line Session Feedback form
on the Attendee Web site
https://web.mseventseurope.com/teched/