PocketPC Application Development 2

1
PocketPC Application Development 2
2
Smart Client Mobile User

• Local Database
• Aids in increased performance
• Faster to write to local data store and
  synchronize later than execute remote/wireless
  commands
• Data Synchronization
• Majority of synching is done by server, but
  client needs to know the location of it

3
Data Access Direct DB Access
Windows Forms
SQL Server
ADO.NET Data Provider
ADO.NET
.NET Compact Framework
Windows Server

• Cons
• Must be constantly connected
• Not scalable on server classic two tier design
• No synchronization model for typical dynamic data

• Pros
• Rich query into a large data source
• Access to entire data source device queries for
  and uses whats relevant

4
Server

• Data Synchronisation
• Ability for enterprise data to be moved from back
  end server to mobile device and vice versa
• Over wired connections trivial
• Over wireless connections more difficult
• Data Sources
• Needs to provide a level of Integration for your
  synchronization server
• Messaging
• Client to Server message passing.
• Client can store messages and pass them to Server
  when connection is available

5
Data Access SQLCE
Windows Forms
SQL Server
ADO.NET
SQL CE Replication (HTTP)
SQL Mobile
.NET Compact Framework
Windows Server

• Pros
• Very robust local store
• Complex SQL querying
• Advanced/performant synchronization
• Utilizes HTTP

• Cons
• Server configuration required
• Engine footprint on device

6
SQL Server Mobile Edition

• Upward compatibility with SQL Server
• Same result sets
• Compatible data types
• Right footprint size for devices
• 2MB
• Managed access to rich, robust device database
• Efficient, complex querying
• Enables online/offline applications

7
SQL Server Mobile Edition

• Essential to writing a useful application if you
  are not connecting to a network
• Data synchronization to SQL Server with server
  and client tracking
• Data access/synchronization technologies take
  advantage of Internet standards, including HTTP
  Secure Sockets Layer (SSL) encryption, through
  integration with Microsoft Internet Information
  Services (IIS).

8
Use of SqlServerCe

• Need of add the System.Data.SqlServerCe reference
• Namespace
• using System.Data.SqlServerCe
• using System.IO

9
Creating SQL CE Database

• Make sure you know where your database.
• Use a SqlCeEngine object to create the database
• SqlCeEngine engine new SqlCeEngine( "Data
  Source \\My Documents\\MovieManager.sdf")
• engine.CreateDatabase()

10
Verify Database Creation
11
Verify Database Creation (more)

• The SQLCE Query Analyzer program will only let
  you browse certain directories to locate your sdf
  file
• Therefore you will have to type in the location
  of the file manually in the text box
• Or use the file explorer and navigate to your
  working directory

12
SQL Commands

• Once you create a database. You can create and
  execute SQL command on the database.
• The typical usage is
• Create a SqlCeConnection object with data source
  pointing to the newly created database.SqlCeConn
  ection conn new SqlCeConnection( "Data
  Source \\My Documents\\MovieManager.sdf")
• Open the connection.conn.Open()

13
SQL Commands

• 3. Create the SQL command object.SqlCeCommand
  sqlCreateTable conn.CreateCommand()
• 4. Set the command text (i.e. The sql command to
  be executed)
• sqlCreateTable.CommandText
• "CREATE TABLE MOVIE(ID int IDENTITY(0,1)"
  " PRIMARY KEY, "
• "Title ntext, "
• "Year int, "
• "Rating int, "
• "Length int)"

14
SQL Commands

• 5. Execute the command sqlCreateTable.ExecuteNo
  nQuery()
• (What is the meaning of NonQuery?)
• 6. You can keeping changing the command text
  and execute the SQL commands as shown in the
  next slide
• 7. Remember to close the connection after
  finishing using the database. conn.Close()

15
Adding Records

• As shown in the previous slides, you can create a
  "Movie" table.
• To insert the data, use the SQL INSERT command.
• sqlCreateTable.CommandText
• "INSERT INTO MOVIE (Title, Year, Rating,
  Length) VALUES "
• "('Matrix', 1997, 2, 132)"
• sqlCreateTable.ExecuteNonQuery()
• sqlCreateTable.CommandText
• "INSERT INTO MOVIE (Title, Year, Rating,
  Length) VALUES "
• "('Harry Potter 1', 1998, 1, 110)"
• sqlCreateTable.ExecuteNonQuery()

16
Adding Records
17
Alternate way to create database

• You can use SQL Server 2005 or visual studio2005
  to create the SQL Server Mobile database.
• Example Visual Studio 2005
• Add a new data source and new connection.

18
Create Table

• Use the ServerExplorer to view the new database.
• You can create the schema and enter the records.

19
Create Records
20
ADO.NET Capabilities

• You can use ADO.NET for accessing database.
• ADO.NET classes in System.Data namespace
• Same as desktop DataSet, DataTable, DataRow,
  DataColumn, DataRelation
• DataSet object
• Ideal for disconnected client
• Encapsulates relational data
• Disconnected from Data Provider such as SQL
  Server
• Methods to identify DataRow changes

21
ADO.NET Support

• Handling data offline with DataSet
• Communicating DataSet with XML
• Common data model from server to PC to device
• Extensible ADO.NET provider model

22
Use of DataSet

• Before use the dataset you need to update the
  DataSet created.
• To use the dataset, simply drag the Movie table
  to the form. A DataGrid will be created for you.

23
Data Binding Controls

• You can change what kind of data-binding controls
  should be used to create the form
• Clicking Details indicates that each database
  column is displayed individually, with each
  having a separate label and text box

24
Data Binding Controls

• Drag the Inventory node to form.
• Visual Studio creates controls for the four table
  columns Description, Item Number, Price, and
  Image Filename.

25
Data Binding Controls

• Three components have been added
  ordersDataSet, inventoryBindingSource,
  and inventoryTableAdapter.
• You can make use of these objects to update the
  controls to reflect the data change.
• private void menuNext_Click(object sender,
  EventArgs e)
• inventoryBindingSource.Position
• (inventoryBindingSource.Position 1)
• inventoryBindingSource.Count
• Reference - MSDN Technical Article
• http//msdn2.microsoft.com/en-us/library/Aa454889
  .aspx

26
Data Binding Controls
27
Mobile Sync

• SQL Server CE is for managing data on a device
• Data resides at device only unless there is a way
  to replicate it to a desktop SQL Server
• Thus we need to merge data when the device is
  connected to the database server, whether it is
  SQL Server or not

28
Challenges in Mobile Sync

• Concurrency
• Division of workflow across mobile user base
• Data changing on device, server, or both
• Conflict resolution (Single Master vs.
  Multi-Master)
• Connectivity
• Reliability
• Bandwidth
• Security/Firewalls
• Administration

29
Choosing Sync ArchitectureConsider Your Options.

• XML Web Services
• You design and control all behavioral aspects
• Only option for non-SQL Server synchronization
• Remote Data Access (RDA)
• SQL-driven PULL and PUSH model
• Change tracking with optimistic concurrency
• Merge Replication
• Publisher - Subscriber Model
• Advanced tracking and conflict resolution
• Centralized Control over Pubs and Subscribers

30
Choosing Sync ArchitectureStart at the beginning

• Questions for Architecture Planning
• Read-only data on device with occasional updates?
• Data changing at one end or the other?
• Data changing at both ends concurrently?
• How large is the data to be synchronized?

31
Remote data access (RDA)

• RDA provides loosely-coupled connectivity between
  SQL CE SQL Server
• talks to SQL Server w/o being connected
• store SQL Server query results directly in SQL CE
• automatically tracks changes locally
• send locally changed records back to SQL Server

32
RDA Architecture
33
Merge ReplicationOverview

• Provides data synchronization between SQL Mobile
  and SQL Server
• SQL Server is the publisher
• SQL Mobile is the subscriber
• SQL Mobile receives initial snapshot from SQL
  Server
• Both SQL Mobile and SQL Server can modify the
  data
• Conflict resolution can be customized

34
Merge Replication

• Merge replication in SQL Server Mobile is based
  on SQL Server 2005 merge replication
• It enables autonomous data updates on the
  portable device and the server.
• Usage scenarios for merge replication include
• read-only replication
• data capture and upload
• and replicate, update, and synchronize.

35
Merge Replication

• Most applications will use a combination of these
  replication alternatives.
• For example, a sales support application might
  use
• read-only replication to download a price list to
  a device (i.e. price cannot be changed)
• relying on data capture and upload to capture new
  orders on the device and upload them to a server
  (i.e. new data directly add to server database)
• use replicate, update, and synchronize to
  download customer information, enabling
  information to be updated on the device. The
  resulting data could then be uploaded to the
  server. (the most difficult scenario, need to
  resolve conflicts)

36
Choosing A Data Sync Strategy
37
Implementation

• The first few slides show the implementation of
  the 3 mobile sync methods
• XML Web Services
• Remote Data Access (RDA)
• Merge Replication
• Note that the purpose of these codes are for
  completeness of this chapter. They will NOT be in
  test or exam.

38
Setting up SQL Server CE - RDA

• Integrates with IIS
• ISAPI Application to access remote data
• E.g. http//ltmachinenamegt/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//xyz/sqlce/sscesa20.d
ll", "Data
Sourcenorthwind.sdf")
39
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
"ProvidersqloledbData Sourcexyz"
"Initial CatalogNorthwindUser
IdSamplePasswordADONET" rda.Pull("Customers",
"SELECT FROM Customers",
rdaOleDbConnectString, RdaTrackOption.TrackingOnWi
thIndexes) rda.Pull("Products", "SELECT FROM
Products", rdaOleDbConnectString,
RdaTrackOption.TrackingOnWithIndexes) rda.Pull("O
rders", "SELECT FROM Orders",
rdaOleDbConnectString, RdaTrackOption.TrackingOnWi
thIndexes) rda.Pull("OrderDetails", "SELECT
FROM OrderDetails", rdaOleDbConnectString
, RdaTrackOption.TrackingOnWithIndexes)
40
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 SqlCeConnecti
on 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")
41
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 step

rda.Push("Customers",
rdaOleDbConnectString, RdaBatchOption.BatchingOn)
rda.Push("Products",
rdaOleDbConnectString, RdaBatchOption.BatchingOn)
rda.Push("Orders", rdaOleDbConnectStrin
g, RdaBatchOption.BatchingOn) rda.Push("OrderDeta
ils", rdaOleDbConnectString,
RdaBatchOption.BatchingOn)
42
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() re
pl.InternetUrl "http//xyz/sqlce/sscesa20.
dll" repl.Publisher "xyz" repl.Publish
erDatabase "Northwind" repl.PublisherLogin
"sample" repl.PublisherPassword
"ADONET" repl.Publication
"Northwind" repl.Subscriber
"OrderTaker" repl.SubscriberConnectionString
"Data Sourcenorthwind.sdf" // Create the Local
SSCE Database subscription repl.AddSubscription(Ad
dOption.CreateDatabase) // Synchronize to the
SQL Server 2000 to populate the Subscription
repl.Synchronize()
43
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 SqlCeConnecti
on 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")
44
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()
45
Web Services

• SOAP Based Data Access
• Only real data access on SmartPhones
• Doesnt 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
46
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()
47
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)
48
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.SetDataSe
t(doc)
WebMethod public XmlDocument SaveChanges(XmlDocu
ment doc) MyTypedDs ds new MyTypedDs()
ds.ReadXml(doc, XmlReadMode.DiffGram) DataSet
updated UpdateDataSet(ds) return new
XmlDataDocument(updated)