Error converting from smallint database to C # nullable int

Question

I have a security_role_cd column in a smallint datatype type smallint . I select this column using the following code in a nullable int variable.

I get the following error:

Error 3 The type of conditional expression cannot be determined because there is no implicit conversion between 'null' and 'short'

What is the correct code to resolve this error?

 SELECT R.security_role_cd FROM Security_Role R WHERE security_role_name = 'Admin' 

FROM#

  int? roleID = null; string commandText = "SELECT R.security_role_cd FROM Security_Role R WHERE security_role_name = @roleName"; SqlCommand command = new SqlCommand(commandText, connection); command.CommandType = System.Data.CommandType.Text; command.Parameters.AddWithValue("@roleName",roleName); SqlDataReader readerRole = command.ExecuteReader(); if (readerRole.HasRows) { while (readerRole.Read()) { roleID = readerRole.GetInt16(0) == 0 ? null : readerRole.GetInt16(0) ; } } readerRole.Close(); 

Answer 1

You just need to know how to enter null :

 roleID = readerRole.GetInt16(0) == 0 ? (int?)null : (int)readerRole.GetInt16(0); 

Personally, I would cache the value, though:

 int tmp = readerRole.GetInt16(0); // implicit widening to int here roleID = tmp == 0 ? (int?)null : tmp; 

Although I also questioned the wisdom of turning 0 into null - it's better to use IsDBNull - something like:

 if(reader.IsDBNull(0)) { roleID = null; } else { roleID = (int)readerRole.GetInt16(0); } 

Answer 2

try it

 roleID = readerRole.GetInt16(0) == 0 ? (int?) null : readerRole.GetInt16(0) ; 

According to the documentation of the ternary operator, the data type on both sides of the colon (:) must be the same. Since you had this without casting, the null type could not be determined (i.e., if nullable int or empty string or null object)

Update

 roleID = readerRole.GetInt16(0) == 0 ? (int?) null : readerRole.GetInt32(0) ;