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
  }
}

Perhaps the stored procedure that you are calling is reading it as an integer rather than a bit. — dub stylee
– dub stylee, Commented May 27, 2015 at 23:57
what line gives you an exception? Do you have sample stack trace? — vmg
– vmg, Commented May 28, 2015 at 0:20
Can we see the actual SP? Or at least a better representation of it? — Mark
– Mark, Commented May 28, 2015 at 2:34
The issue maybe in the actual SP. Make sure you return bit value instead of int for the BoolValue. Maybe try explicitly type convert: convert(bit,BoolValue) as BoolValue — Tran Nguyen
– Tran Nguyen, Commented May 28, 2015 at 2:49
I have updated my original post to include the actual stored procedure being executed. — fizch
– fizch, Commented May 28, 2015 at 15:56

Solomon Rutzky · Accepted Answer · 2015-06-02 02:43:10Z

12

+50

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:

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

edited Jun 2, 2015 at 2:43

answered Jun 2, 2015 at 2:37

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Giorgi Nakeuri Over a year ago

I think this is the answer. Quite easy when you see actual code rather then some masked, shorten one

fizch Over a year ago

Yeah, once I saw the union that is what i figured. I appreciate the lengths that you went through to prove it.

fizch Over a year ago

@GiorgiNakeuri yeah, i had been staring at the wrong code for a while apparently. I was trying to simplify everything by "masking" it.

Solomon Rutzky Over a year ago

@fizch Not a problem :-). I didn't like the sound of my "it makes sense that it implicitly converted". A lot of incorrect things still make sense, so I feel somewhat obligated to provide some proof, just to make sure that what I am saying doesn't just sound plausible ;-). And regarding the "masked" code: sometimes you are so focused on one part and have been working on, and looking at, 20 or even 50 parts that it is easy to get lost in it and it helps to have another pair of eyes.

Juan · Accepted Answer · 2015-06-02 02:55:02Z

0

Internally sql server converts the bit values to tiny int so with the union will try to evaluate and say ok this two values together are this data type now.

the 1 and the null will result in an int data type most likely

answered Jun 2, 2015 at 2:55

Juan

1,3821 gold badge15 silver badges20 bronze badges

Collectives™ on Stack Overflow

Invalid cast exception from SqlDataReader when reading bit

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related