Notes (hidden)

1. Notes (hidden)

2. PC40 Embedding SQL Server Compact In Desktop And Device Applications  Steve Lasker Program Manager Microsoft Corporation blogs.msdn.com/SteveLasker

3. What we'll cover today… • Why data on the client is important • How SQL Server Compact “changes the game” • Performance Best Practices • Differences between Compact & Express • Getting the most by understand the architecture • Interesting ways to leverage local data • Custom Document Format • Run directly from a DVD • Visibility into Client Application Health

4. Types Of Local Data • Read Only, Reference Data • Is it 100% static for the life of a version? • Are there changes? (list of States, Product Catalog?) • Is it 100% the same for all users? • User Data • Each user has “their data” and they make changes • Orders, My Customers, Settings • Aggregation • Ability to slice and dice on data across various sources

5. Caching Simplifies Data Access • Offloads detailed questions to the client • Enables local processing, closer to the user Enables offline scenarios Products w/’a’ Products > 5, Products in Category 2, Products Discontinued, Most sold products for last 3 months Top Products for My Customers What’s changed for my data Added, Updated, Deleted

6. Enabling data movement from the smallest device, capturing data at the point of activity through the largest data center, to the offsite cloud Cloud From embedded devices to desktops From desktop to data center To hosted services Clients Servers Synchronizing data from the point of activity to the data center

7. One Size Fits All? • Best practices, not all that different • Architectural differences, but suited to the task • Choose your weapon

8. Choosing The Right Tool

9. demo How Compact “Changes The Game”  What good is a database if you can’t deploy it?

10. Deploying Target = Any • Change Managed Provider reference to Copy Local = true • Include both 32 and 64 bit Compact engine dlls • Place 32bit version under an X86 • Place 64bit version under AMD64 • AMD64 includes Intel X64Folder Name based off: Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE")

11. SQL Server Compact DLLs

12. Architectural Comparison One Size Fits All?

13. Architectural Comparisons

14. Demo Performance Best Practices SqlCeCommandcmd = conn.CreateCommand();cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 50);while (reader.Read()) {cmd.CommandText = "INSERT INTO Customer (CustomerId, Name) VALUES(@id, @name";cmd.Parameters["@customerId"].Value = id; Eeeking out that extra bit of performance

15. Best Practices – Performance • Parameterized Commands, with type and precision SqlCeCommandcmd = conn.CreateCommand();cmd.Parameters.Add("@customerId", System.Data.SqlDbType.Int);cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 50);cmd.CommandText = "INSERT INTO Customer (CustomerId, Name) VALUES(@id, @name");while (reader.Read()) {cmd.Parameters["@customerId"].Value = id; cmd.Parameters["@name"].Value = name; • Bulk Insert w/SqlCeResultSet cmd.CommandText = "Customer"; cmd.CommandType = System.Data.CommandType.TableDirect; SqlCeResultSetrst = cmd.ExecuteResultSet(ResultSetOptions.Updatable); SqlCeUpdatableRecordnewRow; while (reader.Read()) {newRow = rst.CreateRecord(); newRow["customerId"] = customerId; newRow[“name"] = name; rst.Insert(newRow);

16. "Bulk Load" Performance

17. Deferred Index Creation

18. Using Trace Listener To Gather Information In The Field

19. Monitoring Client Health Acme Widgets ? Data Center • How to see what’s happening at the client? • Clients aren’t reachable • Either aren’t Online when you need them • Or they’re not addressable with a static IP • What to do? • Phone home?

20. Web Service TraceListner Acme Widgets Data Center try { productInfo = productSvc.GetProductInf(barcode); } catch (NetworkException ex) { Trace.WriteLine(“UnableToConnect - " + ex.Message, “Product Lookup") } try { barcode = scanner.Scan(); } catch (Exception ex) { Trace.WriteLine("Scan Failed - " + ex.Message, "Barcode Scanner"); } Trace.Listeners.Add(newWebServiceTraceListner("http://MyTraceService.svc")) Web Service TraceListener

21. SQL Server Compact TraceListner Acme Widgets Data Center try { productInfo = productSvc.GetProductInf(barcode); } catch (NetworkException ex) { Trace.WriteLine(“UnableToConnect - " + ex.Message, “Product Lookup") } Trace.Listeners.Add(newSqlCeTraceListner("Log.sdf", "http://LogSyncService.svc")); Compact TraceListener Sync Services to move Log info when a connection is available

22. demo Using Trace Listener to Gather Information in the Field Getting visibility to what’s actually going on

23. SQL Server Compact TraceListener Acme Widgets Data Center

24. Custom Doc Formats • Structured document • Single file, code free, email attachment safe • Post “Documents” to SharePoint • Create custom extensions, map to your app • Password protect, requiring users to open documents w/your app • File Associations Supported by Visual Studio 2008 & ClickOnce

25. demo Using a SQL Server Compact Database as a Document ClickOnce, w/.NET FX 3.5 enables file associations

26. Recap • Why data on the client is important • Power of the client, freedom to add value • How SQL Server Compact “changes the game” • No need to worry about deployment • Performance Best Practices • Take advantage of the in-proc nature • Interesting ways to leverage local data • Custom Document Format • Run directly from a DVD • Visibility into Client Application Health

27. Summary • SQL Server Compact • ~1mb embedded database, no brainer deployment • It’s free, fast, safe/encryptable and compact • It’s easy to program and manage • Single file, code free, doc centric file format • It’s an alternative to JET, XML, Persisted DataSets, SQL Server Express • It’s integrated into: • Visual Studio 2005 SP1 • Visual Studio 2008 (Orcas) • Adds Sync Services for ADO.NET • SQL Server Management Studio 2008 (Katmai) • 3.1 In ROM for Windows Mobile 6.x

28. Related Content TL40 Microsoft Sync Framework Advances – Lev Novik Day 1 PC45 WPF: Data-centric Applications Using the DataGrid and Ribbon Controls - Samantha Durante, Mark Wilson-Thomas Day 4-Noon TL08 Offline-Enabled Data Services and Desktop Applications – Pablo Castro Day 3 BB15 SQL Server: Database to Data Platform - Road from Server to Devices to the Cloud – David Campbell Day 1 BB40 Sync Framework: Enterprise Data in the Cloud and on Devices – Liam Cavanaugh Day 2

29. SQL Server Compact Resources • SQL Server Compactwww.Microsoft.com/SQL/Compact • SQL Server Compact Q&ABlogs.msdn.com/SqlServerCompactBlogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi • BooksMicrosoft SQL Server 2005 Compact - PrashantDhingraHitchhikerGuides.net - Bill VaughnSQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization – Rob Tiffany • ToolsPrimeworksGUI Innovations • Microsoft Synchronization Services for ADO.NETMSDN.Microsoft.com/Syncblogs.msdn.com/Synchronizer

30. Evals & Recordings Please fill out your evaluation for this session at: This session will be available as a recording at: www.microsoftpdc.com

31. SQL Server Compact Resources • SQL Server Compactwww.Microsoft.com/SQL/Compact • SQL Server Compact Q&ABlogs.msdn.com/SqlServerCompactBlogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi • BooksMicrosoft SQL Server 2005 Compact - PrashantDhingraHitchhikerGuides.net - Bill VaughnSQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization – Rob Tiffany • ToolsPrimeworksGUI Innovations • Microsoft Synchronization Services for ADO.NETMSDN.Microsoft.com/Syncblogs.msdn.com/Synchronizer

32. © 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.