SQL Server Compact 4.0 SP1 hotfix available

I was recently made aware that a hotfix for SQL Server Compact was made available in 2014 –  but better late than never.

The hotfix can be requested via the Microsoft support knowledgebase here: https://support.microsoft.com/en-us/kb/2960153

The file/build version of the hotfix is: 4.0.8902.0 (The .NET assembly version stays at either 4.0.0.0 (or 4.0.0.1 for the “Private” .NET Assembly)

The hotfix includes the following fixes:

2984052
FIX: Unexpected ordering when you run ORDER BY statement on a column that contains “-” in SQL Server Compact 4.0

2984053
FIX: “ConnectionString cannot be null” when you use folder redirection with offline files

2979868
FIX: It takes longer time than flush interval to flush committed transactions in SQL Server Compact 4.0

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

After more than 470.000 downloads, version 4.5 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 GitHub issue tracker

I have moved the source code and issue tracker to GitHub, and I am planning to move the remaining CodePlex content “soon”. I have also moved the source of the scripting API that the Toolbox uses extensively to the same GitHub repository. As always, and suggestion, bug reports and pull requests are welcome!

New icons

The icons of the extension have all been overhauled, and updated to use the latest VS 2015 icons/images. From users already trying out the latest bits, I have received positive feedback on this visual change.

image

Query editor toolbar redesign and more

I have also started using Azure AppInsights to track (anonymously) feature usage etc. This revealed (maybe not surprisingly) that the most use feature is the query editor. So for this release, the query editor has received a lot of attention. In addtion to the changes below, the general layout of the editor has been streamlined (no more rounded corners).

image

The first button is new, and will open a “New Qery” window against the same database file. An options dropdown have been added to expose some of the (maybe overlooked) options that are available for the query editor. Setting these only affects the current query window, use the general settings (which you can reach via the Setting button in the query editor now) to change the “default” value for a setting.

Lets have a quick look at each of the options:

“Show result in Grid”: By default, the query results (from a SELECT statement) will be displayed as text for performance reasons, but you can opt-in to have the results displayed in an Excel like grid instead.

“Show Binary Values in Result”: By default, binary values (from for example “image” or “rowversion” columns) will be displayed a “System.Byte[]”. Enabling this will instead display the actual value as a hexadecimal string – but may affect performance.

“Show null values as NULL”: Until recently, NULL values were displayed as an empty string, not as “NULL”. Disabling this reverts to the old behaviour.

“Ignore DDL errors”: Enabling this (per query window only) feature, allows you to run the database creation scripts generated by the Entity Framework “Generate Database from Model” feature, and ignore errors caused by for example pre-existing tables.

“Use classic (plain) grid”: This will revert to using the “old” grid for grid results – about the “new” grid, see below!

New result grid in query editor

A new grid that allows you to further drill into the results of a query without writing any SQL is now the “default” result grid in the query editor. The grid uses http://wpfextendeddatagrid.codeplex.com/

image

“Autofilter” :The grid has built in filters, in the example above to filter on invoices for a particular customer (or even set of customers).

image

“Grouping”: Add columns you want to group by to the surface above the grid. In the example above, Invoices are grouped by Customer Id.

Options now part of Visual Studio options dialog

image

The options for the Toolbox have finally been integrated in the standard Visual Studio Tools/Options pages.

Improved SQL Server support

SQL Server Connections defined in Server Explorer now appear in the Toolbox, in order to better expose the SQL Server releated features of the Toolbox:

image

And scripting of a SQLite schema (only) based on a SQL Server database is now possible:

image

Improvements

A number of smaller improvements are also included in this release:

Improve format of INSERT statements generated by CSV import (to make the more readable)
Added latest System.Data.SQLite version (1.0.99)
Added link to blog post to DataAccess.cs to better explain how to use it.
Support for row-returning SQLite statements beginning with “WITH”
Added “Feedback” button
Using Azure Application Insights for metrics and crash reports
Ability to remove password from password protected SQL Compact file (by leaving password box blank)
Progress messages during export now displayed in Output window
Enable scripting of hierarchyid as varbinary(892)
Now able to script Adventureworks2014 schema and data to both SQL Compact and SQLite without errors
Table Data script (INSERTs) now have “RESEED” command at end always
Option to Opt out of Telemetry added

Bug fixes

In addition to the above improvements and features, and number of bug fixes are included in this release:

API:
Fix bug in SQLMetal.exe detection (on some configurations)
SQLite CREATE TABLE datetime default should be current_timestamp, not GETDATE()
Improved SQLite datetime handling in SELECT and Edit in Grid

User Interface:
CSV export from the SQL editor included one separator too many in the heading
Editor keyboard bugs fixed
Scroll issues in treeview fixed
Autohide of Toolbox bug fixed

Table builder:
Table builder was not scripting default values
Table builder should not script default value for IDENTITY columns

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

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

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

Scan solution for SQLite database files

image

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

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

image

Rename Connection name

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

image 

SQLite-net DataAccess.cs improvements

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

image

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

NULL value display

image

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

image

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

Other improvements

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

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

“SQL Server Compact & SQLite Toolbox” related news

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

Visual Studio 2013 Community Edition

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

image

Visual Studio Auto Updater

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

image

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

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

VSToolbox2

Version 4.2 preview

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

Editor2

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

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

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

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

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

After nearly 290.000 downloads, version 3.7.3 of my SQL Server Compact Toolbox extension for Visual Studio  “14”, 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker.

New features

Export SQL Server database to SQLite script (beta)

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

SQLIteExport

“Set Password” option added to Maintenance menu

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

setpw

Enable entry of multiline text in DataGridView

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

Support for VS “14” CTP

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

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

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

image

 

Bug Fixes

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

image

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

image

Add Column – defaults to Allow Null

image

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

image

image

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

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

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

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

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

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

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

String functions
CharIndex
NChar
PatIndex
Replicate
Space
StringConvert
Stuff
Unicode

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

Date functions
DateAdd
DateDiff
DateName
DatePart
GetDate,

Other
DataLength

So you can compose LINQ to Entities queries like:

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

And the resulting SQL will look like this:

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

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

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

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

db.Database.Log = Console.Write;

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

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

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

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

I am sure you will find this new feature useful.

Entity Framework ”reboot” – EF7 – Get a sneak peek via TechEd US live stream

The next version of Entity Framework has just been named “EF7” (code name EF EveryWhere). Before the presentation, which will be streamed live on Channel 9 during TechEd US on Wednesday May 14 at 1:30 PM CDT (Houston, Texas), allow me to summarize what we know so far about the next version of Entity Framework.

The Entity Framework team has already published some thoughts about the new Framework here, and based on this and other sources, we can summarize the following (some or maybe most of which is me guessing, of course):

– A completely new codebase, will not be based on the 1 million+ line codebase of EF6

– Will be open source, and accept pull requests and other community feedback

– Will support Windows Phone, Windows Store, Windows Desktop/Server/Cloud, and also support Mono/Xamarin platforms

– Will be based on a provider model, so SQL and NoSQL data sources can “plug in”. Will initially support SQLite on Phone and Store apps. Will also support SQL Server and Azure Table Storage

– Will only include a productive subset of the current, huge API – Code First Mappings, DbContext, POCO classes, and less mapping schemes. So current investments in DbContext, Code First/Second and POCO will be forward reusable.

– Will not contain ObjectContext, Entity SQL, EntityConnection, EntityCommand etc. Phew.

Sounds ambitious and great to me. Can’t wait to see some code and start playing!

UPDATE: The source code is now available on Github as part of ASP.NET vNext. And Rowan Miller, Program Manager on the EF team speaks about and demonstrates EF7 in this TechEd session available online.