Monday, April 23, 2012

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)

image
(Root context menu)

image
(Database context menu)

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

image
(Database context menu)

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

image
(Database context menu)

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

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

WP DataContext - option to include a ConnectionStringBuilder class

image

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

Thursday, April 12, 2012

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:


image

New dialog:
image

Friday, April 6, 2012

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.


image


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.


image


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.