My First Journey to Mobile Database Application

My company assigns me and my team mates to automate our warehouses processes. We need to automate the manual recording of inventory flows processes.
Our first development approach is ASP.NET 2.0. This approach is the fastest to get done because we don't need to think about mobile database and server database. As long as there is wireless connection and a capable browser, the mobile devices can be used for data input. Our intended device is Motorola Symbol MC5590 with QWERTY keypad and built in barcode scanner. For development purpose we use HTC TyTN II with Windows Mobile 6.1 Pro OS. We found a China company specializing in barcodes, Sumlung and tried their software for reading a barcode through PDA camera. But their software is incompatible with our mobile device. When budget is your main consideration in developing a barcode application, you can review their solutions. We use AJAX control toolkit for automating our web pages. But to be our disappointment this ASP.NET 2.0 is failed. Our ASP.NET 2.0 website was running fine on Windows Mobile 6 Pro Emulator Images IE Mobile. But some features failed on our HTC TyTN II IE Mobile. The most obvious one is asp:Login control, the button doesn't react to a mouse click. So we find alternative browsers for our WM6. Opera Mobile can be used for running our web pages but the layout size doesn't seem right to us. Luckily we found the Iris browser. The browser can run our web pages just like we want them. Since there are no problems with software development, the project rolls onto hardware infrastructures planning. We are pretty surprised with the wireless infrastructure site survey result. Our company needs to spend a large amount of money for a full wireless LAN coverages on its warehouses. We thought an alternative from in house wireless infrastructures to unlimited HSDPA data plan from mobile telco operators. We use Hamachi Mobile from LogMeIn for connecting our mobile devices to our server. Everything seems right unless one crucial problem, Indonesia mobile telco operators are unreliable. At least there is 1 day in a week we can not connect from our mobile telco operator. We came back to wireless LAN infrastructures with reduced coverages. It seems there is no easy path on Mobile Database Application, we have to develop a .NET CF 2.0 database application.

Second approach, .NET CF 2.0 database application. First things we do are download all necessary software:
Windows Mobile 6 Pro Emulator Images (downloaded previously)
Windows Mobile 6 SDK
ActiveSync 4.5 for Windows XP (allow DMA connection for docking emulator)
Windows Mobile Device Center for Windows Vista
.NET CF 2.0 SP 2 for our Visual Studio 2005
SQL Server CE 3.1 (we download the Developer SDK and Tools for Visual Studio SP 1)
We are pretty surprised there are no significant differences between developing a desktop ADO.NET 2.0 application and a mobile ADO.NET 2.0 application. To cut the learning curve, we do not use Microsoft Synchronization Services, we use compact framework SqlClient directly to upload to and download from SQL Server 2005. There are several things about SqlCE we bumped into:

1. Do not use ComboBox for looking up a table with a lot of records. We create a SearchDialog that utilizes a scrollable SqlCeResultSet bound to a DataGrid instead of ComboBox. The SqlCeConnection must be always connected for using SqlCeResultSet. SqlCeResultSet will only fetch shown records on DataGrid so you don't need to wait the whole records to be loaded. Our SearchDialog construct incremental search select SQL and return a HashTable of a row selected.
2. SqlCE doesn't support update and delete using join, sub query and batch command. So we must keep this in our minds.
3. Eliminate unneeded columns on SqlCE.
4. Denormalize wisely for gaining performance.
   
5. We use untyped dataset and it took time to load it so we serialize the dataset schema to database and deserialize it on form load. PS: We hate typed dataset :-).
   
6. Our select, insert, update and delete command constructed on the fly based on table schema and it took time to load also. So we serialize and deserialize them using XML into database also.

The problem about performance and incompatible SQL make us considering several alternatives such as: SQLite, VistaDB and SSCE Direct. But time constraint makes us stay with SqlCE. For SQL Server 2005 side, all tables to be synchronized to SqlCE equipped with timestamp columns CreateTime and UpdateTime and Deleted tables for storing the deleted primary keys and their DeleteTime for each tables. We also applied these for all SqlCE tables to be uploaded to SQL Server. Records inserted in SqlCE use negative integer primary key, they will have positive integer primary key after synchronize with server.

I hope my share about our experience in developing mobile database application can be useful for all of you. Thank you.