ADO.NET, i Data Provider e l'architettura connessa

1. ADO.NET, i Data Provider e l'architettura connessa Silvano Coriani Developer & Platform Evangelism MCTrainer MCSDeveloper MCADeveloper MCSEInternet MCDBA

2. Da dove iniziare...

3. Agenda

4. .NET Data Access Overview

5. .NET Application DataSet System.Data.Oracle System.Data.SqlClient System.Data.OleDb System.Data.Odbc OLE DB OLE DB Provider ODBC Other DBMS Non-relational sources Oracle SQL Server

6. .NET Data Provider • System.Data

7. .NET Data Provider • connessaCommand

8. .NET Data Provider • DataAdapter • DataReader

9. ADO.NET e SQL Server 2000

10. SqlClient .NET Data Provider

11. 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()

12. SqlClient .NET Data Provider Object Model SqlConnection SqlCommand System.Xml.XmlReader SqlDataReader SqlParameterCollection Object SqlParameter

13. SqlClient e il CLR Common Language Runtime SqlServer SqlClient TDS Parser

14. Accesso ai dati nel database server

15. Query ad-hoc

16. Prepare / Execute • PreparedSqlCommand

17. 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

18. Stored Procedure • WITH RECOMPILE

19. Stored Procedure • SqlCommandCommandType • Definizione di tutti i parametri tipizzati utilizzati dalla stored procedure • MAI

20. 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

21. 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

22. 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

23. SQL Server 2000 Best Practices

24. Cursori • row-based • set

25. SqlConnection • IDbConnection • ConnectionStringSqlCommandSqlDataReader • Close()Dispose() • BeginTransaction • SqlTransaction

26. SqlConnection • AttachDBFilenameSqlClientPermission

27. 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();

28. SqlCommand

29. 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()

30. 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

31. SqlCommand e le Stored Procedure • Parameters

32. SqlCommand e le Stored Procedure • ExecuteReader()DataReader • DBNull.Value per impostare a null un parametro di input di una SP • CommandBehavior

33. 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()

34. SqlDataReader • SqlCommand • Read()IEnumerable

35. 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()

36. SqlDataReader • NextResult() • La struttura del result set è descritta attraverso una “schema table” • GetSchemaTable()

37. 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();

38. 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();

39. Operazioni di Data Definition Language • ExecuteNonQuery()

40. Transazioni in ADO.NET • Transazioni locali • All’interno del codice SQL delle Stored Procedure • BEGIN/COMMIT TRAN • SqlOleDbTransaction

41. 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();

42. 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

43. Error & Info handling • SqlInfoMessageEventHandlerInfoMessage

44. 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; }

45. 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"); } }

46. Considerazioni sulle performance • Dispose()DataReader

47. Considerazioni sulle performance • FOR XMLXmlReader

48. 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

49. Codice portabile tra più Data Provider

50. Integrare applicazioni .NET con altri database server