Create a SQL Server CE Database From SQL Schema File

Introduction

In this article, I will explain how to create a SQL Server CE .sdf database file from a database schema defined in .sql scripts. In one project recently, I've automatically generated the .sql scripts from the logic model, and because there is no tool (at least, I don't know) for generating the SQL Server CE .sdf database file from these SQL scripts. Then, I decided to create a tool whose input is SQL scripts with the database schema and whose output is the .sdf database file.

Getting started with the solution

Let's open the Visual Studio.NET 2008 IDE and create a solution named create_sqlcedb. You have to create a reference to the System.Data.SqlServerCe assembly (see Figure 1).

Figure 1

Now let's add the SqlCEDatabaseCreator class and its ISqlCEDatabaseCreator interface to implement the underlying business logic of the application. The interface is very simple. It comprises two methods: one to create the database file and the other to get and set the command separator between the SQL commands in the database schema. Traditionally, this separator is the GO string, but the component is flexible to receive a set of define command separators (see Listing 1).

Listing 1

public interface ISqlCEDatabaseCreator
{
    void Create(string strCEDatabaseFilePath, string strSqlScriptsFilePath);
    string CommandSeparator { get; set; }
}

Now the SQLCEDatabaseCreator class realizes this interface (see Listing 2).

Listing 2

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlServerCe;

namespace Olamendy.Data.Utils.SqlCEDatabaseCreatorPkg
{
    public class SqlCEDatabaseCreator : ISqlCEDatabaseCreator
    {
        #region Private attributes
        private string m_strCommandSeparator = "go";
        #endregion
        #region Private methods

        private void prvCreateDatabaseSchema(string strDatabaseSchema, string strConnString)
        {
            using (SqlCeConnection objConn = new SqlCeConnection(strConnString))
            {
                string[] arrCommands = strDatabaseSchema.Split(new string[] { this.m_strCommandSeparator }, StringSplitOptions.RemoveEmptyEntries);

                SqlCeCommand objCmd = new SqlCeCommand();
                objCmd.Connection = objConn;
                objConn.Open();
                foreach (string strCmd in arrCommands)
                {
                    string strTrimmedCmd = strCmd.Trim();
                    if (!String.IsNullOrEmpty(strTrimmedCmd))
                    {
                        objCmd.CommandText = strTrimmedCmd;
                        objCmd.ExecuteNonQuery();
                    }
                }
            }
        }
        #endregion
        #region ISqlCEDatabaseCreator Members

        public void Create(string strCEDatabaseFilePath, string strSqlScriptsFilePath)
        {
            if (System.IO.File.Exists(strCEDatabaseFilePath))
            {
                string strMessage = String.Format("SQL CE database file {0} already exists",strCEDatabaseFilePath);
                throw new Exception(strMessage);
            }
            string strDatabaseSchema = File.ReadAllText(strSqlScriptsFilePath);
            string strConnString = "Data Source=" + strCEDatabaseFilePath;

            using (SqlCeEngine objCeEngine = new SqlCeEngine(strConnString))
            {
                objCeEngine.CreateDatabase();
                this.prvCreateDatabaseSchema(strDatabaseSchema, strConnString);
            }
        }

        public string CommandSeparator
        {
            get
            {
                return this.m_strCommandSeparator;
            }
            set
            {
                this.m_strCommandSeparator = value;
            }
        }
        #endregion
    }
}