Mobile Data with the Compact Framework

1. Mobile Data with the Compact Framework Shawn Wildermuth Senior Consultant/Architect Magenic Technologies

2. Who I Am • Shawn Wildermuth (swildermuth@adoguy.com) • C# MVP • INETA Speaker • Book Author • “Pragmatic ADO.NET” • Editor of http://ONDotnet.com • This Presentation can be found at: • http://adoguy.com/presentations

3. Agenda • How Mobile Data is Different • Deciding on a Solution • Limitations of the Compact Framework • Using SqlClient • Using SQL Server CE • Using Web Services • Data Binding on the Compact Framework • SQL Server CE 2005 (Yukon)

4. How Mobile Data is Different • The form factor is small • Not much screen real estate • Not much memory room • Needs to work disconnected • Very few Mobile devices are always connected • Smaller bandwidth usually when connected

5. Deciding on a Solution • Is a Smart Client the right solution? • How much data manipulation is needed? • Is offline support needed? • Does state need to be shared with servers • If Smart Client, which Data Access? • Options include: • SQL Server Data Access • SQL Server CE Data Access • Web Services

6. Limitations of the CF • System.Data • DataSets work fine • Typed DataSets don’t • No Design-time Support • System.Xml • No XML Serialization Support • OpenNETCF’s Xml Serialization isn’t Complete • System.Runtime.Serialization • No SOAP or Binary Formatters

7. Limitations of the CF (2) • SqlClient • Only on CE and Pocket PC (no SmartPhone) • Requires Connectivity • SQL CE • Only on CE and Pocket PC (no SmartPhone) • Web Services • Support on all platforms • Somewhat slower (not compressed like above)

8. Limitations of the CF (3) • DataSet is limited • Merge() and GetChanges() are missing • Deal with changes row-by-row

9. SQL Client Data Access • Remarkable similar to SqlClient in Full Fx • Full Set of Adapter Functionality Supported • Connection Strings are to real SQL Server // Create the connection and command SqlConnection conn = new SqlConnection("..."); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM Customers"; // Create a DataSet to put our results in DataSet ds = new DataSet(); // Create the adapter SqlDataAdapter da = new SqlDataAdapter(cmd); // Fill it da.Fill(ds, "Customers");

10. SQL Client Data Access (2) • Readers are also supported • Same caveats apply to Mobile Apps • Connected apps are hard on low bandwidth // Create the connection and command SqlConnection conn = new SqlConnection("..."); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM Customers"; // Use a Reader try { conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { string name = rdr["CompanyName"].ToString(); } } finally { conn.Close(); }

11. SQL Server CE .NET CF / ManagedStack VS .NET (VB.NET, C#) ADO.NET SQL Server CE Data Provider CLR / .NET CF OLEDB CE 802.11b, CDPD, GSM, CDMA, TDMA, etc. Data Provider SQL CE Edition v2.0 Client Agent: Replication and RDA IIS Server Agent: Replication and Remote Data Access OLEDB QP/Cursor Engine/ES OLEDB HTTP Storage Engine / Repl Tracking Occasionally Connected CLIENT SERVER

12. SQL Server CE (2) • Two Methodologies • Remote Data Access • Local Database • Push and Pull Access from SQL Server • Optional Change Tracking • Merge Replication • Use Replication Engine to Sync Changes • Use Subscription model instead of RDA Model • Allows vertical and horizontal partitioning

13. Setting up SQL Server CE - RDA • Integrates with IIS • ISAPI Application to access remote data • E.g. http://<machinename>/sqlce/sscesa20.dll • Compresses results across the wire • SqlCeRemoteDataAccess • Takes an URL to the ISAPI App; • and Connection String to database on mobile device SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://shawnw-lptd/sqlce/sscesa20.dll", "Data Source=northwind.sdf");

14. SQL Server CE - RDA • Pulling Data to Mobile Device • Creation of the local DB with ad-hoc queries • Uses an OLEDB Connection String to connect • Pull to bring data from remote db to mobile db • Can include tracking information for updating string rdaOleDbConnectString = "Provider=sqloledb;Data Source=shawnw-lptd;" + "Initial Catalog=Northwind;User Id=Sample;Password=ADONET"; rda.Pull("Customers", "SELECT * FROM Customers", rdaOleDbConnectString, RdaTrackOption.TrackingOnWithIndexes); rda.Pull("Products", "SELECT * FROM Products", rdaOleDbConnectString, RdaTrackOption.TrackingOnWithIndexes); rda.Pull("Orders", "SELECT * FROM Orders", rdaOleDbConnectString, RdaTrackOption.TrackingOnWithIndexes); rda.Pull("OrderDetails", "SELECT * FROM OrderDetails", rdaOleDbConnectString, RdaTrackOption.TrackingOnWithIndexes);

15. SQL Server CE – RDA (2) • Uses local database storage • Use of SqlCe* classes to access data normally • Supports full suite of classes // Create the connection and command SqlCeConnection conn = new SqlCeConnection("..."); SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM Customers"; // Create a DataSet to put our results in DataSet ds = new DataSet(); // Create the adapter SqlCeDataAdapter da = new SqlCeDataAdapter(cmd); // Fill it da.Fill(ds, "Customers");

16. SQL Server CE – RDA (3) • Pushing data back • Pushes changed rows back to remote DB • Only if tracking is enabled • Batching does all rows in single Tx rda.Push("Customers", rdaOleDbConnectString, RdaBatchOption.BatchingOn); rda.Push("Products", rdaOleDbConnectString, RdaBatchOption.BatchingOn); rda.Push("Orders", rdaOleDbConnectString, RdaBatchOption.BatchingOn); rda.Push("OrderDetails", rdaOleDbConnectString, RdaBatchOption.BatchingOn);

17. SQL Server CE – Merge Replication • Retrieves Data with Replication • Fills the local DB from replication subscription • Like RDA, uses ISAPI Application • And local database connection for local cache SqlCeReplication repl = new SqlCeReplication(); repl.InternetUrl = "http://shawnw-lptd/sqlce/sscesa20.dll"; repl.Publisher = "SHAWNW-LPTD"; repl.PublisherDatabase = "Northwind"; repl.PublisherLogin = "sample"; repl.PublisherPassword = "ADONET"; repl.Publication = "Northwind"; repl.Subscriber = "OrderTaker"; repl.SubscriberConnectionString = "Data Source=northwind.sdf"; // Create the Local SSCE Database subscription repl.AddSubscription(AddOption.CreateDatabase); // Synchronize to the SQL Server 2000 to populate the Subscription repl.Synchronize();

18. SQL Server CE – Merge Replication (2) • Local access is identical to RDA • Uses same local database • And same data access classes // Create the connection and command SqlCeConnection conn = new SqlCeConnection("..."); SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM Customers"; // Create a DataSet to put our results in DataSet ds = new DataSet(); // Create the adapter SqlCeDataAdapter da = new SqlCeDataAdapter(cmd); // Fill it da.Fill(ds, "Customers");

19. SQL Server CE – Merge Replication (3) • Merges back to remote db with Replication • Uses same replication object as filled local db • Synchronize uses Merge Replication SqlCeReplication repl = new SqlCeReplication(); // ... // Synchronize to the SQL Server 2000 // to populate the Subscription repl.Synchronize();

20. SQL Server CE - Caveats • Several SQL Constructs don’t work • Batch queries • Object names with spaces (cannot use delimiters) • SELECT * FROM [Order Details] • Queries are run with unchangable defaults • QUOTED_IDENTIFIER_ON, ANSI_NULLS_ON, ANSI_PADDING_ON, ANSI_NULL_DFLT_ON_ON, CONCAT_NULL_YIELDS_NULL_ON

21. Web Services • SOAP Based Data Access • Only real data access on SmartPhones • Doesn’t require local connectivity • Use WriteSchema to pass whole structure of DS • Can use DataSets for updates [WebMethod] public XmlDocument Products() { // ... // Using WriteSchema to make sure the entire // structure is included on client side MemoryStream strm = new MemoryStream(); ds.WriteXml(strm, XmlWriteMode.WriteSchema); strm.Position = 0; XmlDocument doc = new XmlDocument(); doc.Load(strm); return doc; }

22. Web Services (2) • The Client consumes it normally • Can use DataSets on the client for cache • Use ReadSchema to fill in DS Structure // Create the service GetPhoneDataService theSvc = new GetPhoneDataService(); // Load the data through the Web Service XmlDocument doc = theSvc.Products(); // NOTE: Can't use a Typed DataSet Here // Make it into a DataSet DataSet ds = new DataSet(); XmlNodeReader rdr = new XmlNodeReader(node); ds.ReadXml(rdr, XmlReadMode.ReadSchema); // Must use AcceptChanges // (ReadXml makes rows new) ds.AcceptChanges();

23. Web Services (3) • Can store DataSet locally for cache • This allows for the device to be disconnected • Saving the data locally includes the changes • Use DiffGrams to preserve the changes // Write the data locally ds.WriteXml("local.xml", XmlWriteMode.DiffGram); // Read it locally ds.ReadXml("local.xml", XmlReadMode.DiffGram);

24. Web Services (4) • Updating happens the same way • Limitations of CF DataSets makes it chatty • Prefer to use DiffGrams • though whole DS are still sent XmlDocument doc = new XmlDocument(); MemoryStream strm = new MemoryStream(); XmlTextWriter writer = new XmlTextWriter(strm, System.Text.Encoding.UTF8); ds.WriteXml(writer, XmlWriteMode.DiffGram); strm.Position = 0; doc.Load(strm); svc.SetDataSet(doc); [WebMethod] public XmlDocument SaveChanges(XmlDocument doc) { MyTypedDs ds = new MyTypedDs(); ds.ReadXml(doc, XmlReadMode.DiffGram); DataSet updated = UpdateDataSet(ds); return new XmlDataDocument(updated); }

25. DataBinding • Performance Issues • DataBinding on devices is very slow • Manually filling-in controls much faster • E.g. 2 seconds vs. 450ms // This is faster foreach (DataRow cust in ds.Tables["Customers"].Rows) { listBox1.Items.Add(cust["CompanyName"]); } // This is slower, but more functional listBox1.DataSource = ds.Tables["Customers"]; listBox1.DisplayMember = "CompanyName"; listBox1.ValueMember = "CustomerID";

26. DataBinding (2) • DataBinding is worth it sometimes • If binding multiple items to a single source • If you need a CurrencyManager • If you need Master/Detail Binding CurrencyManager mgr = (CurrencyManager)listBox1.BindingContext[ds.Tables["Customers"]]; mgr.Position++; DataTable tbl = ds.Tables["Customers"]; listBox.DataSource = tbl; comboBox.DataSource = tbl; textBox.Bindings.Add("Text", tbl, "CompanyName");

27. DataBinding (3) • Use of background threads can help // Launch the process on a background thread ThreadPool.QueueUserWorkItem(new WaitCallback(BindDataToForm)); void BindDataToForm(object o) { // Do Data Binding }

28. SQL Server Mobile Edition • What’s New (Replacement for SQL CE) • SQL Server 2005 Integration • API Access to Merge Replication Engine • Download only Tables (read-only) • Partitioned Articles • Synchronization • Sync Progress API’s • Column level Synchronization • Variable Compression Levels • Multiple Subscriptions per DB Table

29. SQL Server Mobile Edition (2) • What’s New (continued) • Client Storage • Improved ACID Support • Improved Query Processor • Client Performance • Multi-User Support (allow sync while in use) • SqlCeResultSet (Updatable Cursor) • Client Support • Additional Device Support • Including SmartPhone

30. Questions?