PC40: Embedding SQL Server Compact In Desktop And Device ...

PC40: Embedding SQL Server Compact In Desktop And Device ...

Notes (hidden) PC40 Embedding SQL Server Compact In Desktop And Device Applications Steve Lasker Program Manager Microsoft Corporation blogs.msdn.com/SteveLasker 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 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 Caching Simplifies Data Access Offloads detailed questions to the client Enables local processing, closer to the user Products w/a Products > 5, Products in Category 2, Products Discontinued, Most sold products for last 3 months Top Products for My Customers Enables offline scenarios

Whats changed for my data Added, Updated, Deleted Enabling data movement from the smallest device, capturing data at the point of activity through the largest data center, to the offsite cloud Clients From embedded devices to desktops Servers From desktop to data center Synchronizing data from the point of activity to the data center Cloud To hosted services

One Size Fits All? Best practices, not all that different Architectural differences, but suited to the task Choose your weapon Choosing The Right Tool demo How Compact Changes The Game What good is a database if you cant deploy it?

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 X64 Folder Name based off: Environment.GetEnvironmentVariable("PROCESSOR_ARCHITEC TURE")

SQL Server Compact DLLs DLL Name Functionality sqlcese35.dll Native* Storage Engine sqlceqp35.dll Native* Query Processor sqlceme35.dll Native / Managed Translation Layer System.Data.SqlServerCe.dll

ADO.NET Managed Provider sqlcecompact35.dll Compression & Upgrade APIs sqlceca35.dll Merge Replication, RDA Client APIs Only needed for Merge & RDA, Not needed for Sync Services for ADO.NET sqlceoledb35.dll OleDB API Needed for C++, VB Classic, & Merge/RDA OleDB APIs Designed for Mobile Devices Not a full desktop OleDB Implementation sqlceer35EN.dll

Localized Error Strings Only needed when errors returned directly to the user Architectural Comparison One Size Fits All? Architectural Comparisons SQL Server (Express*) SQL Server Compact Shared Data Services Local Data Documents

Multiple Connections from multiple clients Multiple Connections from multiple processes & threads Runs as a service Runs In-Proc w/Application Connection Pooling through SqlClient Connections through SqlCeConnection Query Plans cached in SQL Server Engine

Query Plans cached in SqlCeCommand object Multi-proc access through shared memory Demo mand cmd = conn.CreateCommand(); eters.Add("@name", System.Data.SqlDbType.NVarChar, 50); er.Read()) { d.CommandText = "INSERT INTO Customer (CustomerId, Name) VALUES(@id, Performance Best Practices .Parameters["@customerId"].Value = id; Eeeking out that extra bit of performance

Best Practices Performance Parameterized Commands, with type and precision SqlCeCommand cmd = 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; SqlCeResultSet rst = cmd.ExecuteResultSet(ResultSetOptions.Updatable); SqlCeUpdatableRecord newRow; while (reader.Read()) { newRow = rst.CreateRecord(); newRow["customerId"] = customerId; newRow[name"] = name; rst.Insert(newRow); "Bulk Load" Performance Device 400 350 Desktop 12000 10000

300 250 150 6000 4000 100 50 Co Ca m ch Pa m an ed ra m

ds Co et m er m ize an d ds Co m m an Pa ds ra m sw

Sq /S lC ize eR es ul tS et 0 Ne w Inserts/Second 200

8000 2000 0 Deferred Index Creation 14000 12000 10000 8000 6000 4000 2000 0 Active Index Post Index

Using Trace Listener To Gather Information In The Field Monitoring Client Health Data Center ? Acme Widgets How to see whats happening at the client? Clients arent reachable Either arent Online when you need them Or theyre not addressable with a static IP What to do? Phone home? Web Service TraceListner

Acme Widgets Data Center try { try { productInfo barcode = productSvc.GetProductInf(barcode); scanner.Scan(); } catch (NetworkException } catch (Exception ex) {ex) { Trace.Listeners.Add(new Trace.WriteLine(UnableToConnect + ex.Message, Product Trace.WriteLine("Scan Failed - " -+" ex.Message, "Barcode

WebServiceTraceListner("http://MyTraceService.svc")) Lookup") Scanner"); } } Web Service TraceListener SQL Server Compact TraceListner Acme Widgets Data Center try { productInfo = productSvc.GetProductInf(barcode); } catch (NetworkException ex) { Trace.Listeners.Add( Trace.WriteLine(UnableToConnect - " + ex.Message, Product new SqlCeTraceListner("Log.sdf",

Lookup") "http://LogSyncService.svc")); } Compact TraceListener Sync Services to move Log info when a connection is available demo Using Trace Listener to Gather Information in the Field Getting visibility to whats actually

going on SQL Server Compact TraceListener Data Center Acme Widgets 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 demo

Using a SQL Server Compact Database as a Document ClickOnce, w/.NET FX 3.5 enables file associations 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 Summary SQL Server Compact

~1mb embedded database, no brainer deployment Its free, fast, safe/encryptable and compact Its easy to program and manage Single file, code free, doc centric file format Its an alternative to JET, XML, Persisted DataSets, SQL Server Express Its 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 Related Content TL40 Microsoft Sync Framework Advances Lev Novik Day 1 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 TL08 Offline-Enabled Data Services and Desktop Applications Pablo Castro Day 3 PC45 WPF: Data-centric Applications Using the DataGrid and Ribbon Controls

- Samantha Durante, Mark Wilson-Thomas Day 4-Noon SQL Server Compact Resources SQL Server Compact www.Microsoft.com/SQL/Compact SQL Server Compact Q&A Blogs.msdn.com/SqlServerCompact Blogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi Books Microsoft SQL Server 2005 Compact - Prashant Dhingra HitchhikerGuides.net - Bill Vaughn

SQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization Rob Tiffany Tools Primeworks GUI Innovations Microsoft Synchronization Services for ADO.NET MSDN.Microsoft.com/Sync blogs.msdn.com/Synchronizer Evals & Recordings ll fi e s a e

l P r out you for n o ti a u l eva t: a n o i s s

this se This sess ion will be availa ble as a record ing at: www.microsoftpdc.com SQL Server Compact Resources SQL Server Compact www.Microsoft.com/SQL/Compact

SQL Server Compact Q&A Blogs.msdn.com/SqlServerCompact Blogs.msdn.com/SteveLaskerBlogs.msdn.com/Laxmi Books Microsoft SQL Server 2005 Compact - Prashant Dhingra HitchhikerGuides.net - Bill Vaughn SQL Server Compact - Jose M. Torres Windows Mobile Data Synchronization Rob Tiffany Tools Primeworks GUI Innovations Microsoft Synchronization Services for ADO.NET MSDN.Microsoft.com/Sync blogs.msdn.com/Synchronizer

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.

Recently Viewed Presentations

  • Carbohydrates - images.pcmac.org

    Carbohydrates - images.pcmac.org

    Organic Polymers . Organic=contains carbon. Polymer=Macromolecule. Polymers are made during polymerization. Polymers are made of many monomers. There are 4 organic polymers: Carbohydrates, Lipids, Nucleic Acids, and Proteins
  • Presentación de PowerPoint - ITU

    Presentación de PowerPoint - ITU

    World Ocean Council . International, Cross-Sectoral. Business. Leadership Alliance. Bringing ocean industries together, e.g. shipping, oil/gas, fisheries, aquaculture ...
  • Trades and Selection

    Trades and Selection

    22.1 Leslie's "Best" Restaurant Rule 22.2 Tradeoff Matrices 22.2.1 Weighted Scoring Technique 22.2.2 Trade Matrix Formula 22.3 Difficulties of Selection 22.3 Value of a Recounted Vote 22.4 Ranking by "Somehow" 22.4 Ranking through Pairing 22.5 Social Factors and Optimization 22...
  • Medi-Cal Childrens Health Advisory Panel May 22, 2015

    Medi-Cal Childrens Health Advisory Panel May 22, 2015

    The SMHS medical necessity determination is based on an assessment of the beneficiary by the MHP. If medical necessity criteria for Medi-Cal SMHS are not met, the MHP will refer the beneficiary back to the MCP for mental health services...
  • Assessment Report Department School of Education & Human Services

    Assessment Report Department School of Education & Human Services

    PLSO 1: Students graduating from the program shall be able to identify the foundations of the profession in history, science, and philosophy PLSO 6: Students graduating from the program shall be able to apply information on the basic facts, concepts,...
  • Of fice of Governmentwide Policy GSAs Per Diem

    Of fice of Governmentwide Policy GSAs Per Diem

    (b) Meals. Expenses for breakfast, lunch, dinner and related tips and taxes (specifically excluded are alcoholic beverage and entertainment expenses, and any expenses incurred for other persons). (c) Incidental expenses. Fees and tips given to porters, baggage carriers, hotel staff,...
  • Littletown FC Plan - s3-eu-west-1.amazonaws.com

    Littletown FC Plan - s3-eu-west-1.amazonaws.com

    Littletown FC Plan . Aim. Encourage an atmosphere & feeling of inspiration to evolve every time a player plays for the club. Players are encouraged to have Empathy, ownership and empowerment .
  • ATTITUDE OF PROSPECTIVE TEACHERS Dr. Aniruddha Chakraborty Associate

    ATTITUDE OF PROSPECTIVE TEACHERS Dr. Aniruddha Chakraborty Associate

    An attitude is "a relatively enduring organization of beliefs, feelings, and behavioural tendencies towards socially significant objects, groups, events or symbols" (Hogg and Vaughan, 2005) "Attitudes are the evaluative judgments that integrate and summarize . . . cognitive/affective reactions" (Crano....