Schlagwort-Archive: database

Slippery When Wet #2: SCOPE_IDENTITY and SqlDataReader

Schreibe eine Antwort

I proudly present to you the second in a infinite number of posts of “Slippery When Wet.” In these posts I show you a little bastard I stubled on.

Imagine you have a database table with a cloumn id of the data type integer that has set the IDENTITY. You use a stored procedure to insert a new entry into this table. Inside this stored procedure you use the SCOPE_IDENTITY() function to get the identifier created for this row. With RETURN SCOPE_IDENTITY() you give the identity to the caller.

You call this stored procedure from a SqlComand with ExecuteReader() that returns you an SqlDataReader object.

From this SqlDataReader you read now the identifier with GetInt32().

Wrong !

This will give you an InvalidCastException. SCOPE_IDENTITY()’s return type is numeric, although your identifier column is an integer. SqlDataReader’s GetXY functions do not convert the data and throw the exception when the data is not already of the right type.

The first solution

You can read the value with GetDecimal() and cast the value to an int:

int identifier = (int)reader.GetDecimal(0);

The second solution

You cast the identifier inside the stored procedure und return it already as an integer:

SELECT CAST(SCOPE_IDENTITY() AS INT)

Whatever solution you choose, take care that you use always the same inside your application.