Everything SQL Server Compact

Monday, September 5, 2011

Useful Windows Phone advice from Nick Randolph

Fellow MVP Nick Randolph (@BTRoam) publishes an excellent blog, Nick’s .NET Travels, often with articles that relate to SQL Server Compact and synchronization technologies, but also very useful articles for any Windows Phone developer, with a practical, hands-on approach. Highly recommended.

He has recently published the following articles:

Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces

This article demonstrates the importance of implementing the INotifyPropertyChanging interface on your DataContext classes to improve memory management. As he points out, you should use SQLMetal to generate your DataContext, as this will avoid missing to implement these interfaces. Or even better use the SQL Server Compact Toolbox Visual Studio add-in, as it adds the following features on top of SQLMetal:

1. Removes unneeded/unsupported constructors

2. Adds any [Index] attributes to each table

3. Adds the CreateDatabaseIfExists method

4. Optionally splits the generated files into a file per table (in next version (2.4), currently available in beta)

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

This article demonstrates how to get started using SQL Server Compact Change Tracking with a Windows Phone SQL Server Compact database, despite the fact that the Change Tracking APIs are not available on Windows Phone. He also gets thrown in how to use the Windows Phone SDK ISETool to move the database from the Phone (Emulator) to your local disk. Good stuff.

Thursday, September 1, 2011

SqlCeBulkCopy, a library for fast SQL Server Compact INSERTS released

Version 2.1 of my SQL Server Compact Bulk Insert Library has now been released. This library exposes an API similar to the SqlBulkCopy API implemented for the SqlClient (working against SQL Server). The library allows you to quickly load data inot a SQL Server Compact database.

New features in this release include:

3 editions of the library:
One for .NET Compact Framework for version 3.5 databases - ErikEJ.SqlCe.NetCF.dll
One for full .NET Framework for version 3.5 databases - ErikEJ.SqlCe.dll
One for full .NET Framework for version 4.0 databases - ErikEJ.SqlCe40.dll

- New overloads of the WriteToServer method allows you to load any .NET list that implements IEnumerable or IEnumerable<T>

- API Documentation is now available in HTML format here. The API documentation was created very easily using the open source ImmDoc.NET utility. All this command line utility requires is XML comments file and the DLL file(s).

- NuGet package available

A NuGet package, that includes the SQL Server Compact 4.0 library is now available via the NuGet Package Manager.

If you need to load data fast from a DataTable, DataReader or .NET List to a SQL Server Compact database file, this is the library for you.

Monday, August 15, 2011

Major update to SQL Server Compact 3.5 SP2 available

A major update to SQL Server Compact 3.5 SP2 has just been released, disguised as a “Cumulative Update Package”. Microsoft knowledgebase article 2553608 describes the update. The update contains the following product enhancements:

Support for Windows Embedded CE 7.0

The update contains updated device components. This expand the supported device platforms to this impressive list: Pocket PC 2003 Software, Windows CE, Windows Mobile 5.0, Windows Mobile 6, Windows Mobile 6.1 , Windows Mobile 6.5 Professional, Windows Mobile 6.5 Standard, Windows Embedded CE 7.0

Support for Merge Replication with SQL Server “Denali” CTP3

The update contains new Server Tools, that support Merge Replication with the next version of SQL Server, codename “Denali”. The replication components also work with Windows Embedded CE 7.0.

For a list of fixes in the Cumulative Updates released for SQL Server Compact 3.5 SP2, see my blog post here.

It is nice to see that the 3.5 SP2 product, with it’s full range of device support and synchronization technologies is kept alive and kicking.

NOTE: Currently, the only download available is the desktop runtime, I will update this blog post and tweet (@ErikEJ) when the other downloads are available.

Thursday, August 11, 2011

Viewing SQL statements created by Entity Framework with SQL Server Compact

Sometimes it can be useful to be able to inspect the SQL statements generated by Entity Framework against your SQL Server Compact database. This can easily be done for SELECT statements as noted here. But for INSERT/UPDATE/DELETE this method will not work. This is usually not a problem for SQL Server based applications, as you can use SQL Server Profiler to log all SQL statements executed by an application, but this is not possible with SQL Server Compact.

This forum thread contains an extension method, that allows you to log INSERT/UPDATE/DELETE statements before SaveChanges is called on the ObjectContext. I have updated and fixed the code to work with SQL Server Compact 4.0, and it is available in the updated Chinook sample available below in the ObjectQueryExtensions class in the Chinook.Data project.

You can now use code like the following to inspect an INSERT statement:

using (var context = new Chinook.Model.ChinookEntities())
{ 
    context.Artists.AddObject(new Chinook.Model.Artist { ArtistId = Int32.MaxValue, Name = "ErikEJ" });
    string sql = context.ToTraceString();
}

The “sql” string variable now contains the following text:

--=============== BEGIN COMMAND ===============

declare @0 NVarChar set @0 = 'ErikEJ'

insert [Artist]([Name])

values (@0)

; select [ArtistId]

from [Artist]

where [ArtistId] = @@IDENTITY

--=============== END COMMAND ===============

This statement reveals some of the magic behind the new support for “server generated” keys with SQL Server Compact 4.0 when used with Entity Framework 4.0. SQL Server Compact is “tricked” into executing multiple statements in a single call.

Sunday, August 7, 2011

SQL Server Compact Toolbox 2.3–Visual Guide of new features

After more than 44.000 downloads, version 2.3 of my 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

Generate database documentation

This feature allows you to create documentation of all tables and columns in your database, in HTML or XML (raw) format, for use with product documentation etc. If you have added descriptions to database, table or column, these will also be included.

From the database context menu, select Create Database Documentation…

You will be prompted for a filename and can choose between HTML and XML format. The generated document will then open in the associated application (for example your browser).

The format of the HTML and XML file comes from the excellent DB>doc for Microsoft SQL Server CodePlex project. You can use the XML file as the data in your own documentation format.

By default, tables beginning with __ are not included in the documentation (this includes the table with object descriptions). They can optionally be included via a new option:

Please provide any feedback for this new feature to the CodePlex issue tracker

Handle password protected files better

When trying to open a password protected file, where the password is not saved with the connection string, you are now prompted to enter the database password, instead of being faced with an error.

Show result count in status bar

The query editor status bar now displays the number of rows returned.

Other fixes

Improvements to Windows Phone DataContext generation, improved error handling to prevent Visual Studio crashes, and the latest scripting libraries included.

Wednesday, July 13, 2011

Using PowerShell to manage SQL Server Compact database files

I noticed that someone asked if you could manage SQL Server Compact from PowerShell, and yes you can, based on the reply here I put together the following sample:

[Reflection.Assembly]::LoadFile(“C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll”)

$connString = "Data Source=C:\data\sqlce\test\roads.sdf"
$cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlServerCe.SqlCeCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT TOP (100) * FROM Road"
$cmd.Connection = $cn

#get the data
$dt = new-object "System.Data.DataTable"

$cn.Open()
$rdr = $cmd.ExecuteReader()

$dt.Load($rdr)
$cn.Close()

$dt | Out-Default | Format-Table

Sunday, July 3, 2011

Windows Phone / SQL Server Compact resources

This blog post collects links to relevant blog posts and articles about Windows Phone support for SQL Server Compact. You can also follow me on Twitter (@ErikEJ) to get notified of any SQL Server Compact related news.