与Dapper一起使用SQL Server存储过程的错误日期
我正在使用存储过程和Dapper从表中检索数据。存储过程在SQL Server中执行时工作正常,并返回所需的信息。与Dapper一起使用SQL Server存储过程的错误日期
但是,当我使用Dapper运行存储过程并检索Date
时,Date
返回为01/01/0001
。
这里是我的存储过程,而在SQL Server中完美的作品:
ALTER PROCEDURE [dbo].[spRankings_GetByEventAndGender]
@Event varchar(50),
@Gender varchar(10)
AS
BEGIN
DECLARE @event_factor INT = CASE
WHEN @Event IN ('Javelin', 'Discus', 'Shot Put', 'Hammer', 'Long Jump', 'High Jump', 'Triple Jump', 'Pole Vault')
THEN -1 /* reverse ranking = highest value is best */
ELSE 1 /* normal ranking = lowest value is best */
END;
SELECT
CASE
WHEN a.mark = ABS(b.mark)
THEN CAST(b.rank AS VARCHAR)
ELSE ''
END AS [Rank],
/*
,a.athleteid
,a.teamid
,a.eventid
*/
CASE
WHEN @event_factor = -1
THEN LTRIM(STR(a.mark, 10, 2))
ELSE FORMAT(DATEADD(SECOND, FLOOR(a.mark), 0),case when a.mark<60 then '' else 'm:' end+'ss')
+substring(ltrim((str(cast(a.mark as decimal(12,5))%1,10,2))),2,10)
end as Mark
,a.wind as Wind
,d.eventname as [Event]
,c.firstname+' '+c.lastname as Athlete
--,Convert(varchar(10),c.BirthDate,103) as [Birth Date]
,c.BirthDate as [BirthDate]
,e.teamname as [Team]
,a.venue as Venue
--, Convert(varchar(10),a.PerformanceDate,103) as [Performance Date]
,a.PerformanceDate as [Performance Date]
from dbo.Performances as a
inner join (select a.PersonId
,a.eventid
,min(a.mark*@event_factor) as mark
,rank() over(partition by a.eventid order by min(a.mark*@event_factor)) as [rank]
,avg(a.mark) as avg_mark
from dbo.Performances as a
inner join dbo.Persons as b
on b.PersonId=a.PersonId
inner join dbo.[Events] as c
on c.eventid=a.eventid
inner join dbo.Meets as d
on d.MeetId = a.MeetId
where [email protected]
and [email protected]
group by a.PersonId
,a.eventid
) as b
on b.eventid=a.eventid
and b.PersonId=a.PersonId
inner join dbo.Persons as c
on c.PersonId=a.PersonId
inner join dbo.events as d
on d.eventid=a.eventid
inner join dbo.teams as e
on e.teamid=a.teamid
inner join dbo.Meets as m
on m.MeetId = a.MeetId
order by a.eventid
,a.mark*@event_factor
,b.[rank]
/*
,b.avg_mark
,a.athleteid
*/
end
在SQL Server中的结果:
使用小巧精致得到的结果的方法:
public List<RankingsModel> GetRankingsByEventAndGender(string eventName, string gender) {
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.GetConString("GARanks"))) {
var output = connection.Query<RankingsModel>($"dbo.spRankings_GetByEventAndGender @Event, @Gender", new { Event=eventName, Gender=gender}).ToList();
return output;
}
}
我申请的结果:
您正在获取应用程序中的默认值。请从下面的链接可以帮助:
Date is 01/01/0001 and not the date that is in the database?
DateTime shows the date as 01/01/0001
HTH
感谢。
谢谢你的帮助。问题是Performance Date导致错误的日期,所以当我将Performance Date加入到PerformanceDate时它就起作用了 – Punishrr
你可以把问题分解成几部分。首先确保Dapper返回正确的结果集。你可以
connection.Query<Object>
或
connection.Query<dynamic>
和检查结果,看它是否有它的正确日期时间值,在调试模式。如果确实如此,那么问题不在于从数据库中检索结果,而是因为它无法将其映射到您的POCO /类。
如果结果数据集没有正确的值Performance Date
,那么问题在于Dapper正在执行的sql生成,并且您可以考虑将该列重命名为无空格或追求该途径。 祝你好运!
您可能在RankingsModel
类中有一个名为'PerformanceDate'
的属性,它不会与DB调用返回的'Performance Date'
映射。
请更新SQL以返回'PerformanceDate'
或为两者同名。
另一种选择是使用Column Mapping
什么是在SQL Server表的日期列的数据类型? – CodingYoshi