c# - 读取位时来自 SqlDataReader 的无效转换异常

Question

我有几个存储过程读取同一个表并以相同的布局返回数据。这些存储过程之一导致存储为位的字段出现无效强制转换异常。 SqlDataReader 认为它是一个 int32。

示例表

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

示例存储过程

create proc SampleProcedure
as
    select Id,
           SomeText,
           BoolValue
go

示例扩展类

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

示例存储库类

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

    }
}

这与我的设置类似。在我的代码中，我有一个扩展方法，可以将 SqlDataReader 转换为 SampleModel 的类型，以便在存储库类中的所有加载方法中重用该扩展方法。正是因为这种方法，我知道它可以与所有其他方法一起使用。

关于为什么它将列视为 int 而不是 bit 有什么想法吗？

实际存储过程

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

Active 列是引发错误的列。它被定义为 BIT 并作为第 14 列进行索引。

导致问题的实际存储过程

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

这是实际的扩展类(更改名称以保护无辜者)

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

这是我的存储库类(同样经过少量修改)

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

高度怀疑“SqlDataReader 认为它是一个int32”。 SqlDataReader 只知道每个字段是什么，因为 RDBMS 将结果集的模式与结果集一起发送。因此该列(或更准确地说:结果集中的那个字段)是一个 int。

在最初的最后 3 个代码示例(标记为“实际”)中，存储过程在名称或参数方面与存储库类调用的过程不匹配。

现在代码示例已经更新，真正的过程(如问题评论中所述)有一个 UNION，其中同一列有一个文字 1 被选中。文字 1 的默认类型是 INT，因此 BIT 字段被隐式转换为 INT< 是有意义的 因为 UNION。

如果您想实际查看此操作，只需尝试以下操作(通过 SQL Server 2012 中引入的 sys.dm_exec_describe_first_result_set 查看结果集的架构):

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

下次提示:

1. 在 SSMS 中运行过程并亲自查看每个字段返回的内容。
2. 仔细检查表的列定义。