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
}
}
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With