SQL Server Compact 4.0 ASP.NET Membership provider

According to ScottGu, an ASP.NET membership provider that works with SQL Server Compact 4 is not coming in the near future. This poses a problem, if you would like to use Forms authentication with a website using only SQL Server Compact 4.

To remedy this, I have implemented an ASP.NET Membership provider for SQL Server Compact 4.0 (CTP1), for use with Forms Authentication for small web sites using only a single SQL Server Compact 4.0 database. The project provides files that contain a Membership provider and Role provider for ASP.NET. (Note that SQL Server Compact 4 is in beta, and is not supported for production).

How to use the provided files (3 simple steps)

1. Change SqlCeMembershipProvider::encryptionKey to a random hexadecimal value of your choice.

2. Copy the three files in the /App_Code folder to your web sites' ~/App_Code folder.

3. Modify your web.config using the template on the CodePlex page 
(if you are on a shared hosting server, you will have to set writeExceptionsToEventLog to false).

That's it - you can now create users, roles and use the ASP.NET login controls.
Site file layout (to verify that the provider is working):



If you encounter any bugs, have suggestions or any other issues, please provide feedback here

SQL Server Compact version detector

With three current SQL Server Compact versions (file formats) available 
3.0/3.1 on Windows Mobile 6.x devices,
3.5 with Visual Studio 2008 and 2010,
and 4.0 currently available as a CTP (Community Technical Preview),
it can sometimes be hard to tell what the file version of a SQL Compact file in your possession is.

To help you, I have created a small utility that you can place on your desktop and drop any SQL Server Compact file on.

You can download a .zip file with the utility here. And the source code for the utility is available here.

Place the SQL Compact Detector.exe on your desktop, and drop a SQL Server Compact file on it!

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

Release 3.0.0.6 of my SQL Compact schema and data script utility on Codeplex contains a command line utility, named Export2SqlCe, that allows you to export schema and data from a SQL Server 2005/2008 database in a SQL Compact compatible SQL format.

The Export2SqlCe tool does not create a SQL Compact sdf database file, but just creates a T-SQL script , that you can run with a tool like my SqlCeCmd Codeplex utility or SQL Server Management Studio 2008. This approach gives you the flexibility to modify the script in a text editor before creating the sdf file – but requires an extra step.

Below are the steps required to migrate a SQL Server database (tables and table indexes/constraints only) to a SQL Server Compact database.

First, run Export2SqlCe against your SQL Server database (2005 and 2008 are supported), using a command line similar to:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce

This will create a file named C:\aw.sqlce – let’s have a look:

This file contains a script to create tables, data and constraints (indexes and foreign keys), all in SQL Server Compact 3.5/4.0 compatible T-SQL script.

Then you can either open the aw.sqlce script in SQL Server Management Studio 2008 or use sqlcecmd to create the sdf file and populate the file based on the script – meaning the whole process can be run from a batch file and completely automated!

(To manage SQL Server 4.0 databases, use SqlCeCmd40.exe, for 3.5 use SqlCeCmd.exe)

First create the database:

sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create

Then run the generated script against the database:

sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt

Examine the log.txt to ensure no errors occurred - search for “error code” – please let me know if you encounter any errors, so they can possibly be fixed in an update to the utility.

Entire batch file:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce
sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create

sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt

del log.txt

for %%f in (aw*.sqlce) do sqlcecmd -d "Data Source= C:\aw.sdf " -i %%f >> log.txtt

SQL Server Compact 4.0 news roundup

Since ScottGu’s recent announcement about SQL Compact support for ASP.NET, there have been additional releated postings on the web. This post is an attempt to give an overview of these.

At the recent MVC Virtual Conference, Ambrish Mishra, Senior Program Manager in the SQL Server Compact product development team, gave a presentation on SQL Compact 4.0. The slides and video from this presentation are now available.

On Channel 9, Scott Hanselman has posted a video where he chats with developer Damian Edwards about "Razor," IIS Express, SQL 4 Compact Edition and VS2010 Tooling.

Finally, Chris Auld demonstrates how to use Windows Azure Drives and SQL Server Compact 4.0

SQL Server Compact 3.5 Toolbox updated

My Visual Studio 2010 add-in for SQL Server Compact 3.5 has just been updated. The new version 1.2 contains the following new features and fixes a bug:

- Parse and Show Estimated Plan features to editor
- improved DGML diagram (table fields with various info added)
- check for newer version on load
- BUG: Identity columns were ignored in scripts

Download the latest version from here: http://visualstudiogallery.msdn.microsoft.com/en-us/0e313dfd-be80-4afb-b5e9-6e74d369f7a1

SQL editor improvements

To show the Estimated Execution Plan, you must either have Visual Studio 2010 Premium or Ultimate installed. If you are using Visual Studio 2010 Professional, you must have SQL Server Management Studio installed to see the graphical execution plan.

Improved DGML diagram

In addition to showing table relationships, the generated DGML diagram (which you can view in Visual Studio 2010 Premium or higher) now contains all table fields and a visual indication if a field is either a primary key, a foreign key, optional (NULLable).

Update notification

 

If the add-in has been updated, you will get a visual indication, and a link to the CodePlex site.

HOW TO: Upgrade a version 3.x database file to SQL Server Compact 4.0

See this excellent sample: http://blogs.msdn.com/b/jimmytr/archive/2010/04/26/upgrade-sql-ce-database-from-3-1-to-3-5.aspx

I have updated the sample extension method to work with 4.0. Notice that the Upgrade method allows you to upgrade both from 3.1 and 3.5 file formats.

Due to a bug in the 4.0 CTP1, it is currently not possible to do in-place database upgrades (as the code below does), see this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/872e49da-1d3e-457a-9288-c1e1f0c90dc8

public static class SqlCeUpgrade
{
    public static void EnsureVersion40(this System.Data.SqlServerCe.SqlCeEngine engine, string filename)
    {
        SQLCEVersion fileversion = DetermineVersion(filename);
        if (fileversion == SQLCEVersion.SQLCE20)
            throw new ApplicationException("Unable to upgrade from 2.0 to 4.0");

        if (SQLCEVersion.SQLCE40 > fileversion)
        {
            engine.Upgrade();
        }
    }
    private enum SQLCEVersion
    {
        SQLCE20 = 0,
        SQLCE30 = 1,
        SQLCE35 = 2,
        SQLCE40 = 3
    }
    private static SQLCEVersion DetermineVersion(string filename)
    {
        var versionDictionary = new Dictionary<int, SQLCEVersion> 
        { 
            { 0x73616261, SQLCEVersion.SQLCE20 }, 
            { 0x002dd714, SQLCEVersion.SQLCE30},
            { 0x00357b9d, SQLCEVersion.SQLCE35},
            { 0x003d0900, SQLCEVersion.SQLCE40}
        };
        int versionLONGWORD = 0;
        try
        {
            using (var fs = new FileStream(filename, FileMode.Open))
            {
                fs.Seek(16, SeekOrigin.Begin);
                using (BinaryReader reader = new BinaryReader(fs))
                {
                    versionLONGWORD = reader.ReadInt32();
                }
            }
        }
        catch
        {
            throw;
        }
        if (versionDictionary.ContainsKey(versionLONGWORD))
        {
            return versionDictionary[versionLONGWORD];
        }
        else
        {
            throw new ApplicationException("Unable to determine database file version");
        }
    }


}

Sample usage:

string filename = @"C:\Data\SQLCE\Northwind31.sdf";
var engine = new System.Data.SqlServerCe.SqlCeEngine("Data Source=" + filename);
engine.EnsureVersion40(filename);