730 likes | 885 Views
ADO.NET, i Data Provider e l'architettura connessa. Silvano Coriani Developer & Platform Evangelism MCT rainer MCSD eveloper MCAD eveloper MCSEI nternet MCDBA. Da dove iniziare. Agenda . .NET Data Access Overview. .NET Application. DataSet. System.Data.Oracle. System.Data.SqlClient.
E N D
ADO.NET, i Data Provider e l'architettura connessa Silvano Coriani Developer & Platform Evangelism MCTrainer MCSDeveloper MCADeveloper MCSEInternet MCDBA
.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
.NET Data Provider • System.Data
.NET Data Provider • connessaCommand
.NET Data Provider • DataAdapter • DataReader
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()
SqlClient .NET Data Provider Object Model SqlConnection SqlCommand System.Xml.XmlReader SqlDataReader SqlParameterCollection Object SqlParameter
SqlClient e il CLR Common Language Runtime SqlServer SqlClient TDS Parser
Prepare / Execute • PreparedSqlCommand
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
Stored Procedure • WITH RECOMPILE
Stored Procedure • SqlCommandCommandType • Definizione di tutti i parametri tipizzati utilizzati dalla stored procedure • MAI
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
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
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
Cursori • row-based • set
SqlConnection • IDbConnection • ConnectionStringSqlCommandSqlDataReader • Close()Dispose() • BeginTransaction • SqlTransaction
SqlConnection • AttachDBFilenameSqlClientPermission
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();
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()
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
SqlCommand e le Stored Procedure • Parameters
SqlCommand e le Stored Procedure • ExecuteReader()DataReader • DBNull.Value per impostare a null un parametro di input di una SP • CommandBehavior
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()
SqlDataReader • SqlCommand • Read()IEnumerable
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()
SqlDataReader • NextResult() • La struttura del result set è descritta attraverso una “schema table” • GetSchemaTable()
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();
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();
Operazioni di Data Definition Language • ExecuteNonQuery()
Transazioni in ADO.NET • Transazioni locali • All’interno del codice SQL delle Stored Procedure • BEGIN/COMMIT TRAN • SqlOleDbTransaction
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();
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
Error & Info handling • SqlInfoMessageEventHandlerInfoMessage
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; }
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"); } }
Considerazioni sulle performance • Dispose()DataReader
Considerazioni sulle performance • FOR XMLXmlReader
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