Invalid cast exception from SqlDataReader when reading bit

Question

I have several stored procedures that read the same table and return the data in the same layout. One of these stored procedures is causing an invalid cast exception on field that is stored as a bit. The SqlDataReader thinks that it is an int32.

Sample table

create table dbo.SampleTable
(
    Id        INT NOT NULL IDENTITY(1,1),
    SomeText  VARCHAR(40),
    BoolValue BIT
)

Sample stored procedure

create proc SampleProcedure
as
    select Id,
           SomeText,
           BoolValue
go

Sample extension class

using System;
using System.Data.SqlClient;

namespace SampleApp.Extensions{
    public static class SampleModelExtension{
        private const int INDEX_ID = 0;
        private const int INDEX_SOMETEXT = 1;
        private const int INDEX_BOOLVALUE = 2;

        public static SampleModel ToSampleModel(this SqlDataReader rdr){
            SampleModel myModel = new SampleModel();

            myModel.Id = !rdr.IsDbNull(INDEX_ID) ? rdr.GetInt32(INDEX_ID) : 0;
            myModel.SomeText = !rdr.IsDbNull(INDEX_SOMETEXT) ? rdr.GetString(INDEX_SOMETEXT) : String.Empty;
            myModel.Boolvalue = !rdr.IsDbNull(INDEX_BOOLVALUE) ? rdr.GetBool(INDEX_BOOLVALUE) : false;

            return myModel;
        }
    }
}

Sample repository class

using SampleApp.Extensions;
using System;
using System.Collections.Generic;
using System.Data.SqlCient;

namespace SampleApp {
    public SampleRepository : BaseDataConnection {
        public List<SampleModel> GetSampleData(){
            SqlCommand cmd = new SqlCommand("SampleProcedure", base.Connection);

            List<SampleModel> retVal = new List<SampleModel>();

            using(SqlDataReader rdr = base.GetDataReader(cmd)){
                while(rdr.Read()){
                    retVal.Add(rdr.ToSampleModel());
                }
            }

            return retVal;
        }

        public List<SampleModel> GetMoreSampleData(){
            SqlCommand cmd = new SqlCommand("AnotherSampleProcedure", base.Connection);

            List<SampleModel> retVal = new List<SampleModel>();

            using(SqlDataReader rdr = base.GetDataReader(cmd)){
                while(rdr.Read()){
                    retVal.Add(rdr.ToSampleModel());
                }
            }

            return retVal;
        }

    }
}

This is a similar setup to what I have. In my code, I have an extension method that will convert the SqlDataReader to the type of SampleModel, so that extension method is reused in all loading methods in the repository class. It is because of this approach that I know it is working with all of the other methods.

Any ideas on why it would seeing the column as an int instead of a bit?

Actual Stored Procedure

ALTER PROCEDURE [dbo].[GetAllActiveScheduledEventsByDateRange]
    @SiteGuid       VARCHAR(38),
    @StartDate      DATE,
    @EndDate        DATE
AS
    SELECT  se.EventId,
            se.AvailableDate,
            se.StartTime,
            se.NumberOfPatrons,
            se.AgeOfPatrons,
            se.ContactEmailAddress,
            se.ContactPhone,
            se.ContactName,
            se.EventTypeId,
            se.PartyName,
            se.ConfirmationDateTime,
            se.ReminderDateTime,
            se.UserComments,
            se.AdminComments,
            se.Active,
            se.CheckInTime,
            se.CheckOutTime,
            se.GunSize,
            (
                Select Count(p.playerid) from 
                    (select * from waiver2 where waiverid in (
                        (Select WaiverId 
                        from Waiver2
                        inner join 
                        (
                            Select max(CreateDateTime) as LatestDate, PlayerId
                            from Waiver2
                            WHERE siteguid = @SiteGuid
                            Group by PlayerId
                        ) SubMax 
                        on Waiver2.CreateDateTime = SubMax.LatestDate
                        and Waiver2.PlayerId = SubMax.PlayerId))) w,
                    player p, PlayDateTime updt
                where p.playerid = w.playerid 
                and p.playerid = updt.PlayerId
                and updt.EventId = se.EventId) AS WaiverCount,
            se.DepositAmount,
            se.CreateDateTime,
            se.PaymentReminderDateTime,
            se.PaymentStatusId,
            se.PackageId
    FROM    ScheduledEvent se
    WHERE   se.SiteGuid = @SiteGuid
    AND     se.AvailableDate BETWEEN @StartDate AND @EndDate
    AND     se.PaymentStatusId < '99'
    AND     se.Active = 1
    ORDER BY se.StartTime, se.ContactName

The Active column is the one that is throwing the error. It is defined as a BIT and indexed as column 14.

The actual stored procedure causing the problem

ALTER proc [dbo].[W2_GetAllActiveScheduledEventsByDateWithWaivers]
  @SiteGuid     VARCHAR(38),
  @AvailableDate    DATE
AS
  SELECT    se.EventId,
        se.AvailableDate,
        se.StartTime,
        se.NumberOfPatrons,
        se.AgeOfPatrons,
        se.ContactEmailAddress,
        se.ContactPhone,
        se.ContactName,
        se.EventTypeId,
        se.PartyName,
        se.ConfirmationDateTime,
        se.ReminderDateTime,
        se.UserComments,
        se.AdminComments,
        se.Active,
        se.CheckInTime,
        se.CheckOutTime,
        se.GunSize,
        (
            Select Count(p.playerid) from 
                (
                select * from waiver2 where waiverid in 
                    (
                        (
                            Select WaiverId 
                            from Waiver2
                            inner join 
                                (
                                    Select max(CreateDateTime) as LatestDate, PlayerId
                                    from Waiver2
                                    WHERE siteguid = @SiteGuid
                                    Group by PlayerId
                                ) SubMax 
                            on Waiver2.CreateDateTime = SubMax.LatestDate
                            and Waiver2.PlayerId = SubMax.PlayerId
                            and DateDiff(year,Waiver2.CreateDateTime,GETDATE()) = 0
                        )   
                    )   
                ) w,
                player p, PlayDateTime updt
            where p.playerid = w.playerid 
            and p.playerid = updt.PlayerId
            and updt.EventId = se.EventId
            and ((
                    FLOOR(DATEDIFF(day,p.DateOfBirth,GETDATE())/365.242199) >= 18
                    and 
                    w.ParentId is null
                )
                or
                (
                    FLOOR(DATEDIFF(day,p.DateOfBirth,GETDATE())/365.242199) < 18
                    and 
                    w.ParentId is not null
                ))
        ) AS WaiverCount,
        se.DepositAmount,
        se.CreateDateTime,
        se.PaymentReminderDateTime,
        se.PaymentStatusId,
        se.PackageId
FROM    ScheduledEvent se
WHERE   se.SiteGuid = @SiteGuid
AND     se.AvailableDate = @AvailableDate
AND     se.PaymentStatusId <= '90'
AND     se.Active = 1
--ORDER BY se.StartTime, se.ContactName

union   select  null,
        pdt.PlayDate,
        pdt.PlayTime,
        null,
        null,
        null,
        null,
        null, 
        null,
        'Walk-up Players',
        null,
        null,
        null,
        null,
        1,
        null,
        null,
        null,
        COUNT('x') AS WaiverCount,
        0,
        null,
        null,
        null,
        null
from PlayDateTime pdt
where pdt.PlayDate = @AvailableDate
and pdt.EventId is null
and pdt.PlayerId in (
    Select p.playerid from 
        (select * from waiver2 where waiverid in (
            (Select WaiverId 
             from Waiver2
             inner join 
             (
                Select max(CreateDateTime) as LatestDate, PlayerId
                from Waiver2
                WHERE siteguid = @SiteGuid
                Group by PlayerId
              ) SubMax 
              on Waiver2.CreateDateTime = SubMax.LatestDate
              and Waiver2.PlayerId = SubMax.PlayerId
              and DateDiff(year,Waiver2.CreateDateTime,GETDATE()) = 0))) w,
        player p
    where p.playerid = w.playerid 
    and ((
            FLOOR(DATEDIFF(day,p.DateOfBirth,GETDATE())/365.242199) >= 18
            and 
            w.ParentId is null
        )
        or
        (
            FLOOR(DATEDIFF(day,p.DateOfBirth,GETDATE())/365.242199) < 18
            and 
            w.ParentId is not null
        ))
)   
group by pdt.PlayDate, pdt.PlayTime
order by 2, 3, 10

This is the actual extension class (with the names changed to protect the innocent)

namespace MyNameSpace.Svc.Core.Extensions.Registration {
  public static class ScheduledEventExtension {
    #region attributes
    private const int INDEX_ID = 0;
    private const int INDEX_DATE = 1;
    private const int INDEX_STARTTIME = 2;
    private const int INDEX_NUMBEROFPATRONS = 3;
    private const int INDEX_AGEOFPATRONS = 4;
    private const int INDEX_CONTACTEMAIL = 5;
    private const int INDEX_CONTACTPHONE = 6;
    private const int INDEX_CONTACTNAME = 7;
    private const int INDEX_EVENTTYPE = 8;
    private const int INDEX_PARTYNAME = 9;
    private const int INDEX_CONFIRMDATE = 10;
    private const int INDEX_REMINDDATE = 11;
    private const int INDEX_USERCOMMENTS = 12;
    private const int INDEX_ADMINCOMMENTS = 13;
    private const int INDEX_ACTIVE = 14;
    private const int INDEX_CHECKINTIME = 15;
    private const int INDEX_CHECKOUTTIME = 16;
    private const int INDEX_GUNSIZE = 17;
    private const int INDEX_WAIVERCOUNT = 18;
    private const int INDEX_DEPOSITAMOUNT = 19;
    private const int INDEX_CREATEDATETIME = 20;
    private const int INDEX_PAYMENTREMINDERDATETIME = 21;
    private const int INDEX_PAYMENTSTATUS = 22;
    private const int INDEX_PACKAGEID = 23;
    #endregion

    #region methods
    public static ScheduledEvent ToScheduledEvent(this SqlDataReader rdr) {
        ScheduledEvent retVal = new ScheduledEvent();

        retVal.Id = !rdr.IsDBNull(INDEX_ID) ? rdr.GetInt32(INDEX_ID) : 0;
        retVal.SelectedDate.SelectedDate = !rdr.IsDBNull(INDEX_DATE) ? rdr.GetDateTime(INDEX_DATE) : DateTime.MinValue;
        retVal.SelectedDate.StartTime = !rdr.IsDBNull(INDEX_STARTTIME) ? rdr.GetTimeSpan(INDEX_STARTTIME) : TimeSpan.MinValue;

        int numOfPatrons = 0;
        int.TryParse(rdr.GetString(INDEX_NUMBEROFPATRONS), out numOfPatrons);
        retVal.NumberOfPatrons = numOfPatrons;

        retVal.AgeOfPatrons = !rdr.IsDBNull(INDEX_AGEOFPATRONS) ? rdr.GetString(INDEX_AGEOFPATRONS) : string.Empty;
        retVal.ContactEmailAddress = !rdr.IsDBNull(INDEX_CONTACTEMAIL) ? rdr.GetString(INDEX_CONTACTEMAIL) : string.Empty;
        retVal.ContactPhone = !rdr.IsDBNull(INDEX_CONTACTPHONE) ? rdr.GetString(INDEX_CONTACTPHONE) : string.Empty;
        retVal.ContactName = !rdr.IsDBNull(INDEX_CONTACTNAME) ? rdr.GetString(INDEX_CONTACTNAME) : string.Empty;
        // event type is obsolete
        retVal.PartyName = !rdr.IsDBNull(INDEX_PARTYNAME) ? rdr.GetString(INDEX_PARTYNAME) : string.Empty;
        retVal.ConfirmationDateTime = !rdr.IsDBNull(INDEX_CONFIRMDATE) ? rdr.GetDateTime(INDEX_CONFIRMDATE) : DateTime.MinValue;
        retVal.ReminderDateTime = !rdr.IsDBNull(INDEX_REMINDDATE) ? rdr.GetDateTime(INDEX_REMINDDATE) : DateTime.MinValue;
        retVal.Comments = !rdr.IsDBNull(INDEX_USERCOMMENTS) ? rdr.GetString(INDEX_USERCOMMENTS) : string.Empty;
        retVal.AdminComments = !rdr.IsDBNull(INDEX_ADMINCOMMENTS) ? rdr.GetString(INDEX_ADMINCOMMENTS) : string.Empty;
        retVal.Active = !rdr.IsDBNull(INDEX_ACTIVE) ? rdr.GetBoolean(INDEX_ACTIVE) : false;
        retVal.CheckInDateTime = !rdr.IsDBNull(INDEX_CHECKINTIME) ? rdr.GetDateTime(INDEX_CHECKINTIME) : DateTime.MinValue;
        retVal.CheckOoutDateTime = !rdr.IsDBNull(INDEX_CHECKOUTTIME) ? rdr.GetDateTime(INDEX_CHECKOUTTIME) : DateTime.MinValue;
        // gun size is obsolete
        retVal.WaiverCount = !rdr.IsDBNull(INDEX_WAIVERCOUNT) ? rdr.GetInt32(INDEX_WAIVERCOUNT) : 0;
        retVal.DepositAmount = !rdr.IsDBNull(INDEX_DEPOSITAMOUNT) ? rdr.GetDecimal(INDEX_DEPOSITAMOUNT) : 0;
        retVal.CreateDateTime = !rdr.IsDBNull(INDEX_CREATEDATETIME) ? rdr.GetDateTime(INDEX_CREATEDATETIME) : DateTime.MinValue;
        retVal.PaymentReminderDateTime = !rdr.IsDBNull(INDEX_PAYMENTREMINDERDATETIME) ? rdr.GetDateTime(INDEX_PAYMENTREMINDERDATETIME) : DateTime.MinValue;
        retVal.PaymentStatus = !rdr.IsDBNull(INDEX_PAYMENTSTATUS) ? PaymentStatusExtension.ToPaymentStatusEnum(rdr.GetString(INDEX_PAYMENTSTATUS)) : PaymentStatusEnum.Unpaid;
        retVal.SelectedPackage.Id = !rdr.IsDBNull(INDEX_PACKAGEID) ? rdr.GetInt32(INDEX_PACKAGEID) : 0;

        return retVal;
    }
    #endregion
  }
}

This is my repository class (again with minor modifications)

using MyNameSpace.Svc.Core.Extensions;
using MyNameSpace.Svc.Core.Extensions.Registration;
using MyNameSpace.Svc.Core.Interfaces.Registration;
using MyNameSpace.Svc.Core.Models.Registration;

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace MyNameSpace.Svc.Impl.Repositories.Registration {
  public class ScheduledEventRepositoryImpl : DatabaseConnection, IScheduledEventRepository {
    #region attributes
    private const string PARMNAME_RETURN = "retval";
    private const string PARMNAME_ID = "EventId";
    private const string PARMNAME_GUID = "SiteGuid";
    private const string PARMNAME_AVAILABLEDATE = "AvailableDate";
    private const string PARMNAME_STARTTIME = "StartTime";
    private const string PARMNAME_NUMPATRONS = "NumberOfPatrons";
    private const string PARMNAME_AGEPATRONS = "AgeOfPatrons";
    private const string PARMNAME_CONTACTEMAIL = "ContactEmailAddress";
    private const string PARMNAME_CONTACTPHONE = "ContactPhone";
    private const string PARMNAME_CONTACTNAME = "ContactName";
    private const string PARMNAME_PARTYNAME = "PartyName";
    private const string PARMNAME_CONFDATE = "ConfirmationDateTime";
    private const string PARMNAME_REMINDDATE = "ReminderDateTime";
    private const string PARMNAME_USERCOMMENTS = "UserComments";
    private const string PARMNAME_ADMINCOMMENTS = "AdminComments";
    private const string PARMNAME_CHECKINTIME = "CheckInTime";
    private const string PARMNAME_CHECKOUTTIME = "CheckOutTime";
    private const string PARMNAME_DEPOSITAMT = "DepositAmount";
    private const string PARMNAME_CREATEDATE = "CreateDateTime";
    private const string PARMNAME_PAYMENTREMINDDATE = "PaymentReminderDateTime";
    private const string PARMNAME_PAYMENTSTATUS = "PaymentStatusId";
    private const string PARMNAME_PKGID = "PackageId";
    private const string PARMNAME_EMAIL = "EmailAddress";
    private const string PARMNAME_DAYSOUT = "DaysOut";
    private const string PARMNAME_EVENTTYPE = "EventTypeId";
    private const string PARMNAME_STARTDATE = "StartDate";
    private const string PARMNAME_ENDDATE = "EndDate";

    private const string SPNAME_GETALLACTIVEBYDATERANGE = "GetAllActiveScheduledEventsByDateRange";
    private const string SPNAME_GETALLACTIVEBYDATEWITHWAIVERS = "W2_GetAllActiveScheduledEventsByDateWithWaivers";
    #endregion

    #region methods
    public List<ScheduledEvent> GetAllActiveScheduledEventsByDateRange(Guid siteGuid, DateTime startDate, DateTime endDate) {
        List<ScheduledEvent> retVal = new List<ScheduledEvent>();

        SqlCommand cmd = new SqlCommand(SPNAME_GETALLACTIVEBYDATERANGE, base.Connection);

        cmd.Parameters.AddWithValue(PARMNAME_GUID, siteGuid.ToFormattedString());
        cmd.Parameters.AddWithValue(PARMNAME_STARTDATE, startDate);
        cmd.Parameters.AddWithValue(PARMNAME_ENDDATE, endDate);

        using(SqlDataReader rdr = base.GetDataReader(cmd)) {
            while(rdr.Read()) {
                retVal.Add(rdr.ToScheduledEvent());
            }
        }

        return retVal;
    }

    public List<ScheduledEvent> GetAllActiveScheduledEventsByDateWithWaivers(Guid siteGuid, DateTime availableDate) {
        List<ScheduledEvent> retVal = new List<ScheduledEvent>();

        using(SqlDataReader rdr = base.GetDataReader(SPNAME_GETALLACTIVEBYDATEWITHWAIVERS, PARMNAME_AVAILABLEDATE, availableDate, siteGuid)) {
            while(rdr.Read()) {
                retVal.Add(rdr.ToScheduledEvent());
            }
        }

        return retVal;
    }
    #endregion
  }
}

Answer 1

It is highly doubtful that the "SqlDataReader thinks that it is an int32". The SqlDataReader only knows what each field is because the RDBMS sends the schema of the result set along with the result set. So that column (or more correctly: that field in the result set) is an int.

In the original final 3 code samples (labeled as "actual"), the stored proc did not match the proc being called by the repository class in either name or params.

Now that the code samples have been updated, the real proc (as noted in a comment on the question) has a UNION in which that same column has a literal 1 being selected. The default type of a literal 1 is INT, so it makes sense that the BIT field is being implicitly cast into an INT because of the UNION.

And if you want to see this in action, just try the following (which peeks at the schema of the result set via sys.dm_exec_describe_first_result_set which was introduced in SQL Server 2012):

SELECT [system_type_name]
FROM sys.dm_exec_describe_first_result_set('SELECT CONVERT(BIT, 1) AS [BITorINT?]',
                                           NULL, NULL);
-- bit

SELECT [system_type_name]
FROM sys.dm_exec_describe_first_result_set('SELECT 1 AS [BITorINT?]', NULL, NULL);
-- int

SELECT [system_type_name]
FROM sys.dm_exec_describe_first_result_set('SELECT CONVERT(BIT, 1) AS [BITorINT?]
                                            UNION ALL
                                            SELECT 1', NULL, NULL);
-- int

Tips for next time:

1. Run the proc in SSMS and see for yourself what is returned for each field.
2. Double-check the column definition of the table.