make select @@IDENTITY; a long?

Question

I am grabbing the last rowid and i am doing this select @@IDENTITY

 pk = (long)cmd.ExecuteScalar();

I get an invalid typecast bc this is int instead of long. Why doesnt this return a long? can i make it return long?

Solution for now is to use

 pk = Convert.ToInt64(cmd.ExecuteScalar());

Answer 1

In your SQL query convert @@IDENTITY to bigint:

select CONVERT(bigint, @@IDENTITY) ...

Answer 2

SELECT CAST(@@IDENTITY AS BIGINT) maybe?

Answer 3

@@IDENTITY is an int because of its range considered sufficient like so.

Data type

Range - (Storage)

bigint

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) - (8 Bytes)

int

-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) (4 Bytes)

smallint

-2^15 (-32,768) to 2^15-1 (32,767) (2 Bytes)

tinyint

0 to 255 (1 Byte)

Answer 4

Use SCOPE_IDENTITY... which is correct and decimal(38,0) anyway...

SELECT CAST(SCOPE_IDENTITY() AS bigint)

However, you should note that @@IDENTITY is also decimal(38,0)

This is because it must encompass any datatype that can be autonumbered such as decimal and bigint

Edit:

Apparently it's caused by unboxing. YMMV.

Answer 5

This doesn't work because ExecuteScalar returns an object. That will be a boxed integer. It can only be unboxed to an integer, anything else will generate a type cast exception. The C# language specification doesn't waste a lot of words on it in chapter 4.3.2:

An unboxing operation to a non-nullable-value-type consists of first checking that the object instance is a boxed value of the given non-nullable-value-type, and then copying the value out of the instance.

Converting it to a long is not wise btw. There's nothing you could do with that long that wouldn't eventually produce some kind of SQL error. Like incrementing it yourself.