DatabaseHelper for WebMatrix

Helper method that makes it easier to create a new database from a database schema file.

648

This helper makes it easier to create a database based upon a schema file. WebMatrix beta has some unfinished features in the database editor, so instead of maintaining the database using WebMatrix, it's easier to just write simple SQL code and run that in the startup of your ASP.NET Web Pages 1.0.

using System;
using System.IO;
using System.Web;
using System.Data.SqlServerCe;

public static class DatabaseHelper
{
    private const string SEPARATOR = "GO";

    /// <summary>
    /// Helps you create a new SQL CE database with the specified filename and database schema.
    /// </summary>
    /// <param name="fileName">Can either be filename without extension, with extenion or full path.</param>
    /// <param name="schema">The database schema to run on the newly created database.</param>
    public static void Create(string databaseFilename, string schemaFilename)
    {
        var filePath = ParseDatabaseFilename(databaseFilename);
        var schemaPath = ParseSchemaFilename(schemaFilename);

        if (File.Exists(filePath))
        {
            throw new Exception("The database file already exists.");
        }

        if (!File.Exists(schemaPath))
        {
            throw new Exception("Database schema file does not exist.");
        }

        var schemaContent = File.ReadAllText(schemaPath);
        var connectionString = "Data Source=" + filePath;

        using (var engine = new SqlCeEngine(connectionString))
        {
            engine.CreateDatabase();
            CreateDatabaseSchema(schemaContent, connectionString);
        }
    }

    /// <summary>
    /// Verifies if a database already exists.
    /// </summary>
    /// <param name="databaseFilename">Filename of the database, can be full physical path or simply the name of database inside the App_Data folder.</param>
    /// <returns>Indication if the database exists.</returns>
    public static bool Exists(string databaseFilename)
    {
        var filePath = ParseDatabaseFilename(databaseFilename);
        return File.Exists(filePath);
    }

    private static string ParseSchemaFilename(string schemaFilename)
    {
        var filePath = schemaFilename;

        if (!Path.IsPathRooted(filePath))
        {
            filePath = HttpContext.Current.Server.MapPath("~/App_Data/" + filePath);
        }

        return filePath;
    }

    private static string ParseDatabaseFilename(string databaseFilename)
    {
        var filePath = databaseFilename;
        var fileExtension = Path.GetExtension(filePath);

        // Append the .sdf (SQLCE) extension if not supplied.
        if (fileExtension.ToLower() != ".sdf")
        {
            filePath += ".sdf";
        }

        if (!Path.IsPathRooted(filePath))
        {
            filePath = HttpContext.Current.Server.MapPath("~/App_Data/" + filePath);
        }

        return filePath;
    }

    private static void CreateDatabaseSchema(string databaseSchema, string connectionString)
    {
        using (var conn = new SqlCeConnection(connectionString))
        {
            string[] commands = databaseSchema.Split(new string[] { SEPARATOR }, StringSplitOptions.RemoveEmptyEntries);

            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = conn;
            conn.Open();

            foreach (var command in commands)
            {
                var commandLine = command.Trim();

                if (!string.IsNullOrEmpty(commandLine))
                {
                    cmd.CommandText = commandLine;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

Here is a sample database schema included for reference:

CREATE TABLE Author(
	  Id uniqueidentifier NOT NULL DEFAULT(newid()) CONSTRAINT Author_PK PRIMARY KEY,
	  Created datetime NOT NULL DEFAULT(getdate()),
	  Modified datetime NULL,
	  Name nvarchar(200) NOT NULL,
	  Email nvarchar(200) NULL,
	  UserName nvarchar(100) NULL,
	  PasswordHash nvarchar(100) NULL,
	  PasswordSalt nvarchar(100) NULL
)
GO

CREATE TABLE Category(
	  Id uniqueidentifier NOT NULL CONSTRAINT Category_PK PRIMARY KEY,
	  Created datetime NOT NULL DEFAULT(getdate()),
	  Modified datetime NULL,
	  Name nvarchar(200) NOT NULL
)
GO

CREATE TABLE Comment(
	  Id uniqueidentifier NOT NULL CONSTRAINT Comment_PK PRIMARY KEY,
	  Created datetime NOT NULL DEFAULT(getdate()),
	  Modified datetime NULL,
	  [Status] smallint NULL,
	  Title nvarchar(200) NULL,
	  Content ntext NULL,
	  UserName nvarchar(100) NULL,
	  UserEmail nvarchar(100) NULL,
	  UserUrl nvarchar(200) NULL
)
GO

CREATE TABLE Post(
	  Id uniqueidentifier NOT NULL CONSTRAINT Post_PK PRIMARY KEY,
	  Created datetime NOT NULL DEFAULT(getdate()),
	  Modified datetime NULL,
	  [Status] smallint NULL,
	  Url nvarchar(200) NULL,
	  Title nvarchar(200) NULL,
	  Content ntext NULL,
	  AuthorId uniqueidentifier NULL
)
GO

CREATE TABLE Setting(
	  Id int NOT NULL IDENTITY CONSTRAINT Setting_PK PRIMARY KEY,
	  Created datetime NOT NULL DEFAULT(getdate()),
	  Modified datetime NULL,
	  Title nvarchar(200) NULL,
	  Subtitle nvarchar(200) NULL,
	  FeedUrl nvarchar(200) NULL,
	  SchemaVersion smallint NOT NULL DEFAULT(1)
)
GO

INSERT INTO Author (Name, Email, UserName) VALUES('Administrator', '', 'admin')
GO

INSERT INTO Setting (Title, Subtitle) VALUES('My Personal Blog', 'my random thoughts.')
GO

Examples on use:

    if (!DatabaseHelper.Exists("cirium.blog"))
    {
        DatabaseHelper.Create("cirium.blog", "DatabaseSchema.sql");
    }

The method also supports full path to the database file and schema file, example above shows how the Create method works when files are stores in the App_Data folder.