Getting a boolean from a SELECT in SQL Server into a bool in C#?

mm5n2pyu  于 2023-11-16  发布在  SQL Server
关注(0)|答案(4)|浏览(188)

I have this code in my SELECT:

SELECT  A.CompletedDate,
    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],

and in my ASP.NET DTO I have:

public partial class GetTestsDTO
{
    public bool? GradePass { get; set; }
    // the following line is what is giving the error. If I remove
    // the line it will not try to convert the data and there is no
    // error. Note that I want to put this into a bool as web.api
    // then passes it to the client as a bool
    public bool Current { get; set; } 
    public DateTime? CompletedDate { get; set; }
}

It's giving me an error and I would appreciate some help.

The error is:
message=The specified cast from a materialized 'System.Int32' type to the 'System.Boolean' type is not valid.

gxwragnw

gxwragnw1#

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                   ║
╚═════════════════════════════════╩═════════════════════╩═══════════════════════╝
ar5n3qh5

ar5n3qh52#

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 perform the conversion client-side:

using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        var dto = new GetTestsDTO();
        dto.Current = Convert.ToBoolean(reader.GetInt32(1));
    }
}
ewm0tg9j

ewm0tg9j3#

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.

9nvpjoqh

9nvpjoqh4#

below too works for me :-

I tried returning Boolean in like 'true' / 'false' and it too worked.
Environ: C# - Dapper
SQL :- Sql Server 2012

Example:- CASE WHEN (@AdminTestId IS NULL AND @UserTestId IS NULL) THEN 'false'
WHEN (@AdminTestId = temp.AdminTestId AND @UserTestId = A.UserTestId) THEN 'true'

Both worked 'true' or 'TRUE' OR 'false' or 'FALSE' -- both the cases.

So besides the accepted answer, which is a working answer, this also worked for me, yes, it may be attributed to Dapper as well, if, in case, its doesn't work in normal ADO.NET

相关问题