Friday, October 25, 2013

SQL Server Compact Toolbox 3.6–Visual Guide of new features

After more than 210.000 downloads, version 3.6 (skipping version 3.5 to avoid confusion) of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download. 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


Table Builder

In order to improve the experience for Toolbox users, I have now started adding features to the Toolbox found in the Server Explorer tooling, starting with a Table Builder. This is due to the fact, that Server Explorer support in VS 2012 and VS 2013 is limited (in 2012 SQL Compact 4.0 only, and in VS 2013 none, where as Server Explorer in VS 2010 supports both 3.5 and 4.0).

To use the Table Builder, right click a database, and select Build Table:

image

Then specify the columns to be built, and click Script!

image

A CREATE TABLE script will then be displayed in the SQL editor. In the current version, it is not possible to modify an existing table, maybe next time?

If you have both VS 2012 and VS 2013, feel free to contact me for a way to re-enable Server Explorer support for SQL Server Compact 4.0 in VS 2013.

Report Viewer

You can now view the data in any table via the Microsoft Report Viewer, which also enables you to export data in Excel, Word and PDF format.

image

Right click any table, and select View Data as Report

image

You can right click the report (or use the Toolbar) to Print/Export the table data.

This featured is based on this Stackoverflow reply: http://stackoverflow.com/questions/267091/best-way-to-view-a-table-with-lots-of-columns

Generate Entity Framework 6 Entity Data Model (EDMX) in Visual Studio 2013

This feature is very similar to the existing feature for Visual Studio 2010, allowing you to do Database First development with SQL Server Compact and Entity Framework 6. The new implementation for Visual Studio 2013 takes advantage of assets from the version 6 Entity Framework designer, this includes using the included .tt files to code generate a DbContext and related POCO classes rather than a legacy ObjectContext. To use this feature, first install the EntityFramework.SqlServerCompact NuGet package in your project, and the simply right click the database you would like to generate the model for:

image

image

Then you will be presented with a very basic “Entity Data Model” dialog, which allows you to select which tables to include in the model, and to specify other relevant options:

image
Clicking OK will generate an EDMX file and releated .tt files etc, in the project based on the Entity Framework 6 designer way of doing this. image

Other improvements and bug fixes

Scripting: Improved parsing of SELECT statements
Scripting; Proper scripting of float and real values
Scripting: Fixed some schema diff scripts bugs
UI: Improved display of database size and space available
Integration: Improved VS 2013 RTM support

19 comments:

S Pimenta said...

I cannot install this on VS 2013, says it's incompatible

how to fix this?

ErikEJ said...

You must have VS 2013 Pro, Premium or Ultimate to install 3rd party extensions. Upgrade, or use my standalone Toolbox app.

boomhauer said...

I have it installed in 2013, but cannot connect to existing sdf files- the options are greyed out.

ErikEJ said...

Boomhauer: Install the 3.5 SP2 runtime, as the documentation states

Georges BESSIS said...

Hello Erik,
Many thanks and congratulations for your superb job, wery helpfull for us.
I just execute tne "EDMx generation from an existing sqlce 4.0 db" (beta version) on my current VB.Net 2013 project. It runs without problems but now I have some problems :
1) The application won't Build ! the diag is :
----- Rebuild All started: Project: SM2013, Configuration: Debug Any CPU ------
SM2013 -> C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\Shadow Manager 2014.exe
'xcopy' n'est pas reconnu en tant que commande interne
ou externe, un programme exécutable ou un fichier de commandes.
'xcopy' n'est pas reconnu en tant que commande interne
ou externe, un programme exécutable ou un fichier de commandes.
C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(4429,5): error MSB3073: The command "
C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(4429,5): error MSB3073: if not exist "C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\x86" md "C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\x86"
C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(4429,5): error MSB3073: xcopy /s /y "C:\Users\Georges\Dropbox\SM2013\packages\Microsoft.SqlServer.Compact.4.0.8876.1\NativeBinaries\x86\*.*" "C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\x86"
C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(4429,5): error MSB3073: if not exist "C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\amd64" md "C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\amd64"
C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(4429,5): error MSB3073: xcopy /s /y "C:\Users\Georges\Dropbox\SM2013\packages\Microsoft.SqlServer.Compact.4.0.8876.1\NativeBinaries\amd64\*.*" "C:\Users\Georges\Dropbox\SM2013\MyTileApp\bin\Debug\amd64"" exited with code 9009.
========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========

2) I found no fix, so I just started the saùe operaton : Now it won't run, asking me to Build the pp first (which of corse I cannot do, due to previous issue.

I am just stuck there. I'll appreciate any suggestion to get out of this.
ReGards.
Georges

ErikEJ said...

Georges: EF6 vb support is broken in version 3,6, pls download 3,7 Alpha from sqlcetoolbox.codeplex.com where this is fixed

Unknown said...

Please, I'm using VS 2013 Ultimate and it happens the same that boomhauer says, so where do I finf these he mentioned:

Install the 3.5 SP2 runtime, as the documentation states

Because it appears many features deactivated and I read the docs it carries where it talks about something similar.

Thanks in advance.

ErikEJ said...

Felix: Install 3.7 alpha from here: https://sqlcetoolbox.codeplex.com/releases/view/114453 or install the 3.5 SP2 runtime from https://sqlcetoolbox.codeplex.com/documentation

Unknown said...

Hi, again. I solved the absence of Sql Server Compact 3.5 sp2, I downloaded from here: http://www.microsoft.com/es-es/download/details.aspx?id=5783
Just extract in your favorite folder and install both binaries (in x64 case is recommended both).
Finally, restart VS 2013 (any version mentioned before...)
Good luck.
Great tool.

ErikEJ said...

Felix: Happy you got it working!

Eagle3386 said...

Hi Erik,

I'm thankful for SQL Server Compact Toolbox 3.7 as I'm in the process of migrating a SQLCE 3.5 database in conjunction with Entity Framework 3.5 (aka EF 1) using legacy ObjectContext within a C# project that got upgraded from .NET 3.5 to 4.5.1.

However, I came to a problem I do neither understand nor am I able to fix it myself: clicking "Add EDMX to current project" in order to generate the EF code opens the dialog for naming the model, but confirming it only results in the dialog being closed. No file(s) or code is added/edited and no error or warning is displayed.

Using EdmGen2 to generate a new EDMX file results in the following error:
"The item with identity 'ID' already exists in the metadata collection. Parameter name: item"

The stack trace is:
"at System.Data.Metadata.Edm.MetadataCollection`1.AddInternalHelper(T item, CollectionData collectionData, Boolean updateIfFound)
at System.Data.Metadata.Edm.MemberCollection.Add(EdmMember member)
at System.Data.Metadata.Edm.EntityTypeBase.CheckAndAddMembers(IEnumerable`1 members, EntityType entityType)
at System.Data.Metadata.Edm.EntityType..ctor(String name, String namespaceName, DataSpace dataSpace, IEnumerable`1 keyMemberNames, IEnumerable`1 members)
at System.Data.Entity.Design.EntityStoreSchemaGenerator.CreateEntityType(LoadMethodSessionState session, IList`1 columns, ICollection`1 primaryKeys, DbObjectType objectType, List`1 errors)
at System.Data.Entity.Design.EntityStoreSchemaGenerator.CreateTableTypes(LoadMethodSessionState session, IEnumerable`1 tableDetailsRows, Action`5 createType, DbObjectType objectType)
at System.Data.Entity.Design.EntityStoreSchemaGenerator.CreateTableEntityTypes(LoadMethodSessionState session)
at System.Data.Entity.Design.EntityStoreSchemaGenerator.DoGenerateStoreMetadata(IEnumerable`1 filters, Version targetEntityFrameworkVersion)"

Can you help me or point me in the right direction?
Thanks in advance!

Best regards,
Martin

ErikEJ said...

Martin: There must be something special about your database schema, so the only way forward would be to email me a schema script or the sdf file.

Eagle3386 said...

Erik, thanks for the quick reply and sorry for being late with my follow-up.

But I was able to figure out the source of the problem: all tables had a primary key which was named "PK_ID".

It's no problem for your toolbox, because it just renames them "PK_ID" - and that's how I came to the root of all evil.

So I used your toolbox to drop them all (had to rename them within the script as the number was carried over, though) and then recreated each one in compliance with common SQL naming guidelines ("PK_ID").

So, thank you for your work, because it probably would have taken quite a bit longer without your toolbox.

Since the PKs are fixed now, the EDMX file was generated just fine.
After adding a repository builder for DbContext (for POCO) and configuring it, everything seems good so far.

But as a last question: In order to build the project correctly, I have to create a class for each repository so that EF can map between table data and program objects, right?

Best regards,
Martin

ErikEJ said...

Martin:
1: I do not understand your solution, all the pk names look the same to me.
2: Once you add the EDMX, all required classes with be generated by the .tt files

Eagle3386 said...

Erik, that's my bad. I used angle brackets and it seems that Blogger removed them, because it tried to match them with HTML tags but failed to do so and therefore removed them.

The corresponding lines should be read like this (I used square brackets this time):
---
But I was able to figure out the source of the problem: all tables had a primary key which was named "PK_ID".

It's no problem for your toolbox, because it just renames them "PK_ID[number]" - and that's how I came to the root of all evil.

So I used your toolbox to drop them all (had to rename them within the script as the number was carried over, though) and then recreated each one in compliance with common SQL naming guidelines ("PK_[table's name]ID").
---

Regarding your second helpful advice, I'm totally confused now.
If you're right (and I do believe you are! ;)), then why do I get several compiler errors like this one:

"The type or namespace name 'Project' could not be found (are you missing a using directive or an assembly reference?)"

The corresponding code reads like this:
---
(...)
public class MyDataContextContextWrapper : ContextWrapperBase, IMyDataContextContextWrapper
{
(...)
public IDbSet Projects { get { return Context.Projects; } }
}
(...)
---

Thanks in advance!

ErikEJ said...

martin: Thanks, now it makes sesne. Re your issue: Maybe somthing with namespaces/pluralization, but I can probably best help if you send me a schema file, then I can try to repro.

Eagle3386 said...

Erik, thanks for your help so far.

Since the project is pretty much security-related, I'm not allowed to share data which isn't "abstracted" (like I did by naming the class "MyDataContext" or the property "Projects") with you.

I guess, I'm on my own trying to figure it out. Well, this is what happens if there's no refactoring on a software project at all and multiple developers touched the code, but left used frameworks untouched/without updates.. :-/

Unknown said...

I'm a new C# developer. I've developed and deployed a few C# applications with .sdf databases in VS2010.
Currently, I'm using VS2013 and developing another C# application with .mdf database as there was no support for .sdf.
Now, when my application is almost complete and I'm trying to deploy the application on client machine. It always gives me an error saying the underlying provider failed on open.
My first question is that is it possible for me to deploy the application using .mdf database and without installing SQL Server on client machine?
If it is impossible to run the application with .mdf on client machine without SQL Server installed, what should I do using VS2013?
My first priority now is to convert my .mdf database to .sdf database as I've already used and deployed projects on it.
Thanks

ErikEJ said...

Hi Ahmad: To use .mdf you must install SQL Server Express on the clients PC. To use VS 2013, install my Toolbox, and most features you need should be available. Including several ways of moving from a SQL Server database to .sdf.