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; } }
what is “m_cmd” (FbCommand?) and where should I create it?
LikeLike
Hi Efrat.
m_Cmd is an instance of an wrapper class that handles FbCommand.
LikeLike