SQL Server Compact 4.0 SP1 Released To Web

SQL Server Compact 4.0 SP1 has now been released as a web download (it is also included and installed with Visual Studio 2012). The release version is 4.0.8876.1

This release adds support for Windows 8 and Visual Studio 2012. In addition, it also includes some bug fixes and a new feature (so far undocumented), as I have described here

I would say it is a recommended upgrade for anyone using version 4.0.

SQL Server Compact 4.0 under ASP.NET Hosting– common issues

This blog post covers some of the issues that can be encountered when using SQL Server Compact under ASP.NET, in particular in a hosted environment, where the environment can be restricted in unexpected ways.

In order to be able to troubleshoot issues with ASP.NET hosting of web sites using SQL Server Compact 4.0, it can be useful to understand how Medium Trust works.

Medium Trust only works under .NET 4.0 (ASP.NET 4.0), as it depends on a configuration in the global web.config.

The global web.config is located at C:WindowsMicrosoft.NETFrameworkv4.0.30319Config or C:WindowsMicrosoft.NETFramework64v4.0.30319Config, and contains the following setting, that was configured when .NET 4.0 was installed:

<?xml version="1.0" encoding="utf-8"?>
<
configuration>
<
location allowOverride="true">
<
system.web>
<
fullTrustAssemblies>
<
add
assemblyName="System.Data.SqlServerCe"
version="4.0.0.0"
publicKey="0024000004800000940000000602000000240000525341310004000001000100272736ad6e5f9586bac2d531eabc3acc666c2f8ec879fa94f8f7b0327d2ff2ed523448f83c3d5c5dd2dfc7bc99c5286b2c125117bf5cbe242b9d41750732b2bdffe649c6efb8e5526d526fdd130095ecdb7bf210809c6cdad8824faa9ac0310ac3cba2aa0523567b2dfa7fe250b30facbd62d4ec99b94ac47c7d3b28f1f6e4c8"
/>
</
fullTrustAssemblies>
<
partialTrustVisibleAssemblies />
</
system.web>
</
location>


So this was added during installation of .NET 4.0, long before SQL Server Compact 4.0 was released in January 2011. The fullTrustAssemblies element is new in .NET 4.0: http://weblogs.asp.net/asptest/archive/2010/04/23/what-is-new-in-asp-net-4-0-code-access-security.aspx

If this section has been removed from the global web.config file, SQL Server Compact 4.0 will not run under medium trust.

It is possible to configure similar functionality under .NET 3.5 SP1, as described here: http://msdn.microsoft.com/en-us/library/ms174612(v=SQL.110).aspx

Notice that the SQL Server Compact binaries are delivered with two Assembly version numbers, 4.0.0.0 and 4.0.0.1. The 4.0.0.1 Assembly is for Private deployment, but on desktop only, as described here: http://msdn.microsoft.com/en-us/library/gg213826.aspx – it will not work with Medium Trust, as the assembly version registered in the machine.config file is 4.0.0.0, as shown above.

Other issues you may encounter when running ASP.NET and SQL Server Compact 4.0 can be:

– The process identity running your web application must have write access to the App_Data folder or the folder where your database file resides:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/6c1c8798-85d7-4d8f-9908-e9f7dc1c17e4

– SQL Server Compact database files must be located on a local drive, not shared folders:

http://stackoverflow.com/questions/7791345/sqlce-4-ef4-1-internal-error-cannot-open-the-shared-memory-region/7804049#7804049

– If SQL Server Compact is not installed locally, a number of files must be included in the bin folder of your web site:

http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450

http://msdn.microsoft.com/en-us/library/gg286946.aspx

– If only .NET 4.0 is present on the server, you may be missing the Visual C++ 2008 SP1 Redistributable Package (which is installed only with .NET 3.5 SP1)

I will update this blog post if I notice more “common issues”.

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.

clip_image002

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).

clip_image004

– NuGet package available

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

clip_image006

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.

SQL Server Compact ASP.NET Membership, Role and Profile Provider version 2.1 now available

My ASP.NET membership provider is now available in version 2.1, that contains many improvements and some new features based on excellent community feedback – keep it coming!

The ASP.NET membership provider project was prompted last July by the comments to Scott Gu’s blog post about the upcoming version 4.0 of SQL Server Compact, and it’s support for ASP.NET.

Basically the Gu said: “We are looking to potentially ship a set of providers that work with it (and do not use stored procedures). The first beta won’t have this – but it is something we’ll hopefully enable in the future.”

So it was time to start coding, since the absence of a Membership provider would make SQL Server Compact less of an attractive option for ASP.NET web sites.

Since then, the database schema used has been refactored to be in line with the ASP.NET 4.0 SQL Server based schema, which resulted in the first NuGet Package being released in January 2011.

Now version 2.1 is available, also via NuGet:

image

Or from the CodePlex site.

The new features in version 2.1 are:
Profile provider included (contrib davidsk)
Two new methods: UpdateUserName and MigrateMembershipDatabaseToAspNet40 (contrib nekno)

Bug fixes (by various contributors, thank you all):
UpdateUser() doesn’t set LoweredEmail
GetUser w/ providerUserKey returns invalid information
Static salt leads to deterministic output, dynamic salt is better
Configuration error when using a provider

Useful new topics in SQL Server Compact 4.0 Books Online

SQL Server Compact 4.0 Books Online contains a couple of useful new help topics, that relate some to the new features in version 4.0, the OFFSET FETCH clause, support for medium trust under ASP.NET, and a couple of missing ADO.NET APis, that have been implemented.

SQL Server Compact 4.0 Books Online is available for download, and also available online.

Some of the interesting new topics include:

What’s new in SQL Server Compact 4.0

Microsoft SQL Server Compact 4.0 has a group of new features, and enables a new scenario where SQL Server Compact 4.0 can be used as a database for ASP.NET Web applications and Web sites.

OFFSET FETCH Clause

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

Deployment Considerations

SQL Server Compact 4.0 is optimized for use as a database for ASP.NET web applications. Web applications are required to run in Medium Trust or Partial Trust, SQL Server Compact 4.0 can also run in medium or partial trust level. (Includes steps required to make SQL Server Compact 4.0 run under medium trust with .NET Framework 3.5 SP1.

SqlCeConnection.GetSchema method

Returns schema information for the data source of this SqlCeConnection

SqlCeConnectionStringBuilder Class

Provides a simple way to create and manage the contents of connection strings used by the SqlCeConnection class.

Snapshot Synchronization with SQL Server Compact 4.0

It has been made clear by the SQL Server Compact team that version 4.0 does not support Sync technologies like Merge Replication and Sync Framework.

Imagine a scenario where you have a desktop or web app, where the data is mix of the user’s own operational data and lookup data from a central data source. How would you enable this using SQL Server Compact 4.0?

RDA (Remote Data Access) is a simple and proven technology, that allows you to “Pull” an entire table from a SQL Server over http (take a snapshot), without much coding effort. I decided to test if this technology would still work with 4.0, and lo an behold, it does. (Notice that RDA support will be removed from SQL Server Compact in a future release)

That means that you can pull down lookup data from a central server to your SQL Server Compact 4.0 database file as needed. I configured my SQL Server Compact ISAPI agent DLL according to my post here, and was able to pull a table to my version 4.0 database file. (Remember to DROP the local table before you Pull).

Here is the small amount of code required (Console application):

using System;
using System.Data.SqlServerCe;

namespace TestRDA40
{
class Program
{
static void Main(string[] args)
{
// Connection String to the SQL Server
//
string rdaOleDbConnectString = "Data Source=(local);Initial Catalog=ABC; " +
"User Id=xxx;Password=yyy";

// Initialize RDA Object
//
SqlCeRemoteDataAccess rda = null;

try
{
// Try the Pull Operation
//
rda = new SqlCeRemoteDataAccess(
"http://localhost/ssce35/sqlcesa35.dll",
@"Data Source=C:datasqlcetestnw40.sdf");

rda.Pull("ELMAH", "SELECT * FROM dbo.ELMAH_Error", rdaOleDbConnectString);
}
catch (SqlCeException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
rda.Dispose();
}

}
}
}

Hope you find this useful.

SQL Server Compact Toolbox 2.1–Visual Guide of new features

Celebrating more that 20.000 downloads, version 2.1 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, many suggested by users of the tool via the CodePlex issue tracker.

Generate an Entity Data Model (EDMX) in the current project in any applicable project (WPF, WinForms, Class Library)

As you may know, the tooling support for 4.0 is somewhat limited, and therefore you have to use various workarounds to generate an EDMX for a version 4.0 database in projects that are not Web based. Version 2.1 of my Toolbox adds the ability to run edmgen2 via a friendly dialog to generate and include an EDMX file in any applicable project type.

image

image

(I will blog later about how I did this, using the edmgen2 project)

Remove invalid connection definitions from the Toolbox (and Server Explorer)

As you move SQL Server Compact files around and upgrade version 3.5 files to version 4.0, many of the connection entries in Server Explorer / the Toolbox become invalid. Therefore, I have added a menu option to remove broken connections.

image

Option to display binary data in result

image

Preventing invalid values in Max Database Size textbox

By using the NumericUpDown control from the Extended WPF Toolkit, it is no longer possible to enter invalid max database size values.

image

For a similar overview of version 2.0 features, see this blog post.

Updated release of standalone version.

The standalone version for users not running Visual Studio 2010 Pro or higher, is also available in a new release, the main feature being, that it is now a single .exe, with only .NET 4.0 and SQL Server Compact 4.0 RTW required to be present on the system.

As always, please provide feedback, suggestions and reviews at the CodePlex site: http://sqlcetoolbox.codeplex.com

Migrate a SQL Server Compact database to SQL Server using Web Deploy (MSdeploy)

You can use the latest version of the msdeploy command line utility, which is included with WebMatrix, to migrate a SQL Server Compct database to SQL Server, or simply generate a script (in SQL Server T-SQL dialect) of the entire database, both schema and data.

On my PC, the msdeply.exe version 2.0 is located at C:Program Files (x86)IISMicrosoft Web Deploy V2

Below are some sample usages involving a SQL Server Compact 4 source database:

msdeploy -verb:sync
-source:dbFullSql="Data Source=C:datasqlcetestnw40.sdf",sqlCe=true 
-dest:dbFullSql="c:datascriptnw.sql"

This will create a script of the entire database in SQL Server T-SQL dialect, both with schema and data.

msdeploy -verb:sync -source:dbFullSql="Data Source=C:datasqlcetestnw40.sdf",sqlCe=true,scriptdata=False -dest:dbFullSql="c:datascriptnw.sql"

This will create a script of the entire database in SQL Server T-SQL dialect, schema only.

msdeploy -verb:sync -source:dbFullSql="Data Source=C:datasqlcetestnw40.sdf",sqlCe=true,NoCollation=true,SchemaQualify=false -dest:dbFullSql="c:datascriptnw.sql"

This will create a script of the entire database in SQL Server Compact compatible T-SQL dialect, both with schema and data.

Unfortunately, the script generated on my machine contains the following statement, which is not valid T-SQL:

INSERT INTO [Order Details]([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10001,25,9,8,30,0,15)

7 values to 5 columns – it is a localization issue, that I have reported  here, it can be fixed by changing the decimal point in regional settings.

You can read more about the supported scripting options here.

You can read more about the dbFullSql provider and it’s support for SQL Server Compact 4.0 here.

SQL Server Compact 4.0 released!

image_thumb[1]

image_thumb[5]

WebMatrix has been released and will be launched at CodeMash on Thursday 13/1. Sign up for the live streaming event here. 

Download locations

At the same time, SQL Server Compact 4.0 (build 4.0.8482.1) has been  released to web, and is available via Web Platform Installer 3.0 and also available for download here:

Microsoft SQL Server Compact 4.0 (x86 and x64)

SQL Server Compact 4.0 Books Online

Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0

(Team blog post about the VS 2010 SP1 tooling support)

Related blog posts

ScottGu has just issued a long blog post about using SQL Server Compact 4.0 with WebForms + Entity Framework Database First and MVC + Entity Framework Code First.

The SQL Compact team has a concise feature overview. And release announcement, which also includes information about scenarios not enabled with SQL Server Compact 4.0.

I have blogged about SQL Server Compact 4 at several occasions:

Getting started with SQL Server Compact 4.0 and ASP.NET 4.0 (no WebMatrix)

SQL Server Compact 4.0 news roundup

Downsize a SQL Server database to SQL Server Compact 4.0 (and 3.5)

SQL Server Compact version detector

SQL Server Compact 4.0 ASP.NET Membership provider

SQL Compact 4.0 now available as a .zip file

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Visual Studio Tools for SQL Server Compact 4 now available

SQL Server Compact “Private Deployment” on desktop–an overview

Comparison of SQL Server Compact 4 and SQL Server Express 2008 R2

Using SQL Server Compact 4.0 with WPF DataGrid, Entity Framework 4 and Private deployment (part 1)

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Using SQL Server Compact 4.0 with WPF DataGrid, Entity Framework 4 and Private deployment (part 1)

Using SQL Server Compact 4.0 with Desktop Private Deployment and a Setup project (MSI) (part 2)

Access SQL Server Compact 4 with ASP Classic and VbScript

Migrate a SQL Server Compact database to SQL Server using Web Deploy (MSdeploy)

Visual Studio 2010 Service Pack 1 with support for SQL Server Compact 4.0 released

Snapshot Synchronization with SQL Server Compact 4.0

Useful new topics in SQL Server Compact 4.0 Books Online

Saving images to SQL Server Compact with Entity Framework 4.1 Code First

Deployment improvements

The “Private” folder contains both the x64 and x86 related DLL files, and also contains the required C++ runtime DLL (new in RTM):

image

I will update my “Private Deployment” blog post with the new information.

No SQL Server Synchronization Supported

The SQL Server Compact 4.0 release does not support syncing of data with SQL Server using technologies like Microsoft Sync Framework, or merge replication, or remote data access (RDA).

Duplicate constraint names issue

The generation of the Entity Data Model for SQL Server Compact will fail if there are duplicate constraint names in the SQL Server Compact schema.

In SQL Server Compact, the constraint names are unique within a table and this can allow duplicate constraint names in the database. The behavior is different from SQL Server, where the constraint names are unique across the database. If a SQL Server Compact schema has duplicate referential integrity (primary key – foreign key relationship) constraint names, the generation of the Entity Data Model using the ADO.NET Entity Framework’s Entity Data Model Wizard will fail. The workaround is to change the name of the duplicate constraint name to be unique across the database, like by adding the name of the table to the constraint name.

I have worked around this limitation in my SQL Server Compact tools, SQL Server Compact Toolbox and SQL Server Compact Scripting Tools

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

We can now use two additional technologies, that both build on Entity Framework, to add some administrative features to our Chinook music shop, with minimal amount of coding effort:

– A website for administering the rarely used tables in the database (using a Dynamic Data website)

– Access to the Invoice data in Excel (using WCF Data Services)

Since we are not using the standard Entity Framework classes, but use a POCO based Data Access Layer, we need to accommodate slightly, as I will demonstrate.

Adding Dynamic Data website

Continuing where we left in Part One, add a ASP.NET Dynamic Data Entities Web Application to the solution:

image

Set this project as the Startup project. Add references to Chinook.Data and Chinook.Model from this project.

Modify the global.asax to refer to the ChinnokEntities ObjectContext:

public static void RegisterRoutes(RouteCollection routes)
{
DefaultModel.RegisterContext(typeof(Chinook.Model.ChinookEntities), new ContextConfiguration() { ScaffoldAllTables = true });

Also set ScaffoldAllTables = true for now – this means that admin pages are created for all the entities in the context.

Copy the connectionstrings section from web.config in the Chinook.UI project to the Chinnok.Admin project web.config file.

Try to run the site – you will get this error:

Could not find the CLR type for ‘ChinookModel.Track’.

This is due to the fact that we are using POCO, so the Entity Framework initializer does not load the required metadata. The solution for this is here, so we add a new class to the Chinook.Data project (called ChinookEntities.Custom.cs), with the following contents:

namespace Chinook.Model
{
public partial class ChinookEntities
{
//Dynamic Data
public ChinookEntities(bool dynamicData)
: base(ConnectionString, ContainerName)
{
var tracestring = this.CreateQuery<Genre>("ChinookEntities.Genres").ToTraceString();
}

}
}
Then use this constructor in global.asax, and also set scaffold all tables = false:
DefaultModel.RegisterContext((() => new Chinook.Model.ChinookEntities(true)), new ContextConfiguration() { ScaffoldAllTables = false });

When you now run the site, you will get this error, as no tables are available:

There are no accessible tables. Make sure that at least one data model is registered in Global.asax and scaffolding is enabled or implement custom pages.

To include the two tables in question, we must add some partial classes to the Model project. Add a reference to System.ComponentModel.DataAnnotations in the Model project, and add to new classes with the following contents:

// Dynamic Data - add ref to System.ComponentModel.DataAnnotations
// and set scaffoldtable = true to using partial class
using System.ComponentModel.DataAnnotations;

namespace Chinook.Model
{
[ScaffoldTable(true)]
public partial class MediaType
{
}
}

Now you can manage the Genres and MediaTypes tables:

image

Adding WCF Data Service (OData)

The accounting department would like to access the Invoice data in our Music Shop from Excel. PowerPivot can connect to many data sources, but not directly to a SQL Server Compact database file, and access via the OLEDB provider is broken. But PowerPivot can access an OData feed, so let’s create one to expose the SQL Server Compact Invoice and InvoiceLine tables.

Add a WCF Data Service to the Chinook.Admin project:

image

Modify the AccountingService class as follow, and add a “using Chinook.Model” statement:

public class AccountingService : DataService<ChinookEntities>
{
public static void InitializeService(DataServiceConfiguration config)
{
config.SetEntitySetAccessRule("Invoices", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("InvoiceLines", EntitySetRights.AllRead);
config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}

protected override ChinookEntities CreateDataSource()
{
var context = new ChinookEntities(true);
// Avoid dynamic proxies, as they cannot be serialized
context.ContextOptions.ProxyCreationEnabled = false;
return context;
}
}
We only allow read-only access to the Invoices and InvoiceLines entities. In addition, we must override CreateDataSource, to solve the problem with metadata we also had with Dynamic Data and disable ProxyCreation.
You can now point PowerPivot to the AccountingService.svc URL and access the invoice data from Excel:
image
image

Deployment

Finally a few words on deployment and connection handling. As you may know, opening and closing connections to a SQL Server Compact file is a costly operation, and there is no concept of Connection Pooling with SQL Server Compact. A way to mimic a connection pool is to keep a dummy connection (that is not otherwise used) open for the duration of your application’s lifetime. For a web application, this can be achieved I global.asax, in the Application_Start event handler. In this sample, I have implemented a possible solution in the UI project, by calling the: Chinook.Repository.ContextHelper.Open();

The ContextHelper is implemented as follows:

public static class ContextHelper
{
private static ChinookEntities context ;
private static object objLock = new object();

public static void Open()
{
lock (objLock)
{
if (context != null)
throw new InvalidOperationException("Already opened");
context = new ChinookEntities();
context.Connection.Open();
}
}

}

Calling Open in this singleton class will keep a connection to the SQL Sever Compact file open for the lifetime of the application, and make subsequent  calls to Connection.Open fast.

For deployment, we must move the sdf file the App_Data folder, I have done this in the UI project, and also change the connection string in web.config as follows:

provider connection string=&quot;Data Source=|DataDirectory|Chinook40.sdf&quot;

Hope you found this walkthrough useful. For a deeper dive into Entity Framework 4, I highly recommend the Julie Lerman book.

You can download the full solution from here: