1 / 35

DAT413: SQL Server ™ CE: Building Mobile Applications with SQL Server CE and Smart Device Extensions

DAT413: SQL Server ™ CE: Building Mobile Applications with SQL Server CE and Smart Device Extensions. Kevin Collins Senior Program Manager Microsoft Corporation. Agenda. SQL Server CE Overview Deployment Platforms Smart Device Extensions (SDE) Overview

natan
Télécharger la présentation

DAT413: SQL Server ™ CE: Building Mobile Applications with SQL Server CE and Smart Device Extensions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DAT413: SQL Server™ CE: Building Mobile Applications with SQL Server CE and Smart Device Extensions Kevin CollinsSenior Program ManagerMicrosoft Corporation

  2. Agenda • SQL Server CE Overview • Deployment Platforms • Smart Device Extensions (SDE) Overview • SQL Server CE v2.0 Architecture and Features • Deployment and scaling case study

  3. SQL Server CE Overview • Key Scenarios • Disconnected database • Internet / Intranet connectivity / synchronization • Different from Mobile Web Forms / MMIT, ADO.NET Data Set and MSMQ CE • Managed and Unmanaged Support • Customer Scenarios • Medical, Military, DSD, FSA, SFA, Real estate

  4. Nevalon – Real Estate MLS

  5. Windows CE Platforms • H/PC 2K – (Jornada 720, Intermec 6651) • No .NET CF support, Native support • Typically have keyboard • Many have 32mb ROM/RAM • PC Card/USB • PocketPC 2000, 2002, Phone Edition – (Intermec 700, Compaq iPaq) • No keyboard • .NET CF Support and Native support • Many have 64MB RAM/ROM, USB and CF support • Embedded – (Intermec 5020) • Varying CPU/RAM/ROM • PB 3.0 Native support • PB 4.0 .NET CF support

  6. VS.NET/SDE Development Sequence Deployment Options Source Code (Hello.*) VS.NET Windows CE Emulator Win32 Device Compliers Hello.exe Hello.exe Hello.exe .NET Compact Framework Class Libs .NET Compact Framework Class Libs .NET Compact Framework Class Libs .NET Compact Framework Class Libs .NET Compact Framework CLR .NET Compact Framework CLR .NET Compact Framework CLR

  7. .NET CF Class Libraries System.Web System.WinForms Services UI Design ComponentModel Description HtmlControls Discovery WebControls System.Drawing Protocols Caching Security Drawing2D Printing Text Configuration SessionState Imaging System.Data System.Xml ADO.NET SqlClient XmlDocument Serialization Reader/Writers Design SqlServerCe Xslt/XPath System Collections IO Configuration Runtime InteropServices Security Net ServiceProcess Remoting Text Reflection Diagnostics Serialization Globalization Resources Threading

  8. Tech-Ed 2002 KeyNote

  9. SQL Server CE v2.0 Device Data Access SDE / ManagedStack Native/UnmanagedStack VS .NET (VB.NET, C#) ADO.NET eVB 3.0 eVC 3.0 SQL Server CE 2.0 Data Provider ADO CE v3.1 CLR / .NET CF OLEDB CE SQL Server CE v2.0 OLEDB QP/Cursor Engine/ES OLEDB Storage Engine / Repl Tracking

  10. SQL Server CE v2.0 Platform Support

  11. SQL Server 2000Windows CE Edition v2.0 • Upward compatibility with SQL Server • Same result sets • Compatible data types • Right footprint size for devices • ~800kb for X86 CPU

  12. Multi-column indexes Referential integrity Sub-selects (IN) Defaults Inner/outer joins One file database Null support Unicode support Transactions Data-types Real, Numeric, NVarChar, NText, Image, Integer, DateTime, VarBinary, UniqueIdentifier, Money Having/group by Seek on index Set functions Identity attribute Local security - DB encryption (128bit) and database password Database Feature SetRich functionality for small footprint

  13. New V2.0 Features • Intrinsic functions • TAN • UNICODE • CHAR • CHARINDEX • LEN • LOWER • LTRIM • PATINDEX • REPLACE • REPLICATE • @IDENTITY • RTRIM • SPACE • STR • STUFF • SUBSTRING • UPPER • CASE • COALESCE • CONVERT • DATALENGTH • ABS • ACOS • ASIN • ATAN • ATN2 • CEILING • COS • COT • DEGREES • EXP • FLOOR • LOG • LOG10 • PI • POWER • RADIANS • RAND • ROUND • SIGN • SQRT • NEWID

  14. New V2.0 Features • Union • SELECT * FROM A UNION SELECT * FROM B • Parameterized Queries • INSERT INTO mytable (col1, col2) VALUES (?, ?); • Index Pull • Brings indexes down with RDA Pull • Connectivity Setup Wizards • Ease installation of SQL CE Server components • Optional Error Strings • Extra DLL to provide error strings in application • Increased index limit per table

  15. New V2.0 Features • SQL Server Client Data Provider • Direct Access to SQL Server • Enhanced ISQLW

  16. ISQLW CE, new SQL

  17. SQL Server CE V2.0 .NET Integration – ADO.NET • System.Data.SqlClient • Direct connection to SQL Server • Rich interaction with the server • Full TSQL • Stored Procedures • System.Data.SqlServerCE • Local Data Provider • Rich data access while offline • Exposes full functionality of SQL Server CE

  18. SQL Server CE Error Handling • Utilizes Try/Catch • System.Data.SqlServerCe.SqlCeException

  19. CE Data AccessStorage Architecture Enterprise Backend Oracle / db2 / etc .NET CF / ManagedStack XML/BizTalk/CS 2002 VS .NET (VB.NET, C#) Native/UnmanagedStack ADO.NET Ethernet eVB 3.0 eVC 3.0 SQL Server CE Data Provider SQL Server Client Data Provider TDS ADO CE v3.1 Well Connected CLR / .NET CF OLEDB CE OLEDB / Replication API 802.11b, CDPD, GSM, CDMA, TDMA, etc. SQL CE Edition v2.0 Data Provider Client Agent: Replication and RDA IIS Server Agent: Replication and Remote Data Access OLEDB QP/Cursor Engine/ES HTTP OLEDB Storage Engine / Repl Tracking Occasionally Connected CLIENT SERVER

  20. Connectivity – Transport • Utilize Web technology • HTTP communication • Internet and Intranet • Security via IIS • Authentication (anonymous, basic, NTLM) • Authorization • Encryption for secure transfer (SSL) • Access through firewalls

  21. Connectivity – Merge Replication • SQL Server 2000 features crucial for SQL Server CE: • Vertical and horizontal partitioning • Dynamic horizontal filters • Auto ranged identity columns • Standard/custom conflict resolvers

  22. Merge Replication API’s • System.Data.SQLServerCe.Engine.CreateDatabase • Replaces AddSubscription Method • Creates blank database • System.Data.SQLServerCe.Replication.Synchronize • Replaces previous methods • Initialize • Run • Terminate • Initial Sync • Creates schema, downloads data, creates constraints • Subsequent Sync • Push modified data up, then bring modified data down

  23. Connectivity – Remote Data Access • Access to SQL Server 7.0 & SQL Server 2000 • Download to device and disconnect • Disconnected tracking capability • Extremely easy to setup and use • Effective scaling

  24. Remote Data Access – API’s • System.Data.SQLServerCe.RemoteDataAccess.Pull • Brings data from server to device • Data selected via SQL statements • Creates and populates local table • Allows for tracked and untracked data • System.Data.SQLServerCe.RemoteDataAccess.Push • Sends modified data to server • System.Data.SQLServerCe.RemoteDataAccess.SubmitSQL • Pass SQL DML statement

  25. SDE, .NetCF, ADO.Net, SQL CE

  26. Nabisco

  27. Nabisco Case Study And Demo IBM DB/2 802.11b RF SAP Business Objects Ruggedized PocketPC Devicewith SQL Server CE 107 Distribution Centers XML Web Server SQL Server 2000 Failover 1500 Delivery Trucks

  28. Nabisco Deployment • Rolled custom CAB file • Originally used eMVT wizard • Load CAB file and database on CF card • Allows for cold boot recovery in field • Used version flag in publication • If version changes, then it is replicated down to the device • This flags download of new CAB file from IIS server and updates latest version

  29. SQL Server CE v2.0 Licensing • Included with Smart Device Extension (SDE) • SQL Server CE and SQL Server CE server side components • Developer and test license • Downloadable from HTTP://Microsoft.COM/SQL/CE for use with eMVT • CAL/CPU license needed for connectivity to SQL Server • Free distribution rights upon filling out registration form

  30. SQL Server CE v2.0 • eMVT Beta of SQL Server CE • Goto http://www.betaplace.com • BetaID:SQLServerCE • Password:.NetCF • Case sensitive, be sure to include “.” in password • Sign up for SDE beta (SQL CE included) • Goto http://www.betaplace.com • BetaID:SDEBeta • Password:.NetCF • Case sensitive, be sure to include “.” in password

  31. SQL Server CE Summary • Enables data capture in the field • Small size, robust functionality • Enables rapid development • Utilizes new Visual Studio .NET tools • Existing application with eMVT still run • Enable robust connectivity • HTTP, IIS, connect anywhere • RDA, Replication • Expand the enterprise • Upwardly compatible with SQL Server 2000

  32. Questions?

  33. Resources from Microsoft Press MICROSOFT .NET FRAMEWORK • For more information please visit the TechEd Bookshop. • www.microsoft.com/mspress

  34. Don’t forget to complete the on-line Session Feedback form on the Attendee Web sitehttps://web.mseventseurope.com/teched/

More Related