如何执行在C＃中的火鸟块语句 [英] How to execute a block statement in C# for Firebird

问题描述

我尝试使用下面的命令来删除在火鸟数据库中所有表：

I am trying to delete all tables in a database in Firebird using the following command:

string dropAllForeignKeysSql =
  "EXECUTE BLOCK RETURNS (stmt VARCHAR(1000)) AS "+
  "BEGIN "+
  "FOR "+
  "select 'alter table \"'||r.rdb$relation_name ||'\" drop constraint '||r.rdb$constraint_name||';' "+
  "from rdb$relation_constraints r "+
  "where (r.rdb$constraint_type='FOREIGN KEY') "+
  "into :stmt "+
  " DO begin execute statement :stmt; end "+
  "END "+
  ";";

using (var connection = sessionFactory.OpenSession().Connection)
  {
    var command = connection.CreateCommand();
    command.CommandText = dropAllForeignKeysSql;
    command.ExecuteNonQuery();
  }

不幸的是，不执行该命令。然而，当我暂停调试器，复制在的CommandText 变量字符串，并执行手动/不代码，执行语句的查询。如果我像 DROP TABLE myTable的一个简单的命令替换的CommandText ，那么这个命令在代码中执行，即表被删除。运行代码（在对比的时候我故意输入一个错误的命令或外部工具打开数据库）时，不会引发错误。

Unfortunately, the command is not executed. However, when I pause the debugger, copy the string in the CommandText variable and execute the query manually/ not in code, the statement is executed. If I replace the CommandText with a simpler command like DROP TABLE myTable, then this command IS executed in the code, i.e. the table is deleted. No error is thrown when running the code (in contrast to when I intentionally enter a wrong command or open the database in an external tool).

从那里，我的结论是错误是不是在SQL语句中，而不是在命令设置，而不是在已经建立了一个错误的连接。还有什么地方可能出错呢？

From there, I conclude that the error is not in the SQL statement and not in the command setup and not in having set up a wrong connection. Where else could the error be?

推荐答案

您应该使用FbBatchExecution

You should use FbBatchExecution

FirebirdSql.Data.FirebirdClient.FbTransaction fbt = fbc.BeginTransaction(); // object fbc is your FirebirdSql.Data.FirebirdClient.FbConnection

FirebirdSql.Data.Isql.FbBatchExecution fbe = new FirebirdSql.Data.Isql.FbBatchExecution(fbc);
fbe.SqlStatements.Add(dropAllForeignKeysSql); // Your string here

fbe.Execute(true);

fbt.Commit();

和变化'术语'之前就开始脚本：

And change 'term' before begin script:

SET term #;

这篇关于如何执行在C＃中的火鸟块语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！