查询结果超过1000条记录时,Dapper抛出System.Data.SqlClient.SqlException

c# dapper dapper-extensions sql sql-server

下面的方法调用失败,并显示消息“将varchar数据类型转换为日期时间数据类型导致超出范围的值。”:

public IEnumerable<SomeResult> GetResults(SqlConnection connection, string attribute)
    {
        var sql = string.Format(@"
        SELECT TOP 2000
            r.Id
            ,r.LastName
            ,r.FirstName
            ,r.Ssn
            ,r.CurrentId
            ,BeginDate = case when isdate(rli.BeginDate) = 1 then convert(datetime, rli.BeginDate) else NULL end
            ,EndDate = case when isdate(rli.EndDate) = 1 then convert(datetime, rli.EndDate) else NULL end
            ,rli.LcknTyCd
            ,rli.ProvId
        FROM 
            [dbo].[Span] rli
            INNER JOIN [dbo].Recipient r
                ON rli.SysId = r.SysId
            INNER JOIN [dbo].ValidRecipient lc
                ON r.SysId = lc.SysId
        WHERE 
            BeginDate <= GETDATE()
            AND EndDate >= GETDATE()
            AND rli.LcknTyCd = @LcknTyCd);

        return connection.Query<SomeResult>(sql, new { LcknTyCd = attribute}).ToList();
    }

public struct SomeResult
{
    public string Id{ get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string Ssn { get; set; }
    public string CurrentId{ get; set; }
    public DateTime? BeginDate { get; set; }
    public DateTime? EndDate { get; set; }
    public string LcknTyCd{ get; set; }
    public string ProvId{ get; set; }
}

如果结果集包含1000(或更少)记录,则代码可以正常工作。当我在SQL Server Management Studio(2014版)中执行查询时,我也没有收到错误。即使我从选择中删除TOP并在SSMS中执行它,也不会发生错误(按预期返回12,000多条记录)。

我应该做什么而不是上面的实现来成功检索超过1000行的结果集?在这种情况下,存储过程是否更合适?

一般承认的答案

听起来您的日期字段存储在varchar列中。理想情况下,您应该将这些更改为datetime字段。如果这不是一个选项,请将WHERE子句更改为如下所示:

WHERE 
  case when isdate(rli.BeginDate) = 1 then convert(datetime, rli.BeginDate) else NULL end <= GETDATE()
  AND case when isdate(rli.EndDate) = 1 then convert(datetime, rli.EndDate) else NULL end >= GETDATE()
  AND rli.LcknTyCd = @LcknTyCd);

它在您的前1000个查询中成功的原因可能是因为找到的前1000个记录都包含有效日期。


专家解答

这是一个数据库服务器错误:dapper不知道varchar并且不考虑varchar - 它谈论.net String s。所以:你的一个date-stored-as-varchar被破坏了,并且不包含有效值。

基本上:在SSMS中尝试此查询:我希望它也会在那里打破!

更改为存储过程根本不会更改此设置。需要改变的是破碎的数据 - 以及(更重要的是)在基于文本的列中存储日期/时间数据的错误选择。



许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因