Error casting tiny int to int

问题:

This error looks like it was caused by installing framework 4.5 on the server even though the project is still targeted to 4.0.

4.5 replaces the CLR and it looks like it has changes in unboxing an object of type tinyint to a int. This was working in 4.0 but not after installing 4.5.

============================================

Please read the whole question before answering, most current answers do not answer the question I am asking.

We today got an error in a cast from a tinyint in sql to an int using

Daterow datarow = GetOneDatarow(connection,
                         "SELECT tinyintcolumn FROM table1 WHERE id = 4");
int i = (int)datarow["tinyintcolumn"];

This is old code that has been in the product for several years without any changes and it has been working up until yesterday. (And it is not the exact code, just enough to show the context)

=== UPDATE

The exact error message was: "The specified cast is not valid!" and the last line

int i = (int)datarow["tinyintcolumn"];

is the exact row from our code casting the error with only variable names and column name changed.

And the database column was tinyint with default value 0, no indexes or other constraints.

=== End update

=== UPDATE 2

Henk Holterman in his response informed me that FW 4.5 replaces the CLR of 4.0 even for projects compiled specifically for 4.0 and this could remotely change existing 4.0 behaviour just like this.

I will keep this open a while more but his answer is the most promising so far :D === End

We changed from framework 3.5 to 4.0 a few weeks ago but it was only yesterday afternoon after a recompile that this happened, yesterday morning the same code (even after recompile) worked like clockwork.

Do anyone have any idea on why this was working before and is not working now?

Has Microsoft made any under the hood changes that removed an implicit conversion or has it worked by pure magic before?

We solved it by changing the database column to int, but I am still curious on what might have caused it to fail right now.

=== UPDATE 3

Just to complete this.

I found the change between frameworks. In an update Microsoft changed how boxing and unboxing is done. This caused the implicit cast from byte to int that older FW did to fail when the byte was boxed, as it is in a datatable.

An unboxed byte will will in 4.5 be implicitly cast to an int, but a boxed byte is an generic object which cannot be implicitly cast.

Not, this was changed in 3.5 SP1 so our FW 4.0 should also have failed, unless the update to SP1 was not in the 4.0 update. This has yet to be answered :)

Here is the ticket from MS on this ;) https://connect.microsoft.com/VisualStudio/feedback/details/766887/casting-tinyint-from-sql-datarow-to-int-no-longer-possible

回答1:

It should never have worked. Which makes it likely that something was fixed in the framework.

The problem in C#:

byte b = 3;       // TinyInt is a Byte
object o = b;     // DataRow[i] is an object
int i = (int)o;   // invalid cast

The fix:

int i = (byte)datarow["tinyintcolumn"];

---

And from the comment trail below:

We have installed it, but this project is not compiled towards 4.5, only to 4.0, ... could that be the case?

Yes, framework 4.5 replaces parts of 4.0.

回答2:

a TinyInt type will by default return a byte type which itself is castable to an int, but the db's TinyInt type is not, so try this:

(int)(byte)datarow["tinyintcolumn"];

回答3:

Because tinyint is a Byte type. Here's a list: LIST

You need to convert byte array to int. Solution here: SOLUTION

回答4:

To make the answer usable with other connection libraries I share this. I'm using MariaDb and MySql connector/Net so selected answer did not work for me directly. So first you have to find out the C# datatype of returned sql tinyint field.

Here is example:

I'm using MySqlHelper.ExecuteDataset() and executing query to fetch tinyint(4) column:

SELECT tinyintcolumn FROM datatable WHERE ...

I was getting the "The specified cast is not valid!" exception even if i used cast function in SQL query:

SELECT CAST(tinyintcolumn AS int) ...

I was first trying all suggested here and elsewhere, but finally what worked is finding out what is the type of field in c#:

DataTable datatable MySqlHelper.ExecuteDataset(connString, sql).Tables[0];
DataRow datarow = datatable .Rows[0];
Type datatype = datarow.ItemArray[0].GetType();

Result: System.SByte! So for me it's fine to use one of this:

SByte sbTinyint = datarow.Field<SByte>(0);
SByte sbTinyint2 = (SByte)datarow.ItemArray[0];
int iTinyint = (int)datarow.Field<SByte>(0);
int iTinyint2 = (int)(SByte)datarow.ItemArray[0];

回答5:

I believe the size of SQL's tinyint is 1 byte (8 bits) so try casting it to a 'byte' instead of casting it to an 'int' (which is 32 bits). But I have not tried this since I don't use tinyint in my database.

byte i = (byte)datarow["tinyintcolumn"];

Also, if your column in the database tinyintcolumn is nullable, you also need to take that into account when you cast the value.