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

Entity Framework 6 (and SQL Server Compact) (5)–Entity Framework 6 extensions

This is a list of some of all the nice Entity Framework 6 extensions out there that expand the functionality of the Entity Framework 6 runtime. Code generator tools and Frameworks using Entity Framework are not included here, only libraries that extend DbContext or similar.

The EF team has lists of Tools and 3rd party EF providers here: http://msdn.microsoft.com/en-us/data/ee712907

Please let me know if I have missed anything, and I will add it to the list.

Store Functions for EntityFramework CodeFirst 

Why: This project uses the basic building blocks to build end to end experience allowing using TVFs in Linq queries and invoking stroed procedures without having to drop down to SQL. (By EF Team member)
Project: https://codefirstfunctions.codeplex.com/
NuGet: Coming soon

Interactive Pre-Generated Views for Entity Framework 6

Why: An alternative is a solution where views are created dynamically only when needed (i.e. views don't exist or are out dated) and then persisted and made available for use by other instances of the same application (be it running in parallel or the same application after a restart). This is exactly the problem Interactive Pre Generated Views project is trying to solve. (By EF Team member)
Project: https://efinteractiveviews.codeplex.com/
NuGet: https://www.nuget.org/packages/EFInteractiveViews

Second Level Cache for Entity Framework 6.1

Why: Entity Framework does not currently support caching of query results. A sample EF Caching provider is available for Entity Framework version 5 and earlier but due to changes to the provider model this sample provider does not work with Entity Framework 6 and newer. This project is filling the gap by enabling caching of query results for Entity Framework 6.1 applications.  (By EF Team member)
Project: https://efcache.codeplex.com/
NuGet: http://www.nuget.org/packages/EntityFramework.Cache

Entity Framework 6 Contrib

Why: https://ef6contrib.codeplex.com/documentation
Project: https://ef6contrib.codeplex.com/
NuGet: https://www.nuget.org/packages/EF6.Contrib/

Entity Framework Extended Library

Why: Batch Update and Delete, Future Queries, Query Result Cache, Audit Log

Nuget: http://www.nuget.org/packages/EntityFramework61.Extended (forked)
and http://www.nuget.org/packages/EntityFramework6.Extended (forked)
and https://www.nuget.org/packages/EntityFramework.Extended/
Project: https://github.com/loresoft/EntityFramework.Extended and https://github.com/AdvancedREI/EntityFramework.Extended


EntityFramework.Utilities

Why: EntityFramework.Utilities provides some batch operations for using EF that the EF team hasn't yet added for us. (CUD)
Project: https://github.com/MikaelEliasson/EntityFramework.Utilities
NuGet: https://www.nuget.org/packages/EFUtilities/

EntityFramework.BulkInsert

Why: Bulk insert extension for EntityFramework. Insert large amount of data over 20 times faster than regular insert. Supports DB first and code first.
Project: https://efbulkinsert.codeplex.com/
NuGet: https://www.nuget.org/packages/EntityFramework.BulkInsert-ef6

Trackable Entities

Why: Visual Studio 2012 and 2013 project templates and NuGet packages for client-side entity change-tracking and server-side persistence with Entity Framework extensions.
Project: http://trackableentities.github.io/
NuGet: https://www.nuget.org/packages/TrackableEntities.EF.6/

EntityFramework.MappingAPI

Why: EntityFramework mapping API gives you ability to know the database info behind the context.
Project: https://efmappingapi.codeplex.com/
NuGet: https://www.nuget.org/packages/EntityFramework.MappingAPI

EntityFramework.Filters

Why: Create and configures filters applied to queries across a DbContext
Project: https://github.com/jbogard/EntityFramework.Filters
NuGet: https://www.nuget.org/packages/EntityFramework.Filters/

EntityFramework.DynamicFilters

Why: allow you to create global and scoped filters for Entity Framework queries. The filters are automatically included in every query and can be used to support use cases such as Multi-Tenancy, Soft Deletes, Active/Inactive, etc.
Project: https://github.com/jcachat/EntityFramework.DynamicFilters
NuGet: https://www.nuget.org/packages/EntityFramework.DynamicFilters/

EntityFramework.Seeder

Why: Using EntiyFramework.Seeder, you can seed your database from CSV files.
Project: https://github.com/dpaquette/EntityFramework.Seeder/
NuGet: https://www.nuget.org/packages/EntityFramework.Seeder.EF6/

EntityFramework.Triggers

Why: Add triggers to your entities with insert, update, and delete events. There are three events for each: before, after, and upon failure.
Project: https://github.com/NickStrupat/EntityFramework.Triggers
NuGet: https://www.nuget.org/packages/EntityFramework.Triggers/

EntityFramework.IndexingExtensions

Why: Adds fluent API to model indexes
Project: https://github.com/mj1856/EntityFramework.IndexingExtensions
NuGet: https://www.nuget.org/packages/EntityFramework.IndexingExtensions/

Z.EntityFramework.Plus.EF6

Why: Entity Framework Bulk Operations & Utilities (EF Bulk SaveChanges, Insert, Update, Delete, Merge | LINQ Query Cache, Deferred, Filter, IncludeFilter, IncludeOptimize | Audit)
Project: https://github.com/zzzprojects/EntityFramework-Plus
NuGet: https://www.nuget.org/packages/Z.EntityFramework.Plus.EF6/

SQL Server Data Tools for Visual Studio – versions, downloads and compatibility

In Visual Studio, SQL Server enables four different project categories under the umbrella of “SQL Server Data Tools” that target various components in the SQL Server product. There is a lot of confusing advice and no single overview of the various versions and downloads available. This blog post hopes to remedy this.

Project types

The four project types are:

SQL Server Analysis Services (SSAS) for creation of various types of Analysis Services models

SQL Server Reporting Services (SSRS) for creation of reports and related artifacts

SQL Server Integration Service (SSIS) for creation of data integration and workflow solution, including ETL (extract, transform, load) workflows

The three project types above are distributed together under the name: SQL Server Data Tools – Business Intelligence (SSDT-BI)

SQL Server Data Tools  (SSDT) database project and SQL Server Object Explorer (SSOX) provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. Database developers can use SSOX in VS to easily create or edit database objects and data, or execute queries.

Releases

An overview of current releases download links are available on the MSDN page here.

The following packages are available:
SSDT-BI for Visual Studio 2012 (SSDT-BI 2012)
SSDT-BI for Visual Studio 2013 (SSDT-BI 2013)

SSDT for Visual Studio 2012 (SSDT 2012)
SSDT for Visual Studio 2013 (SSDT 2013)

Version support

Once you have determined what kind of project is relevant for your task at hand, you need to decide which download and Visual Studio version to use, based on the SQL Sever version that you target:

SQL Server platform	2005 / 2008	2012	2014	Azure SQL DB
Tool/Project
Reporting
SSDT-BI 2012 SSRS	No	Yes	No	N/A
SSDT-BI 2013 SSRS	No	Yes	Yes	N/A
Analysis
SSDT-BI 2012 SSAS	No	Yes	No	N/A
SSDT-BI 2013 SSAS	No	Yes	Yes	N/A
Integration
SSDT-BI 2012 SSIS	No	Yes	No	N/A
SSDT-BI 2013 SSIS	No	No	Yes	N/A
SQL DB Projects and SSOX
SSDT 2012	Yes	Yes	Yes	Yes
SSDT 2013	Yes	Yes	Yes	Yes

NOTE: For SQL Server 2008 based SSRS, SSAS and SSIS projects, you use Business Intelligence Development Studio, which is installed from the SQL Server installation disk.

NOTE: If you open a SSIS project created in SSDT-BI 2012 with SSDT-BI 2014, a one way upgrade will occur, so be careful and use source control. More info here.

Hope this helps

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.

Merge Replication with SQL Server Compact 3.5 SP2 and SQL Server 2014 and 2012

Time for a SQL Server 2014 related blog post to celebrate the release of this product. (And to celebrate me entering my 6th year a s Microsoft MVP).

SQL Server 2012

SQL Server Compact based Merge Replication is "supported" by SQL Server 2012, but there are a few "gotchas" that you need to be aware of before implementing this.

This blog post covers these in detail. As you may know, there are 3 components involved in a replication setup: Client, Web Server and Database Server. For each of these components, certain requirements must be met for the solution to work. And each component does not “just work” out of the box.

Client (Windows desktop/server/Mobile)

The client, which can be a Windows desktop or Windows Mobile device, must run a recent build of the SQL Server Compact runtime files. The file version/ServerVersion of the runtime must be 3.5.8088 or later. You can find download locations for the installers of the recent runtime here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

Web Server (IIS)

The SQL Server Compact agent (sqlcesa35.dll) must be version 3.5.8088 or later. You can find download locations for the installers of the most recent Server Agent here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

In addition if you have a separate web and database server, you must install the SQL Server 2012 Database Engine component (and most likely you want to disable the SQL Server service after install) in order to get the required SQL Server COM files installed. 

You must also install SP1 and the latest CU, as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

Database Server (SQL Server 2012)

You must install Service Pack 1 and the latest CU (Cumulative Update), as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

SQL Server 2014

SQL Server 2014 does NOT support merge replication with SQL Server Compact: http://msdn.microsoft.com/en-us/library/bb500342(v=sql.120).aspx – end of story.

But I am working on a blog post about an alternative solution that “just works” and enables you to Synchronize from any device, including Windows desktop/server, WinRT, Windows Mobile, Windows Phone, Android, iOS, Mac OS X and Linux to SQL Server 2008 R2, 2012 and 2014 (even SQL Server Express). Stay tuned, and follow me on Twitter @ErikEJ and subscribe to my blog RSS feed.

SQL Server - using newsequentialid() as a function, similar to newid()

This blog post shows how you can use newsequentialid() as a function in scripts etc., not only as a column default value.

In many scenarios, unique identifiers are used a clustered, primary keys in database tables for various reasons. This blog post will not discuss the pros and cons of doing this.

Usage of GUID/uniqueidentifer and it’s implication on fragmentation, and how newsequentialid() can help improve this, has been documented in various places

A limitation of newsequentialid() is that it can only be used as a default value for a column, not as a function, in for example ad-hoc INSERT scripts. By taking advantage of SQLCLR, this situation can be changed.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class SqlFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlGuid newsequentialid()
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            var sql = @"
DECLARE @NewSequentialId AS TABLE (Id UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()))
INSERT INTO @NewSequentialId DEFAULT VALUES;
SELECT Id FROM @NewSequentialId;";
            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                object idRet = cmd.ExecuteScalar();
                return new SqlGuid((Guid)idRet);
            }
        }
    }

}

The code above implements a SQLCLR function named newsequentialid(), To build this code, simply create a C# class library, include the code, and build. The code is inspired by this thread on SQLServerCentral: http://www.sqlservercentral.com/Forums/Topic1006731-2815-1.aspx

To make deploying the function even simpler, the script outlined below can add the assembly code to your database and register the function:

EXEC sp_configure @configname=clr_enabled, @configvalue=1;
GO
RECONFIGURE;
GO

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlFunctions' and is_user_defined = 1)
CREATE ASSEMBLY [SqlFunctions]
FROM 0x4D5A… (rest omitted, use full script)
WITH PERMISSION_SET = SAFE

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newsequentialid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
    CREATE FUNCTION [dbo].[newsequentialid]()
    RETURNS uniqueidentifier
    AS EXTERNAL NAME [SqlFunctions].[SqlFunctions].[newsequentialid];
' 
END
GO

You can download the full script from here: http://sdrv.ms/1hhYDY1

Testing with 50.000 inserts, like in the CodeProject article reveals the following figures:

Newsequentialid as DEFAULT:
Run time: 1:18, pages: 1725, fragmentation: 0,7 %

Newsequentialid as function in INSERT statement, no default value on table:
Run time: 2:03, pages: 1725, fragementation: 0,7 %

To use the function as a replacement for newid(), simply use dbo.newsequentialid() instead. But please also consider using another column as clustering key in your table…

Migrating databases between SQL Server and SQL Server Compact

In this post, I will try to give an overview of the free tools available for developers to move databases from SQL Server to SQL Server Compact and vice versa. I will also show how you can do this with the SQL Server Compact Toolbox (add-in and standalone editions).

Moving databases from SQL Server Compact to SQL Server

This can be useful for situations where you already have developed an application that depends on SQL Server Compact, and would like the increased power of SQL Server or would like to use some feature, that is not available on SQL Server Compact. I have an informal comparison of the two products here. Microsoft offers a GUI based tool and a command line tool to do this: WebMatrix and MsDeploy. You can also use the ExportSqlCe command line tool or the SQL Server Compact Toolbox to do this. To use the ExportSqlCE (or ExportSqlCE40) command line, use a command similar to:

ExportSQLCE.exe "Data Source=D:\Northwind.sdf;" Northwind.sql

The resulting script file (Northwind.sql) can the be run against a SQL Server database, using for example the SQL Server sqlcmd command line utility:

sqlcmd -S mySQLServer –d NorthWindSQL -i C:\Northwind.sql

To use the SQL Server Compact Toolbox:

Connect the Toolbox to the database file that you want to move to SQL Server:

Right click the database connection, and select to script Schema and Data:

Optionally, select which tables to script and click OK:

Enter the filename for the script, default extension is .sqlce:

Click OK to the confirmation message:

You can now open the generated script in Management Studio and execute it against a SQL Server database, or run it with sqlcmd as described above.

Moving databases from SQL Server to SQL Server Compact

Microsoft offers no tools for doing this “downsizing” of a SQL Server database to SQL Server Compact, and of course not all objects in a SQL Server database CAN be downsized, as only tables exists in a SQL Server Compact database, so no stored procedures, views, triggers, users, schema and so on. I have blogged about how this can be done from the command line, and you can also do this with the SQL Server Compact Toolbox (of course):

From the root node, select Script Server Data and Schema:

Follow a procedure like the one above, but connecting to a SQL Server database instead.

The export process will convert the SQL Server data types to a matching SQL Server Compact data type, for example varchar(50) becomes nvarchar(50) and so on. Any unsupported data types will be ignored, this includes for example computed columns and sql_variant. The new date types in SQL Server 2008+, like date, time, datetime2 will be converted to nvarchar based data types, as only datetime is supported in SQL Server Compact. A full list of the SQL Server Compact data types is available here.