Tuesday, February 11, 2014

SQL Server Compact Toolbox 3.7.1–Visual Guide of new features

After 250.000 (!) downloads, version 3.7.1 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download (and available via Tools/Extensions in Visual Studio). This blog post is a visual guide to the few new features included in this minor release, which all centre around Server Explorer (DDEX) , including support for Visual Studio 2012 and 2013.


Simple DDEX providers for Visual Studio 2012 and 2013


I already blogged about this feature here, it has been extended to also support SQL Compact 3.5. Follow these steps to use the providers with Entity Framework Tools:

- Install this version of the toolbox and launch Visual Studio 2012/2013.
- Open the Toolbox (this will silently run the “installation” of the DDEX provider if required).
- Restart VS 2012/2013.
- Add new Toolbox connection to a new or existing SQL Server Compact 4.0 or 3.5 database file.
- Add EntityFramework.SqlServerCompact or EntityFramework.SqlServerCompact.Legacy NuGet package to project and build project.
- You can now use this connection for Generate Model from Database, Generate Database from Model and Update Model from Database, and with the Power Tools.

With this release, the DDEX support matrix looks like this:

  SQL Server Compact 3.5 SQL Server Compact 4.0
Visual Studio 2010 Microsoft None
Visual Studio 2010 SP1 + SQL CE 4.0 Tools Microsoft Microsoft
Visual Studio 2012 ErikEJ Microsoft
Visual Studio 2013 ErikEJ ErikEJ

“Microsoft” means full featured, Microsoft supplied provider
ErikEJ” means simple, basic support, enough to enable Entity Framework Tools, provided by this version of SQL Server Compact Toolbox.

Currently, the 3.5 DDEX provider does not fully work with the Entity Framework Tools, due to a bug (my bug!) in the EntityFramework.SqlServerCompact.Legacy Entity Framework provider; I am working on getting it fixed.

Scan Solution for sdf files

This new menu item will scan the active Solution for any sdf files, and add those to the Toolbox:


Prefer DDEX


This new option allows you to always use the Toolbox supplied dialogs for connecting to a SQL Server Compact database file, which will allow you to connect to any file, and does not require an .sdf file type. (By choosing not to prefer!)

Bug fixes and enhancements

Add connection will now always use DDEX provider if available
Handle apostrophe in file and folder names when saving connections
Fixed error: The specified table does not exist. (Databases)
Make it clearer that sqlmetal dependent features requires 3.5 to be installed

Please report any issues you encounter and provide feature suggestions and requests here.

Monday, February 3, 2014

Entity Framework 6 & SQL Server Compact 4.0 (4) - Restoring full EF Tools support in Visual Studio 2013

As described in my blog post here, Entity Framework Tools support for SQL Server Compact 4.0 in Visual Studio 2013 is broken, because SQL Server Compact is no longer supported by Server Explorer in VS 2013. The latest release of my SQL Server Compact Toolbox, version 3.7.1, currently available in beta release from here, restores the Entity Framework Tools support for SQL Server Compact 4.0 by installing a simple DDEX provider.

Developing DDEX providers is a bit of a dark art, which has put me off even thinking about it, but a recent discussion on CodePlex drew my attention to the DDEX sample provider included with the Entity Framework source code. In particular the fact that the Entity Framework Tools does not get any information from the DDEX provider apart from the connection information, so all Tables, Columns etc. are enumerated via code in the Entity Framework provider, not the DDEX provider. Quote from the included Word document: “Thus, the information shown in the wizard GUI comes directly from the EF provider. The wizard lets users select which tables, views and stored procedures to include in the model”.

So my “simple” provider enables you to use the Entity Framework Tools, for example “Generate Model from Database”, “Generate Database from Model”, “Update Model from Database” and also the EF Power Tools Reverse engineer feature. (Soon to be included directly in the EF Tools).

Once the DDEX Provider has been installed, (which consists of placing the provider DLL on disk, and add a number of registry entries), you can add a connection to any SQL Server Compact 4.0 database from the Toolbox, and it will be added to Server Explorer:


(If you do not see the label at the bottom, but potentially a warning instead, one of the requirements for using the provider have not been fulfilled)

The requirements for the provider are:
- Visual Studio 2013 Pro or higher (it is not possible to add 3rd party DDEX providers to any Express edition, please correct me if I am wrong)
- The Simple DDEX provider must be registered (may require a VS restart after launching with version 3.7.1 the first time), you can verify via About dialog in Toolbox.
- The 4.0 DbProvider must be properly registered, use the About dialog in the Toolbox to confirm:


Finally, you can of course confirm the presence of the provider in the Server Explorer Choose Data Source dialog:


To connect via Server Explorer, select the “SQL Server Compact 4.0 (Simple by ErikEJ)” data source, click continue, and all you normally would need to enter is the path to the database file:


Notice that this UI does not let you create a new database file, but you can do that from the “Add Connection…” dialog in the Toolbox.

With that in place, you have access to the exact same Entity Framework Tools features that you had in Visual Studio 2012. Happy coding (and please let me know if your encounter any issue with this feature)