Getting a boolean from a SELECT in SQL Server into a bool in C#?
As noted in the comments you are returning an integer. You need to return a bit
, which ASP.NET will understand as a Boolean.
SELECT A.CompletedDate,
CASE
WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) THEN
CONVERT(bit, 0)
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) THEN
CONVERT(bit, 1)
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId IS NULL) THEN
CONVERT(bit, 1)
ELSE
CONVERT(bit, 0)
END AS [Current],
Or as pointed out Damien_The_Unbeliever in the comments below, you could wrap the whole CASE
statement in a CONVERT
.
SELECT A.CompletedDate,
CONVERT(bit,
CASE
WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) THEN 0
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) THEN 1
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId IS NULL) THEN 1
ELSE 0
END
) AS [Current],
You could also use CAST(1 AS bit)
and CAST(0 AS bit)
in replace of CONVERT(bit, 1)
and CONVERT(bit, 0)
respectively (see T-SQL Cast versus Convert for more information).
You could also the conversion client-side:
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
var dto = new GetTestsDTO();
dto.Current = Convert.ToBoolean(reader.GetInt32(1));
}
}
The easiest way (SQL Server side) is to convert values 0
and 1
to BIT
datatype:
SELECT A.CompletedDate,
CASE
WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL)
THEN CAST(0 AS BIT)
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId)
THEN CAST(1 AS BIT)
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId IS NULL)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS [Current],
or entire expression at once:
SELECT A.CompletedDate,
CAST((CASE
WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) THEN 0
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) THEN 1
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId IS NULL) THEN 1
ELSE 0
END)
AS BIT) AS [Current],
SQL Server Data Type Mappings:
╔═════════════════════════════════╦═════════════════════╦═══════════════════════╗
║ SQL Server Database Engine type ║ .NET Framework type ║ SqlDbType enumeration ║
╠═════════════════════════════════╬═════════════════════╬═══════════════════════╣
║ bit ║ Boolean ║ Bit ║
╚═════════════════════════════════╩═════════════════════╩═══════════════════════╝
As the previous comments suggest, SQL cannot return the "boolean" value, and you are not returning it either.
My suggestion/solution (that I use, and have seen used everywhere I worked) is to simply recover that Integer in your object, and then use a 'map' function that will transform that Integer to a Boolean.
The (simple) method will do something like this (attention; Java version ahead):
public static boolean integerToBoolean(Integer myInt){
return myInt == 1 ? true : false;
}
Best of luck.