NAVmoble - the pocket-sized ERP
Optimized for Microsoft Dynamics NAV and Windows Mobile powered devices

Tuesday, February 21, 2006

Web Services,Datasets and Compact Framework

Transferring datasets through the wire between a Compact Framework based application and Web Service is a common data transfer practice. However, it may turns to a big pain from a performance point of view if not done properly.

So, when dealing with datasets consider the following approaches:


  • Design your application so that only the needed data is transferred. For example transfer only changed data. In case you are targeting CF2.0 you may use DataSet.GetChanges() and DataSet.Merge() methods. However if you are still targeting CF1.0 you should deal with it manually. This post explains it in general


  • Compress the data transferred. Read the following articles:



  • And finally serialize DataSet to Xml and then send it over the wire as a string. This could improve performance in some scenarios; prototype, measure and analyze to see if this could really help in your particular case. Transfer the DataSet schema as well to imrpove DataSet load time

Wednesday, February 08, 2006

Data transfer options between mobile devices and Desktop with Compact Framework

Following brief list of data transfer options, which may be used in CF applications:

1.Sockets
   The following classes from System.Net.Sockets namespace may be used for TCP/IP based communication: Socket,TcpClient and TcpListener. They provide a fast stream-based communication model.
This post of David Kline provide a simple object remoting by using Xml serialization and TcpClient/TcpListener

2.HTTP Request
   Namespace System.Net provide 2 classes (HttpWebRequest,HttpWebResponse) which may be use to deal with raw http requests.
See more here and here

3.Xml Web Services
   .NET CF provide good support for Xm Web Services consuming. According to MSDN it is the "primary feature" of the compact framework. Consuming Xml Web Service is easier as pointing the location of the web service.
A good start may be found here

4.Sql Ce and Remote Data Access (RDA)
   RDA is a lightweight mechanism for data synchronization between Sql Server Mobile(Sql Ce) and Sql Server running on the PC. You may store your data locally on the mobile side in Sql Ce database and synchronize the data with a remote Sql Server database when the device is connected.
See more here and here

5.Sql Ce Merge Replication
   It is just another and more powerful approach for synchronizing data between Sql Ce and Sql Server databases. Sql Ce Merge replication is based on SQL Server merge replication. It requires more configuration and maintenance at the server, but provides built-in and custom conflict resolution capabilities and tools for configuration and monitoring
See more here

6.RAPI
RAPI stands for "Remote API". It allows desktop applications to make function calls on the mobile device.
See more here
OpenNETCF Desktop Communication namespace provide easier way to make your desktop application RAPI enabled

7.ActiveSync data synchronization
ActiveSync may be used for automatic synchronization of contacts,calendar and even files. You need to just to put the files in the right folder.
See more here

8.File copying over a network share
Files may be copied by using windows network shares.

Selecting the right way to transfer data requires good understanding of the pros and cons. A lot of factors should be considered before taking the right one. Some of these factors are maintenance, scalability, locus-control, performance, dev.cost, interoperability,security,the mobile platform characteristics,etc.

Thursday, January 26, 2006

Portable Database Choice

This is a really nice post discussing portale database choise related issues

Migrating to SqLite - Part 2

What took us to migrate to SqLite?


In general we had the following checklist:
1.Replace the classes from the System.Data.SqlCe namespace with the classes from the System.Data.SqLite namespace.
2. DataType compatibility problems
3. Foreign key related problems
4. Sql syntax compatibility problems


The migration became really easy and smooth. The major reason was the system architecture. Our system design utilizes the common idea for separating the business and data access logic. Out business logic layer operates over custom business objects (not DataSets) and invokes the data access layer for fetching and persistence operations. It has a performance impact of course, however it is a reasonable tradeoff I may say.

Checklist item #1:
We have only one place in our code to create IDataConnection implementations:
The code is like:
public IDataConnection GetConnection()
{
return new System.Data.SqlServerCe.SqlCeConnection(cnnString)
}

so we've changed it to
public IDataConnection GetConnection()
{
return System.Data.SQLite.SQLiteConnection(cnnString)
}

We use IDataCommand, IDataParameter,IDataReader and other interfaces to perform the actual database interactions. So we didn't change anything in data manipulating routines. The code was like:
using(IDataConnection cnn=DataManager.GetConnection())
{
cnn.Open()
IDataCommand cmd = cnn.GetCommand(cnn);
cmd.CommandText = "Select * from sometable";
...
IDataReader reader = cmd.ExecuteDataReader()
...


Checklist item #2
Are we going to have issues related to data types. For example very often the datetime data type may be a big pain when using the same code to access different dbms. So we did a test application where we performed some database operations identified as potentially problematic. The operations involved reading, selecting, fitlering ,joining and aggregating in SqLite db populated with a real data (from a customer). We have operations with DataReaders,ExecuteScalar calls,etc. It showed us the problematic areas and they were few indeed.

Checklist item #3
Fortunately it was not a big deal for us. Our data access layer provide some features usually available in the ORM products and dealing with business objects relations at application and database levels was one of them.

Checklist item #4
The test application helped us to identify the possible problems. In general there was no need to rewrite sql statements. We had the following issues:
- statements for the foreign constraint creation
- primary key creation statements. We used ALTER TABLE ..ADD PRIMARY KEY, which is not supported in SqLite

Note that this post only scratches the surface of the possible problems around such a migration.The issues are of course specific to the actual application migrted.
What was the benefit?
The migration process helped us to identify some design and implementation issues in our code so in result we had a better code as a whole. And we have a really better performing application as a whole. The bottleneck features are performing up to 9 times faster after migration. It worth it!

Migrating from SqlMobile 2005 to SqLite requires understanding of the tradeoffs which have to be made.
Read the SqLite documentation and make a test bed before before proceeding.

Migrating to SqLite -Part 1

Finally I am taking my time to blog

We decided to investigate the idea for migrating our product from Sql Mobile 2005 to SqLite in order to benefit from the better performance. This was decided after investigating a large list of performance optimization tips without having the desired results.

We have 2 features identified as unacceptable from a performance point of view:
The first one involves a "discounts and prices calculation", which involves a number of unrelated db hits (no way to rewrite them as one). There are some scenarios, when this takes too long time to have the user satisfied. The second is the synchronization feature of the product. It has the ability to synchronize the mobile database with a remote (server-side )system using our proprietary mechanism. It is actually the most important feature from a marketing point of view. It involves massive insert and update operations. In most cases sync operation takes few seconds, however there are some scenarios, when the customer has a really huge amount of data and initial bulk loading of the mobile system may take few minutes. And we don't want to have the user waiting so long.

Some of the considerations against that migration was :
- the lack of database level encryption
- the lack of foreign key constraints enforcement
- the lack of strong type enforcement at a database level.
- the lack of experience with SqLite

However spending some time with SqLite in combination with the last version of the ADO .NET 2.0 SqLite Data Provider
made us optimistic about the success.

What it took us to migrate to SqLite? See here.