Mobile Application Pocket Guide v1.1

This guide has just been updated, and has advice on design of Mobile Applications, including advice on using SQL Compact in mobile applications. A very useful guide with a practical approach. Get it at: http://www.codeplex.com/AppArch/Release/ProjectReleases.aspx?ReleaseId=19798

Technorati Tags: SQL Compact,SQLCE,SQL CE

ExportSqlCe version 1.5 released

The latest version of the ExportSqlCe command line utility for scripting entire SQL Compact database files has been released on CodePlex.

Since I last blogged about this utility, the following missing features have been added:

Unicode support – the script file is now in Unicode format, which means that all text data is scripted “properly”, also for non-English text.

Image/binary/varbinary in INSERTs – binary and image fields are now scripted with their proper contents (as a hex string), and not “System.Byte[]”

INSERT with IDENTITY columns – CREATE TABLE now properly scripts IDENTITY columns, and using the SET IDENTITY INSERT feature of SQL Compact 3.5, it is now possible to do inserts on the table with the IDENTITY column.Numeric fraction and precision

INSERT statement improvements – INSERTs are now scripted without single quotes around numeric fields, which improves readability, INSERT performance and script size.
Support for rowversion – rowversion (timestamp) is a read-only column and cannot be updated. Therefore the generated script has been improved to exclude rowversion fields in INSERT statements.
Foreign keys with multiple fields – were not properly scripted as reported (and partly fixed) by user hugo on CodePlex.
Handle merge replicated databases – Merge replicated database files contains a number of system fields and tables. These should not be scripted – this  has been implemented.
Handle large tables – scripts of large tables are now flushed to disk in several files, to save memory.

Go and get the latest release/source, and give feedback at http://www.codeplex.com/ExportSqlCE

Technorati Tags: SQL Compact,SQLCE,SQL CE,SQL Server Compact Edition

Working with Case Sensitive SQL Compact databases

Up until version 3.5 SP1, SQL Compact have always been case in-sensitive, meaning that a string stored as "Albert" is considered the same as a string stored as "albert" in terms of sorting and selecting etc.

With 3.5 is is now possible to create Case Sensitive SQL Compact databases. This is always done a file creation time, and covers data in all tables in the entire database file.

When working with databases fro SQL Server 2008 Management Studio and VS 2008 SP1, the new database dialog has been updated to include a checkmark for "Case sensitive". In code, add "Case sensitive=true" to the connection string used for creating the database file.

The following testing is done using SQL 2008 SSMS (RC0).

Let's create a table:

CREATE TABLE CsTest ( TestVal nvarchar(50) NOT NULL )

And add some rows:

INSERT CsTest (TestVal) VALUES ('Albert')
INSERT CsTest (TestVal) VALUES ('albert')

So previously the statement below would have returned 2 rows:

SELECT * FROM CsTest WHERE TestVal = N'albert'

But as the database is case sensitive, only a single row is returned!

If we try to open this database in VS 2008 RTM (with only SQL Compact 3.5 RTM (version 3.5.5386.0) installed) - the engine will throw error 28609:
You are trying to access an older version of a SQL Server Compact 3.5 database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact 3.5 database, run Compact/Repair. (Not extremely helpful)

UPDATE: Converting a database to case sensitive:

This can be done with the Compact command as follows (in this sample Northwind.sdf is converted to a case sensitive database):

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf");
engine.Compact("Data Source=; Case Sensitive=True;");

Dynamics Mobile – SQL Compact used in MS Dynamics application

Microsoft has recently released version 1.5 of their Dynamics Mobile solution, which is a set of tools, device applications and server tools to enable synchronization of data between mobile devices and Dynamics AX (formerly Axapta) and Dynamics NAV (formerly Navision). It includes tools to develop your own application as well as a ready made application called “Dynamics Mobile Sales”. It uses SQL Compact 3.5 extensively, such a Merge Replication, and boasts features such as:

Complete no-coding customization of the application (based on XML), on a per-role basis.

Ability to queue web services requests when off-line.

Merge replication of lookup data from NAV and AX ERP servers.

Server based deployment and monitoring tools.

Well worth considering if you need to integrate with one of these backend systems.

Read more here: http://msdn.microsoft.com/en-

Technorati Tags: SQL Compact,SQL CE

us/library/bb986981.aspx and http://www.microsoft.com/dynamics/ax/product/mobilesolutions.mspx

Using ExportSQLCE from Visual Studio

Using an external tool from Visual Studio is an easy way to make use of command line utilities like ExportSQLCE. In this post I will show how to add ExportSQLCE to your Visual Studio tool set.

Start Visual Studio, and select the Tools menu, External Tools option.

This will bring up this dialog:

Click Add to add ExportSQLCE:

Click OK, and you now have a new menu option on the Tools menu, called ExportSQLCE.

Try it out:

Fill in the sdf file name and the name of the output file, which will be placed in your VS project folder and click OK.

Notice how the output appears in the VS Output window:

Technorati Tags: SQL Compact,SQL CE

And the cstest.sql file is now available in the project folder (use Show All Files):

SQL Compact data and schema export utility on CodePlex

Technorati Tags: SQL Compact,SQL Server Compact Edition,SQL CE

I have made some modifications to the utility I blogged about here and made the code available as a project on CodePlex. Please feel free contribute feature requests, patches and bug reports. The utility exports the object definition from a SQL Compact database, including tables, indexes, foreign keys and other constraints. It also scripts INSERT statements for all the data in the SQL Compact table (currently missing features are support for INSERT to image columns, and proper creation of IDENTITY columns).

New edition of Merge Replication book

Rob Tiffany has begun a new edition of his excellent Merge Replication book, the new title is:

Mobile Data Synchronization with Microsoft SQL Server 2005 and SQL Server Compact > Second Edition. Achieving Global Scalability via Merge Replication Republishing.

Go download and read Chapter 1.