33

I have been playing around with SQL and databases in C# via SqlCeConnection. I have been using ExecuteReader to read results and BigInt values for record IDs which are read into Longs.

Today I have been playing with SQL statements that use COUNT based statements ('SELECT COUNT(*) FROM X') and have been using ExecuteScalar to read these single valued results.

However, I ran into an issue. I can't seem to store the values into a Long data type, which I have been using up to now. I can store them into Int64's.

I have been using BigInt for record IDs to get the maximum potential number of records.

A BigInt 8 bytes therefore is an Int64. Isn't a Long equal to an Int64 as both are 64-bit signed integers?

Therefore, why can't I cast an Int64 into a Long?

long recordCount =0;

recordCount = (long)selectCommand.ExecuteScalar();

The error is:

Specified cast is not valid.

I can read a BigInt into a Long. It is not a problem. I can't read an SQL COUNT into a long.

COUNT returns an Int (Int32), so the problem is in fact casting an Int32 into a long.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
  • What is recordCount defined as? – Massif Mar 10 '11 at 13:42
  • 1
    @BoltClock: You *can* directly cast `int` to `long` (in fact you don't even need a cast because there's an implicit conversion available). The OP's problem is that you can't unbox a boxed `int` to a `long`. Boxed value-types (generally) need to be unboxed to exactly the same type. – LukeH Mar 10 '11 at 14:18

2 Answers2

36

long is Int64 in .NET; it is just an alias in C#. Your problem is casting the return value to long and unless we know the type coming back from your query for sure, we would not know why you get an error. SQL BigInt must be convertable to long.

If it is the COUNT(*) which is coming back, then it is Int32. You need to use the Convert class:

long l = Convert.ToInt64(selectCommand.ExecuteScalar());
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Aliostad
  • 80,612
  • 21
  • 160
  • 208
  • 3
    +1, You're right that `COUNT` returns a 32-bit integer, and I'm pretty sure that this is the source of the OP's problem when they try to unbox that `int` to a `long`. There's no need for the explicit `Convert.ToInt64` though: `long l = query.ExecuteScalar();` will work just fine since an `int` is implicitly assignable to a `long`. – LukeH Mar 10 '11 at 13:45
  • Yes, that will make sense, I will try finding a similar question that Jon Skeet had replied a few days ago. – Aliostad Mar 10 '11 at 13:49
  • 3
    `long l = query.ExecuteScalar()` will fail at compile time as you cannot convert implicitly an object to a long. Similarly, `long l = (long)query.ExecuteScalar()` fails at runtime as this cast is not valid. Using `Convert.ToInt64` converts the object to Int64, or long. – anothershrubery Mar 10 '11 at 13:53
  • @anothershrubery: Oops, you're right: `long l = (int)query.ExecuteScalar();` will do the trick though (although I'm not sure whether it's more readable than using `Convert.ToInt64` or not). – LukeH Mar 10 '11 at 13:55
  • @Aliostad I can store a BigInt into a long no problem I cant store the COUNT result as a long I get {"Specified cast is not valid."}, however, I can cast into a int64 which should be the same as a long? –  Mar 10 '11 at 14:05
  • @andicrook: The result of `COUNT` is an `INT`, not a `BIGINT`, and I bet that you can't unbox it to an `Int64` or a `long` (since they're *exactly* the same type). If you're convinced that you can then please show us *(1)* the exact working code, and *(2)* the exact broken code. – LukeH Mar 10 '11 at 14:11
  • @andicrook: To store it in a `long`/`Int64` variable then you need to either do `long l = Convert.ToInt64(selectCommand.ExecuteScalar());` as in Aliostad's answer above, or `long l = (int)selectCommand.ExecuteScalar();`. – LukeH Mar 10 '11 at 14:15
  • @LukeH your right COUNT returns a Int (Int32) so the problem is in fact casting a Int32 into a long. –  Mar 10 '11 at 14:46
6

If you're thinking that your counts are going to overflow an int/Int32, you ought to use COUNT_BIG() in your SQL instead - it has the correct return type.

As to why the casts aren't working, I'm not sure. The following C#:

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
long lCount = (long)cmd.ExecuteScalar();
Int64 iCount = (Int64)cmd.ExecuteScalar();

Compiles to this IL:

L_0000: nop 
L_0001: newobj instance void [System.Data]System.Data.SqlClient.SqlCommand::.ctor()
L_0006: stloc.0 
L_0007: ldloc.0 
L_0008: callvirt instance object [System.Data]System.Data.Common.DbCommand::ExecuteScalar()
L_000d: unbox.any int64
L_0012: stloc.1 
L_0013: ldloc.0 
L_0014: callvirt instance object [System.Data]System.Data.Common.DbCommand::ExecuteScalar()
L_0019: unbox.any int64
L_001e: stloc.2 
L_001f: ret

That is, they appear to compile to identical code.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • COUNT_BIG() doesnt cast into a long either. COUNT and COUNT_BIG will cast into a int64 but not into a long when a long is a int64 why? –  Mar 10 '11 at 14:14
  • @andicrook - could you decompile your code to IL for the long and Int64 versions, as I've done above, and add your results to the question? – Damien_The_Unbeliever Mar 10 '11 at 14:16
  • My fault i didn't recompile still didn't work i dont think COUNT_BIG is supported by SQL Compact Edition {"The function is not recognized by SQL Server Compact Edition. [ Name of function = COUNT_BIG,Data type (if known) = ]"} –  Mar 10 '11 at 14:21
  • 1
    @andicrook - the return type of `COUNT()` is int/Int32, so storing it in an int/Int64 isn't gaining you anything. You're right that compact doesn't support `COUNT_BIG()`, but I think this is the first time you've mentioned that that's your target SQL Server. – Damien_The_Unbeliever Mar 10 '11 at 14:27
  • @ Damien_The_Unbeliever I did specify i was using compact edition I said via SqlCeConnection (Ce = compact edition) not SqlConnection. You right i didnt realise the limitation of COUNT therefore I cant get the benefits from a int64 or long anyway. Need to look up the maximum records per table for this edition as well and possibly drop everything to int32. However, i will be adding support for full SQL server later so its good to know about COUNT_BIG. Thanks –  Mar 10 '11 at 14:35