tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Gotcha with Firebird 3, COUNT and ExecuteScalar’s common casting pattern

2 Mar 2016 2 mins C#, Firebird

There’s a common pattern used in ADO.NET with ExecuteScalar method. And this one is going to be bit broken with changes introduced in Firebird 3 (currently RC2).

Very often you’re selecting number of values from some table (probably based on some condition). And the code looks like this.

using (var cmd = connection.CreateCommand())
{
	cmd.CommandText = "select count(*) from rdb$database";
	return (int)cmd.ExecuteScalar();
}

But the COUNT function in Firebird 3 now returns 64bit integer (described in release notes). So the above casting will fail. The ExecuteScalar is returning object so it can return whatever needed. In this case the long is wrapped into it. Casting it to int is obviously going to fail.

There’s a bunch of options. Sure you can do the casting in SQL directly and then it datatype will be fine. It’s just bit clunky. Maybe better option is to not cast it to some type directly, but convert it. .NET offers a handy Convert class where a ToInt32 method is. With that it’s enough that the value ExecuteScalar returned is a “number” and fit’s into int (which it should if it worked before).

using (var cmd = connection.CreateCommand())
{
	cmd.CommandText = "select count(*) from rdb$database";
	return Convert.ToInt32(cmd.ExecuteScalar());
}

And it’s OK again. I think I’ll not be alone changing a lot of code as customers will start using Firebird 3. 😃

Profile Picture Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at www.tabsoverspaces.com.