The Primeworks Mobile Blog

SQL Compact 4.0 CTP1 Support

2010-07-14T01:48:00.000-07:00

A new version of Data Port Console (1.8 preview) has been uploaded with support for SQL Compact 4.0 CTP1. The maintenance features are not yet available.

Primeworks.Data Licensing

2010-06-10T11:36:00.000-07:00

If you are trying out the latest version of Primeworks.Data, please make sure that you are correctly using the Primeworks.Data.License class:

Create an instance of the object using empty strings on both arguments of the constructor
Assign it to the License property of either the Pipe object (if you are using the SqlCe.Core classes) or to the License property of the SqlCeConnection object (if you are using the ADO.NET-compatible classes)

Here's a sample (C#):

Primeworks.Data.License license = new Primeworks.Data.License("", "");
SqlCeConnection connection = new SqlCeConnection();
connection.License = license;

Data Port Wizard 2.1.290

2009-04-27T02:14:00.000-07:00

Data Port Wizard 2.1.290 has just been released. This version includes the following improvements:

Persistent data entry on most dialog fields;
The data transfer log is available even when the data transfer succeeds;
Corrected an error that caused data corruption after transferring very large databases to desktop SQL Compact.

Data Port Wizard 2.1.280

2009-04-14T03:15:00.000-07:00

Data Port Wizard has been updated to version 2.1.280. Besides correcting some minor bugs, this version adds a new feature: the data transfer event log. If an error occurs during data transfer, a detailed log of the data transfer process is displayed along with the exception to help determine the cause of the error.

Data Port Wizard 2.1.250

2009-02-02T07:48:00.001-08:00

There is a new minor revision of Data Port Wizard available for download: 2.1.250. This version adds the ability to attach an MDF database file to the SQL Express database server when importing and exporting.

During the testing period I found out that this feature may cause some strange errors, but they are all generated if you have a faulty installation. If when opening the attached MDF file you get an error E_FAIL with an error number in the "Native error" box, then odds are that you must somehow change your SQL Express setup. I have found a few different scenarios:

Your user does not have admin privileges, so the database cannot be attached;
The name you give to the attached database already exists on the list of attached databases;
Your user does not have read rights;
You don't have the latest Service Pack installed.

If you find some other error, just search the internet for "SQL Server Native Error ##".

Primeworks.Data Preview

2008-12-15T03:30:00.000-08:00

The preview version of Primeworks.Data is now available for download. Before you download the ZIP file, please read through this post where I will explain what the new product is and how to use it.

What is Primeworks.Data?
Primeworks.Data is the successor of DesktopSqlCe and DataPortSync. To put it simply, it replaces and enhances both products in a single package. This is a desktop .NET 2.0 / 3.5 component that allows your application to:

Access local and remote SQL Compact 2.0, 3.0 and 3.5 databases. Local database access is limited to 3.0 and 3.5 because Microsoft never shipped a 2.0 desktop provider. Remote databases can be accessed through RAPI (ActiveSync or WMDC), Sockets and through a serial port (enabling a Bluetooth connectivity scenario). Access to the databases is achieved through two layers of classes: the high level ADO .NET classes and the lower level Core classes. The ADO .NET classes are implemented on top of the Core classes and these provide extra features such as the ability to enumerate the database schema, rename tables and columns, and much more.
Import data from Microsoft Access (JET 4.0 and ACE 12.0), SQL Server (2000, 2005 and 2008) to any version of SQL Compact, both local or remote.
Export SQL Compact data to Microsof Access, SQL Server and DataSet XML formats (same database versions as above).
Synchronize data between a Microsoft Access database and multiple SQL Compact databases through the simplified desktop synchronization mechanism.

Both Data Port Wizard 2.0 and Data Port Console 1.4 make extensive use of Primeworks.Data 1.0. This means that by using Primeworks.Data, your application will be able to implement any of these product's features.

What's in the distribution file?
The distribution ZIP file contains the full C# sources, the VS 2005 and VS 2008 projects, the desktop redistributable files (please note that a zlib DLL is included) and the device installation CAB files.

How can I set up Primeworks.Data on my development PC?
Please download the distribution ZIP file and expand it into one directory. You will find no compiled assemblies, so you must make include the Primeworks.Data.csproj file (in the Primeworks.Data\FX directory) in your solution and add a reference to it.

What other components do I need to run my applications?
On the desktop, please make sure that all the DLL files in the Redist\Desktop folder are copied to your application directory. If you are developing for a Windows CE or Windows Mobile device, please make sure that you install the correct CAB file (just copy it to the device and "execute" it using the device File Explorer). Please note that you will also have to install the SQL Compact runtimes on the desktop or on the device. When installing SQL Compact on a device, please make sure that you install the replication CAB (it contains the required OLE DB provider).

Can I use Primeworks.Data in my projects?
The current version of Primeworks.Data is not the final 1.0 and will change in the course of the next weeks. You can expect to see some (maybe more than some) breaking changes to the code. Some classes may still change and others are still being built, most notably on the ADO .NET layer where support is being added to SqlCeResultSet. The short answer is: no, please use it for testing purposes only.

Is there any online documentation for Primeworks.Data?
Yes, you can find the preliminary reference documents here. Please note that these will change quite frequently and in lockstep with the code.

Does this version require a license?
Yes, but you can work with this code for 90 days without having to worry about it. If you have a licensed version of DesktopSqlCe you can use that license in your code. Please note that this will change in the final release.

What are the major changes from DesktopSqlCe?
We have moved from a flat namespace to a richer namespace design. The distributed code declares the following namespaces:

Primeworks - Generic classes.
Primeworks.API - These classes interface to the low-level DLLs.
Primeworks.Data - Base classes for data access.
Primeworks.Data.Jet - Microsoft Access classes.
Primeworks.Data.OleDb - Base OLE DB classes.
Primeworks.Data.Sql - SQL Server classes.
Primeworks.Data.SqlCe - SQL Compact classes (ADO .NET).
Primeworks.Data.SqlCe.Core - Core SQL Compact classes.
Primeworks.Data.Sync - Synchronization classes.
Primeworks.Data.Xml - XML export classes.
Primeworks.Remote - Remote device access classes.

Are there any source code samples?
There are no samples for this component yet, but you can adapt the DesktopSqlCe samples. A ful set of samples (in both C# and VB) will be made available in the course of the next few weeks.

What are the upgrade options from DesktopSqlCe?
Current DesktopSqlCe users will have the option to upgrade to the new product - when it becomes available - at a reduced price. Customers that own licenses of both DesktopSqlCe and DataPortSync will have a six-month period to claim a free upgrade.

What's new in Console 1.4 - Statistics

2008-11-26T10:13:00.001-08:00

New in version 1.4 of Console is the ability to display index statistics for the whole database, a single table or an index. Index statistics are very useful when determining the "useful indexes" for the Query Analyzer. A useful index generally has a high selectivity (or low density), meaning that each index value matches the minimum number of table rows. A primary key is by definition a useful index because it has the lowest possible density and the highest selectivity: each index value matches one and only one table row.

To display the index statistics, select the new "View Statistics" context menu option on the database, table and index tree nodes of the "Data Explorer" view. The views correspond to the execution of the following stored procedures:

This option is not available for SQL CE 2.0 databases due to lack of engine support.

Data Port Wizard 2.0.160.56

2008-11-26T10:07:00.000-08:00

Following the release of Data Port Console 1.4.5100.56, Wizard has also been updated to version 2.0.160.56. This new version supports the new version of the server (1.56) and also allows for correct exporting of UDT columns from SQL Server into SQL Compact's IMAGE columns.

Data Port Console 1.4.5100.56

2008-11-25T01:42:00.001-08:00

The new version of Data Port Console (1.4.5100.56) is now online and can be downloaded from here or here. This version corrects an error that prevented the version 1.55 of the server code to open password-protected SQL Compact 3.0/3.1 databases. The client code also has been revised for better behavior when the connection times out and the timeout dialog is shown to the user.

Bug on Server version 1.55

2008-11-20T10:56:00.000-08:00

The last version of Data Port Server (1.55) shipped with a bug that prevents SQL Compact 3.0/3.1 databases to open correctly. There is no workaround for this and a new version of the server will ship very soon. Apologies for the inconvenience!

What's new in Console 1.4 - Schema Views

2008-11-03T01:49:00.000-08:00

Data Port Console 1.4 now adds a "Views" folder under every open database containing the available SYSTEM_INFORMATION views (these vary with the database server version). To open each view, just double-click on the view tree node and a Query View will be displayed with the appropriate query that will be automatically executed.

Console 1.4 shipped with wrong cab

2008-10-30T16:45:00.000-07:00

The first copy of the Console install ZIP file that was uploaded this morning contained a faulty Windows Mobile 5.0/6.x setup cab. When connecting to the device you will see an error reporting that the server DLL has a wrong version number. To correct this, please uninstall that version, download the new zip (same version number) and install again.

Sorry!

Preparing a JetDatabase

2008-10-08T08:04:00.000-07:00

This is a brief guideline on how to prepare an Access database to become the synchronization source for "Desktop Sync". In order to follow these indications, you must have the latest version of the Sync project (here).

Create a JetDatabase object with the appropriate properties.
Open the database and call LoadSchema to load the collection of tables and columns into memory.
If you want to synchronize the whole database, call TagSchema with the TagSchemaOption.All value as parameter.
If you just want to synchronize a subset of the database, make sure you manually tag the tables and columns (set the UserInt value to: 0-exclude, 1-synchronize, 2-export only).
Initialize a PrepareProgressCallback delegate and make it point to a method for progress reporting purposes.
Call the Prepare method using the progress reporting callback. You can call this method from a thread or from a background worker.
Done.

The database is now prepared and you can close the JetDatabase object. The schema selection information is persisted on the database so is is readily available when creating synchronization clients.

To remove the tracking tables and columns, use the Unprepare method.

DesktopSqlCe 1.9.2590 reference

2008-10-08T06:41:00.000-07:00

The latest version of the DesktopSqlCe reference is here.

Using Desktop Sync in Console - I

2008-10-08T01:37:00.000-07:00

Before you can synchronize a Microsoft Access with a SQL Compact database, you must first "prepare" the Access database. Desktop Sync uses a hub-and-spoke data synchronization model where the Access database is the hub and the synchronized SQL Compact databases (clients) are the spokes. This means that the Access database must contain its own data tracking tables (to be able to check what changed between synchronizations) and also to track what changes on the client databases.

To prepare an Access database in Console, go to the Data Explorer pane and expand the "Desktop Sync" node. Right-click the "Microsoft Access" folder and select the "Add Database..." context menu option to display the Microsoft Access properties dialog.

This dialog allows you to enter the database file name as well as other optional properties such as the file password. After clicking the "OK" button, a new dialog is displayed to allow you to select what tables and columns are synchronized or simply exported to the client database. This selection is made by right-clicking on each node (table or column) and setting the properties for each:

Synchronize - The table or column will be exported to the client and changes to it will be tracked.
Export only - The table or column will be exported to the client and no changes will be tracked.
Unselect - The table or column will not be exported to the client.

Note that this version of the code assumes that when you select a table or column to synchronize, both the master and the client databases will track changes to it. There is no way to force this feature on only one of the ends of the synchronization.

When you are done selecting the required tables and columns, Console will prepare the Access database and will add it to the Data Explorer tree under the "Microsoft Access" folder. This preparation phase adds tables and columns to the tracked tables in order to allow for later tracking of changes. It is very possible that the database file grows very large - if this happens open the Microsoft Access application and compact the database file.

Under the new database tree node, you will find two folders:

Clients - Contains the list of synchronization clients. After preparing a database, this node should be empty. To add a new synchronization client, please use the database node's context menu.
Tables - Contains all the tables and columns as you selected when preparing. These are displayed for your reference only.

Data Port Wizard 2.0 Manual

2008-10-07T07:28:00.000-07:00

You can find the new Data Port Wizard 2.0 manual here and here.

New .eu domain

2008-10-07T04:35:00.000-07:00

I have recently received some complaints about the availability of the www.primeworks-mobile.com site. To help with this issue, the new www.primeworks.eu site is now online and content will be slowly transitioned from the old to the new domain.

DesktopSqlCe 1.9.2590

2008-10-06T02:49:00.001-07:00

The latest revision of DesktopSqlCe (1.9.2590.54) is now online an you can download the installer ZIP file from here. To install this version, just expand the ZIP file contents to a new directory and run the Setup.exe file. This version updates the source code to match the binaries that ship with Data Port Console 1.3 and Data Port Wizard 2.0.

How many SqlCeCommand objects can you create?

2008-10-06T01:40:00.000-07:00

The answer is 1024, and it also applies to SqlCeTable objects. Why?These things do consume a lot of memory on the device and must be used with care. When executing SqlCeCommand on a loop, please make sure the you do one of the following:

Create a new object at the beginning of the loop and then explicitly call Dispose on it at the end (C# users can use the using statement).
Create the SqlCeCommand object before the loop starts and reuse it throughout the loop. After the loop ends you should call Dispose if it is no longer needed.

Finally, don't rely on the desktop GC to reclaim the unused SqlCeCommand objects for you because it only understands the desktop heap. Also, garbage collection is triggered when certain desktop heap conditions are met, not the device heap. So the desktop GC is essentially blind to the device heap and memory consumption.

The bottom line is this: always treat SqlCeCommand objects as unma

Downgrading SQL Compact databases

2008-10-01T07:48:00.000-07:00

If you find yourself in the awkward situation where you need to downgrade a SQL Compact 3.5 to a SQL Compact 3.0 or even 2.0 (on the device only), you can now use the data import and export services of Data Port Console 1.3 or Data Port Wizard 2.0. You will find that Wizard is a bit more flexible than Console because it allows the data transfer between SQL Compact databases to occur on the device only, while Console requires that at least one of the databases must reside on the desktop PC. Before you try to upgrade or downgrade a SQL Compact database, please make sure that you have all the required SQL Compact database engines installed.

Desktop Sync?

2008-09-30T13:41:00.000-07:00

If you are using Data Port Console 1.3 you may have noticed the new "Desktop Sync" item in the Data Explorer view. What is this for? This feature grew out of an old product named "Data Port Sync" whose main purpose was to enable a simple means of synchronizing desktop Microsoft Access databases with SQL Compact databases (of any version) on a mobile or embedded device. The main idea was to replace the now defunct ActiveSync Access synchronization feature.

What are the target scenarios?
You can use this for small to medium-sized databases.

How does it work?
The Desktop Sync component is composed of two subsystems: change detection and transport. The change detection mechanism works both for SQL Compact databases (built into the RemSqlCe and LocSqlCe DLL files) and for Microsoft Access databases (built into the desktop dptrack DLL file). On both databases, this component works by sweeping through the database and by comparing each row CRC value with a previously stored one. On SQL Compact databases, this may also work for application-marked rows for better device performance.

The transport subsystem merely provides a means to copy rows from one database to another, performing the appropriate data conversions. Out of this subsystem grew the code that now allows both Data Port Console and Data Port Wizard to import and export data between the three SQL Compact formats (2.0, 3.0 and 3.5), Microsoft Access 2000 and later and all the 200x versions of SQL Server (2000, 2005 and 2008).

How can I use it?
The Desktop Sync feature was added to Console in order to provide an easier interface for developers - this is not an end-user product (not yet, at least). To use this code, you will need a license of DesktopSqlCe and the project that implements the Primeworks.DataPortSync namespace (available under request).

Data Port Wizard 1.7.1100

2008-09-01T03:10:00.000-07:00

The latest version of Data Port Wizard (1.7.1100) has been released and adds support to SQL Server 2008. You can download the latest version from here.

Data Port Console last Beta

2008-08-26T03:59:00.000-07:00

The last Beta of Data Port Console (1.3.3995.54) has just been uploaded. This version corrects some bugs and is the last Beta before release.

RemSqlCe diagnostics

2008-07-20T03:56:00.000-07:00

I have uploaded a new RemSqlCe RAPI connection diagnostic tool (download here). Please run it from your PC to get a better understanding on what might be causing your connection failure.

A typical successful run shows this output:

RAPI initialization request succeeded.
RAPI initialization reply from device.
Registry RAPI policy value is 2
RemSqlCe.1.54.dll found on \Windows.
GetVersion reported version 54

The first two lines show that the RAPI connectivity is working with your device. The second line reports the current RAPI security policy value (0: Closed Mode; 1: Open Mode; 2: Restricted Mode). The fourth line reports that the RemSqlCe.1.54.dll file is present on the \Windows directory where all the clients expect it to be. Finally, the last line shows two things:

The DLL is actually RAPI-callable. If you get an error here, then it's very likely that your device prohibits RAPI calls (due to carrier configuration). The device DLL is signed with unprivileged certificates only which may be insufficient for some Smartphone devices.
The DLL version matches. If you get a value different from 54 (the same as on the file name) then you have a bogus DLL.

Database Connection Failure

2008-07-01T06:16:00.000-07:00

If your product refuses to connect to a device database, please make sure that you have SQL Compact fully installed on your device. This includes installing the replication CAB file (file name with '.repl.') because it deploys the OLE DB provider on the device.

The device-side component (RemSqlCe.1.54.dll file in the \Windows directory) is a native code OLE DB consumer that requires the SQL Compact OLE DB provider. One of the nice advantages of being a native code consumer is that it can simultaneously open SQL CE 2.0, 3.0 and 3.5 databases. The downside of being a native client is that Microsoft split the managed and native client deployment and by default only the managed client is installed.

When this situation happens your product will complain that it cannot open the database file. On Console, you may see an empty error message (something to be solved in a near future upgrade).

Detecting when the device connects or disconnects

2008-04-28T02:36:00.000-07:00

DesktopSqlCe allows you to monitor device connectivity via ActiveSync or WMDC via the DeviceMonitor class. This class is very simple to use: create a new instance and listen to the DeviceMonitorEvent. When the device connects, your listener will receive a DeviceMonitorMsg.IPAddress value in the Message property of DeviceMonitorEventArgs.

Data Port Console 1.3 Beta

2008-04-23T06:32:00.000-07:00

After a few hiccups, the Console 1.3 Beta (3920) is now uploaded. This is the first public build entirely compiled under Visual Studio 2008 and this is the probable cause for some errors when installing through the exe. Now the Beta installer is available only via a ZIP file that you must download and extract to your local disk before installing.

The 1.3 Beta runs side by side with the existing 1.2 and has much better support for data transfer between database formats. You will also note that there is a new root item on the Data Explorer for "Desktop Sync". This is the very simple Access to SQL Compact database synchronization code that was available with the Data Port Sync component. You can prepare and track changes on Access databases but the Synchronization feature is not yet available because it requires some more work.

Apart from this, you should be able to use the 1.3 Beta (3920) without any other issues. Error reports are welcome!

Data Port Console 1.2

2008-02-23T08:39:00.000-08:00

I have just released Data Port Console 1.2 to the site. Besides adding more menu options on the main menu bar, I also added the ability to run multiple SQL commands. You do this by separating the commands with a ';' character. This is great for running DDL scripts.

You can get your copy here.

DesktopSqlCe 1.9.2510

2008-01-17T04:02:00.001-08:00

Version 1.9.2510 of DesktopSqlCe has just been uploaded. Besides minor corrections to the C# source code, I added the full source code of the now defunct SQL CE Console product.

EXE Setup
ZIP Setup

DesktopSqlCe FAQ

2008-01-14T01:43:00.000-08:00

I have started a FAQ for DesktopSqlCe and you can read it here.

Data Port Command 1.5.900

2008-01-11T07:17:00.000-08:00

Data Port Command has been updated to version 1.5.900. This corrects a command line parser bug as reported in the forum and updates the server code to 1.54.

ZIP Installer
EXE Installer

DesktopSqlCe 1.9

2008-01-10T06:41:00.000-08:00

The DesktopSqlCe 1.9 installers are now available for download here:

EXE Setup
ZIP Setup

I will be adding more samples to the installer which features a new device installer option on the Start menu (much like I did for DPW). This allows you to quickly deploy the device server on a connected device using the appropriate CAB file. All the CAB files are located on the Redist\Device directory and if you like, you can also reuse the cabinstall.exe that I use to deploy them. Anyway, one of the samples I will shortly add is a device installer dialog similar to the one you find in the Console.

Please send me any feedback on this installer so I can make it better.

DPW 1.7.1050 problem

2008-01-10T00:36:00.000-08:00

I'm looking at an issue with DPW 1.7.1050 that prevents importing databases into a SQL Compact Edition 3.5 database. The new installer will be uploaded as soon as this is solved. Sorry for this!

[Edit]
Somehow the code was specifying a value of zero for the maximum temporary file size and SQL Compact 3.5 was not eating it. Now where did this come from?

Here are the download links for the 1.7.1060:

EXE Setup
ZIP Setup

Data Port Wizard 1.7.1050 Released

2008-01-08T08:10:00.000-08:00

A new minor revision of Data Port Wizard has been published. This updates the device component (Data Port Server) to version 1.54 and corrects a problem with the Windows CE CAB files. Please use the device installer to set up your device before using DPW.

You can download the installer from the following links:

EXE Setup
ZIP Setup

Welcome!

2008-01-08T00:52:00.000-08:00

In this blog I will post about everything that relates to my work at Primeworks, the company's products and support information.