Create a Firebird database from script file with ActiveRecord

Once I needed to create a Firebird database from a SQL script file using the ActiveRecord component from Castle Project. First problem was to convince ActiveRecord to execute a non-query command and the second, to execute the script file using the Firebird embedded engine.

Execute non-query commands with ActiveRecord

The main idea is to grab the IDbConnection object from the current ActiveRecord session. After that, you can use this connection any way you want to.

C# .NET

ActiveRecordMediator.Execute(typeof(ActiveRecordBase),

    delegate(ISession session, object data)

    {

        IDbConnection conn = session.Connection;

        IDbCommand cmd = conn.CreateCommand();

 

        session.Transaction.Enlist(cmd);

 

        cmd.CommandText = sqlCommand;

 

        return null;

    },

    null);

Create database by script file on Firebird embedded

Here the problem is that Firebird can't execute the entire sql script file as a sigle statement. Doing so, you will get an "Dynamic SQL error" right before the second "create table" command.

The solution is to split the script file content by ";" to get each SQL statement as a single chunk. Some code clean-up is also required.

C# .NET

string[] scriptCommands = scriptFileContent.Split(";".ToCharArray());

 

string pattern = @"[\n\r]";

string fixupPattern = @"\x20{2,}?";

 

foreach (string scriptCmd in scriptCommands)

{

    Regex reg = new Regex(pattern, RegexOptions.Multiline);

    string sqlCommand = reg.Replace(scriptCmd, "\x20");

    Regex fixup = new Regex(fixupPattern);

    sqlCommand = fixup.Replace(sqlCommand, "\x20").Trim();

 

    if (!String.IsNullOrEmpty(sqlCommand))

    {

        ActiveRecordMediator.Execute(typeof(ActiveRecordBase),

    delegate(ISession session, object data)

    {

        IDbConnection conn = session.Connection;

        IDbCommand cmd = conn.CreateCommand();

 

        session.Transaction.Enlist(cmd);

 

        cmd.CommandText = sqlCommand + ";";

        cmd.ExecuteNonQuery();

 

        return null;

    },

    null);

    }

}