SQL Server Compact & SQLite Toolbox 4.3 – Visual Guide of new features

After more than 415.000 downloads, version 4.3 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the CodePlex issue tracker

This new version was released in July at the time that Visual Studio 2015 went live, and is of tested with Visual Studio 2015 – keep in mind that all extensions now work with the free Visual Studio Community Edition. This release has a couple of new features that improves on the SQLite support originally added in version 4.0

Scan solution for SQLite database files

image

The “Add Connections from Solution” feature has been enhanced to scan all projects in the current solution for any SQL Server Compact and SQLite files. If any are found (based on the file patterns defined in Options), the Toolbox will attempt to add them as new connections.

The current file patterns are defined as shown below, but you can change them to anything you like:

image

Rename Connection name

The names of connections currently defaults to simply the file name of the database file. This becomes an issue, if you have many same named files in different folders connected. Therefore, you can now rename the Connection name:

image 

SQLite-net DataAccess.cs improvements

The SQLIite-net code generation feature has received a couple of small updates based on user feedback:

image

The generated classes will also contain any views defined in the SQLite database, and nullable values will be mapped accordingly (for example int?).

NULL value display

image

NULL values are now displayed as ‘NULL’ in Query results text and grid. Remember that you can see query results both as text and in a grid, the default is text for performance reasons, but this can easily be changed in Options:

image

Also notice that if you prefer the previous behaviour, this can be changed here, via the ShowNullValuesAsNULL option.

Other improvements

Unique indexes now always scripted as UNIQUE constraints (a long standing omission, which hit me when I was creating the reverse engineering for the SQL Server Compact providers for Entity Framework 7)

As usual, any feedback and comments are welcome, and if you like my free tools, I am always very grateful for a review on Visual Studio Gallery

“SQL Server Compact & SQLite Toolbox” related news

This blog post contains three bits of news, that all relate to my SQL Server Compact / SQLite Toolbox Visual Studio add-in

Visual Studio 2013 Community Edition

Microsoft recently released Visual Studio 2013 Community, a free, full featured edition of Visual Studio, has essentially all features found in VS Professional, expect some Office/SharePoint project templates, and is free for many scenarios (please check the licensing requirements). Previously, users of the SQL Server Compact Toolbox with VS Express had to use the standalone edition of the Toolbox. If you replace your Express edition with Community, this is no longer the case, as it supports all VSIX extensions from the VS Gallery / Extensions and Updates dialog in Visual Studio. A very bold move from Microsoft, and a major  boost for open source/hobby developers. If you have any questions about this VS edition, start by reading the Q&A here

image

Visual Studio Auto Updater

Mads Kristensen, a Microsoft Program Manager (and fellow Dane), recently released the “one extension to rule them all”, the Visual Studio Auto Updater extension. It allows you to specify which of your Visual Studio extensions you want to automatically update when a new version is released. I have submitted a pull request to have the SQL Server Compact / SQLite Toolbox included in the list of extensions that are always updated. Highly recommended, ensures that you Visual Studio installation is always fresh.

image

SQLite Toolbox on the Channel 9 “Visual Studio Toolbox” show

In early November last year I had the opportunity to appear on the popular Channel 9 show “Visual Studio Toolbox” hosted by Robert Green. In the show, I present the new SQLite support in the Toolbox. A good intro if you want to get started using the Toolbox.

VSToolbox2

Version 4.2 preview

The next release of the Toolbox, version 4.2, is currently available in preview from CodePlex. The main focus for this release is a number of (overdue) improvements to the SQL query editor:

Editor2

1: The editor now has proper “file” handling, with Save and Save As buttons, and the saved file name appearing in the tab caption.

2: Keyboard shortcuts have been enabled, allowing you to use F5 to execute queries, and use Ctrl+O to open scripts, and Ctrl+S to save scripts.

3: A button to export the current result as CSV (Excel) format has been added.

Try out the preview, and let me know if you have any suggestions or find any issues via the CodePlex issue tracker.

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

After nearly 290.000 downloads, version 3.7.3 of my SQL Server Compact Toolbox extension for Visual Studio  “14”, 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.

New features

Export SQL Server database to SQLite script (beta)

This feature enables you to create a script of all tables and data in an existing SQL Server database, that can be run using the SQLite3.exe tool as described in my blog post here, allowing you to quickly migrate to SQLite, for example for use in Windows Store/Phone Apps.

SQLIteExport

“Set Password” option added to Maintenance menu

This feature enables you to set or change the password of an existing database.

setpw

Enable entry of multiline text in DataGridView

This feature allows you to enter multi line text in the data edit grid, by pressing Shift+Enter to get to a new line.

Support for VS “14” CTP

Support for VS “14” was actually already enabled, but there was a minor issue with registration of the simple DDEX providers, that has now been fixed.

UPDATE: Microsoft has blocked add-ins that claim to support VS 14 from VS 2013, so in the meantime you can download a build for VS “14” from here:

https://sqlcetoolbox.codeplex.com/releases/view/123666

image

 

Bug Fixes

Result Grid column headers were displayed without the first _ (underscore) character if they had one

image

Download Count now fetched async, so the About dialog opens faster.

image

Add Column – defaults to Allow Null

image

“Migrate” and “Export” features broken for large databases with multiple script files

image

image

Entity Framework 6 and SQL Server Compact (9) –SqlCeFunctions and DbFunctions

One of the major improvements to the SQL Server Compact Entity Framework provider in version 6 is the addition of the SqlCeFunctions class, and enhanced support for the so-called “canonical” database functions (DbFunctions/EntityFunctions).

Just to repeat, the SQL Server Compact providers are delivered in the EntityFramework.SqlServerCompact (for 4.0) and EntityFramework.SqlServerCompact.Legacy (for 3.5 SP2) NuGet packages.

The DbFunctions (previously named EntityFunctions) in the System.Data.Entity namespace define a set of (CLR) methods that expose conceptual model canonical functions in LINQ to Entities queries.

Before EF6.1, the SQL Server Compact provider only supported the functions defined for Entity Framework 1, not it supports all the functions listed here, except the following: Date/Time functions with micro and nanosecond precision (as only datetime exists as a data type in SQL Server Compact), StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes.

This means you can now have the SQL Compact engine excute LINQ to Entities expressions like String.Contains, String.EndsWith, String.Left etc.

The System.Data.Entity.SqlServerCompact.SqlCeFunctions class allows you to call database specific functions directly in LINQ to Entities queries, and the following functions have been implemented (for documentation of these, see the equivalent functions for SQL Server listed here):

String functions
CharIndex
NChar
PatIndex
Replicate
Space
StringConvert
Stuff
Unicode

Math functions
Acos
Asin
Atan
Atan2
Cos
Cot
Degrees
Exp
Log
Log10
Pi
Radians
Rand
Sign
Sin
SquareRoot
Tan

Date functions
DateAdd
DateDiff
DateName
DatePart
GetDate,

Other
DataLength

So you can compose LINQ to Entities queries like:

var result = db.Album.Where(a => SqlCeFunctions.DataLength(a.Title) > 20).ToList();

And the resulting SQL will look like this:

SELECT
    [Extent1].[AlbumId] AS [AlbumId],
    [Extent1].[Title] AS [Title],
    [Extent1].[ArtistId] AS [ArtistId]
    FROM [Album] AS [Extent1]
    WHERE (DATALENGTH([Extent1].[Title])) > 20

Entity Framework 6 & SQL Server Compact (8) –Logging SQL statements

I previously blogged about how to enable logging of INSERT/UPDATE/DELETE statements with SQL Server Compact and Entity Framework 4. Keep in mind that there is no “SQL Profiler” equivalent for SQL Server Compact, which makes this feature a vital tool for debugging, understanding and improving queries and CUD operations.

In Entity Framework version 6.1, this has been made much simpler, thanks to the implementation of the new System.Data.Entity.Infrastructure.Interception.DatabaseLogger class. In version 6.0, you could enable logging in code by using

db.Database.Log = Console.Write;

In version 6.1, it is now possible to enable logging by adding entries to your app.config file, making it possible to add logging to a deployed application.

Enabling logging is as simple as adding the following section to your app.config/web.config file in the entityFramework section.

<interceptors> 
<interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
<parameters>
<parameter value="C:TempLogOutput.txt"/>
<parameter value="true" type="System.Boolean"/>
</parameters>
</interceptor>
</interceptors>

The first parameter is the name of the file to log to (if this is not specified, logging will be sent to Console.Out). The second parameter specifies that the file should be appended to rather than being overwritten (default).

I am sure you will find this new feature useful.

Entity Framework 6 & SQL Server Compact (7) –New features and fixes in version 6.1

Entity Framework 6.1 is now available on NuGet, and this blog post by Rowan Miller, Program Manager for the Entity Framework team, covers the major new features in this release. Most of the new features are also applicable to SQL Server Compact users, for example Code First Model from Database in the EDM Wizard, which is also available with SQL Server Compact if the latest SQL Server Compact Toolbox version is  installed. Rather than repeating the blog post from Rowan, this blog post will look at some of the minor features in EF 6.1 affecting SQL Server Compact. (A few of which I have had Pull Requests accepted)

#824 Designer: DDL Generation Template for SQL CE should separate each statement with GO

This fix allows you to run the script created by the “Generate Database from Model” EF Tools feature in the latest version of the Toolbox, as described here.

#898 Reverse Engineer Code First: Using database name as entity container name causes issues when database name has invalid chars (bad for SQL Compact)

This fix effectively makes “Code First from Database” work with SQL Server Compact, as it was previously broken.

#1322 SqlCePropertyMaxLengthConvention should be updated to reflect changes in the standard PropertyMaxLengthConvention

This fix makes code more portable between database engines

#1852 Migration for EF 6.0 wrong migration script for altering property (required->non required) for SQL CE 4

This fix ensures that NULL or NOT NULL is always specified in migration SQL when changing between [Required] and not required

#1863 Add DbProvider registration to SQL Server Compact NuGet package

This fix enables private deployment of SQL Server Compact, both for use in desktop, web and unit test scenarios (and makes the workaround I describe here obsolete)

#1878 Add SQL Server Compact 3.5 provider + NuGet package

This feature enables you to use SQL Server Compact 3.5 with Entity Framework 6.x, via the EntityFramework.SqlServerCompact.Legacy NuGet package, and includes all the same fixes and features as the SQL Compact 4.0 provider. I blogged about the new provider earlier.

#1962 SQL Server Compact data provider for EF does not support some Entity SQL canonical functions

It turned out, that the current SQL Server Compact data provider only supported the “canonical” functions defined for EF 1.0  – this fix adds support for most applicable functions from EF 4. The following functions are not supported due the SQL Server Compact having a limited number of data types and built-in functions:  StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes, CreateDateTimeOffset, CreateTime, AddMicroseconds, AddNanoseconds, DiffMicroseconds, DiffNanoseconds

SQL Server Compact Toolbox 3.7.2–Visual Guide of new features

After more than 260.000 downloads, version 3.7.2 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. The focus for this release is a number of features that help improve the experience when using the new support for SQL Server Compact 3.5 (supplied by yours truly) in Entity Framework 6.1 and the related Entity Framework 6.1 Tools.

Run “Generate from Model” script without errors

When using the “Generate Database from Model” option in the Entity Data Model Tools, the tool generates a script to DROP and CREATE the generated objects in the database. If you have tried to run the script until now in the SQL Server Compact Toolbox, the script would fail. A new option “Ignore DDL errors” in the Toolbox SQL Editor will allow you to run the script without any errors:

clip_image001

DDEX install requires VS restart

The simple DDEX providers supplied by the Toolbox for VS 2012 and VS 2013 are installed when the Toolbox is activated, and therefore require VS to be restarted to be used. A notification is now available to notify you of this. You can read more about the DDEX providers in my previous blog post here and here.

clip_image002

(Also, notice the new, “modern” icons in the toolbar of the Toolbox.)

Preserve SQL Server date types

A new option is now available to control scripting of the SQL Server date, datetime2 and datetimeoffset data types. The default behaviour is to always convert these column types to datetime (SQL Server Compact does not have these datatypes). But this can cause data loss (as datetime has limited precision) or overflow errors, as datetime only supports dates from 1753 to 9999, unlike date, datetime2 and datetimeoffset, that all support dates from 0001 to 9999.

clip_image003

Improved About dialog

The About dialog has been improved for readability, and contains a counter for the current number of Toolbox downloads:

clip_image004

Bug fixes

EDMX generation feature failed if only VS 2012 and not VS 2013 was installed.

“Refresh” of tables, including refresh after schema changes, was broken

Scripting API improvements

– improved SQLCE runtime check

– datetimeoffset columns scripted as datetime by default now, not nvarchar()

– sql_variant and hierarchyid handling improved, to avoid getting wrong ordinal for identity and timestamp

– sqlite indexes are now always given unique names

The scripting API improvements are also included in the latest version of my SQL Compact command line export tool and Scripting API, available here.

Feedback

As usual, please provide feedback if you have any feature requests, ideas or encounter any issues (or even bugs!!) by using the CodePlex Issue tracker.

Entity Framework 6 & SQL Server Compact (6)–Entity Framework Reverse POCO Code First Generator

The Entity Framework Reverse POCO Code First Generator is a customizable T4 template, that reverse engineers an existing database and generates Entity Framework Code First POCO classes, Configuration mappings and DbContext It is available directly in Visual Studio via the Tools, Extensions and Updates menu item. It can be used with VS 2010 or later, and the latest version supports Entity Framework 6 and both SQL Server and SQL Server Compact (both 3.5 and 4.0).

This T4 template is similar to the Entity Framework Power Tools “Reverse Engineer Code First” feature, but much more versatile and flexible, and constantly updated by the developer. You can see how this project compares with the Power Tools feature here.

To get started using the template with SQL Server Compact, follow these simple steps:

Install the relevant Entity Framework NuGet package:

  • EntityFramework.SqlServerCompact.PrivateConfig (EF 6.0.2 with SQL CE 4.0)
  • EntityFramework.SqlServerCompact (EF 6.1 when released with SQL CE 4.0)
  • EntityFramework.SqlServerCompact.Legacy (EF 6.1 when released with SQL CE 3.5 – currently available as beta)

Add a connection string to your app.config/web.config:

<connectionStrings>
<add name="MyDbContext"
providerName="System.Data.SqlServerCe.4.0"
connectionString="Data Source=C:datamydb.sdf" />
</connectionStrings>

In Visual Studio, right click project and select “add – new item”.

Select Online, and search for “reverse poco”. 

Select the “EntityFramework Reverse POCO Code First Generator” template

Give the file a name, such as Database.tt and click Add.

Edit the Database.tt file and specify the connection string name as “MyDbContext” which matches your name in app.config.

Save the Database.tt file, which will now generate the Database.cs file.

There are many options you can use to customise the generated code. All of these settings are in the Database.tt files with comments attached.

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

image

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:

image

Prefer DDEX

image

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.

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:

image

(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:

image

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

image

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:

image

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)