Monday, January 12, 2015

“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.

Monday, December 29, 2014

A breaking change in Entity Framework 6.1.2 when using EDMX and SQL Server 2008/2005

Version 6.1.2 of Entity Framework has just been released, as noted on the ADO.NET blog. One of the new features in 6.1.2 is support for the OFFSET..FETCH SQL Server 2012+ paging syntax. This is used when you have LINQ queries with syntax similar to:

db.Albums.OrderBy(a => a.Name).Take(20).Skip(80).ToList();

The issue


But if you are using EDMX based Entity Framework development (and not Code First), this new feature can cause your application to break at runtime. This can happen under the following scenario:

You use SQL Server 2012 (or later, including LocalDb) for development, with Entity Framework 6.1.2 and generate the EDMX model based on a database hosted on that server. This sets the value of ProviderManifestToken="2012" in your EDMX file.

image

You then run the application against a SQL Server 2008 or 2005 instance (for example in test or production), and will get errors like the one reported here: http://entityframework.codeplex.com/workitem/2619:
Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.

I will consider this a breaking change, as before EF 6.1.2 the same paging SQL was generated against all SQL Server versions, but in 6.1.2, special T-SQL using the new OFFSET..FETCH syntax is used when running against SQL Server 2012 or later.

The fix

The fix is to modify your EDMX file, using the XML editor, and change the value of ProviderManifestToken from 2012 to 2008. I found that on line 7 in my EDMX file. After saving that change, the paging SQL will be generated using the “old”, SQL Server 2008 compatible syntax.

image

Whether a EF code change, which would include SQL Server version detection, is the “real” fix is debatable, as it could break expectations of a desired behaviour from the runtime.

Tuesday, December 2, 2014

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

After nearly 340.000 downloads, version 4.1 of my SQLite & SQL Server Compact Toolbox extension for Visual Studio  2015, 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.

Improved SQLite integration


"Add SQLite Connection" dialog improved

The “Add SQLite Connection” dialog has been enhanced to include all the available ADO.NET connection string options, by using SQLiteConnectionStringBuilder. This allows you to easily specify advanced “PRAGMA” statements to be configured, for example:

PRAGMA foreign_keys=ON;

image

Enumerate SQLite Server Explorer connections

If you have the SQLite support for Server Explorer installed (requires Visual Studio Community or higher, will not work with Express). See my blog post here for more info on the SQLite DDEX provider. Those connections are now also listed in the Toolbox list of connections:

image

Enumerate SQLite views and triggers

image

SQLite allows you to define triggers and views, and these are now listed under each SQLite database file, and you can create CREATE and DROP statements for these object types.

SQL Compact runtime no longer required

The Toolbox no longer requires any version of SQL Server Compact to be installed (useful if you only work with SQLite database files)

image

Other

Added button to "About" to re-register DDEX providers (see screenshot above)

Added option to ask for modified scripts to be saved
Added option to enable using multi-line text in "Edit Table Data"

image

Fixes and improvements

- horisontal scrollbar in Explorer window

- less obtrusive handling of DDEX providers installation

- no longer using MessageBox.Show

- update to latest SQLite ADO.NET provider (1.0.94)

- improved parsing/separation of SQL statements spilt with GO in Editor

Monday, November 24, 2014

Using SQLite with the Entity Framework 6 designer - a troubleshooting guide

Many users would like to be able to use the SQLite database with the Entity Framework 6 EDM Wizard and Designer, but looking at Stack Overflow, they often face problems attempting to do that. This blog post describes the steps required to do that, and gives some troubleshooting advice and tips.

Install the SQLite DDEX provider

In order to use the Entity Data Model Wizard to generate a Code First or EDMX Model from an existing database, you must be able to connect to the database from Server Explorer in Visual Studio. In order to do that, a so called DDEX provider for the database and Visual Studio version in use must be installed. 

For SQLite you must download the provider from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki - this page is an unreadable mess, sadly.

For Visual Studio 2013, the download you need is named: sqlite-netFx451-setup-bundle-x86-2013-1.0.94.0.exe  for the current version of the ADO.NET provider, so 1.0.94 might change to 1.0.95 or higher in the future.

image

It is important that the DDEXprovider version matches the version of the current SQLite EF6 NuGet package that you use in your project!

Make sure to enable Visual Studio integration and GAC registration during installation.

You can check which version of the DDEX provider you have installed in “Add or Remove Programs”:

image

Once installed, you can add a connection to your SQLite database from Server Explorer:

image

image

image


Add the correct SQLite EF6 NuGet package to your project

To demonstrate using the SQLite EF6 provider, let’s go through creating a console app in Visual Studio 2013, that uses the Chinook sample database that we connected to above.

To add the SQLite EF6 provider to your project, select the System.Data.SQLite.EF6 package:

image

This package will install all other required dependencies, including Entity Framework 6.1.1.

After installation of this package in a project that target .NET 4.5, your packages.config should look like this:

image

And the system.data section of your app.config file should look like this (UPDATE: notice the additional add entry added to avoid runtime errors!)

image

Now BUILD your project!

You should now be able to add a EDMX or Code First based model to your project, using Add New Item. Data, ADO.NET Entity Data Model:

image

Add some code to the Main method to test that the application also works in runtime:

            using (var db = new ChinookEntities())
            {
                var albums = db.Albums.ToList();
            }

If you are unable to see the connection to the SQLite database we made earlier, here are a couple of tips:

Try building the project and possible changing the build configuration to x86 has worked for me.

Installing the latest Entity Framework 6.1.2 Visual Studio tools have worked very well for me – download link in the blog post here. I think it is due to this fix in the Tools beta.

Hope this helps! 

Monday, October 27, 2014

“Database First” with SQLite in a Universal App using the SQLite Toolbox and sqlite-net

In this blog post I will describe how to use the reverse engineering feature of the SQLite Toolbox to generate code for use with the sqlite-net codebase in a Universal App. A Universal App is a special solution template, that allows you to shared code and resource assets between a Windows Phone 8.1 and Windows Store 8.1 Application, in order to minimize code duplication and increase code reuse, if your application targets both these platforms.

My fellow MVP Nicolò Carandini has a couple of great blog posts on how to get started with SQLite in a Universal App. Since he posted this, the SQLite extension SDKs have been updated and are now directly available in the Extension Manager in Visual Studio 2013, so go and install these two downloads – go to Tools, Extensions and Updates, select Online and search for “sqlite”:

image

Install these three extensions:
- SQLite for Windows Phone 8.1
- SQLite for Windows Runtime (Windows 8.1)
- SQL Server Compact/SQLite Toolbox

The two other SQLite extensions are for version Windows/Windows Phone 8.0. Notice that in order to use the SQLite Toolbox, currently you must have SQL Server Compact 4.0 or 3.5 installed, this will not be required in the next release. Now restart Visual Studio.

Follow the steps in Nicolo’s blog until you have the two SQLite-net files available in the .Shared “project”:

050814_2231_UniversalAp3[1]

Instead of typing the table classes by hand, the SQLite Toolbox can help you generate the required classes for you based on an existing SQLite database file. You could even then include the SQLite database file with your application, for example if any of the tables contain reference data. I describe how to handle reference data in my previous blog post here.

Notice that the sqlite-net classes are not a full blown OR-M, but rather a simple SQL to class translator. It does not support for example change tracking, relationships (foreign keys), Unit of Work, multi column primary keys etc. Those features will be present and available for Universal apps in the new Entity Framework release coming beginning of 2015, EF7.

Open the SQLite Toolbox from Server Explorer or the Tools menu, and connect to an existing SQLite database. For an overview of all SQLite features in the Toolbox, see my blog post here.

image

Select the shared project in Solution Explorer, then right click the database and select “Add sqlite-net Model.cs to current project”

image

The generated code (Model.cs) contains a SQLiteDb class with a method to create all the tables in the database (if you just want to use the existing database file as a template), and class definitions for each table in the database:

    public partial class Album
{
[PrimaryKey]
[Unique(Name = "IPK_Album", Order = 0)]
public Int64 AlbumId { get; set; }

[MaxLength(160)]
[NotNull]
public String Title { get; set; }

[Indexed(Name = "IFK_AlbumArtistId", Order = 0)]
[NotNull]
public Int64 ArtistId { get; set; }

}

As you can see, attributes like MaxLength, Index and NotNull help define the table.


Hope you find this timesaving feature and the other SQLite features in the Toolbox helpful, and please provide any feedback here.

Monday, October 20, 2014

Entity Framework 6 and SQL Server Compact 4.0 – “Proper” private desktop deployment

About a year after I published the blog post Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment it has occurred to me that the solution proposed in that blog post is far from perfect – my apologies! The suggestions for location of the database file etc. are still valid points, however.

TL;DR – Use my new NuGet package for Private Desktop deployment with SQL Server Compact 4.0 and Entity Framework 6, and make sure that:
- The binding redirect is set to: oldVersion="4.0.0.0-4.0.0.1"
- Prefer32Bit project option is disabled

The goal

First, let’s clarify what the goal behind “private desktop deployment” with SQL Server Compact is:

The goal is to be able to drop a folder of files on any PC and just run  a .NET application that includes a self-contained relational database and a sophisticated ORM over that database.

This goal is achievable with SQL Server Compact 4.0 and Entity Framework 6, as long as the PC in question has .NET Framework 4.0 installed.

The issues


First, let us look at the issues with the approach in the above blog post:
1: This approach uses the Microsoft.SqlServer.Compact package, which explicitly uses the non-private assembly version of System.Data.SqlServerCe.dll (which is 4.0.0.0). And it assumes that your project targets AnyCPU. This means that if you have SQL Server Compact installed in GAC, then the version from there will be picked up. That will work fine, as long as you have the exact same build of SQL Server Compact in both GAC and your application folder. But this leads us to issue number 2!
2: The EntityFramework.SqlServerCompact package does not depend explicitly on a release version of the Microsoft.SqlServer.Compact NuGet package and will pick up build 8854, which is 4.0 SP1 CTP1, instead of build 8876, which is 4.0 SP1 proper. (I wish that the 8854 builds were no longer visible on NuGet) – I am working on a fix for this, which is planned for EF 6.1.2. So the chances of not having the same version in GAC and locally are relatively big, and this will cause an error to be thrown, preventing your app from working:
Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality.


Manual fix

If you would like to ensure that your desktop application (console, Windows Forms or WPF) app can run anywhere use proper private deployment, follow these steps (in outline):

1: Install EntityFramework.SqlServerCompact

2: Copy files from Private folder in C:\Program Files (x86) to the project folder (as described in my Private deployment with LINQ to SQL blog post)

3: Make project x86 only (or at least remove the “Prefer 32 bit” option if you include all unmanaged dll files 

4: Disable post build event (via Project Properties/Build events tab) to prevent the 4.0.0.0 dll from being deployed

5: Modify app.config

a) add assembly redirect, as the EntityFramework.SqlServerCompact.dll references version 4.0.0.0, and we want to use 4.0.0.1:

<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
<bindingRedirect oldVersion="4.0.0.0-4.0.0.1" newVersion="4.0.0.1"/>
</dependentAssembly>
</assemblyBinding>
</runtime>

b) With version 4.0.0.0 => 4.0.0.1 in DbProviderFactories section:

 

“Automatic” fix

I have published a NuGet package EntityFramework.SqlServerCompact.PrivateDeployment  that performs most of the actions above, specifically 1, 2 (via a clever package that performs a build step), 3 (via an additional command in install.ps1), 4, and partly 5 (for some reason NuGet generates an incorrect bindingRedirect, so it must be fixed manually). Currently in pre-release, please provide feedback!

image

This package includes both the EntityFramework.SqlServerCompact provider for EF 6.1.1 and the SQL Server Compact 4.0 files for private deployment. I intend to update the package with the upcoming version 6.1.2 release of Entity Framework.

Thursday, September 25, 2014

Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB

Now that SQL Server 2014 and SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2014 (including LocalDB)

I have updated the comparison table from the excellent discussion of the differences between Compact 3.5 and Express 2005 here to reflect the changes in the newer versions of each product.

Information about LocalDB comes from here and SQL Server 2014 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the current “User Instance” feature in SQL Server Express.

Deployment/ Installation Features

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl. ADO.NET Provider

SQL Server
Express 2014

SQL Server 2014 LocalDB

Installation size

2.5 MB download size
12 MB expanded on disk

2.5 MB download size
18 MB expanded on disk

10 MB download, 14 MB expanded on disk

120 MB download size
> 300 MB expanded on disk

32 MB download size
> 160 MB on disk

ClickOnce deployment

Yes

Yes

Yes

Yes

Yes

Privately installed, embedded, with the application

Yes

Yes

Yes

No

No

Non-admin installation option

Yes

Yes

Yes

No

No

Runs under ASP.NET

No

Yes

Yes

Yes

Yes

Runs on Windows Mobile / Windows Phone platform

Yes

No

Yes

No

No

Runs on WinRT (Phone/Store Apps) No No Yes No No
Runs on non-Microsoft platforms No No Yes No No

Installed centrally with an MSI

Yes

Yes

Yes

Yes

Yes

Runs in-process with application

Yes

Yes

Yes

No

No (as process started by app)

64-bit support

Yes

Yes

Yes

Yes

Yes

Runs as a service

No – In process with application

No - In process with application

No - In process with application

Yes

No – as launched process

Data file features

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl. ADO.NET Provider

SQL Server
Express 2014

SQL Server 2014 LocalDB

File format

Single file

Single file

Single file

Multiple files

Multiple files

Data file storage on a network share

No

No

No

No

No

Support for different file extensions

Yes

Yes

Yes

No

No

Database size support

4 GB

4 GB

140 TB

10 GB

10 GB

XML storage

Yes – stored as ntext

Yes - stored as ntext

Yes, stored as text

Yes, native

Yes, native

Binary (BLOB) storage

Yes – stored as image

Yes - stored as image

Yes

Yes

Yes

FILESTREAM support

No

No

No

Yes

No

Code free, document safe, file format

Yes

Yes

Yes

No

No

Programmability

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl. ADO.NET Provider

SQL Server
Express 2014

SQL Server 2014 LocalDB

Transact-SQL - Common Query Features

Yes

Yes

No

Yes

Yes

Procedural T-SQL - Select Case, If, features

No

No

Limited

Yes

Yes

Remote Data Access (RDA)

Yes

No (not supported)

No

No

No

ADO.NET Sync Framework

Yes

No

No

Yes

Yes

LINQ to SQL

Yes

No (not supported)

No

Yes

Yes

ADO.NET Entity Framework 4.1

Yes (no Code First)

Yes

Yes

Yes

Yes

ADO.NET Entity Framework 6 Yes (fully) Yes (fully) Yes (limited) Yes Yes

Subscriber for merge replication

Yes

No

No

Yes

No

Simple transactions

Yes

Yes

Yes

Yes

Yes

Distributed transactions

No

No

No

Yes

Yes

Native XML, XQuery/XPath

No

No

No

Yes

Yes

Stored procedures, views, triggers

No

No

Views and triggers

Yes

Yes

Role-based security

No

No

No

Yes

Yes

Number of concurrent connections

256 (100)

256

Unlimited

Unlimited

Unlimited (but only local)

There is also a table here that allows you to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact 3.5 (which are the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.