SQL Server Compact Code Snippet #20 : change database password

The SqlCeEngine class (in the System.Data.SqlServerCe ADO.NET provider) includes a number of useful methods for handling global database chores, like verifying the integrity of the database, and shrink the database file. It also includes the Compact method, which creates a new database file (optionally in-place) of an existing database file. During this process, you can specify a number of database creation parameters, namely the collation order, the case sensitivity or the password of the database. In code:

    using (SqlCeEngine engine = 
        new SqlCeEngine(@"Data Source=C:\temp\mysecretdb.sdf;Password=oldpassword"))
    { 
        engine.Compact("Data Source=;Password=newpassword");
    }

Notice the special data source key used, specifying that the new database should be created in-place.

SQL Server Compact Code Snippet #19 : migrate a SQL Server database to SQL Compact

This snippet again demonstrates my SQL Server Compact scripting API, some initial guidance here. For a blog post describing the opposite direction, see this. Notice, that is you are scripting a SQL Server Compact 4.0 database file, you can install the required DLL files via NuGet (ErikEJ.SqlCEScripting).

This time I will demonstrate how to migrate a complete SQL Server (LocalDB/Express/Full) database to SQL Server Compact. The requirements are simply that the current user has read access to the SQL Server database. Then all tables, constraints, indexes and data will be moved to an empty SQL Compact database, all in just 6 lines of code:

using (IRepository serverRepository = new ServerDBRepository4(@"Data Source=.;Trusted_Connection=true;Initial Catalog=Chinook"))
{
    string fileName = Path.GetTempFileName();
    var generator = new Generator4(serverRepository, fileName);
    generator.ScriptDatabaseToFile(Scope.SchemaData);

    var helper = new SqlCeHelper4();
    var sqlCeConnectionString = @"Data Source=C:\temp\newdb.sdf";
    helper.CreateDatabase(sqlCeConnectionString);
                
    using (IRepository sqlCeRepository = new DB4Repository(sqlCeConnectionString))
    {
        sqlCeRepository.ExecuteSqlFile(fileName);
    }
}

The code requires the following using statements:

using ErikEJ.SqlCeScripting;
using System.IO;

The ServerDBRepository constructor simply requires any valid SQL Server ADO.NET connection string.

The ScriptDatabaseToFile creates a script file with all content of the database, and the ExecuteSqlFile method runs the script against a SQL Server database.

Notice the use of the SqlCeHelper4 class, which creates an empty database file.

Primeworks, supplier of tools for SQL Server Compact, is closing, making all products free, open source

João Paulo Figueira, owner of Primeworks, http://primeworks-mobile.com/, just announced that the company is closing. In a blog posting João announced yesterday: “The business volume has decreased so steeply that it can no longer support the product development and support.”

Primeworks offers complete set of excellent tools for working with all versions of SQL Server Compact database files, both on the desktop and directly on a Windows Mobile/CE device.

Luckily, the tools will continue to be available for download, and eventually even become open source.  João states: “In the very near future, all products will be removed from our online business platform and the licensing requirements removed from the distribution installers. All products will be essentially free (copyrights retained). Next, I will devote some time to publish all the source code and convert these products into open source projects.”

I am sorry to see Primeworks leave the market, but very happy that the excellent tools will now become available for free.