ADO.NET, i Data Provider e
l'architettura connessa
Silvano Coriani
Developer & Platform Evangelism
MCTrainer
MCSDeveloper
MCADeveloper
MCSEInternet
MCDBA
1 / 70
Da dove iniziare...
2 / 70
Agenda
3 / 70
.NET Data Access Overview
4 / 70
.NET Application
DataSet
System.Data.Oracle
System.Data.SqlClient
System.Data.OleDb
System.Data.Odbc
OLE DB
OLE DB Provider
ODBC
Oracle
SQL Server
Other DBMS
Non-relational
sources
5 / 70
.NET Data Provider
• System.Data
6 / 70
.NET Data Provider
• connessaCommand
7 / 70
.NET Data Provider
• DataAdapter
• DataReader
8 / 70
ADO.NET e SQL Server 2000
9 / 70
SqlClient .NET Data Provider
10 / 70
SqlClient Managed Provider
• Non utilizza direttamente i cursori server
di SQL Server
– Nel caso, occorrono comandi T-SQL espliciti
(OPEN CURSOR, sp_cursor, sp_executesql ecc.)
– Oppure utilizzare ADO 2.x attraverso COM
Interop
• Permette di interagire con le funzionalità
XML di SQL Server 2000
– ExecuteXmlReader()
11 / 70
SqlClient .NET Data Provider Object Model
SqlConnection
SqlCommand
System.Xml.XmlReader
SqlDataReader
SqlParameterCollection
Object
SqlParameter
12 / 70
SqlClient e il CLR
Common Language Runtime
SqlClient
TDS
Parser
SqlServer
13 / 70
Accesso ai dati nel database
server
14 / 70
Query ad-hoc
15 / 70
Prepare / Execute
• PreparedSqlCommand
16 / 70
sp_executesql
• Alternativa valida alle stored procedure
– Elimina la gestione lato server
• Se si vuole ottenere il riutilizzo del piano di
esecuzione, anche qui occorre definire
correttamente I parametri con il relativo
tipo
• Eseguita in modalità RPC
– Più efficiente nell’esecuzione sul server
– Riutilizzo del piano di esecuzione della query
exec sp_executesql N‘Insert T1 Values (@p)’,N’@p float’,1
17 / 70
Stored Procedure
• WITH RECOMPILE
18 / 70
Stored Procedure
• SqlCommandCommandType
– Definizione di tutti i parametri tipizzati
utilizzati dalla stored procedure
– MAI
19 / 70
Classica Stored Procedure
CREATE PROC spGetCustomerOrders
@CustomerID int
@NumOrder int OUTPUT
AS
-- Validazione di parametri di input
...
-- Logica applicativa (eventualmente chiama altre SP)
...
-- Restituzione di uno o più result set
...
-- Restituisco il valore di ritorno
20 / 70
Stored Procedure FOR XML
CREATE PROC spGetCustomerOrdersXML
@CustomerID int
@NumOrder int OUTPUT
AS
-- Validazione di parametri di input
...
-- Logica applicativa (eventualmente chiama altre SP)
...
-- Restituzione di uno o più result set
SELECT
C.CustomerID,O.OrderID,O.OrderDate
FROM
Customer C Left Join Orders O ON C.CustomerID=O.CustomerID
WHERE
C.CustomerID = @CustomerID
FOR XML AUTO, ELEMENTS –- o EXPLICIT
-- Restituisco il valore di ritorno
21 / 70
Posso passare un set di record ad una SP?
CREATE PROC spInsertCustomersXML
@RecordSetXML varchar(1024)
AS
-- Carico il doc XML nel DOM
sp_xml_preparedocument @h OUTPUT, @ RecordSetXML
-- Eseguo un inserimento in una tabella filtrando
-- le informazioni che mi servono attraverso XPath
OpenXML(@h,'//Customer')
WITH (CustomerID varchar(5) '@ID',
CompanyName varchar(32),'@name')
-- Rimuovo il documento dalla memoria
sp_xml_removedocument
22 / 70
SQL Server 2000 Best Practices
23 / 70
Cursori
• row-based
– set
24 / 70
SqlConnection
• IDbConnection
• ConnectionStringSqlCommandSqlDataReader
– Close()Dispose()
• BeginTransaction
– SqlTransaction
25 / 70
SqlConnection
• AttachDBFilenameSqlClientPermission
26 / 70
SqlConnection
• SqlCommandSqlDataAdapter
[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data _
Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
nwindConn.Open()
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost; Integrated
Security=SSPI; Initial Catalog=northwind");
nwindConn.Open();
27 / 70
SqlCommand
28 / 70
SqlCommand
• Implementa l’interfaccia IDbCommand
– ExecuteReader()
• Da utilizzare quando è previsto un result set come
ritorno
– ExecuteScalar()
• Da utilizzare per aggregazioni o risultati di calcoli
• Ritorna solo la prima colonna della prima riga, gli altri
dati vengono persi
– ExecuteNonQuery()
• Ottimizzato per query che non ritornano result set ma
solo parametri di ritorno o numero di record modificati
– ExecuteXmlReader()
29 / 70
SqlCommand e query libere
Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Dim rowsAffected As Integer
Try
' Creo la connessione
sqlConn = New SQLConnection(myConnString)
' Creo il comando
sqlCmd = New SQLCommand()
' specifico il tipo di comando
With sqlCmd
.CommandType = CommandType.Text
.CommandText = "Insert Customers (Alias, CustomerName) _
Values ('myAlias','myName')"
.Connection = sqlConn
End With
' apro la connessione
sqlConn.Open()
' eseguo il comando, vengono ritornate le righe inserite
rowsAffected = sqlCmd.ExecuteNonQuery()
Catch e As Exception
' gestisco l’eccezione …
Finally
' chiudo la connesione
sqlConn.Close()
End Try
30 / 70
SqlCommand e le Stored
Procedure
• Parameters
31 / 70
SqlCommand e le Stored
Procedure
• ExecuteReader()DataReader
• DBNull.Value per impostare a null un
parametro di input di una SP
• CommandBehavior
32 / 70
SqlCommand e le Stored
Procedure
sqlConn = New SQLConnection(myConnString)
' Create a new command object
sqlCmd = New SQLCommand()
' Specify the stored procedure and connection
With sqlCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "InsertCustomer"
.Connection = sqlConn
End With
' Define and add a parameter to the parameters collection
param = sqlCmd.Parameters.Add(New SQLParameter("@p", SQLDBType.NVarChar, 100)
With param
.Direction = ParameterDirection.Input
' Set the parameter value
.Value = “xyz"
End With
' Add remaining parameters
…
' Open the connection
sqlConn.Open()
' Execute the command
rowsAffected = sqlCmd.ExecuteNonQuery()
33 / 70
SqlDataReader
• SqlCommand
• Read()IEnumerable
34 / 70
SqlDataReader
• IDataReaderIDataRecord
– Per accesso a informazioni in formato
rettangolare e non
• È possibile accedere ai dati nelle singole
colonne per indicenome del campo o
attraverso metodo accessori dedicati ai
tipi di dato contenuti
– GetDateTime()GetDouble()GetGuid(),
GetInt32()
35 / 70
SqlDataReader
• NextResult()
• La struttura del result set è descritta
attraverso una “schema table”
– GetSchemaTable()
36 / 70
Utilizzo di SqlDataReader
[Visual Basic]
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0),
myReader.GetString(1))
Loop
myReader.Close()
[C#]
SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();
37 / 70
XmlReader
• ExecuteXmlReader() FOR XML
[Visual Basic]
Dim custCMD As SqlCommand = New SqlCommand("SELECT * FROM Customers FOR XML _
AUTO, ELEMENTS", nwindConn)
Dim myXR As System.Xml.XmlReader = custCMD.ExecuteXmlReader()
[C#]
SqlCommand custCMD = new SqlCommand("SELECT * FROM Customers FOR XML AUTO,
ELEMENTS", nwindConn);
System.Xml.XmlReader myXR = custCMD.ExecuteXmlReader();
38 / 70
Operazioni di Data Definition
Language
• ExecuteNonQuery()
39 / 70
Transazioni in ADO.NET
• Transazioni locali
– All’interno del codice SQL delle Stored Procedure
• BEGIN/COMMIT TRAN
– SqlOleDbTransaction
40 / 70
Transazioni in ADO.NET
[C#]
SqlConnection conn = new SqlConnection();
conn.Open("...");
SqlTransaction tx = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "...";
cmd.Transaction = tx;
cmd.ExecuteNonQuery();
tx.Commit();
[VB.NET]
OleDbConnection conn = new OleDbConnection();
conn.Open("...");
OleDbTransaction tx = conn.BeginTransaction();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "...";
cmd.Transaction = tx;
cmd.ExecuteNonQuery();
tx.Commit();
41 / 70
Error & Info handling
• Errori durante l’esecuzione di operazioni
sulla fonte dati vengono intercettati
attraverso le eccezioni
• Ogni Managed providers implementa le
proprie eccezioni
– SqlException
• SqlError
42 / 70
Error & Info handling
• SqlInfoMessageEventHandlerInfoMessa
ge
43 / 70
Error & Info handling
public static int Main(string[] args) {
try
{
SqlConnection conn = new SqlConnection(“ConnectionString”);
// Associo un event handler all’evento di InfoMessage
conn.InfoMessage += new SqlInfoMessageEventHandler(myHandler);
conn.Open();
}
catch (SqlException e) {
for (int i=0; i < e.Errors.Count; i++)
{
Console.WriteLine("Index #" + i + "\n Error: " + e.Errors[i].ToString() + "\n");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{ conn.Close(); }
return 0;
}
44 / 70
Error & Info handling
public static void myHandler(object conn, SqlInfoMessageEventArgs e)
{
Console.WriteLine("caught a SQL warning");
for (int i=0; i < e.Errors.Count; i++)
{
Console.WriteLine("Index#" + i + "\n" + "Warning:" + e.Errors[i].ToString() +
"\n");
}
}
45 / 70
Considerazioni sulle
performance
• Dispose()DataReader
46 / 70
Considerazioni sulle
performance
• FOR XMLXmlReader
47 / 70
Considerazioni sulle
performance
• Tenere sempre presente il tipo di
comando e di valori di ritorno
– Singola entità letta per chiave primaria
• Stored Procedure con parametri di ritorno
– Valore scalare
• ExecuteScalar()
– DataReader
48 / 70
Codice portabile tra più Data Provider
49 / 70
Integrare applicazioni .NET con altri database
server
50 / 70
OleDb Managed Provider
51 / 70
OleDb Managed Provider
Provider
OleDb
Managed
Provider
OLEDB
Common Language Runtime
Data Source
Relazionale
e non
52 / 70
OLEDB Managed Provider Object
Model
OleDbConnection
.CreateCommand
.ExecuteReader
OleDbCommand
.Connection
.Parameters
OleDbDataReader
.Item
Object
OleDbParameterCollection
.Add
.Item
.CreateParameter
.OleDbParameter
53 / 70
OleDbConnection
[Visual Basic]
Dim myConnection As String = "Provider=SQLOLEDB;Data Source=localhost;Initial _
Catalog=Northwind;Integrated Security=SSPI;"
Dim myConn As New OleDbConnection(myConnection)
myConn.Open()
[C#]
String myConnection = "Provider=SQLOLEDB;Data Source=localhost;Initial
Catalog=Northwind;Integrated Security=SSPI;";
OleDbConnection myConn = new OleDbConnection(myConnection);
myConn.Open();
54 / 70
OleDbCommand
public void ReadMyData(string myConnString)
{
string mySelectQuery = "SELECT OrderID, CustomerID FROM Orders";
OleDbConnection myConnection = new OleDbConnection(myConnString);
OleDbCommand myCommand = new OleDbCommand(mySelectQuery,myConnection);
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
try
{
while (myReader.Read())
{Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));}
}
finally
{
// da chiudere sempre dopo aver terminato la lettura
myReader.Close();
// da chiudere sempre dopo aver terminato la lettura
myConnection.Close();}
}
55 / 70
ODBC Managed Provider
56 / 70
ODBC Managed Provider
Driver
Odbc
Managed
Provider
ODBC
Common Language Runtime
Data Source
Relazionale
57 / 70
ODBC Managed Provider Object
Model
OdbcConnection
.CreateCommand
.ExecuteReader
OdbcCommand
.Connection
.Parameters
OdbcDataReader
.Item
Object
OdbcParameterCollection
.Add
.Item
.CreateParameter
OdbcParameter
58 / 70
OleDbDataReader
• ExecuteReader()
59 / 70
Utilizzo di OleDbDataReader
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, CustomerID FROM Orders"
Dim myConnection As New OleDbConnection(myConnString)
Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDbDataReader
myReader = myCommand.ExecuteReader()
' La chiamata a Read posiziona sul primo record
While myReader.Read()
Console.WriteLine(myReader.GetInt32(0).ToString() + ", " _
+ myReader.GetString(1))
End While
' chiudere il reader
myReader.Close()
' chiudere la connessione
myConnection.Close()
End Sub
60 / 70
DB2 e ADO.NET
Application
Application
Application
System.Data.OleDb
Microsft.Data.ODBC
IBM.Data.DB2
OLE DB .Net
Data Provider
ODBC .Net Data
Provider
DB2 .Net Data
Provider
OleDbConnection
OdbcConnection
DB2Connection
OleDbCommand
OdbcCommand
DB2Command
OleDbAdapter
OdbcDataAdapter
DB2DataAdapter
OleDbDataReader
OdbcDataReader
DB2DataReader
IBM DB2 OLE
DB Provider
IBM DB2 ODBC
Driver
DB2
61 / 70
DB2Connection Object
62 / 70
DB2Command Object
63 / 70
DB2DataReader Object
64 / 70
Oracle e .NET
• Microsoft .NET Data Provider per Oracle
– Prodotto da Microsoft
– Usa gli strumenti client di Oracle
• Oracle Data Provider per .NET (ODP.NET)
– Sviluppato da Oracle
– Integrazione completa
– Espone tutte le caratteristiche di un Oracle DB
• Oracle Connect per .NET
65 / 70
.NET Data Providers
66 / 70
ODP.NET
DataSet
Oracle
DataAdapter
OracleCommand
Builder
Oracle
DataReader
Oracle
Command
Oracle
Transaction
Oracle
Connection
67 / 70
ODP.NET
68 / 70
In Sintesi
69 / 70
Link utili
70 / 70
News
71 / 70
72 / 70