C# SqlDataReader.GetInt64 System.InvalidCastException: Specified cast is not valid

Question

I know the usual answers for this and they don't seem to apply.

We have a process that goes through data from numerous vendors and gets it into one consistent stream. We added a vendor recently that required a change to our primary key to a bigint.

Some of the string massaging was just easier to do in C#, so I changed the C# code from

primaryKey = reader.GetInt32(0);

to

primaryKey = reader.GetInt64(0);

figuring that the older vendors in the system would just cast cleanly up to Int64.

I got everybody up and running and I noticed that some but not all of one of the older vendor's records (the int32 primary key) were getting processed. Looking in the log files, I was seeing InvalidCastException on the reader.GetInt64() call processing some batches.

I know the usual response is "Oh, you're probably getting nulls", but

it is a primary key. It can't be null
The code's been running with the reader.GetInt32(0) call for over a decade
all the numbers in all the batches (successful and unsuccessful) are in the 181 million range

I changed the code to throw an ArgumentNullException if IsDBNull() came back true, but that never tripped.

The stack trace has

at System.Data.SqlClient.SqlBuffer.get_Int64()

but without source or line number it's hard to get farther.

I put

CAST(keyID as bigint) keyID

in the stored procedure returning the batches, and the issue has gone away.

Still curious though. Why would reader.GetInt64() throw an InvalidCastException sporadically when given an Int32 in the query result?

I'm not going to speculate on reasons, but yes: database types can do weird things (source: I wrote Dapper, I've dealt with a lot of data reader code). Tools like Dapper will do the right thing for you, checking the types in advance. Maybe just use one of them? — Marc Gravell
– Marc Gravell, Commented Nov 21, 2023 at 22:05
The obvious thing to do would be to check the column type reader.GetFieldType — Charlieface
– Charlieface, Commented Nov 22, 2023 at 2:57

Michael Liu · Accepted Answer · 2023-11-21 22:40:59Z

0

Why would reader.GetInt64() throw an InvalidCastException sporadically when given an Int32 in the query result?

reader.GetInt64() should throw an InvalidCastException not just "sporadically" but every single time. According to the documentation:

No conversions are performed; therefore, the data retrieved must already be a 64-bit signed integer.

In other words, if the column value is an Int32, then GetInt64 will throw InvalidCastException. It won't convert the Int32 to an Int64 for you. The same goes for all the other SqlDataReader.Get... methods.

Probably the best fix is to adjust the SQL query to always return Int64 values, as you've done. Alternatively, to handle both Int32 and Int64 values, you could read the column value as an object and then perform a conversion using Convert.ToInt64(reader[0]).

answered Nov 21, 2023 at 22:40

Michael Liu

55.8k14 gold badges126 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# SqlDataReader.GetInt64 System.InvalidCastException: Specified cast is not valid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related