SQL Server Compact Toolbox 3.0–Visual Guide of new features

After more than 110.000 downloads, version 3.0 of my free, open source SQL Server Compact Toolbox extension for Visual Studio 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

Extensive support for Sync Framework 2.1

Thanks to a fantastic effort from fellow MVP June Tabadero (blog | twitter), extensive support for Sync Framework 2.1 has been added to the Toolbox, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration. You can read a nice walkthrough of the features in June’s blog post here. Notice that you will need to install the Sync Framework 2.1 bits for any of these features to work, you can download from here. June also recently blogged about using SQL Server Compact 4.0 with Sync Framework here.

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases

I  decided to add the option to generate LINQ to SQL desktop classes, as I recently discovered that you can actually use LINQ to SQL with SQL Server Compact 4.0 (though NOT supported in any way by Microsoft). Read the blog post before you start using LINQ to SQL with 4.0.

Migrate a SQL Server Compact database directly to SQL Server (LocalDB/Express)

As you may know, the Toolbox already has features that allow you to generate a script of a SQL Server Compact database, and run it against a SQL Server database. But this release includes a feature to simplify this process, by not only generating a script, but also immediately executing it against a SQL Server database connected via Server Explorer.

Script only data (with "correct" table ordering)

Due to the increasing number of Database context menu items, I have moved all the script options to a separate “Script Database” sub-menu:

I have also added “Script Database Data”, which scripts only the data (!), sorted correctly by using the QuickGraph topological sorting of a DataSet.

WP DataContext - option to include ConnectionStringBuilder class

I have added the option to also have a ConnectionStringBuilder class generated for Windows Phone, to help constructing valid Windows Phone connection strings, with the limited amount of advanced options available on Windows Phone.

Other improvements

Scripting API fixes:
Tables are now ordered by topological sort when scripting entire database.
Data scripting now uses DbDataReader (or speed and to avoid some OOM issues)
“date” and “datetime2” SQL Server values are converted to “datetime” by default.
SQL scripts with DGML no longer generated.
Server based DGML now includes schema
Duplicate Execution plans fixed.
Improved script execution

READ MORE - SQL Server Compact Toolbox 3.0–Visual Guide of new features

Visual Studio 11 beta - Tooling for SQL Server Compact

Visual Studio 11 beta includes SQL Server Compact 4.0 SP1 CTP1, as I blogged about here. In this post, I will describe in greater detail the tooling support included with Visual Studio 11 beta. Notice the Visual Studio 11 is in beta, and things can change before release.

Visual Studio 11 beta only supports SQL Server Compact 4.0 in Server Explorer and other tools, so no longer support for 3.5 SP2, for development with that, you can still reference the 3.5 DLL files, but will not get any built-in tooling support.

This also means, that the EDM Wizard now only supports 4.0, so no longer the confusing mix of 4.0 support for Web projects, and 3.5 support for other project types.

The Transact-SQL Editor in Premium and Ultimate, that previously supported SQL Server Compact 3.5 and 4.0 now only support SQL Server, sadly. So in order to analyse SQL Server Compact queries, you must have SQL Server 2008 R2 Management Studio Express  (which is a free product) installed.

The Server Explorer is “missing colours”, but otherwise looks familiar:

The Server Explorer dialogs for SQL Server Compact, that were previously available, look very much the same!

And you can still add a “Local Database”, which creates an empty SQL Server Compact 4.0 database in your project:

The SQL Server Compact Toolbox also works with Visual Studio 11 beta, notice that is also support connections to 3.5 database files!

READ MORE - Visual Studio 11 beta - Tooling for SQL Server Compact

Preview of SQL Server Compact Toolbox version 3.0 now available

This short blog post lists the main new features in version 3, with pointers to the menu location of the new features in the upcoming version 3.0 of my SQL Server Compact Toolbox add-in for Visual Studio. Please go ahead and download the preview, and let us know what you think.

New features

Extensive support for Sync Framework 2.1, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration - thanks to great effort from fellow MVP JuneT (blog | twitter)

(Root context menu)

(Database context menu)

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases (see this blog post for more info)

(Database context menu)

Migrate a SQL Server Compact database directly to SQL Server (LocalDB/Express)


(Database context menu)

Script only data (with "correct" table ordering, using QuickGraph DataSetGraph with Topological Sort)

(Database context menu)

Add own Compact 3.5 based connections when using Visual Studio 11 beta

WP DataContext - option to include a ConnectionStringBuilder class

Go and try it out, and let us know what you think!

READ MORE - Preview of SQL Server Compact Toolbox version 3.0 now available

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

Now that SQL Server 2012 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 2012 (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 2012 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	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	120 MB download size > 300 MB expanded on disk	32 MB download size > 160 MB on disk
ClickOnce deployment	Yes	Yes	Yes	Yes
Privately installed, embedded, with the application	Yes	Yes	No	No
Non-admin installation option	Yes	Yes	No	No
Runs under ASP.NET	No	Yes	Yes	Yes
Runs on Windows Mobile / Windows Phone platform	Yes	No	No	No
Installed centrally with an MSI	Yes	Yes	Yes	Yes
Runs in-process with application	Yes	Yes	No	No (as process started by app)
64-bit support	Yes	Yes	Yes	Yes
Runs as a service	No – In process with application	No - In process with application	Yes	No – as launched process
Data file features
File format	Single file	Single file	Multiple files	Multiple files
Data file storage on a network share	No	No	No	No
Support for different file extensions	Yes	Yes	No	No
Database size support	4 GB	4 GB	10 GB	10 GB
XML storage	Yes – stored as ntext	Yes - stored as ntext	Yes	Yes
Binary (BLOB) storage	Yes – stored as image	Yes - stored as image	Yes	Yes
FILESTREAM support	No	No	Yes	No
Code free, document safe, file format	Yes	Yes	No	No
Programmability
Transact-SQL - Common Query Features	Yes	Yes	Yes	Yes
Procedural T-SQL - Select Case, If, features	No	No	Yes	Yes
Remote Data Access (RDA)	Yes	No (not supported)	No	No
ADO.NET Sync Framework	Yes	No	Yes	Yes
LINQ to SQL	Yes	No (not supported)	Yes	Yes
ADO.NET Entity Framework 4.1	Yes (no Code First)	Yes	Yes	Yes
Subscriber for merge replication	Yes	No	Yes	No
Simple transactions	Yes	Yes	Yes	Yes
Distributed transactions	No	No	Yes	Yes
Native XML, XQuery/XPath	No	No	Yes	Yes
Stored procedures, views, triggers	No	No	Yes	Yes
Role-based security	No	No	Yes	Yes
Number of concurrent connections	256 (100)	256	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.

READ MORE - Comparison of SQL Server Compact, SQL Server Express 2012 and LocalDB

Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!)

This question on StackOverflow more than hinted at the fact, that it was possible to use LINQ to SQL with SQL Server Compact 4.0. (Despite “rumours” of the opposite). I decided to find out, if this would work, and what it required tooling wise. (Please be aware that this is not supported by Microsoft). The SQLMetal.exe command line utility, that is used to generate the LINQ to SQL DataContext and related table classes is hardcoded via a string constant to work with 3.5. But using my command line utilities in combination with SQLMetal would allow you to create the classes based on a 4.0 database file. Using a batch (cmd) file similar to the following:

   1:  set sdf=C:\projects\ChinookPart2\Chinook40.sdf

   2:  set class=Chinook

   3:   

   4:  c:\data\sqlce\bin\exportsqlce40 "Data Source=%sdf%" c:\temp\temp.sqlce schemaonly

   5:  del c:\temp\%class%.sdf

   6:   

   7:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -e create -n

   8:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -i c:\temp\temp.sqlce –n

   9:   

  10:  sqlmetal /code:%class%.cs C:\temp\%class%.sdf

In line 4, the exportsqlce40 utility is used to export the schema (table definitions) from the 4.0 based file.

In line 7, and empty 3.5 file is created, and in line 8 it is populated with the generated schema.

Finally, in line 10, sqlmetal is run against this newly created 3.5 file, and generates the required classes.

To test that this worked, I created a Console project, added a reference to System.Data.Linq and System.Data.SqlServerCe.dll version 4.0.0.0. I also included the Chinook.cs file generated above in the project.

Then I used the code below to test that I could access the database via LINQ to SQL:

using System;
using System.Data.SqlServerCe;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\projects\Chinook\Chinook40.sdf"))
            {
                using (Chinook db = new Chinook(conn))
                {
                    db.Log = Console.Out;
                    var list = db.Album.ToList();
                    if (list.Count > 0)
                        System.Diagnostics.Debug.Print("It works!");
                }
            }

        }
    }
}

NOTE: Notice that the Chinook DataContext class is initialized with a SqlCeConnection object, I could not make it work with a full connection string, or the name of an existing connection string setting in app.config.

In the next release of the SQL Server Compact Toolbox, the code generation process has been incorporated, allowing you to very easily add a the LINQ to SQL classes to your project, both based on 3.5 AND 4.0 database files.

New menu item on the database context menu:

New dialog:

READ MORE - Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!)

Windows Phone Local Database tip: Exploring DELETE performance and a “Bug Alert”

In the previous instalments in this series of Windows Phone Local Database tips, I have been exploring SELECT, INSERT, UPDATE, CREATE DATABASE and Encryption. Now the time has come to have a closer look at DELETE, and investigate how they can be optimized (if possible). I will look at deleting multiple rows in a single call to SubmitChanges.

To test this, I will be using the code below, and as usual use the Chinook sample database.

//New database from embedded resource
using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    if (db.DatabaseExists())
        db.DeleteDatabase();

    db.CreateIfNotExists();
}

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{

    try
    {
        //db.LogDebug = true;

        var list = db.InvoiceLine.Take(100);

        db.InvoiceLine.DeleteAllOnSubmit(list);

        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
        sw.Start();

        db.SubmitChanges();

        sw.Stop();
        System.Diagnostics.Debug.WriteLine(sw.ElapsedMilliseconds);
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.ToString());
    }
}

Running this code using the InvoiceLines table from the Chinook sample database will result in 100 calls to a DELETE statement like this:

DELETE FROM [InvoiceLine] WHERE ([InvoiceLineId] = @p0) AND ([InvoiceId] = @p1) AND ([TrackId] = @p2) AND ([UnitPrice] = @p3) AND ([Quantity] = @p4)
-- @p0: Input Int32 (Size = 4; Prec = 0; Scale = 0) [100]
-- @p1: Input Int32 (Size = 4; Prec = 0; Scale = 0) [19]
-- @p2: Input Int32 (Size = 4; Prec = 0; Scale = 0) [581]
-- @p3: Input Decimal (Size = 19; Prec = 10; Scale = 2) [0.99]
-- @p4: Input Int32 (Size = 4; Prec = 0; Scale = 0) [1]

Notice that all columns are included in the WHERE statement, to check for concurrency conflicts (you can argue if this is really required when deleting). The 100 deletes runs in 320 ms on the emulator.

Now let us try to add a rowversion column to the InvoiceLines table, like we did for the UPDATE testing, and see if this has any effect.

You can also use the new feature in the SQL Server Compact Toolbox, which will allow you to add rowversion columns to all your tables, for the benefit of UPDATE and maybe also DELETE statements.

Let us try to run the same test code again.

Now the SQL statement looks like this, indicating that the query processor is bypassed, unlike in the statements above.

-- CURSOR BASED INDEX DELETE [InvoiceLine].[PK_InvoiceLine] (
--     InvoiceLineId: [1]
--    )
-- EQUALITY CHECK [versioncolumn], [System.Byte[]] = [System.Byte[]]

The time to perform the 100 DELETE statements is now down from 320 ms to under 70 ms, a significant improvement.

BUG ALERT!

If you attempt to take advantage of rowversion columns in an existing database, make sure that the table in question does not have more indexes that cover the primary key columns. If you have that, your SubmitChanges statement will “bomb out” your app.

For example the Album table in Chinook has a PK_Album index and PFK_Album index, that both are indexes on the AlbumId column. DROP the PFK_Album index in this case (you can use the new Index context menu option in the Toolbox to do that).

Hope you found this useful.

READ MORE - Windows Phone Local Database tip: Exploring DELETE performance and a “Bug Alert”