SQL CE 2.0 e SQl 2005 Mobile Roberto Brunetti [email protected] http://blogs.devleap.com/rob http://thinkmobile.it/blogs/rob Fabio Santini [email protected] www.fsantini.net http://blogs.msdn.com/fabiosantini .NET CF / Managed Stack VS .NET (VB.NET, C#) Native/Unmanaged Stack eVB 3.0 ADO CE v3.1 ADO.NET eVC 3.0 SQL Server CE Data Provider SQL Server Client Data Provider CLR / .NET CF Ethernet TDS Scenario Connesso OLEDB CE SQL CE Edition v2.0 Data Provider OLEDB QP/Cursor Engine/ES OLEDB Client Agent: Replication and RDA Storage Engine / Repl Tracking 802.11b, CDPD, GSM, CDMA, TDMA, etc. IIS HTTP Scenario Disconnessos CLIENT SERVER OLEDB / Replication API Server Agent: Replication and Remote Data Access SQL Server CE • Compatibilità con SQL Server • Stesso Resultset • Data Type Compatible • Footprint • ~700kb per CPU X86 • Disponibile • Windows CE 2.11 e successive • Usato nativamente o replicando da SQL Cos’é? • OLEDB Provider • Non proprio conforme alle specifiche OLEDB • • • • • E’ un database con un DLL wrapper In CE non esistono i servizi Supporta ADOCE Supporta OLEDB CE API Supporta ADO.NET del .NET CF SQL Server CE v2.0 é supportato • Pocket PC • 2000 – 2002 • 2003 • Windows Mobile 5.0 • Windows CE 4.2 • Non è supportato da SmartPhone 2003 • Sql 2005 Mobile Edition e VM 5.0 Database Feature 1.x • • • • • • • • • • Multi-column indexes Referential integrity Sub-selects (IN) Default Inner/outer join 32 index per table One file database Null support Unicode support Transaction • Data-types • Real, Numeric, NVarChar, NText, Image, Integer, DateTime, VarBinary, UniqueIdentifier, Money • • • • • • Having/group by Seek on index Set functions Identity attribute TCO support Local security - DB encryption (128bit) and database password 2.0 Features • Union • SELECT * FROM A UNION SELECT * FROM B • Intrinsic Function (CASE, @IDENTITY) • Query parametriche • INSERT INTO mytable (col1, col2) VALUES (?, ?); • Index Pull • Recupero indici durante scaricamento con RDA • Connectivity Setup Wizards • Setup più semplice • Optional Error Strings • DLL per messaggi errore • ISQLW CE piú visuale Amministrazione • Nativo • ISQLW • Commovente • Remote SQL CE da Desktop • http://www.guiinnovations.com/html/remotesqlce.html • 30-day trial • 29,99 $ con licenza e supporto Remote SQL CE • Generazione di SDF da DB Desktop Integrazione con .NET CF • System.Data.SqlServerCE • Espone tutte le funzionalitá SQL CE • • • • SqlCEDataReader SqlCEDataAdapter SqlCECommand SqlCEParameter • Utilizzo di Try/Catch • System.Data.SqlServerCe.SqlCeException • Utilizzabile stand-alone • Demo • Cerca in locale • Aggiorna locale SqlCeConnection • “Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Da taSource=\directory\file.sdf” • .Open • .Close • SQL CE 2.0 non supporta connessioni contemporanee !!! Creazione di .sdf da codice try { SqlCeEngine sqlEngine = new SqlCeEngine("Data Source=\Pippo.sdf”); sqlEngine.CreateDatabase(); } catch (SqlCeException ex) { ShowErrors(ex, "Impossibile creare il db locale"); } Tabelle di sistema • INFORMATION_SCHEMA.TABLES • SELECT COUNT(*) • FROM INFORMATION_SCHEMA.TABLES • WHERE TABLE_NAME='Clienti' Sincronizzazione • Due metodologie • RDA – Remote Data Access • SQL Server 6.5 Service Pack 5 + • Merge Replication con SQL 2000 • SQL Server puo’ fare da ponte verso altri database • Utilizzando i Linked Server • L’utilizzo di tabelle locali ha comunque senso per ridurre la banda Connectività verso LAN • Come sempre: Web • HTTP • Internet e Intranet • Sicurezza gestita da IIS • Authentication (anonymous, basic, NTLM) • Authorization • Encryption (SSL) • Accesso consentito tramite firewall • Ma • Compressione per banda ridotta • Message-based per meno roundtrip Configurazione IIS • SSCESA20.DLL e’ l’applicazione (estensione) ISAPI da utilizzare • Serve Virtual Directory con permesse Execute • SQL Server CE Connectivity Management • Configura Virtual Directory • Sicurezza IIS • Sicurezza ACL sulla cartella Remote Data Access • • • • • • • • Se il device è connesso “spesso” Dove la Merge Replication non è necessaria Dove la Merge Replication è troppo pesante Per avere più controllo da codice Elimina la necessità di un Desktop Accesso a SQL Server 7.0 e 2000 Download to device e disconnessione Tracking anche disconnesso Remote Data Access • Download dati clienti e piazzole • Disconnessione • Giro delle piazzole per controllo • Per “irregolarità” o modifiche • Tabella Piazzole • Tabella Segnalazioni • Connessione • Sincronizzazione dei Dati RDA: Metodi • Pull • Porta i dati sul device • Selezione via statement SQL • Viste / Stored Procedure / Query SQL • Creazione tabelle locali • Push • Invia le modifiche al server • Submit SQL • Invio diretto di SQL DML Inizializzazione • • • • • • Dim x = New SqlCeRemoteDataAccess x.InternetLogin = “” x.InternetPassword = “” x.InternetUrl = “x.com/sqlce/sscesa20.dll” x.LocalConnectionString x.InternetProxyServer Pull Object.Pull ( “tabella locale”, “statement sql”, “oledb connection usata lato server”, [RdaTrackOption], [“tabella segnalazione errori”] ) Demo Campeggi OLEDB Connection String • E’ la stringa usata da sscesa20.dll per accedere al database • Specificare il tipo di autenticazione verso SQL • Standard • Specificare Username e Password • Integrata • Viene usato l’account utilizzato da IIS per far girare sscesa20.dll • Se accesso anonimo consentito IUSR_nomemacchina • Altrimenti User e Password specificati sull’oggetto SqlCeRemoteDataAccess Tabelle Create • Le tabelle vengono create in locale • Demo • Vediamo la strutture • s_operation • Tiene l’operazione effettuata • Indice per successivo sort durante il Push • s_BinaryKey • Chiave primaria • Scaricata tramite sp_primary_keys_rowset • NON devono esistere • Gestione incrementale manuale Push Object.Push ( “tabella locale”, “oledb connection usata lato server”, [RdaBatchOption] ) Demo Campeggi BatchingOn SET IMPLICIT_TRANSACTIONS ON declare @P1 int set @P1=1 exec sp_prepexec @P1 output, N'@P1 int,@P2 int,@P3 bit,@P4 varchar(50)', N'INSERT INTO "SEGNALAZIONI"("IdNota", "IdPiazzola", "TipoNota", "Nota") VALUES (@P1, @P2, @P3, @P4)', 1, 1, 0, NULL select @P1 // Lancia tutti i comandi effettuati sulla tabella exec sp_execute 1, 3, 2, 0, 'tutto ok' // Unprepare dei comandi exec sp_unprepare 1 IF @@TRANCOUNT > 0 COMMIT TRAN BatchingOff // Dichiarazione dei comandi di INSERT/UPDATE/DELETE declare @P1 int set @P1=1 exec sp_prepexec @P1 output, N'@P1 int,@P2 int,@P3 datetime,@P4 datetime,@P5 int,@P6 int,@P7 int', N'UPDATE "PIAZZOLE" SET "IdPiazzola" = @P1, "IdCliente" = @P2, "DataArrivo" = @P3, "DataPartenza" = @P4, "Persone" = @P5, "Tende" = @P6 WHERE "IdPiazzola" = @P7', 1, 1, 'Jun 16 2003 12:00:00:000AM', 'Jan 1 1900 12:00:00:000AM', 4, 2, 1 select @P1 Lancia tutti i comandi effettuati sulla tabella exec sp_execute 1, 2, 2, 'May 10 2003 12:00:00:000AM', 'Jan 1 1900 12:00:00:000AM', 1, 1, 2 // Unprepare dei comandi exec sp_unprepare 1 SubmitSql Object.SubmitSQL ( “statement sql”, “oledb connection usata lato server” ) Demo Campeggi N.B. Non si usa se connessione diretta Utilissimo se connessione Http Gestione Eccezioni SQLCE • Classe SqlCeException • Espone collezione Errors • For Each err in SqlCeException.Errors • • • • • err.HResult.ToString(“X”) err.Message err.NativeError err.Source err espone anche NumericErrorParameters • Collezione di string Eccezioni in Push • Per ogni conflitto o problema • Viene utilizzata (se specificata) la tabella locale indicata come tabella di errore • A fronte di una eccezione si possono analizzare i record • Utilizzando ADO.NET direttamente sulla tabella Limiti di RDA • Pull crea le tabelle • Non è incrementale • Campi Identity • Master Detail • Logica Business su diversi record • Facciamo due chiacchere Performance La RAM scarseggia….e l’applicazione non galleggia Native VS Managed • In assoluto il codice via OLEDBCE è più veloce • Più codice da scrivere • Più difficile da manutenere SQLCE SQLMobile In un Post • SELECT IMEI, ProductCode, Quantity FROM (SELECT NULL AS IMEI, product AS ProductCode, (physicalqty - allocatedqty) AS Quantity FROM importstock WHERE (NOT mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) AND product IN (SELECT ProductCode FROM (SELECT importstock.product AS ProductCode FROM StockCountSchedule INNER JOIN StockCountProductCategories ON (StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOIN importstock ON (StockCountProductCategories.Product_Type = importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) AND (StockCountSchedule.ID = 121231) UNION SELECT ProductCode FROM StockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON (StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE (StockCountSchedule.IsRecount = 1) AND (StockCountSchedule.ID = 121231)) AS StockCountProducts) UNION SELECT IMEI.imei AS IMEI, NULL AS ProductCode, NULL AS Quantity FROM importstock INNER JOIN IMEI ON importstock.product = IMEI.product WHERE (mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) AND importstock.product IN (SELECT ProductCode FROM (SELECT StockCountSchedule.ID AS StockCountID, importstock. product AS ProductCode FROM StockCountSchedule INNER JOIN StockCountProductCategories ON (StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOIN importstock ON (StockCountProductCategories.Product_Type = importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) UNION SELECT StockCountSchedule.ID AS StockCountID, ProductCode FROM StockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON (StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE (StockCountSchedule.IsRecount = 1)) AS StockCountProducts)) AS StockCountItems • “Gira bene in SQL Server 2000, ma non sotto SQL CE…” General Performance Tips • Limitare record e colonne nelle SELECT • Fare poche Open/Close • Meglio lasciare aperta una connessione • Occhio al numero di oggetti in menoria • SqlCeDataReader.Close/Dispose • Classi riusabili di accesso ai dati • Scriversele • Data Access Application Block • OpenNETCF 1.3 • Meglio http://www.businessanyplace.net/?p=daabcf • Comprende anche Cache dei parametri SQLCE SQLMobile DAAB Architecture • Versione ridotta dell’analogo per Desktop Non usare SqlCeCommandBuilder SqlCeConnection connection = GetConnection(); DataSet ds = new DataSet(); string sql = “…"; SqlCeHelper.FillDataset(connection, sql, ds, new string[] { “Prodotti" } ); DataTable table = ds.Tables["Products"]; DataRow addedRow = table.Rows.Add(new object[] { DBNull.Value, “Nuovo", DBNull.Value, 10 } ); table.Rows[0]["ProductName"] = “Prod Modificato"; SqlCeCommand insertCmd = SqlCeHelper.CreateCommand(connection, "INSERT INTO Products (ProductName, UnitPrice) VALUES(?, ?)", "ProductName:String", "UnitPrice:Currency"); SqlCeCommand updateCmd = SqlCeHelper.CreateCommand(connection, "UPDATE Products SET ProductName = ? WHERE ProductID = ?", "ProductName:String", "ProductID:Int32" ); SqlCeCommand deleteCmd = SqlCeHelper.CreateCommand(connection, "DELETE Products WHERE ProductID = ?", "ProductID:Int32" ); SqlCeHelper.UpdateDataset(insertCmd, deleteCmd, updateCmd, ds, “Prodotti"); Performance Big-hitters • • • • • Base Table Cursors Parameterized Queries DataReader versus DataSet SqlCeResultSet versus DataSet Indici Base Table Cursor • Bypassano il Query Processor • Tutte le colonne per ogni riga recuperata • Massimo delle performance per leggere tutte le colonne SqlCeCommand cmd = new SqlCeCommand(); cmd.CommandText = "Orders"; cmd.CommandType = CommandType.TableDirect; SQLCE SQLMobile Seek/SetRange – Basic • • • Utilizzabile per aprire l’indice Massimo delle performance per Range Massimo delle performance per singolo record cmd.CommandType = CommandType.TableDirect; cmd.CommandText = "Orders"; //Assunzione: L’indice contiene una sola colonna [datetime] cmd.IndexName = "SomeIndex";// Lettura tradizionale Use Queries? SELECT "Customer ID" FROM Orders WHERE "Order ID" = 10123 VS Base Table Parameterized Queries • Compilate “once” e eseguite molte volte • Usare lo stesso object SqlCeCommand che cacha i “compiled plan” • Sql 2005 Mobile anche Named Parameter cmd.CommandText = “UPDATE myTable set col1=? where col2=?"; // Evitare SqlCeParameter(string, object) constructor cmd.Parameters.Add("p1", SqlDbType.Decimal); cmd.Parameters.Add("p2", SqlDbType.SmallInt); cmd.Prepare(); // Eseguire più volte … SQLCE SQLMobile Parameterized Queries Data Reader VS Data Set • Dove possibile usare il DataReader • Più veloce del DataSet, soprattutto con result sets grandi (5x in alcuni casi) • Accesso diretto a SQL Server CE • Nessun doppio buffering • Contro • Non bindable ai controlli • No scrollin, no update • DataReader non usabile per remotizzare • Web Service / MSMQ …etc SQLCE SQLMobile Performance: Simple • Usare Typed Access • GetString / GetInt… • Usare campi posizionali !!! • X = dr.GetOrdinal(“nome”) • Y = dr.GetOrdinal(“cognome”) • Ciclo • dr.GetString(x) • dr.GetString(y) • Fine Ciclo Data Reader – Optimal Usage • • • • Usare accesso Strongly-typed Usare parametri posizionali Usare CLR Type al posto di SqlType Per leggere un’intera riga usare GetValues() al posto di GetXXX molte volte object[] row = new object[rdr.FieldCount]; while (rdr.Read()) { // Meno function call // processare l’array Encryption…paura ? • • • • 160K rows UPDATE Table Set NonIndexCol = ‘Roberto’ SELECT DateDataType FROM Table Qtek 2020 XScale PXA250 (400mhz) Time in Seconds Encryption vs No Encryption 350 300 250 200 150 100 50 0 347 190 335 188 Encyrpted No Encryption SELECT UPDATE Performance Storage Media? Chi lo sa ?!?! • • RAM, SD, CF, IPSM (NOR), Toshiba NAND SD più veloci di MMC • • http://www.sdcard.com/b2b/TextPage.asp?Page=6CF http://www.dpreview.com/articles/mediacompare/default.asp?sort=fwread Various storage medias 400 Time In Seconds • 314 348 300 209 200 142 155 RAM SD IPSM 144 100 0 SELECT UPDATE Altre cosette • SET RUN_MUCH_FASTER = “true” • Database Compact • Corregge eventuali “corruzioni” • Tabelle contigue • Se indice PK/Unique ordinate per indice • Update delle statistiche • QP Statistic • Servono per la valutazione dei “costi” • Modificare la location del tempdb per transazioni “grandi” • Il QP di SQL CE QP non fa distinzioni fra RAM e Flash SQLCE Sql 2005 Mobile SQL 2005 Mobile VS .NET (VB.NET, C#) XML/BizTalk/CS 2002/DTS Managed SQL CE + eDB Apps System.Data.SqlClient (160KB) System.Data.SqlServerCe (140KB) System.Data.Common (124KB) TDS OLE DB OLEDB / Replication sqlceoledb30.dll (200KB) Internal QP API QP sqlceqp30.dll (800KB) Replication and RDA HTTP eDB API Internal SE API sqlceca30.dll (360KB) Storage Engine eDB – sqlcese30.dll (300KB) CLIENT IIS Server Agent: Replication and Remote Data Access sqlcesa30.dll (150KB) sqlcerp30.dll (100KB) SERVER SQL Mobile Platforms • • • • • PocketPC 2003, Phone Edition Smart Phone 5.0 Windows CE.NET 5.0 TabletPC Visual Studio 2005 (Development) SQL Server CE • Necessità • Integrazione con SQL Server 2005 • Amministrazione • DTS • Integrazione con Visual Studio 2005 e .NET CF v2.0 • Desktop Development e Deployment • Nuove funzionalità • • • • Performance (anche se già buone) Più connessioni contemporanee SmartPhone e TabletPC Upgrade Tool per SDF 2.0 Storage Engine • Riscritto totalmente • Multi-User support • Row level locking data • Page level locking index • Lock Escalation • Stabilità • ACID Transaction • Auto re-use di pagine vuote • Non necessario Compact Query Processor • Cost-Based Optimization • QP usa piani Cost-based • QP usa le statistiche dello storage engine • Execution Plan e Query Hints • Come in SQL 2000: Analisi e correzione query • SqlCeResultSet • Accesso via Scrolling • Accesso Random • Update • Parametri Named SQL Server 2005 Management Studio • Connessione a SQL Server o SQL Mobile • Funzionalità identiche • Object Explorer • Query Editor • Management Dialog • Connessione a DB • • • • Sul desktop o direttamente sul Device Query Plan e Hint Subscription Wizard Supporto SSIS (ex DTS) • Creare SQL Mobile DB Demo SQL 2005 • Registered Server di tipo Sql Mobile • Su DeskTop (SqlMobile\TestDB\Test.sdf) • Su Device (Browse...) • Connect -> Object Browser • New Table / Column / Index • Views – Programmability (Type) • Replication Subscription per Merge Repl • Connect -> New Query • SqlMobile\TestDB\QueryPlan.sqlce VS 2005 Create SDF VS 2005 Create Table Connessione a SDF su Device Demo VS 2005 • Server Explorer • • • • Connect to Database Connessione a SDF su Device/Emulatore/Desktop Gestione Schema (Create Table/Modify) Visualizzazione (New Query) / Edit dei dati • SDF nel progetto (anche Add New Item) • Tipologia Content • Creazione Typed DS automatico (Vedere) • Diventa un Project Data Source • Drag and Drop • • • • • Controllo Binding Source Drag Tabelle o Colonne sul form per creare controlli Bound SmartTasks per Master / Details Typed ResultSet class baked into generated code DataSource Window per legare un SDF al progetto List View Summary View BindingSource Typed DataSet SqlMobile Database Web Service Remoto Edit View Binding The Data To The UI Occhio !!!! • Con Drag & Drop diretto • Creiamo un DataSet • Che fa double buffering • Montiamo l’insfrattura di binding • BindingContext[source].Position ++ • Non possiamo fare operazioni in Background • Sono 5 righe di codice per fare molto molto meglio a mano • Se siamo programmatori SCRIVIAMOLE SqlCeResultSet • • • • • • Connessione diretta ai dati Scrolling Update DataBinding Simile al DataSet come funzionalità Veloce quasi come il DataReader • Accesso diretto • Nessun double-buffer SqlCeResultSet • Deriva daSqlCeDataReader • Strongly Typed • GetInt32(), GetString() • SetInt32(), SetString() • Espone Read per forward • Codice identico al Reader • Creabile da cmd.ExecuteResultSet() SqlCeResultSet private SqlCeResultSet resultSet = null; private ResultSetView view1 = null; /// private void _bindData() { ... command.CommandText = “SELECT * FROM Orders”; ResultSetOptions options = ResultSetOptions.Scrollable | ResultSetOptions.Updatable; this.resultSet = command.ExecuteResultSet(options); this.view1 = this.resultSet.ResultSetView; int[] ordinals = new int[] { 1,3,5,8 }; this.view1.Ordinals = ordinals; this.dataGrid.DataSource = view1; ResultSetOptions • • • • • Combinabili con | None Scrollable Updatable Insensitive • Non intercetta le modifiche al DataSource • Sensitive • Intercetta le modifiche al DataSource • Usare sempre il più leggero possibile SqlCeResultSet.Update() SqlCeResultSet rs = commmand.ExecuteResultSet(ResultSetOptions.Updatable); int idxProductId = rs.GetOrdinal("ProductId"); int idxDesc = rs.GetOrdinal("Desc"); int idxUnitPrice = rs.GetOrdinal("UnitPrice"); while (rs.Read()) { // Retrieve current values string productId = rs.GetString(idxProductId); string desc = rs.GetString(idxDesc); double unitPrice = rs.GetDouble(idxUnitPrice); // Prepend Product ID to the description rs.SetString(idxDesc, productId + " - " + desc); // Increase the price by 10% rs.SetDouble(idxUnitPrice, unitPrice * 1.1); // Apply record changes to db rs.Update(); } SqlCeResultSet • ResultSetOptions.Scrollable • • • • ReadFirst, ReadLast Read, ReadPrevious ReadAbsolute ReadRelative SqlCeResultSet SqlCeResultSet rs = commmand.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable ); // Read entire result backwards rs.ReadLast(); while(rs.ReadPrevious(); { // ... } // Absolute positioning rs.ReadAbsolute(10); // Read 10 records from beginning rs.ReadAbsolute(-10); // Read 10 records from end // Relative positioning rs.ReadRelative(5); // Read forward 5 records rs.ReadRelative(-5); // Read backward 5 records SqlCeResultSet Binding • Qualunque ResultSetOptions • Ma includere ResultSetOptions.Scrollable • Bind verso ResultSetView • Esposta come proprietà • Stesse interfacce del DataView • ITypedList, IBindingList, IList, ICollection, IEnumerable, IDisposable • Utilizzabile con qualunque controllo “bindable” listBox1.DataSource = rs.ResultSetView; listBox1.DisplayMember = "Desc"; listBox1.ValueMember = "ProductId"; textBox1.DataBindings.Add("Text", rs, "UnitPrice"); SQL 2005 Replica • Molto automatizzato • Demo © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Content created by 3 Leaf Solutions