SQL Compact 3rd party tools

This is an attempt at a comprehensive list of SQL Server Compact Microsoft and 3rd party tools, both commercial and non-commercial. Please let me know if I have missed anything, and I will add it.

Microsoft supplied tools

Visual Studio Community 2017 free – when used with the SQL Server Compact / SQLite Toolbox: SQL CE 3.5, SQL CE 4.0

Visual Studio 2008 Professional (SQL CE 3.5)

Visual Studio 2005 Standard (SQL CE 3.1)

SQL Server 2008 R2 Management Studio Express (free, SQL CE 3.5 SP2)
Link: http://www.microsoft.com/en-us/download/details.aspx?id=30438
SQL Server 2005 Management Studio Express SP2 (free, SQL CE 3.1)
Link: http://www.microsoft.com/en-us/download/details.aspx?id=15366

New or updated entries are noted with (new) or (updated) ”Last updated” indicates when the tool was last updated by it’s author.

Non-commercial (free) tools

My GitHub Tools

Name: SQL Server Compact Toolbox (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 
Added to list: 29th June 2010
”Add-in for Visual Studio 2071/2015/2013/2012 (for 3.5 and 4.0). Standalone app (for 4.0 and 3.5)
Adds several features to help your SQL Server Compact development efforts:
– Scripting of tables, including data.
– Script of entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases, including SQL Azure.
– Import to SQL Compact 3.5 from a server database or CSV file.
– Basic, free form query execution.
– Generation of DGML files for visualizing table relationships
– Database Schema Diff Script
– Detect database file version
– Fix broken connection definitions”

Name: SqlCeScripting.dll (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox  
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 
Added to list: 29th June 2010
”Scripting library for inclusion in your own application, enumerates SQL Compact objects and allows scripting of these.”

Name: SqlCeCmd (updated)
Link: https://github.com/ErikEJ/SqlCeCmd
SQL CE Versions: 3.5, 4.0
Last updated: 2015
Platforms: Win32, Win64
”Administer SQL Compact databases directly from the command line, without having Visual Studio or SQL Server Management Studio installed. Only requires NetFx 2.0 and SQL Compact installed. Command line options similar to sqlcmd. Runs scripts generated by ExportSqlCe.”

Name: ExportSqlCe (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox/
SQL CE Versions: 3.0, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 
”This command line utility helps you better manage your SQL Compact development efforts. If allows you to script schema and data from any SQL Compact database file to a .sql file, which can be used in any context. The utility is developed in C# with Visual Studio 2008. You can use the resulting script for documentation, programmatic scripting of SQL Compact database files, or migration of schema and data to SQL Server (Express) 2005 or later.

Name: Export2SqlCe (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox
SQL Server Versions: 2005, 2008, 2012, 2014 
Last updated: Current
Platforms: Win32
Added to list: 4th March 2010
”A command line utility to script an entire SQL Server 2005 or later database with or without data to a SQL Compact T-SQL script.”

Name: SqlCeBulkCopy (updated) 
Link: https://github.com/ErikEJ/SqlCeBulkCopy
SQL CE Versions: 3.5, 4.0
Last updated: Current 
Platforms: .NET library, .NET CF library 
Added to list: 4th March 2010 
”.NET Library for loading data fast (doing bulk inserts) into a SQL Server Compact database file. Attempts to mimic the SQLClient SqlBulkCopy API. Includes support for BULK INSERTing any IEnumerable”

Query and database management tools

Name: CompactView
Link: http://sourceforge.net/p/compactview/home/Home/
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: 2015
Platforms: Win32, Win64
Added to list: 14th November 2011
CompactView is a viewer for Microsoft® SQL Server® Compact Edition (SQLCE) database files (*.sdf). CompactView can open database files of versions 3.1, 3.5 and 4.0

Name: SQL Compact Query Analyzer
Link: http://sqlcequery.codeplex.com/
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: 2013
Platforms: Win32, Win64
Added to list: 3rd July 2011 
Features:
– Execute SQL Queries against a SQL Server Compact Edition database
– Table Data Editor to easily edit the contents of the database
– Supports SQLCE 3.0, 3.1, 3.5 and 4.0
– Execute multiple SQL queries (delimited by a semi colon ;)
– Display query result as XML
– Shrink and Compact Databases
– Generate Schema and Data Scripts
– Display database and schema information

Name: LINQPad
Link: http://www.linqpad.net/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 14th December 2010
Linqpad allows you to query various data sources via LINQ, including SQL Server Compact 3.5 and 4.0. See this blog post.

Name: Database .NET
Link: http://fishcodelib.com/Database.htm
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 20th September 2010
“Database .NET is a simple and intuitive database management tool for multiple databases. Making Local and Remote Database Administration Easier, With it you can Create databases, Design tables, Edit data, Export data, Generate scripts and run SQL queries.”

Name: SQL CE Browser
Link: http://ericwillis.com/notes/2009/12/sql-ce-browser-v-110/
SQL CE Versions: 3.5
Last updated: 2010
Platforms: Win32, Win64 (?)
Added to list: 4th March 2010 
”Open regular and encrypted/passworded databases for viewing or editing. Write and work with any number of queries at once via tabs. SQL syntax highlighting and SQL syntax verification. Explore columns, keys, and indexes“

Name: Mini SQL Query
Link: http://minisqlquery.codeplex.com/ 
SQL CE Versions: 3.5
Last updated: 2014
Platforms: Win32, Win64 (?)
Added to list: 4th March 2010 
”Mini SQL Query is a minimalist SQL query tool for multiple databases (MSSQL, MSSQL CE, SQLite, OLEDB, MS Access/Excel files etc). The tool uses a generic schema engine taking advantage of the ADO.NET providers. Mini SQL Query is also easily extended with plugins.“

Name: SQL Server CE Query Tool
Link: http://cequery.codeplex.com
SQL CE Versions: 3.5, 4.0
Last updated: 2011 
Platforms: Win32, Win64 (?)
CEQuery is written in C# with the help of Visual Studio 2008 platform. Some of the features of this tool are described below:
User is able to create CE database. If the schema is provided, the tool is able to create the tables and columns. User is able to overview an existing CE database with its table and column structure in tree format. User can design a query by dragging and dropping tables and columns from the database tree. User can manipulate [Insert/Update/Delete] records in a selected table. User is able to generate scripts from a single table or of whole databases. [Both for SQL CE or SQL Server]. User is able to open multiple SQL Server CE files simultaneously. User is able to convert a SQL Server database to a SDF file with schema and data. User is able to change password of a SQL CE DB. The tool is now capable of handling the database with no password.“

Name: SeasonStar Database Management(SSDM)
Link: http://ssdm.codeplex.com/
SQL CE Versions: 3.5?, 4.0 
Last updated: 2011
Platforms: Win32
”With support for all major database formats, top-class export functions and advanced feaures like Linq Data Context availabe, SSDM is probably your best choice for DB management. It’s even free software!”

Name: SQL CE Database Editor
Link: http://sqlcedatabaseeditor.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2009 
Platforms: Win32
“Small lightweight application to browse and edit Microsoft SQL CE server databases. Does not require SQL CE server to be installed and can run custom queries.”

Name: DataBoy
Link:http://databoy.codeplex.com/
SQL CE Versions: 3.0, 3.5
Last updated: 2008
Platforms: Win32
“Small footprint, portable and simple database query tool. Support SQL CE 3.1, SQL CE 3.5 and SQL Server.”

Other tools and libraries

Name: Lyare.SqlServerCe.MultiQuery (new)
Link: https://github.com/lokiworld/Lyare.SqlServerCe.MultiQuery
SQL CE Versions: 4.0
Last updated: 2014
Platforms: Win32, Win64
Added to list: 2nd March, 2014 
“An extension to the Microsoft SQL Server Compact Edition client library, which simulates the support of the multiple statements in a single command.”

Name: SQLCE SCRIPTER
Link: http://sqlmgmt.com/sql/SQLCE-Scripter
SQL CE Versions: 3.5, 4.0
Last updated: 2011
Platforms: Win32, Win64
Added to list: 14th November, 2011
“Create SQL schema scripts for tables, indexes and referential relations. Create data scripts so that CE data can be moved to SQL Server or other data store. User can select specific tables only. User can select Script Schema, Script Data or both.”

Name: SQLCE Code Generator
Link: http://sqlcecodegen.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: 2012 
Platforms: Win32, Win64
Added to list: 27th March, 2011
“Contains a stand alone GUI application and a Visual Studio Custom Tool for automatically generating a .NET data access layer code with Unit tests for objects in a SQL Server Compact Edition database. Also generates a Windows Phone DataContext”

Name: OpenNETCF.ORM Framework
Link: http://orm.codeplex.com/
SQL CE Versions: 3.5
Last updated: Current 
Platforms: WinCE, Win32, Win64
Added to list: 27th March, 2011
“The OpenNETCF.ORM library is a lightweight object-relational mapping framework designed for use with the .NET Compact Framework.The framework is extensible to allow any type of backing store. An initial implementation using SQL Server Compact is provided.”

Name: SQL Metal Open Source User Interface
Link: http://sqlmetalosui.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2008 
Platforms: Win32
“This project provides an user interface for SqlMetal.exe tool.
SQLMetalOSUI allows users to generate code and mapping files for their SQL Server Compact Edition databases. SQL Server Compact Edition 3.5 is fully compatible with LINQ to SQL but the designer does not. This project will be focused to reach this initial purpose.”

Commercial tools

Name: MS Compact Maestro (new)
Link: http://www.sqlmaestro.com/products/mssql/compact_maestro/ 
SQL CE Versions: 3.5, 4.0
Last updated: 2013
Platforms: Win32, Win64
”MS Compact Maestro is a top-class SQL Server Compact management and development tool.”

Name: SDF Viewer 
Link: http://www.flyhoward.com/SDF_Viewer.aspx
SQL CE Versions: 3.0, 3.5, 4.0
Last updated: Current
Platforms: Win32
“The simple way to create, view, edit or export the data in your Windows Mobile or Pocket PC device’s SQLCE database, from the convenience of your Desktop. Run SQL commands on your SQLCE databases, and view the results instantly.”

Name: RemoteSqlCe
Link: http://www.gui-innovations.com/html/remotesqlce.html
SQL CE Versions: 2.0, 3.0
Last updated: 2007
Platforms: Windows CE, Win32
”SqlServerCE is a great database for Pocket PC’s, but designing, maintaining and querying databases on the PPC, especially whilst developing, can be wearing. We developed this tool to help us when developing for SqlServerCE. With RemoteSQLCe you can completely manage your SqlServerCE database from the desktop. RemoteSQLCE works in the cradle, and over ethernet/bluetooth.” 

SQL Server Compact books

A number of SQL Compact (or a least SQL Compact related) books have appeared on the market.
I will attempt to keep this list of books up to date as new material appears.

Building Standalone Data-Driven Applications using SQL Server Compact 4.0 and Visual Studio 2010”
“Microsoft SQL Server Compact 4.0 (SQL Compact onwards) is a free SQL Server embedded database ideal for building standalone desktop, web, and mobile applications that need local data storage.”
 
“Microsoft SQL Server Compact 4.0 Books Online”
Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. Download the documentation to learn about SQL Server Compact 4.0 and to build applications for it.

“La réplication du fusion avec SQL Server Compact: Implémentation and conseils”
“Free eBook in French about implementation and advise regarding Merge Replication – in many ways similar to the Rob Tiffany book.”

“.NET Compact Framework 3.5 Data Driven Applications”
“Develop data-driven mobile applications from the ground up on top of the Oracle Lite and SQL Server Compact”
 
“Microsoft SQL Server Compact 3.5 Service Pack 2 Books Online”
This download contains the Books Online for the SP2 version of SQL Server Compact 3.5.
“SQL Server MVP Deep Dives”
This book with contribution from 53 SQL Server MVPs includes 2 chapters relating to SQL Compact:
Placing SQL Server in your pocket by Christopher Fairbairn and Mobile data strategies by John Baird
http://www.manning.com/nielsen/
 
"Enterprise Data Synchronization with SQL Server 2008 and SQL Server Compact 3.5"
An update of the previous book, equally excellent and hands-on.
Available from Amazon
 
"Microsoft Mobile Development Handbook"
This book covers development using .NET Compact Framework 2.0 in broad terms, and has two chapters relating to SQL Compact:
Chapter 3 describes how to persist data using SQL Compact and how to bind SQL Compact data to UI elements and chapter 7 briefly covers using RDA and merge replication with SQL Compact.
 
"Windows Mobile Data Synchronization with SQL Server 2005 and SQL Server Compact 3.1"
I have already recommended the excellent Merge Replication book by Rob Tiffany here:
Available from amazon

"Microsoft® SQL Server 2005 Compact Edition"
This book covers the product in full, including a chapter on Native development, and several chapters on replication. The main perspective of this this book is a DBA approach, with only a single chapter covering development per se. There are also useful chapters on security and performance tuning.
 
"Hitchhiker’s Guide to Microsoft SQL Server 2005 Compact Edition"
For desktop developers diving into SQL Compact, the e-book by Bill Vaughn is an excellent introduction, which covers many aspects and pitfalls of developing with SQL Compact edition in a desktop application.
 

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

As you may know by now, the major new feature of SQL Server Compact 4.0 is support for ASP.NET starter websites. In addition, the Entity Framework support has been improved with the following features:

– Support for Code First development (requires EF 4 Feature CTP 4), allowing you to create database and schema in code.

. Support for paging (can be implemented with Skip and Take in LINQ).

– Support for server generated keys (IDENTITY)

The purpose of this post is to show how to use Entity Framework 4 with SQL Server Compact 4.0 and ASP.NET without having the full support for SQL Server Compact 4.0 in VS 2010.

I will cover the following scenarios in the sample application:

1: Migrate data and schema for the solution from either SQL Server Compact 3.5 or SQL Server 2005 or later.

2: Create a Type-Per-Table Entity Data Model (EDM) based on a SQL Server Compact 4.0 database.

3: Create a layered application, where the UI has no knowledge of Entity Framework, in order to improve testability, minimize UI code and allow the Data Access layer to be replaced with another Data Access Technology (unlikely, I know Smile)

4: Provide the initial implementation of a music track list displayed in a grid, which can be sorted and paged.

5: Provide access to invoice data in Excel

6: Allow easy management of lookup tables like Genre, Artist and MediaType

7: Use various extension methods, that make your life with Entity Framework smoother.

For this sample I am using the Chinook database from CodePlex, which is delivered in script format and as a 3.5 SDF file. The Chinook database schema represents a digital media store, including tables for artists, albums, media tracks (3500 tracks), invoices and customers.

Creating the database (optionally migrating from 3.5 or Server)

Depending on your starting point, there are many ways to get to a 4.0 database.

If you have a 3.5 database, you can use SqlCeCmd40.exe to upgrade a copy of this database to 4.0:

sqlcecmd40 –d”Data Source=C:dataChinook40.sdf” –e upgrade

will do an in-place upgrade for you. (This requires 4.0 CTP2)

If you have a database script, either from a SQL Server database (created by Export2SqlCe.exe or from elsewhere), you can use SqlCeCmd40.exe to create an empty 4.0 database file and import the script:

sqlcecmd40 –d”Data Source=C:dataChinook40.sdf” –e create

sqlcecmd40 –d”Data Source=C:dataChinook40.sdf” –i C:x.sql

Or you can use WebMatrix to graphically create the database. (Support for doing this in Visual Studio will come with VS 2010 SP1).

Creating the Data Access Layer

As mentioned, I will create a database first EDM. Due to the missing Server Explorer / Data Sources integration with SQL Compact 4, you will have to do this by using a workaround. I know of 2:

Either by using the command line tools as described in the blog: http://getsrirams.blogspot.com/2010/10/adonet-entity-data-model-for-sqlserver.html or

Have a 3.5 database handy with the exact same table definition (schema) as the 4.0 database. After generating the EDM based on the 3.5 database, tweak the model to work with a 4.0 database. Let’s see how this is done:

Start by creating an empty solution, named Chinook, to hold our projects:

image

Then add a C# Class library project to the solution, and name it Chinook.Data:

image

Remove Class1.cs, then go to file system, and copy the Chinook.sdf (the 3.5 version) to the solution folder (C:projectschinook).

image

(I have also copied the 4.0 version, so I can easily find it later). Now we can add the EDM based on the 3.5 database file:

image

Name the model ChinookModel.edmx and click Add. In the next step, select Generate From Database, and click Next.

On the “Choose you data connection” step, create a new connection to the Chinook.sdf 3.5 version database, using the SQL Server Compact 3.5 provider, and click Next:

image

Then you get this question:

image

(I suggest you reply No)

image

Select all tables, select pluralize and click Finish. Now we have our Model:

image

Now, let’s make the Model work with our SQL Server Compact 4.0 file. First, modify the connection string in app.config as follows:

image

Then modify the emdx file (right click the file, and select Open With.. XML Editor).

image

Verify your changes by opening the edmx file in the visual designer.

Creating the POCO Entities for use with Repository

Now let us create a separate class library for our POCO entities based on the model, This will allow us to reference this Model project from both the data access layer and the UI layer. In order to do this, follow the steps in this EF Team blog post, and you will end up with a solution structure like this:

image

Notice the following change,that was made to ChinookModel.tt in order to have the entities in a separate project:

string inputFile = @"..Chinook.DataChinookModel.edmx";

Also, a reference to the Chinook.Model project was added to Chinook.Data:

image

And the Custom Tool Namespace for the Chinook.Model.Context.tt file was set to Chinook.Model:

image

Creating the Repository

Now, add a Chinook.Repository project to the solution, to create the data access methods used by the UI. This project should reference the Chinook.Model and the Chinook.Data projects. As always, remove the Class1.cs file. Now, we want to create a method that allows the UI to get a page of Track data for display in a GridView. This is an initial implementation (notice the parameters, which are passed by default when using and ObjectDataSource from the UI):

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
using (var context = new ChinookEntities())
{
return context.Tracks.Include("Albums").Include("Artists").Include("Genres").ToList();

}
}

There are some things missing/not ideal with this:

– The Include statements are based on text strings.

– Sorting and paging is not implemented

In order to make the Include statement strongly typed, I have located an extension method from http://blogs.microsoft.co.il/blogs/shimmy/archive/2010/08/06/say-goodbye-to-the-hard-coded-objectquery-t-include-calls.aspx (Implemented in the ObjectQueryExtensions.cs file in the Chinook.Data project)

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
using (var context = new ChinookEntities())
{
return context.Tracks.Include(t => t.Album.Artist).Include(t => t.Genre).ToList();
}
}

That’s much better, and avoids nasty runtime errors.

The sortBy parameter from the ObjectDataSource is just a text string (field name), optionally followed by “DESC”. So to do sorting, we could write a long case statement to set the sortexpression, or use another extension method, which accepts a sort expression. I found one here: http://landman-code.blogspot.com/2008/11/linq-to-entities-string-based-dynamic.html

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
using (var context = new ChinookEntities())
{
if (string.IsNullOrWhiteSpace(sortBy))
{
sortBy = "Name";
}
return context.Tracks.Include(t => t.Album.Artist).Include(t => t.Genre).OrderUsingSortExpression(sortBy).Skip(startRowIndex).Take(maximumRows).ToList();
}
}

In addition to the GetAll method, in order to support paging, the repository must also implement a GetCount method: 

public int GetCount()
{
using (var context = new ChinookEntities())
{
return context.Tracks.Count();
}
}

Creating the UI Layer

Now, add a ASP.NET Web Application project. Call it Chinook.UI. Add a “WebForm using Master”, and call it TrackList.aspx. Add references to Chinook.Model and Chinook.Repository.

Add this content to TrackList.aspx:

image

Notice the highlighted sections in the markup.

Add the connection string entry from the Data project to web.config.

Set the UI project as Start Project and the TrackList.aspx as start page.

Press F5 and see it run!

To display the Album title rather than the albumId, add a AlbumName readonly property to a new Track.Custom.cs POCO partial class, and display the Album.Name when requested:

//For display only
public string AlbumName
{
get
{
if (this.Album != null)
{
return this.Album.Title;
}
else
{
return string.Empty;
}
}
}

Then change the asp:boundfield from AlbumId to AlbumName in the TrackList.aspx file (you can do the same for other foreign key fields)

<asp:BoundField DataField="AlbumName" HeaderText="Album" 
SortExpression="" />

Download the project so far from here.

In part 2, we will continue with the following additional features:

– Adding a Dynamic Data Project to quickly manage the lookup tables

– Creating an OData layer, allowing access from PowerPivot in Excel

– Deployment advice

(Thanks to Morten Hedekær Olsson for technical review)