ATKA1D 'NET Programming ADO'NET

1
ATKA1D .NET Programming ADO.NET
2
ADO.NET Architecture
Source
3
ADO.NET Documentation
4
System.Data Namespace
5
ADO.NET Data Providers
Source Understanding .NET David Chappell
Managed Code
System.Data.SqlClient
SQL .NET Data Provider
SQL Server
System.Data.OleDb
Client
OLE DB .NET Data Provider
OLE DB Provider
Other DBMS
System.Data.Odbc
ODBC Driver
Other DBMS
ODBC .NET Data Provider
CLR
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002
6
ADO.NET Data Provider
Source Understanding .NET David Chappell
.NET Data Provider and the Core
Objects
Client
Connection
Parameters
Command
objects
DataReader
DBMS
DataSet
DataAdapter

• .Net data provider using the namespace
• Provider for SQL Server System.Data.SqlClient
• Provider for OLE DB System.Data.OleDb
• Provider for ODBC System.Data.Odbc
• Provider for Oracle
  System.Data.OracleClient

Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002
7
ADO.NET Data Provider Objects
Source Understanding .NET David Chappell
Generic objects with interfaces, versions
depending on the Data Provider
.NET Data Provider
Client
.ConnectionString .Open .Close
Transaction
.Commit .Rollback
Connection
.BeginTransaction
Parameters
.CreateCommand
.ExecuteNonQuery
Command
Object
.ExecuteScalar
.ExecuteReader
.CommandText
DBMS
Rows
DataReader
IDataReader
.Read .Getlttypegt
DataSet
DataAdapter

• Data Providers implement object classes of their
  own
• SqlClient Sqlltobject typegt
• OleDb OleDbltobject typegt
• Odbc Odbcltobject typegt
• OracleClient Oracleltobject typegt

Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002-2003
8
Implicit, Local Transaction
Source ADO.NET and System.Transactions by John
Papa, msdn mag Feb 2005
(new in ADO.NET 2)
// Create the TransactionScope using
(TransactionScope txs new TransactionScope())
using (SqlConnection con new
SqlConnection(connectionString))
con.Open() SqlCommand cmd new
SqlCommand("UPDATE ...", con)
cmd.ExecuteNonQuery() // etc ...
con.Close() // Tell the transaction
scope that the transaction is in a //
consistent state and can be committed
txs.Complete() // The following bracket
completes, commits, and disposes // the
transaction
9
TransactionScope
(new in ADO.NET 2)
using (TransactionScope txs new
TransactionScope( )) using (SqlConnection
cn1 new SqlConnection(strConn))
txs.Complete( )
DTC
If more connections then all connections will be
enlisted to DTC promoting them into a global
transaction cn2
cn1
(start as local transaction)
If Complete is skipped gt Rollback
(local transaction)
If global transaction
2PC
If local transaction then Commit
database
Same or other database
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2006
10
TransactionScope defaults

• IsolationLevel Serializable
• of the possible values
• ReadUncommitted
• ReadCommitted
• ReapeatableRead
• Serializable
• TimeOut 1 minute
• TransactionScopeOption Required
• of the possible values
• Required
• RequiresNew
• Suppresses

11
Data Access into DataSets
Source Understanding .NET David Chappell
1) .Fill(DataSet, TableX)
SQL Server
SQL .NET Data Provider
3) Return DataTable

SqlDataAdapter
DataSet
SelectCommand
DataRelation
2) Execute query
Table X
Table Y
OLE DB .NET Data Provider
Oracle
OleDbDataAdapter
6) Return DataTable
SelectCommand
5) Execute query
4) Fill(DataSet, TableY)
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002-2003
12
.. Updating Database with DataSet
Source Understanding .NET David Chappell
1) .Update(DataSet)
.NET Data Provider
CommandBuilder

DataAdapter
2) Execute commands
DataSet
InsertCommand
DBMS
Table X
UpdateCommand
DeleteCommand
A DataSet cannot itself maintain an open
connection to any underlying database
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002
13
ADO.NET DataSet Object Model
FCS Partners
DataSet
.Select
Tables
DataTable
Columns
Relations
DataColumn
Rows
DataRelation
DataRow
Constraints
Constraint
ExtendedProperties
ParentRelations
DataRelation
ChildRelations
DataRelation
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002
14
Using DataSets with XML-Defined Data
Source Understanding .NET David Chappell
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002-2003
15
Synchronizing with XmlDataDocument
Source Understanding .NET David Chappell
Relational view
DataSet
Hierarchical view
XmlDataDocument
Table X
Table Y
Notes by Helia / Martti Laiho, Tuomo Ketomäki,
2002-2003