Transcript Data Access on Mobile Devices

Mobile Data with the
Compact Framework
Shawn Wildermuth
Senior Consultant/Architect
Magenic Technologies
Who I Am
Shawn Wildermuth
([email protected])
C# MVP
INETA Speaker
Book Author
– “Pragmatic ADO.NET”
Editor of http://ONDotnet.com
This Presentation can be found at:
– http://adoguy.com/presentations
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)
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
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
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
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)
Limitations of the CF (3)
DataSet is limited
– Merge() and GetChanges() are missing
– Deal with changes row-by-row
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");
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();
}
SQL Server CE
.NET CF / Managed Stack
VS .NET (VB.NET, C#)
ADO.NET
SQL Server CE Data Provider
CLR / .NET CF
OLEDB CE
SQL CE Edition v2.0
Data Provider
OLEDB
QP/Cursor Engine/ES
OLEDB
Client
Agent:
Replication
and
RDA
Storage Engine / Repl
Tracking
802.11b,
CDPD,
GSM,
CDMA,
TDMA, etc.
IIS
HTTP
Occasionally
Connected
CLIENT
SERVER
Server
Agent:
Replication
and
Remote
Data
Access
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
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");
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);
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");
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,
rda.Push("Products",
rdaOleDbConnectString,
rda.Push("Orders",
rdaOleDbConnectString,
rda.Push("OrderDetails",
rdaOleDbConnectString,
RdaBatchOption.BatchingOn);
RdaBatchOption.BatchingOn);
RdaBatchOption.BatchingOn);
RdaBatchOption.BatchingOn);
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();
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");
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();
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
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;
}
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();
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);
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);
[WebMethod]
public XmlDocument SaveChanges(XmlDocument doc) {
svc.SetDataSet(doc);
MyTypedDs ds = new MyTypedDs();
ds.ReadXml(doc, XmlReadMode.DiffGram);
DataSet updated = UpdateDataSet(ds);
return new XmlDataDocument(updated);
}
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";
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");
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
}
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
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
Questions?