Oracle & SQL Server in a .NET World

1. Oracle & SQL Server in a .NET World Silvano Coriani (silvano.coriani@microsoft.com Developer Evangelist Microsoft

2. Agenda • Il miglior RDBMS per .NET  • Microsoft SQL Server 2005 • Ok, ok…viviamo in un mondo “meticcio”  • Oracle 8i/9i/10g (10.1.0,10.2.0) • IBM DB2 UDB 8.2.2 • .NET Development • Problemi simili, ambienti e soluzioni diverse • Best practices e dintorni

3. RDBMS su Windows • Intendiamoci… Microsoft SQL Server 2005 è il miglior RDBMS su Windows • Miglior TPC-C su hw commerciale (x86-IA64) • Migliori risultati su 2-4-6 processori (x86) • Ha il Query Processor più evoluto • Miglior integrazione con la piattaforma di sviluppo • Semplicità di gestione • “Auto-everything” da sempre • News con la versione 2005 • Uno Storage Engine all’altezza di qualsiasi confronto • Nuovi servizi integrati come il Broker, Notifications, ecc.

4. RDBMS su Windows • Ma non siamo soli…  • Oracle, dalla 8i alla 10g (10.2.0) • Pro (cosa gli invidio) • La reputazione  e il know how • Lo storage engine • Contro • Query Proc “solido” ma non particolarmente smart • Costi licenze comunque elevati, e componenti aggiuntive a pagamento • Complessità di gestione (capacity planning, installazione, tuning, amministrazione) • IBM DB 2 • Pro • Query proc decisamente interessante • Il TPC-C benchmark su AIX e POWER5  • Contro • Versioni differenti sulle diverse piattaforme • Funzionalità, comportamenti, modelli di programmazione • Strumenti di gestione “incoerenti” • Servizi poco integrati tra di loro

5. RDBMS su Windows • Ma in Oracle si fa…. come faccio in SQL Server? • Nello Storage Engine di SQL Server 2005 ci sono novità per i DEV che utilizzano normalmente Oracle • Snapshot Isolation (e Read Committed S.I.) • Data & index partitioning • È stato aggiunta la funzione ROW_NUMBER() per numerare i record di un resultset (evvai con le paginazioni!)  • Altre tecniche di programmazione tipiche del mondo Oracle possono essere facilmente imitate con SQL Server • Ad esempio le famigerate Sequence

6. Estendere il DBMS “dall’interno” con .NET • SQL Server 2005 integra il CLR per estendere il DBMS • Sia Oracle che DB2 consentono l’utilizzo di estendere il motore relazionale con il codice managed, in modalità diverse • Utilizzano la v1.1 del CLR • Integrazione molto diversa rispetto a SQL Server 2005 • Semplice hosting attraverso le interfacce COM del CLR • Nessun utilizzo degli host attributes della v2.0 per garantire la stabilità dell’hoster (utilizzo delle risorse, gestione eccezzioni, ecc.) Es. Oracle CLR Host

7. Windows Development Environment Support Oracle Developer Tools for VS.NET IIS (ASPASP.NET) MTS/COM+ C++, C#, VB .NET Application Develop Deploy Visual Studio Environment Application IIS Deploy Database Development ODP.NET Oracle DB Extensions For .NET Oracle Services for MTS

8. Sviluppare applicazioni “client” con .NET • Strumenti • .NET Data Providers di Microsoft, Oracle e IBM • Oracle Developer Tools for Visual Studio .NET • IBM DB2 Tools for Visual Studio .NET • Reusable code • Enterprise Library • Data Access Application Blocks • Oracle • DB2 • Problemi aperti • Differenti dialetti SQL • Ma standard ANSI-SQL 92 più o meno rispettato • Differenti funzionalità • Sequence, Locking model, Stored Proc, supporto XML, ecc. ecc.

9. Oracle e .NET sul client • 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 (DataDirect) • Altri metodi di accesso • OLE DB .NET via OLE DB • ODBC .NET via ODBC

10. ODP.NET Basics • Disponibile gratuitamente • .NET Developer Center • http://www.oracle.com/technology/tech/dotnet/index.html • Può essere usato con Oracle8, Oracle8i, Oracle9i, e Oracle10g • Il database server può essere su Unix, Linux, Windows, etc. • Client basato su .NET • Supporta VS.NET 2002 e 2003 • Supporta .NET Framework 1.0 e 1.1

11. .NET Data Providers

12. ODP.NET Layer Disconnesso Layer Connesso (ODP.NET) Dati Oracle DataAdapter OracleCommand Builder DataSet Oracle DataReader Oracle Command Oracle Transaction Oracle Connection Oracle

13. ODP.NET • Supporto completo a PL/SQL • Packaged, non-packaged, anonymous, autonomous • *Batch SQL disponibile con anonymous PL/SQL • Data type nativi Oracle • LOBS, REF Cursors, BFiles, N-data types, Dates, TimeStamps, Longs, Raws, etc. • Mapping con tipi .NET • *Connection pooling • Min, Max, Timeout, Lifetime, Increment, Decrement

14. Integrazione con Visual Studio .NET • Dynamic Help • Documentazione ODP.NET – F1 key • Server Explorer • Tables, views, e synonyms • Query e view designer • Stored procedures, functions, triggers, package bodies, package specifications • Execute, create, delete • Intellisense • Drag-and-drop da Data toolbox

15. .NET e Oracle • ADO.NET • ODBC.NET • OleDB • Oracle • ODP.NET • VS.NET 2003 • ASP.NET • Web Services

16. Usare ADO.NET con Oracle Sub UseDataReader() Dim conn as New OracleConnection() conn.ConnectionString = _ "data source=symbolicname;" & _ "user id=scott;password=tiger" Dim cmd As New OracleCommand( _ "select * from emp", conn) Dim rdr As OracleDataReader Try conn.Open() rdr = cmd.ExecuteReader() While rdr.Read = True Console.WriteLine( _ "empno = {0}, ename = {1}", _ rdr("empno"), rdr("ename")) End While Catch e As Exception Console.WriteLine(e.Message) Finally If conn.State = _ ConnectionState.Open Then conn.Close() End If cmd.Dispose() conn.Dispose() End Try End Sub Imports System.DataImports System.Data.OracleClient Sub Main() Dim conn as New OracleConnection() conn.ConnectionString = _ "data source=zzz;" & _ "user id=scott;password=tiger" Try conn.Open() Console.WriteLine("Hello Oracle") Catch e as Exception Console.WriteLine("Failed connect") Finally If conn.State = _ ConnectionState.Open Then conn.Close() End If conn.Dispose() End Try End Sub

17. Oracle e i DataSet Sub UseDataSet() Dim ds As New DataSet() Dim da As New OracleDataAdapter( _ "select * from emp", _ "data source=zvm43;" & _ "user id=tiger;password=tiger") Dim bld As New OracleCommandBuilder(da) Try da.Fill(ds, "emp") Dim r As DataRow For Each r In ds.Tables(0).Rows Console.WriteLine( _ "empno = {0}, ename = {1}", _ r("empno"), r("ename")) Next ' Change a row and call Update ds.Tables(0).Rows(0)(2) = "JONES" da.Update(ds, "emp") Catch e As Exception Console.WriteLine(e.Message) End Try End Sub

18. Query parametriche • “Select * from emp where empno > ?”(ODBC,OleDB,DataDirect) • “Select * from emp where empno > @enumber”(System.Data.SqlClient) • “Select * from emp where empno > :enumber”(System.Data.OracleClient) Sub UseParmQuery() Dim conn As New OracleConnection( _ "data source=zmv43;" & _ "user id=scott;password=tiger") Dim cmd As New OracleCommand( _ "select * from emp " & _ "where empno > :thenumber", conn) cmd.Parameters.Add("thenumber", 7000)Dim rdr As OracleDataReader Try conn.Open() rdr = cmd.ExecuteReader() While rdr.Read = True Console.WriteLine( _ "empno = {0}, ename = {1}", _ rdr("empno"), rdr("ename")) End While Catch e As Exception Console.WriteLine(e.Message) Finally If conn.State = _ ConnectionState.Open Then conn.Close() End If cmd.Dispose() conn.Dispose() End Try End Sub

19. Funzionalità specifiche ODP.NET • Stessi nomi di classi dei provider Microsoft e DataDirect • Ottimizzazioni sulle connessioni • Gestione dei Savepoint nelle transazioni locali e distribuite • REF CURSORs • BLOB, CLOB, BFILE

20. REF CURSOR come Resultset OracleConnection conn = new OracleConnection( "data source=zmv43;user id=scott;password=tiger");OracleCommand cmd = new OracleCommand("Employes.GetEmpRecords", conn);cmd.CommandType = CommandType.StoredProcedure; conn.Open(); cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor); cmd.Parameters.Add("j_cursor", OracleDbType.RefCursor); cmd.Parameters.Add("indeptno", OracleDbType.Decimal); cmd.Parameters.Add("p_errorcode", OracleDbType.Int32); cmd.Parameters[0].Direction = ParameterDirection.Output; cmd.Parameters[1].Direction = ParameterDirection.Output; cmd.Parameters[2].Value = 10; cmd.Parameters[3].Direction = ParameterDirection.Output; OracleDataReader rdr = cmd.ExecuteReader(); // fetch output parm Console.WriteLine(cmd.Parameters[3].Value); do { while (rdr.Read()) Console.WriteLine(rdr[0]); } while (rdr.NextResult() == true);

21. ExecuteNonQuery e REF CURSORS // Connection and parameter setup as above ... int i = cmd.ExecuteNonQuery(); // cast to obtain both REF CURSORs as DataReadersOracleDataReader rdr1 = ((OracleRefCursor)cmd.Parameters[0].Value).GetDataReader(); OracleDataReader rdr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader(); // use both REF CURSORs at the same time while ((rdr1.Read()) && (rdr2.Read())) { Console.WriteLine(rdr1.GetName(0) + " = " + rdr1[0]); Console.WriteLine(rdr2.GetName(0) + " = " + rdr2[0]); }

22. ODP.NET e XML • *XML • XMLType data type • Query XML dal DB • Sia su tabelle relazionali che object-relational • Interoperabilità con MS XML API (es. XmlReader) • Supporta XSLT e XPATH * Performance tip

23. ODP.NET e XML • Richiede Oracle8i, Oracle9i, o Oracle 10g DB • Può richiedere XML Developer’s Kits (XDK) sul server • XML DB • Non è indispensabile ma raccomandato • Disponibilità di OracleXmlType • Possibilità di utilizzare Schema XML

24. Classi ODP.NET XML • OracleXmlType • Dati XMLType nativi • OracleXmlStream • Stream XML read-only • OracleXmlQueryProperties • OracleXmlSaveProperties

25. ODP.NET • Transazioni • Utilizzo degli Enterprise Services via Oracle Services for MTS • Locali (Implicite e esplicite) • Savepoints • Globalization • Supporto completo a Unicode • Utilizzo delle impostazioni internazionali dei client • Transparent application failover • Callback di notifica * Performance tip

26. Best Practice - Connection • Utilizzare il connection pooling • Ma attenzione al numero di connessioni nel pool (nell’ultima versione di ODP.NET abbiamo pieno controllo) • Attenzione alle stringe di connessione • Non generare pool differenti • Utilizzare forme di autenticazione “proxy”

27. Best Practice - Connection • Validate Connection • Garantisce una connessione valida, ma al prezzo di un extra round trip • Forse meglio crearsi il proprio exception handler • Utilizzare correttamente gli oggetti ODP.NET (e.g. OracleConnection) • Chiamare Dispose() quando non più utilizzati

28. Best Practice – Letture e aggiornamenti • Concetto base: controllare i roundtrip verso il database • Recuperare solo i dati necessari, solo quando necessario • SELECT • Usare FetchSize (OracleDataReader) e RowSize (OracleCommand) per controllare la dimensione dei dati ritornati • Utilizzare DataAdapter Requery • Evita DB round trip

29. Best Practice – Letture e aggiornamenti • Multi-statement (in un roundtrip) • Eseguire più statement batch differenti • Attraverso anonymous PL/SQL • Unico roundtrip, resultset differenti • Stesso set di comanti più volte? • Utilizzare statement caching • Riutilizzo di comandi SQL o PL/SQL • Ok anche quando il valore dei parametri cambia

30. Best Practice – Letture e aggiornamenti • Multi-statement • Eseguire lo stesso comando più volte? • Utilizzare il parameter array binding • Il comando verrà eseguito una volta per ogni valore dell’array, ma in un unico roundtrip • Limitazioni nell’utilizzo di SELECT statement, tipicamente adatto ad operazioni di insert/update • Passare un array? • Utilizzare gli array associativi di PL/SQL • Il comando verrà eseguito una sola volta per l’intero array • Ok anche per comandi di SELECT • Parameter array binding • Più semplice degli array associativi • Ma gli array associativi hanno migliori performance e funzionalità avanzate

31. Best Practice –Data Type • Concetto base: controllare i roundtrip • Utilizzare I tipi esposti da ODP.NET (es. OracleClob) • Utilizzare OracleRefCursor • Recupero dei dati solo quando necessario • Utilizzare Oracle LOB (DML) • No roundtrip aggiuntivi per aggiornare o inserire LOB

32. Best Practice –Data Type • Usare Oracle LOB (SELECT) • Devo leggere tutto il LOB? • InitialLOBFetchSize e InitialLongFetchSize per controllare l’attività • Devo leggerne solo una parte? • Proprietà Read per controllare la quantità di dati letti per roundtrip • Devo leggere il contenuto successivamente? • Di default, ODP.NET LOBs utilizzano puntatori agli oggetti sul server e non necessitano di rileggere interamente gli oggetti

33. Approfondimenti • .NET Developer Center • http://www.oracle.com/technology/tech/dotnet/index.html • Portale Oracle su Windows • http://www.oracle.com/technology/tech/windows/index.html

34. .NET Application .NET e la famiglia DB2 DB2 for zOS DB2 (partitioned across a cluster) DB2 (Linux for zSeries) DB2 for VSE & VM DB2 (Windows & Linux) DB2 for iSeries DB2 (Linux for iSeries) DB2 Everyplace

35. ODBC .Net Data Provider OLE DB .Net Data Provider DB2 .Net Data Provider OdbcConnection OleDbConnection DB2Connection OdbcCommand OleDbCommand DB2Command DB2DataAdapter OleDbAdapter OdbcDataAdapter OleDbDataReader OdbcDataReader DB2DataReader DB2 e ADO.NET sul client Application System.Data.OleDb Application Microsft.Data.ODBC Application IBM.Data.DB2 IBM DB2 OLE DB Provider IBM DB2 ODBC Driver DB2

36. Data Provider per DB2 • ODBC .NET Data Provider • Per .NET Framework v1.0: • Namespace = Microsoft.Data.Odbc • download from MSDN • per .NET Framework v1.1: • Namespace = System.Data.Odbc • OLE DB .NET Data Provider • Namespace = System.Data.Oledb • IBM DB2 .NET Data Provider • Namespace = IBM.Data.DB2 • Parte del DB2 Run-Time Client V8.1.2

37. DataSet Table Table Table Table Table Modalità disconnessa System.Data IBM.Data.DB2 DB2 WinForm Data Adapter Data Adapter DB2 Data Adapter WebForm SQL Server Data Adapter Mobile WebForm Other Data Adapter

38. DB2Connection Object • Supporto per connection pooling • Può partecipare a transazioni distribuite • Ricordarsi di chiudere la connessione quando finito il lavoro!!! ' set a connection string and connect to SAMPLE database ' using DB2 .NET Data Provider DimconnectionString = "Database=SAMPLE" Dim cnn As New DB2Connection(connectionString) cnn.Open() ' Use the connection in here… If cnn.State = ConnectionState.Open Then cnn.Close() End If

39. DB2Command Object • Per comandi SQL statements o stored procedure • Command Object: • ExecuteReader – ritorna un DataReader • ExecuteNonQuery – non ritorna nulla • ExecuteScalar – ritorna il primo valore scalare ' create a DB2Command with a SELECT statement Dim db2SelectCommand As New IBM.Data.DB2.DB2Command() db2SelectCommand.CommandText = "Select * from Customers" db2SelectCommand.Connection = myDB2Connection

40. DB2DataReader Object • Cursore forward-only, read-only • Solo il record corrente in memoria • ExecuteReader • Chiudere per liberare la connessione Dim cnn As New DB2Connection(conDB2ConnectionString) Dim strSQL As String = "SELECT * FROM Department" Dim cmd As New DB2Command(strSQL, cnn) Dim dr As DB2DataReader cnn.Open() dr = cmd.ExecuteReader(CommandBehavior.CloseConnection) Do While dr.Read lstDept.Items.Add(String.Format("{0}: {1}, {2}", _ dr(“DEPTNO"), dr(“DEPTNAME"), dr(“MGRNO"))) Loop dr.Close()

41. Chiamare Stored Procedure • Attraverso l’oggetto Command • Gestione dei parametri Dim dr As DB2DataReader Dim cnn As New DB2Connection(conDB2ConnectionString) Dim cmd As New DB2Command(“SomeSP", cnn) cnn.Open() cmd.CommandType = CommandType.StoredProcedure Dim prm As DB2Parameter = _ cmd.Parameters.Add(“SomeParam", “Foo") dr = cmd.ExecuteReader() While dr.Read lstDemo.Items.Add(String.Format( _ "{0}: {1}", dr(0), dr(1))) End While

42. DB2DataAdapter • Gestisce la sincronizzazione attraverso i 4 command object: • SelectCommand • UpdateCommand • DeleteCommand • InsertCommand • Riempie un DataSet (metodo Fill) • Aggiornamento dei dati in DB2 (metodo Update)

43. Add-in per Visual Studio.NET • IBM Explorer • IBM Projects (DB2 Database Project) • DB2 SQL Editor • DB2 .Net Data Provider tools • Dynamic Help for DB2 Add-Ins • DB2 Tools menu Add-in

44. IBM Explorer: Data Connection • Simile al Server Explorer per visualizzare gli oggetti in DB2 • Browsing dei dati • Drag and drop degli oggetti e generazione del codice

45. SQL Editor • Estensione dell’editor di VS.Net per editare script DDL • Supporto per : • Sintassi colorata per DB2 SQL • Intellisense e code completion • Gestione dei code fragments per il codice DML utilizzato più di frequente

46. Tools Add-in • Toolbar per lanciare gli strumenti DB2 da Visual Studio.NET 2003 • Development Center • Control Center • Replication Center • Command Center • Task Center • Health Center • Journal

47. Approfondimenti • Download DB2 Personal Developer’s Edition (free) • Articoli e tutorial: • IBM DB2 Development Add-Ins for Visual Studio .Net • DB2 Database Project for Visual Studio .Net • Creating a .NET Client that Uses DB2 DADX Web Services • Creating and Linking LOBs in a Data Application for Microsoft .Net using DB2 Universal Database

48. Risorse • Siti web • IBM DB2 Developer Domain • DB2 for Windows home su developerWorks • DB2 .NET home page • Newsgroup • comp.databases.ibm-db2 • news://news.software.ibm.com/ibm.software.db2.udb.beta • news://news.software.ibm.com/ibm.software.db2.udb