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:

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

Scripting image column data for INSERT statements

I earlier blogged about scripting datetime values for INSERT statements, or for example by use in the SubmitSQL method of the SQL Server Compact RemoteDataAccess API. In this post, I will show how to serialize byte arrays as a hexadecimal string. Notice that for the SubmitSQL method, there is a limit to the size of the SQL statement (not sure what is is, but 64 K is a good guess, I think). So if you have large images, that you want to send to your server, you are out of luck with this method.

Below is the code I currently have implemented in my ExportSqlCE INSERT statement generator.

if (dt.Columns[iColumn].DataType == typeof(Byte[]))
{
    Byte[] buffer = (Byte[])dt.Rows[iRow][iColumn];
    _sbScript.Append("0x");
    for (int i = 0; i < buffer.Length; i++)
    {
        _sbScript.Append(buffer[i].ToString("X2", System.Globalization.CultureInfo.InvariantCulture));
    }
}

Notice the special “X2” string format, this is what performs the magic of creating the hex string. Prepending the string with 0x is required by SQL Server. (_sbScript is a StringBuilder)

There is a long discussion on Stackoverflow on how to do this faster, the code below seems to be the fastest:

private static string ByteArrayToHex(byte[] barray)

{


char[] c = new char[barray.Length * 2];


byte b;


for (int i = 0; i < barray.Length; ++i)


{


b = ((byte)(barray[i] >> 4));


c[i * 2] = (char)(b > 9 ? b + 0x37 : b + 0x30);


b = ((byte)(barray[i] & 0xF));


c[i * 2 + 1] = (char)(b > 9 ? b + 0x37 : b + 0x30);


}


return new string(c);


}




Maybe I should update my ExportSqlCe code? - Happy scripting!

SQL Server Compact Toolbox standalone - including all managed DLLs in a single .exe

The latest release of the standalone version of my SQL Server Compact Toolbox, mainly for users that do not have Visual Studio 2010 Pro or higher, is available as a single .exe. It was actually a Tweet from @scottgal, that pointed me towards this excerpt from Jeffery Richters’ CLR via C#, Third Edition.

In order to implement in the WPF application, that is the standalone Toolbox, I added the following code to App.xaml.cs (and a Startup handler to App.xaml):

private void Application_Startup(object sender, StartupEventArgs e)
{
    AppDomain.CurrentDomain.AssemblyResolve += (ssender, args) =>
    {
        //string[] names = this.GetType().Assembly.GetManifestResourceNames();

        String resourceName = "ErikEJ.SqlCeToolbox." +
           new AssemblyName(args.Name).Name + ".dll";

        using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
        {
            Byte[] assemblyData = new Byte[stream.Length];
            stream.Read(assemblyData, 0, assemblyData.Length);
            return Assembly.Load(assemblyData);
        }

    }; 
}

I also added all the Managed libraries that the Toolbox uses as Embedded Resources.

I use the following libraries, all from CodePlex:

WPF Property Grid 
http://wpg.codeplex.com/ (for the SqlCeConnectionStringBuilder)
Sample usage

KBCsv 
http://kbcsv.codeplex.com/ (for .csv file import)
Sample usage

SQL Server Compact Scripting Library (for database scripting) 
http://exportsqlce.codeplex.com/
Sample usage Sample usage

FabTab WPF Tab Control (the SQL Editor tabs)
http://fabtab.codeplex.com/
Sample usage

And the Microsoft Data Connection Dialog (to prompt fro a SQL Server Connection) from http://archive.msdn.microsoft.com/Connection
Sample usage

Hope you find this tip useful.

New release of Scripting Library and command line utilities–with Schema Diff and DGML from command line

The latest release of my ExportSqlCe SQL Server Compact scripting library and related command line utilities is now available on CodePlex.

This latest version of the command line utilities adds the capability to generate Schema Diff and DGML database graph files.

The schema diff option allows you to compare a SQL Server Compact database file with another SQL Server Compact database file or even a SQL Server database, and creates a script with the required ALTER TABLE etc. statements to synchronize the 2 database schemas.

The DGML option allows you to create a graphical view of the database tables and fields, the resulting .dgml file requires Visual Studio 2010 Premium or higher to be viewed. I blogged about DGML files earlier:

http://erikej.blogspot.com/2010/04/diagram-database-table-relationships.html

http://erikej.blogspot.com/2010/08/sql-server-compact-35-toolbox-updated.html

The latest documentation for the command line utilities is available here.

And both these file types can of course be generated from your own application, using the scripting library. I have some code samples available here.

SQL Server Compact will be available for developers in upcoming Windows Phone “Mango” update

Just a quick note the let you know, that SQL Server Compact will be exposed to Windows Phone developers as announced at the MIX11 conference. It is already on the device, as I blogged about earlier. The Windows Phone Developer blog has an overview of what is included for developers in the update, expected to be available this year. The developer tools update will appear next month, and I will blog more details about the API and other findings. The database will not be exposed via ADO.NET, as System.Data is not available in Silverlight, but the database will be accessible via LINQ. More details to follow when the API is published, and I have had a play with the developer tools. This MIX11 presentation demonstrates some of the details of working with SQL Server Compact on Windows Phone via Linq and DataContext only.

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:\data\sqlce\test\nw40.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.