Populating a Windows Phone “Mango” SQL Server Compact database on desktop

For up to the minute news, outcries, complaints and cheers on SQL Server Compact, follow me on Twitter: @ErikEJ

If you want to prepopulate a Mango SQL Server Compact database with some data, you can use the following procedure to do this. (Notice, that the Mango tools are currently in beta)

First, define your data context, and run code to create the database on the device/emulator, using the CreateDatabase method of the DataContext. See a sample here. This will create the database structure on your device/emulator, but without any initial data.

Then use the Windows Phone 7 Isolated Storage Explorer to copy the database from the device to your desktop, as described here.

You can now use any tool, see the list of third party tools on this blog, to populate your tables with data as required. The file format is version 3.5. (not 4.0)

Finally, include the pre-populated database in your WP application as an Embedded Resource.

UPDATE: I was made aware, that for read only data, you can just include the database file as Content (not Embedded Resource), and it will be available from the special appdata: URI, with a connection string like the following:

“Data Source=appdata:/Chinook.sdf;Mode=Read Only”

So no need to run code to extract from an Embedded resource as below in that case.

You can then use code like the following to write out the database file to Isolated Storage on first run:

public class Chinook : System.Data.Linq.DataContext
{
    public static string ConnectionString = "Data Source=isostore:/Chinook.sdf";

    public static string FileName = "Chinook.sdf";

    public Chinook(string connectionString) : base(connectionString) { }

    public void CreateIfNotExists()
    {
        using (var db = new Chinook(Chinook.ConnectionString))
        {
            if (!db.DatabaseExists())
            {
                string[] names = this.GetType().Assembly.GetManifestResourceNames();
                string name = names.Where(n => n.EndsWith(FileName)).FirstOrDefault();
                if (name != null)
                {
                    using (Stream resourceStream = Assembly.GetExecutingAssembly().GetManifestResourceStream(name))
                    {
                        if (resourceStream != null)
                        {
                            using (IsolatedStorageFile myIsolatedStorage = IsolatedStorageFile.GetUserStoreForApplication())
                            {
                                using (IsolatedStorageFileStream fileStream = new IsolatedStorageFileStream(FileName, FileMode.Create, myIsolatedStorage))
                                {
                                    using (BinaryWriter writer = new BinaryWriter(fileStream))
                                    {
                                        long length = resourceStream.Length;
                                        byte[] buffer = new byte[32];
                                        int readCount = 0;
                                        using (BinaryReader reader = new BinaryReader(resourceStream))
                                        {
                                            // read file in chunks in order to reduce memory consumption and increase performance                    
                                            while (readCount < length)
                                            {
                                                int actual = reader.Read(buffer, 0, buffer.Length);
                                                readCount += actual;
                                                writer.Write(buffer, 0, actual);
                                            }
                                        }
                                    }

                                }
                            }
                        }

                        else
                        {
                            db.CreateDatabase();
                        }
                    }
                }
                else
                {
                    db.CreateDatabase();
                }
            }
        }
    }
}