Monday, March 21, 2011

SQL Server Compact Toolbox 2.1–Visual Guide of new features

Celebrating more that 20.000 downloads, version 2.1 of my SQL Server Compact Toolbox extension for Visual Studio 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.

Generate an Entity Data Model (EDMX) in the current project in any applicable project (WPF, WinForms, Class Library)


As you may know, the tooling support for 4.0 is somewhat limited, and therefore you have to use various workarounds to generate an EDMX for a version 4.0 database in projects that are not Web based. Version 2.1 of my Toolbox adds the ability to run edmgen2 via a friendly dialog to generate and include an EDMX file in any applicable project type.

image

image

(I will blog later about how I did this, using the edmgen2 project)

Remove invalid connection definitions from the Toolbox (and Server Explorer)

As you move SQL Server Compact files around and upgrade version 3.5 files to version 4.0, many of the connection entries in Server Explorer / the Toolbox become invalid. Therefore, I have added a menu option to remove broken connections.

image

Option to display binary data in result

image

Preventing invalid values in Max Database Size textbox

By using the NumericUpDown control from the Extended WPF Toolkit, it is no longer possible to enter invalid max database size values.

image

For a similar overview of version 2.0 features, see this blog post.

Updated release of standalone version.

The standalone version for users not running Visual Studio 2010 Pro or higher, is also available in a new release, the main feature being, that it is now a single .exe, with only .NET 4.0 and SQL Server Compact 4.0 RTW required to be present on the system.

As always, please provide feedback, suggestions and reviews at the CodePlex site: http://sqlcetoolbox.codeplex.com

20 comments:

decompiled said...

I installed this, but the icon does not display inside visual studio 2010 professional edition's server explorer. =(

ErikEJ said...

Odd !? Can you open it from the Tools menu?

ErikEJ said...

OK, the build was broken, but the server explorer integration appears broken - will investigate (SP1 issue??)

ErikEJ said...

Could you test if build 2.1.0.6 fixes this issue?

Chris Kaczor said...

I had the same problem as decompiled and the 2.1.0.6 build fixed it.

One question - is it possible to update an existing model in the project once the add-in created it?

Thanks!

decompiled said...

Thanks a bunch for the update. I tested the new release on a few different platforms x86/x64 with various editions of VS 2010 and they are all working now. I am excited to use this on some SQL CE 4 projects! Cheers.

ErikEJ said...

Chris: Thanks for the follow up on the broken build! You can just delete the emdx and designer and generate again - otherwise I am not sure what you mean by "update" !?

ErikEJ said...

Decompiled: Thanks for the testing :-) - looking forward to hear your feedback on the new features.

Chris Kaczor said...

The problem is that if I delete the model and generate it again I lose any customizations I've made to the model.

In my case I've set the "Default Value" setting for most of the properties so that new objects have the properties set to the defaults I want.

If the schema changes (in a 3.5 database) I can go into the Model Browser and select "Update Model from Database" and Visual Studio will update the model to include the schema changes but not lose my changes to the defaults. If I delete and re-generate the model these default value changes will be lost.

I guess I could extend my entity types with a constructor that sets the default values (in another partial class file that won't get re-generated) but that's not how I have it now.

Thanks!

ErikEJ said...

Chris: OK, I see. I will investigate if there are other options apart from what you suggest.

AK said...

I'm getting a "'System.ArgumentException' occurred in System.Data.Entity.dll" with a very basic example of EDMX generation using Visual Studio 2010 SP1, SQL Server CE 4.0, and sql server compact toolbox 2.1.0.7

I can post the test case if required.

AK said...

Just following up on my prior post about the problem with EDMX Generation, here is the test case solution: https://docs.google.com/leaf?id=0BxNHR6xMzvGEMzM4ODU3ZTEtNWZjMC00OTA2LTk5Y2QtNjhmNWJjMjlkMTcz&sort=name&layout=list&num=50

Hope this helps.

AK

ErikEJ said...

AK: You need to rename the config file to app.config, (or include the connection string in an existing app.config), and also remove the quotes from the database file name.

AK said...

Erik: Thank you for your quick response which solved my issue (-:

By the way, the EDMX Generator in compact toolbox 2.1.0.7 generated the config file prefix and the quotes, so I assumed it would work out of the box. Of course you don't want to override a pre-existing app.config which would be highly problematic. But I don't know why the quotes where generated... (I guess it has something to do with those pesky spaces in "Visual Studio 2010" (-:

Thanks for your help and the cool toolbox.

AK

ErikEJ said...

AK: I will look into better app.config/web.config handling for the next release, and also ensure that the connection string gets html encoded.

pn1 dude said...

Thanks for you continued work with SQLCE!

I had some trouble as well with the edmx file not being able to "Update Model from Database..." after I used "SQL Server Compact Toolbox" to "Create Entity Data Model in current Project...". I know that this can be done manually (see http://social.msdn.microsoft.com/Forums/sr-Latn-CS/sqlce/thread/7e2afcaf-c212-4186-a751-f28e0872f01a) so I started poking around and was able to get it working. Here are my steps:

1. In Server Explorer, create your CE40 db as .sdf within the project directory of your solution directory
2. In Server Explorer, create your table schemas of the .sdf (important or there will be nothing for the EDM Generator to create)
3. In Solution Explorer, to the specific project, "Add Existing Item…" (the .sdf that you created in step 1 and 2)
4. In "Local Database File" Dialog where it states DB version is not supported, click OK button
5. In "SQL Server Compact Toolbox" Dialog, right click the .sdf connection and click "Create Entity Data Model in current Project"
6. In "Generate EDM in Project " Dialog change the Model Name to and click OK button
7. In Server Explorer, open .app.config and grab the line between and tags
8. In Server Explorer, open App.Config and insert the captured line (from 7) in between and tags
9. In Server Explorer, in App.Config edit the inserted line (from 8):
name="Entities" connectionString="metadata=res://*/.csdl|res://*/.ssdl|res://*/.msl
-- to --
name="Entities" connectionString="metadata=res://*/.csdl|res://*/.ssdl|res://*/.msl
== and ==
Data Source=\.sdf;""
-- to --
Data Source=|DataDirectory|\.sdf"" <- NOTE the deleted semi-colon
10. In Server Explorer, open .edmx file, right click in white area and select "Update Model from Database…"
11. If in the "Update Wizard" Dialog you are asked to "Choose Your Data Connection" then make sure your App.Config edits are correct (step 11) and the database is actually where you think your created it (step 1). Fix those things and start back at step 10
12. In "Update Wizard" Dialog ("Choose Your Database objects"), click "Finish" button
13. In .edmx file save your updates

In the connection string I changed from the to the for the <>.csdl, <>.ssdl, and <>.mdl although I you don't have to, but I wanted it to be like how VS2010 does it. The ";" in the name file I think is for the password string to follow it, but it you don't have a password string it shouldn't be there.

ErikEJ said...

Dude: I am not sure I follow completely... But otherwise you can just delete and recreate the model.

pn1 dude said...

But the important part of it is that the "EntityContainer Name=" in the edmx file does not match the name in generated modelname.app.config files connection string name. Also if there is no password the ";" at the end of the data source is not needed.

ErikEJ said...

pn1 Dude: in the next release I will update app.config directly. Does that fix some of your issues? The ";" - is that something I can fix??

pn1 dude said...

Not sure if you can or not. I always use a password with my dbs, but noticed that on a test.sdf with no password it created a problem. I also use the DataDirectory rather than a real path to the db. Thought it was worth mentioning.