Friday, January 10, 2014

SQL Server Compact Toolbox 3.7–Visual Guide of new features

After more than 235.000 downloads, version 3.7 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 new features included in this release, many suggested by users of the tool via the CodePlex issue tracker. A major “theme” for this release has been to improve the experience for Visual Studio 2013 users, which has no Server Explorer/DDEX support for SQL Server Compact at all.

Add Column, Index and Foreign Key to table

As Server Explorer support is completely missing in VS 2013, and only available for 4.0 in VS 2012, I have added dialogs for building script to add columns, indexes and foreign keys to a table. Right click any table, and select the desired dialog:

image

image

image

  image

Note that currently the Index and Foreign Key dialogs only support a single column, I am aware of this, so please vote here and/or here.

Generate Entity Framework 6 Entity Data Model with VS 2012 and SQL Compact 3.5

The code generation menu items have now been moved to a sub-menu:

image

In addition, as the Entity Framework Tools are now available for Visual Studio 2012 as well as Visual Studio 2013, it is now possible to generate a “database first” Entity Framework model (EDMX) based on a SQL Server Compact database in VS 2012. And I have just contributed an Entity Framework SQL Server Compact 3.5 provider (available as prerelease on NuGet), that allows you to use SQL Server Compact 3.5 with Entity Framework 6. 

Copy database file

It is now possible to copy a database file, and then for example paste it into your project, should you so desire, simply by pressing Ctrl+C on the selected database, or selecting the context menu item:

image

Data only export to SQL Server

The current data export feature has been enhanced to support SQL Server and IDENTITY columns by using DBCC CHECKINDENT instead of the unsupported ALTER TABLE statements currently used.

image

New "ALTER column" context menu item

To help you modify columns, a new context menu to script ALTER COLUMN statements has been added:

image

“Database designer”

I am now hosting a web based database designer based on http://code.google.com/p/wwwsqldesigner/ (which implements only the SQL Server Compact column data types). Current state is “alpha” (and there are known issues with IE 11)!

Server DGML now has table selection
When generating a database diagram for a SQL Server database, you can now select which tables to include in the graph, which can be helpful when diagramming databases with many tables. I have already blogged about this earlier.
Other improvements and bug fixes

Missing database files indicated in tree view
No longer depends on the SQL Server Compact 3.5 runtime, and no longer depends on DbProviderFactory, simply requires either 3.5 or 4.0 in GAC
Each Query opens in new window
BUG: "Upgrade to 4.0" was blocked in Visual Studio 2013
BUG: Not ideal support for Dark Theme in VS 2012/2013 (improved slightly)
BUG: EF6 EDMX code generation with VB was broken

8 comments:

Dash said...

Just installed VS 2013 and this update - Using EF I do not have the option of generating a SQL CE database (not listed in the providers), is it this addon that deals with this?

ErikEJ said...

dash: Do you mean Generate database from model? Then no... I am working on a solution... But you can build a new databasevwith the tToolbox, yes, just choose Add connection...

Jeff said...

The Linq2Sql generator menu item is disabled on my installation... what do you make of it?

ErikEJ said...

None: that feature requires the 3.5 runtime to be installed, I will try to make it clearer

Rick said...

I am trying to figure out Microsoft's direction with SQL Server CE. I need to architect a solution that has a very small footprint for a sometimes connected device. Since Microsoft isn't releasing anything new for CE I'm not sure where to turn. Any suggestions?

ErikEJ said...

Rick: What is wrong with the current tech, are you missing any features?

Gene said...

I added a new column to my table and regenerated a new databaseContext to replace the old one. I get this error. What am I missing. Thanks

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in Microsoft.Phone.Data.Internal.ni.dll

ErikEJ said...

Gene: Suggest you ask in a forum, and share mor inforamtion, like the steps you did when adding the column, and some code