Batch Execution in Firebird

Executing a .sql file on Firebird Database can be quite a messy job. The lack of proper documentation and support would hurt you more when encountered issues and that is what I faced when I required to do execute contends of a .Sql File. Having understood that FbBatchExecution is the command I would required, I went to write following Function in C#.
public void ExecuteTransaction()
{
    try
    {
        if (m_cmd.Connection.State == System.Data. ConnectionState.Closed)
            m_cmd.Connection.Open();

        using ( FbTransaction fbTransaction = m_connnection.BeginTransaction())
        {
            FbScript script = new FbScript( this.ScriptFileContends);
            m_cmd.Transaction = fbTransaction;
            script.Parse();
            var BatchExecute = new FbBatchExecution(m_cmd.Connection, script);
            BatchExecute.Execute( true);
        }
    }
    catch ( Exception Ex)
    {
		throw Ex;
    }
}
This looked reasonable, I am reading out the Contends of the File using FbScript object and using the FbBatchExecution Object for bulk queries. I am managing the whole operation using FbTransaction. All seems well until I ran it and came up with following Exception.
“Execute requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized”
After hours of browsing, it turned out to be simple actually. All I needed for to add following lines.
 BatchExecute.CommandExecuting += delegate( object sender, CommandExecutingEventArgs args)
{
      args.SqlCommand.Transaction = fbTransaction;
};
The new Method would like following and you all set to go.
 public void ExecuteTransaction()
{
    try
    {
        if (m_cmd.Connection.State == System.Data. ConnectionState.Closed)
            m_cmd.Connection.Open();

        using ( FbTransaction fbTransaction = m_connnection.BeginTransaction())
        {
            FbScript script = new FbScript( this.ScriptFileContends);
            m_cmd.Transaction = fbTransaction;
            script.Parse();
            var BatchExecute = new FbBatchExecution(m_cmd.Connection, script);
			BatchExecute.CommandExecuting += delegate( object sender, CommandExecutingEventArgs args)
            {
                args.SqlCommand.Transaction = fbTransaction;
            };
            BatchExecute.Execute( true);
        }
    }
    catch ( Exception Ex)
    {
		throw Ex;
    }
}

2 thoughts on “Batch Execution in Firebird”

Leave a comment