tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Running commands on Firebird in background and canceling

3 Apr 2010 .NET, Firebird

The soon to be released Firebird 2.5 has a new ability to cancel running command (or any operation currently being processed by server) via API (in 2.1 you can do it via monitoring tables). This is a nice feature, interesting not only for database administration tools.

I was working on supporting it for a while in .NET provider, but I’m happy to say that it’s done (though I may tune the boundaries based on feedback). From some initial proposal in list I picked one following the design of SqlClient, as it’s a de facto standard in ADO.NET world.

So right now you can do:

using (FbConnection conn = new FbConnection(@"database=localhost:rrr.fdb;user=sysdba;password=masterkey"))
{
	conn.Open();
	//conn.DisableCancel();
	//conn.EnableCancel();
	using (FbCommand cmd = conn.CreateCommand())
	{
		cmd.CommandText =
@"execute block
as
declare cnt int;
begin
cnt = 999999999;
while (cnt > 0) do
begin
--cnt = cnt-100;
end
end";
		IAsyncResult ar = cmd.BeginExecuteNonQuery(null, null);
		Thread.Sleep(4000);
		Console.WriteLine("Canceling");
		cmd.Cancel();
		try
		{
			object result = cmd.EndExecuteNonQuery(ar);
		}
		catch(FbException ex)
		{
			Console.WriteLine(ex.Message);
		}

and you’ll get the result (even though the loop in fact never ends):

Canceling
operation was cancelled

Of course, you can screw things up by i.e. starting command and then trying to do something else with connection/transaction/command (the ADO.NET providers are not thread safe by default).

Oh, BTW, if you wanna test it, download weekly build. ;)