What’s new in SQL Server Compact 3.5 SP2 beta

Disclaimer: I am guessing some of this, as no documentation is currently available!

SQL Server Compact 3.5 SP2 beta is available for download for x86 and x64 (no Windows Mobile yet) here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=411ba1c5-ba57-45b6-9148-91bed6e7a9f1#tm and is also included with Visual Studio 2010 beta 2. I will try to update this blog entry as more information becomes available.

Notice that the installation of 3.5 SP2 beta upgrades a 3.5 and 3.5 SP1 installation. Also, this is beta software, so use at your own risk!

Firstly, 3.5 SP2 beta includes all the hotfixes, that has been released for 3.5 SP1, as listed by me here.

The SQL Compact 3.5 folder look like this after installation:

Notice the “Private” folder, which contains a version of System.Data.SqlServerCe.Dll which should be used for private deployments, to prevent the “Assembly probing” issue described in this forum thread. The problem is, that even if you are using a hotfixed version of SQL Compact in your application folder, assembly probing will load the GAC deployed version, which may or may not be hotfixed. This means that the deployment of a hotfix always must be done by an administrator, which may not always be possible.

Ravi Tandon from the product team add the following comments about EF related fixes in the beta (from same thread):

“In context of this thread this will alleviate these issues for you:
1. Fixes the ntext error, or any EF queries that use String Parameters. (Hotifx - http://support.microsoft.com/kb/958478/EN-US, earlier released has been rolled up)
2. Fixes the "AS" error. (Same Hotifx had this fix too and has been rolled up)
3. This MSI will do the seamless upgrades. You just need to use this MSI for ClickOnce and any other scenarios. No Uninstalls anymore.
4. We also got some reports of bad performance with EF designers if the Schema get's a bit complex and big. We have made changes for that, which should make the performance better.
As always, we are open for feedback, suggestions, comments.
Thanks a lot for using our product and the continued support!
Cheers
Ravi”

The 3.5 SP2 beta adds a few new APIs in the ADO.NET provider:

 

1: A new property has been added to the SqlCeReplication object (merge replication agent): PostSyncCleanup – it is “documented” here.

2: The SqlCeEngine.Repair methods RepairOptions have changed (documented here):

DeleteCorruptedRows –> DeleteCorruptedRows

RecoverCorruptedRows –> RecoverAllPossibleRows (RecoverCorruptedRows is obsolete)

New Enum value: RecoverAllOrFail

 

An entirely new class is now available, called SqlCeChangeTracking. It exposes methods to programmatically enable, query and disable change tracking, something that was only available to the Visual Studio “Local Data Cache” designer (for Sync Framework applications.) I am looking forward to more documentation on this API!

Hope this helps, and looking forward to the final release of SQL Server Compact 3.5 SP2.

UPDATE: Ravi Tandon has posted even more info here

UPDATE 2: The VS 2010 RC contains a newer build (3.5.8078.0), and includes a readme file, that documents a couple of issues:

1: A ClickOnce application that uses DbProviderFactory to load the SQL Server Compact 3.5 SP2 provider will not work in the following cases:

· A computer already has SQL Server Compact 3.5 SP2 and .NET FX 2.0, 3.0, or 3.5 installed, and the ClickOnce application installs .NET FX 4 and SQL Server Compact 3.5 SP2 on the computer.

· A computer already has SQL Server Compact 3.5 SP2 and .NET FX 4 and installed and the ClickOnce application installs .NET FX 2.0, 3.0, or 3.5 on the computer.

The workaround is to repair the SQL Server Compact 3.5 SP2 installation from Add/Remove Programs.

2: Ensure that if a database is created with SQL Server Compact 3.5 SP2 change tracking is also enabled with SQL Server Compact 3.5 SP2.

3: The SQL Server Compact 3.5 SP2 Query Tools Windows installer file (MSI) is uninstalled when SQL Server 2008 and SQL Server 2008 R2 are installed side-by-side and then SQL Server 2008 is uninstalled.

For more information, see the ReadmeSSCE35_ENU.htm in the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5 folder, when build 3.5.8078.0 is installed.

 

UPDATE 3:

The final build released with VS 2010 is 3.5.8080.0.

The Desktop installer is a self-extracting .exe, that includes the following warning – On a 64-bit system both the 64-bit and 32-bit must be installed!

 

UPDATE 4: A workaround for a private deployment bug with SP2 (when using Entity Framework) is described here:

http://blogs.msdn.com/sqlservercompact/archive/2010/05/12/troubleshooting-problem-with-private-deployment-of-sql-server-compact-3-5sp2-entity-dll.aspx

Recent Synchronization and Replication advice from Microsoft bloggers

This is a round-up of some recent blog entries from Microsoft relating to Sync and Replication.

Merge Replication

The Microsoft SQL Server Replication Support Team has started a blog which includes excellent Merge Replication advice. Start at this overview blog entry.

In addition, Rob Tiffany has made a list of SQL Compact Merge Replication performance tips available here. This list is a summary of some of the advice in Robs excellent Merge Replication book. It even includes some of the improvements in SQL Compact 3.5 SP2, including the new PostSyncCleanup property of the SqlCeReplication object, and highlights the new support for Windows 2008 R2 with IIS 7.5 included with the SP2 server tools.

Sync Services

The Sync Services team has published a useful FAQ. And the SQL Support team blog has a long walkthrough of setting up N-tier Sync Services for ADO.NET.

Access a local SQL Compact database from Silverlight 4

Thanks to the new COM support in Silverlight 4, it is now possible for an elevated OOB application to access a local SQL Compact database. Keep in mind that COM suppport only is available on the Windows platform, not on Mac.

And thanks to the excellent SilverlightCOM Toolkit on Codeplex, it is very easy to use the ADO COM objects from your Silverlight application in a familiar (.NET) manner. In addition to data access, the COM Toolkit also enables access to files and directories, including file contents, registry, environment and starting processes (for now).

In order to enable access to COM objects (on Windows platforms only, of course), the following properties must be set for your Silverlight 4 project (which becomes settings in the AppManifest.xaml file):

o

UPDATE: Now updated for SL4 RTM, and hosted by me: http://www.freewebsite.dk/documents/1075668907TestPage.html#/About

Source code for the demo (only for 4.0 RC) is available here.

For the sample to compile with 4.0 RTM, changes as follows:

using System.Windows.Interop;

change to:

using System.Runtime.InteropServices.Automation;

and change from:

ComAutomationFactory.CreateObject

to

AutomationFactory.CreateObject

In addition for the ComToolkit.Data class, changes  to the 3 files included in the .zip file below were required.

 

Install the Silverlight 4 runtime,  scroll down to the test application, right click it and select Install:

You will get this security warning:

Click Install to install and create shortcuts on your Desktop or  Start Menu.

This is the contents of the shortcut created:

"C:\Program Files\Microsoft Silverlight\sllauncher.exe" 2619253130.webite.se

The demo application will launch – change the connection string (which is an OLEDB connection string, not a .NET connection string) to use the SQL Compact OLEDB provider, and change the path to point to a SQL Compact file on your local disk:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\install\projects\nwtest.sdf

Change the SQL statement to a valid statement for your database:

SELECT * FROM [Employees]

Press Connect to open the connection to the database and then press Execute to run the query, and voilá, you are accessing a local SQL Compact database with Silverlight 4!:

UPDATE: A full VS 2010 solution is now available for download:

Diagram database table relationships using DGML, SMO and Visual Studio 2010

DGML (directed graph markup language) is a graphing XML language supported by the new modeling tools in Visual Studio 2010.

You can create various code analysis graphs using Visual Studio 2010 Ultimate, and Visual Studio 2010 Premium includes a DGML viewer for displaying DGML documents, but does not include tools for creating them (other than the XML editor!). Unfortunately, for this release of Visual Studio, the Express versions and Professional does not include a DGML viewer. You can, however, share  graphs by saving them as XPS documents directly from the DGML viewer. You can read more about the DGML format here.

The sample command line utility that comes with this blog demonstrates how you can very easily create a nice graph of the tables and table relationships in your database using DGML and SMO.

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. It allows you to inspect all objects that relate to SQL Server and manipulate these. For this sample, we only need access to the tables and foreign keys of the tables of a specific database.

var connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT;Integrated Security=SSPI;";

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var server = new Server(new ServerConnection(conn));
    var database = server.Databases[conn.Database];
    foreach (Table table in database.Tables)
    {
        System.Diagnostics.Debug.WriteLine(table.Schema + "." + table.Name;

        foreach (ForeignKey key in table.ForeignKeys)
        {

The code above demonstrates how to get a collection of Table objects for a database using SMO. In addition, each table object has a collection of ForeignKeys, that allow you to get information about each foreign key for the table.

A simple DGML document looks like this:

<DirectedGraph xmlns="http://schemas.microsoft.com/vs/2009/dgml">
  <Nodes>
    <Node Id="0" Label="tblDoctor" />
    <Node Id="1" Label="tblDoctorLocation" />
    <Node Id="2" Label="tblFavoriteDoctor" />
  </Nodes>
  <Links>
    <Link Source="0" Target="1" Label="FK_tblDoctorLocation_tblDoctor" />
    <Link Source="1" Target="2" Label="FK_tblFavoriteDoctor_tblDoctorLocation" />
  </Links>
</DirectedGraph>

 

And looks like this in the DGML viewer:

 

 

This is graph for the AdventureWorksLT  database:

 

The complete code for this sample is available for download here.

Hope you find it useful!

Solutions to: “Server-generated keys and server-generated values are not supported by SQL Server Compact” with Entity Framework

SQL Compact does not support IDENTITY keys when used with Entity Framework, since the SQL Compact engine only supports a single statement per batch.

This can be solved in various ways:

You can have all your primary keys be of type uniqueidentifier, and assign the NewGuid() value to the object upon creation:

 Employee employee = new Employee();

employee.Id = Guid.NewGuid();

Or you can create an extension method as described here, noting the limitations of this approach.

SQL Server Compact 3.5 SP2 released!

SQL Server Compact 3.5 (build 3.5.8080.0) has been released to web.

SQL Server Compact 3.5 SP2 includes a host of new features including the following:

• Support for the Transact-SQL Editor in Visual Studio 2010 that can be used to run free-text T-SQL queries and to view query show-plans for optimizing queries.
• Exposes the Change Tracking feature as an application programming interface (API), namely, System.Data.SqlServerCe.SqlCeChangeTracking. The new Change Tracking API provides the ability to configure, enable, and disable change tracking on a table, and to access the change tracking data for the table.
• New assembly versioning support and directory structure that enables the SQL Server Compact applications that use private deployment to load SQL Server Compact assemblies from within the application folder if they are of a higher version than that in the global assembly cache (GAC).
• Support added for deploying the 64-bit version of SQL Server Compact using the ClickOnce deployment.
• Synchronization Services for ADO.NET v1.0 providing the ability to synchronize data from disparate sources over two-tier, N-tier, and service-based architectures.

New feature details also available here: http://erikej.blogspot.com/2009/10/whats-new-in-sql-server-compact-35-sp2.html

- and the product team has more details here: http://blogs.msdn.com/sqlservercompact/archive/2010/04/14/microsoft-sql-server-compact-3-5-with-service-pack-2-released-with-visual-studio-2010-and-on-the-web.aspx

Downloads:

Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop 
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e497988a-c93a-404c-b161-3a0b323dce24

Books Online
http://www.microsoft.com/downloads/details.aspx?FamilyID=746c3a6e-ffb1-4c92-93fa-b3ba41fde681&displaylang=en

Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Mobile
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=5544c638-c532-48e3-871c-58b49c5d855c

Microsoft SQL Server Compact 3.5 Service Pack 2 Server Tools
http://www.microsoft.com/downloads/details.aspx?familyid=5DEEC9D5-C216-446E-A003-5D6026AD19B3&displaylang=en

Using the ADO.NET Data Connection dialog in your own application – and limiting the choice of data sources

In version 3.0 of the ExportSqlCE SSMS add-in, I have used the newly released Visual Studio ADO.NET Add Data Connection dialog to prompt the user for connection information to the server. This dialog is now available in source code here.

This project consists of this well-know dialog (and others) – used in the Visual Studio Server Explorer:

For the purpose of the add-in, I was interested in on the Microsoft SQL Server data source, as this was the target of the scripting code.

This was achieved in the following way:

using Microsoft.Data.ConnectionUI;
…

DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
DataConnectionDialog dcd = new DataConnectionDialog();
dcd.DataSources.Add(sqlDataSource);
dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
dcd.SelectedDataSource = sqlDataSource;
if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
    string connectionString = dcd.ConnectionString;

 

The result is the following dialog, notice that the Change… button is disabled: