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

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:

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:

 

SQLite-net DataAccess.cs improvements

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

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

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:

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 4.2 – Visual Guide of new features

After more than 370.000 downloads, version 4.2 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

The main features in this release have been improved integration with SQL Server & SQLite, and a number of improvements to the SQL editor.

This version is a recommended update for anyone using this tool.

Improved SQL Server integration

I have previously used this old tip from Jon Galloway for prompting users for a connection to a SQL Server/SQL LocalDB/SQL Azure database, but this caused a number of issues when connecting to LocalDB and SQL Azure, so for this release, you will use Server Explorer to create SQL Server connections, and then I will refer to these from the Toolbox.
So to interact with SQL Server from the Toolbox (for example for scripting, generating DGML files or Exporting a full database for SQL Server to SQLite, first connect to the SQL Server via Server Explorer:

Then launch the Toolbox (you can do that directly from Server Explorer), and select the relevant connection from Server Explorer from the dropdown list:

Export SQLite to SQL Server (and LocalDB/Express)

It is now possible to migrate a SQLite database to SQL Server without running creating any intermediate scripts. Simply right click your SQLite database in the Toolbox , and select the “Migrate to SQL Server” option:

Select your Server Explorer SQL Server connection (as described above), and you can follow the progress of the export via the Visual Studio status bar:

Many thanks to @thughesit for help with improving this feature.

As SQLite is much more loosely type than SQL Server, a new option have been added that will truncate string (causing data loss) if a string has been defined with a length like nvarchar(255) and the length of the data stored in SQLite actually exceeds this. This option is disabled by default. Any truncations will be logged to %temp%\SQLiteTruncates.log

You can now also migrate the other way, from SQL Server to SQLite, without any intermediate scripts. Right click the root “Data Connections” node of the Toolbox and select “Export SQL Server to SQLite (beta)”

Choose the Server from the dropdown list:

Choose the tables to Export:

And specify the name for the new SQLite database file, and you can follow the progress of the Export via the Visual Studio status bar.

SQL editor improvements

The query editor has received a number of highly requested improvements:

Improved document handling: There is now both a “Open” (1) “Save” and “Save As” (2) buttons, and each editor window is aware of its underlying document. If there is a document open, it is indicated in the window caption, and a * symbol indicates if the document has unsaved changes:

Keyboard shortcuts: Keyboard shortcuts have been added for "Execute" query: F5, Save: Ctrl+S
and Open: Ctrl+O – a long standing request finally implemented
Export results as CSV: A new button has been added, that will export the first results as a csv (Excel) file, saving you having to copy and paste the results to Excel.

SQLite PRAGMAs: It is now possible to execute PRAGMA commands with SQLite

Other Improvements

Migrating a SQL Compact or SQLite database to SQL Server no longer blocks the UI.
Exporting a database from SQL Server no longer blocks the UI.
No more Error Reporting dialog, I am now using Exceptionless for error reporting instead.
Improved formatting of DataAcccess.cs sqlite-net code
Improved initial directory when adding SQLite and SQL Compact connections
Improved error information when SQLite datetime parsing errors occur (SQLite allows you to put anything in a column defined as datetime, apparently!)
Improved error information when SQL Server script execution errors occur (would mainly happen when exporting from SQLite to SQL Server)
Closing the Edit Data grid now closes the connection to the database, meaning that the Toolbox will have no open connection to the database file when all Edit Grids are closed.
Update to version 1.0.96 of SQLite ADO.NET provider

Bug fixes

Migrate to SQL Server feature was broken
Merge Replication Subscription creation was broken
Edit column should not show "Primary Key" column, and not allow editing of column name
Opening the SQL Editor sometimes crashed VS
FormatException in Explorer window

Using SQLite with the Entity Framework 6.1.x 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

UPDATE: This blog post describes how to generate and use an EDMX file with SQLite. I have not tested “Code First from Database”, and “Generate Database from Model” does not work with SQLite. In addtion, Code First Migrations are not supported with SQLite. If you want these additional capabilities in an embedded database, use the SQL Server Compact provider. If you encounter issues with the SQLite provider, suggest you post an issue here.

In order to use the Entity Data Model Wizard to generate an 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.96.0.exe  for the current version of the ADO.NET provider, so 1.0.96 might change to 1.0.97 or higher in the future.

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”, and also check that no older versions of the DDEX provider are present:

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

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:

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

After installation of this package in a project that target .NET 4.5, your packages.config should look like this (notice that with version 1.0.95 of the SQLite provider, only a single package is required);

And the system.data section of your app.config file should be modified to look like this (notice the added <remove> and <add> statements);

<remove invariant="System.Data.SQLite" />

<add name="SQLite Data Provider"
                     invariant="System.Data.SQLite"
                     description=".NET Framework Data Provider for SQLite"
                     type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

Important: Now BUILD your project!

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

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();
                foreach (var item in albums)
                {
                    Console.WriteLine(item.Title);
                }
            }
            Console.ReadKey();

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

Make sure to build the project and possibly 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 (the version 6.1.1 Tools are included in Visual Studio 2013 Update 4) – download link in the blog post here. I think it is due to this fix in the Tools.

Hope this helps! 

“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

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.

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.

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:

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.

“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”:

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

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.

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

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.

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 (not supported)	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.

SQLite Toolbox 4.0 – Visual Guide of Features

After more than 300.000 downloads, version 4.0 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio  “14”, 2013, 2012 and 2010 is now available for download and can be install from the Tools/Extensions and Updates dialog 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. The focus for this release is support for SQLite databases, and this is also the focus of this blog post.

Overview

This release includes extensive support for SQLite databases, but please bear in mind, that this is v1 in terms of support for SQLite, so there is most likely both room for additional SQLite related features and improvements to the current. I plan to release a “bug fix” update before or around the VS “14” release. Getting used to SQLite has also been a steep learning curve for me!

Root level SQLite features

The about dialog has been enhanced with SQLite related information:

The version of the SQLite ADO.NET provider included with the Toolbox is displayed, and the presence of the DbProvider indicates that SQLite is installed in GAC (not required by the Toolbox)

 

“Add SQLite Connection” will allow you to connect to an existing SQLite database file, or create a new blank one.

“Script SQL Server Database Schema and Data for SQLite” will create a SQLite compatible script (.sql file) from a SQL Server database, allowing you to migrate a database from SQL Server to SQLite. For how to use the generated script, see my blog post here.

Database level SQLite features

When you right click (or press Shift+F10) at the database level, you will get the options above. Let us go through them one by one (notice that all these features are also available for SQL Server Compact database files!)

“Open SQL editor” – will open a SQL editor, where you can execute ad hoc SQL statements, and this editor is also used for any scripts created at the table level 8see below). The editor has a toolbar with various buttons:

Open: Open a saved script (.sql) file
Save As: Save the text in the editor as a SQL file
Execute: Run the commands in the editor, and display results below – results can be displayed as either text or in a grid (grid is slower) – set via Options in the Toolbox,
Estimated plan: Will run EXPLAIN QUERY PLAN for the statements
Search: Search for text in the editor window

The editor status bar displays: Query duration, number of rows returned, and SQLite engine version in use.

“Build Table” gives you a UI to generate CREATE TABLE statements:

“Script Database” will generate various .sql files that you can run using sqlite3.exe.

“Create Database Graph” will generate a DGML interactive graph of your tables and their relations and columns:

“Create Database Documentation” will generate a html file with documentation of all tables in the database:

“Add sqlite-net model.cs to current Project” will code generate a model.cs file with classes for each table in the database, for use with the sqlite-net Nuget package. I will blog in detail about this later, think of it a basic productivity “scaffolding” in this release.

“Database information” generates a script with basic database information in the SQL editor.

”Copy database file” will allow you to paste the file from the file system into your project, for example if you want to include a database file as content with your app.

”Remove connection” will remove the connection from the Toolbox (will not affect the underlying file).

 

Table level SQLite features

(Notice that all these features are also available for SQL Server Compact database files!)

"Edit Top x Rows” will open the table in a grid, and allow you to edit and add data to the table, provided it has a primary key.

In addition to standard Navigation, Add, Delete and Save buttons, the bottom toolbar also contains a Quick Search and free text SQL feature. You can change the limit of rows via Options.

“View Data as Report” will open a Microsoft Report Viewer with the table data. In addition to view and print the data, you can also export as PDF, Excel and Word:

To use this feature, you may have to install the Report Viewer, which you can download from here.

“Script as …” will generate a DML (data manipulation language) and DDL (data definition language) script in the SQL editor for the selected table. In addition, Script as Data (INSERTs) will script all data in the table as INSERT statements in the SQL editor.

“Import Data from CSV” will import a CSV file, that has heading that matches the column names in the current table, and generate INSERT statements.

”Rename” will (unsurprisingly) rename the current table.

Other fixes and improvements

Support for VS "14"
Improved saving of connections with "complex" passwords
Improved handling of missing MS ReportViewer dll files

Getting and building the Entity Framework 7 alpha bits – step by step

The Entity Framework version 7 bits are slowly coming together, for a demo of some features, look at the presentation by Rowan Miller from the Entity Framework Team at the recent dotNetConf.

This blog post will show you how to get the source code and build it on your own machine, in order to better understand EF 7, and maybe give it a early run. Keep in mind that a number of features are still broken/not implemented, keep an eye on the list: https://github.com/aspnet/EntityFramework/wiki/Getting-Started-with-Nightly-Builds

There are instructions on Getting and Building the Code available on the EF7 Wiki: https://github.com/aspnet/EntityFramework/wiki/Getting-and-Building-the-Code

But I have stumbled upon a few issues, that might also hit you.

1: Get the code

You can either do this as clone of the repository as stated in the link above, you you can simply download a .zip file via the Download .zip button on this page.

If you download a .zip, DO use a tool like 7Zip to unpack the file to a folder, do NOT use the built-in Windows zip extractor, in order to avoid files being marked as “Blocked”.

Either way, you will now have a folder called “EntityFramework” on your system, with contents like this:

2: Initialize

This will download all the required NuGet packages that EF7 depends on, and reference them from the projects.

Before you do this, launch Visual Studio 2013 and verify that:

You are running VS 2013 Update 2 (check Help, About):

In Package Manager Settings, check that the official NuGet feed is configured and enabled (the AspNetVNext feed is added by the build process)

Now launch a VS 2013 Developer Command Prompt as Administrator:

Now navigate to the “EntityFramework” folder and run:

build initialize

If the build initialize process succeeds, you will see this message:

Build succeeded.

If the message does not appear, double check the VS 2013 required settings.

3: Build and run tests

The next step will build the EntityFramework projects, and run all the tests in the solution. Before today, running unit tests were not possible on non-US systems, but I and MrJingle have had a few pull requests accepted to enable this (this, this and this).

Let me know if you encounter any related issues, and I will be happy to submit a pull request to get it fixed.

Again, from a VS 2013 Administrator command prompt, run:

build

If the build process succeeds, you will see this message:

4: Work in Visual Studio

You can of course also open the solution in Visual Studio and build there.

In order to run tests in Visual Studio, I found that the built-in Test Window did not detect any tests on my PC, but TestDriven.NET worked well.

You can now add some unit tests of your own in order to give EF7 a run!

Hope you manage to build, and good luck exploring the EF7 source.