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.

Feature

SQL Server Compact 3.5 SP2

SQL Server Compact 4.0

SQLite, incl SQLite ADO.NET Provider

SQL Server
Express 2012

SQL Server 2012 LocalDB

Deployment/ Installation Features

         

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

         

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

         

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.

Monday, August 25, 2014

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:

image

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)

image 

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

image

“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

image

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:

image

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:

image

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

image

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

image

“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

image

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

image

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:

image

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.

image

“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

Monday, June 30, 2014

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:

image

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

image

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

image

Now launch a VS 2013 Developer Command Prompt as Administrator:

image

Now navigate to the “EntityFramework” folder and run:

build initialize

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

Build succeeded.

image

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:

image

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.

Wednesday, June 18, 2014

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

Thursday, June 5, 2014

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