public bool SearchDuplication(Meeting meeting)
{
bool result = false;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Query(
from m in new SQLinq <Meeting>()
where
m.MeetingDate == meeting.MeetingDate &&
m.CountryId == meeting.CountryId &&
m.RaceCourseId == meeting.RaceCourseId
select m).Any();
}
catch (Exception error)
{
RdLogger.Error("Error during execute SearchDuplication type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public List <DTORaceType2> GetAllDto(int countryId)
{
List <DTORaceType2> result = null;
var sql =
"SELECT rt.Id ,rt.Name, rt.CountryId, c.Name AS CountryName, rt.isGroup FROM [dbo].[RaceType] rt LEFT JOIN Country c ON rt.CountryId = c.Id WHERE rt.CountryId=" + countryId;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Query <DTORaceType2>(sql).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetAllDto type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public List <vwvJockey> GetByCountryId(int?countryId)
{
List <vwvJockey> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Query(from jockey in new SQLinq <vwvJockey>()
where jockey.CountryOfRegistrationId == countryId
orderby jockey.Fullname
select jockey).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetByCountryId type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public List <RaceCourse> GetByCountry(int?countryId)
{
List <RaceCourse> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from p in new SQLinq <RaceCourse>()
orderby p.Name
where p.CountryId == countryId
select p).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetByCountry type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public override List <Jockey> GetAll()
{
List <Jockey> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from jockey in new SQLinq <Jockey>() orderby jockey.Fullname select jockey)
.ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetAll type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public List <vwvHorse> GetByCountryFromView(int?countryId)
{
List <vwvHorse> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from h in new SQLinq <vwvHorse>()
where h.CountryOfRegistrationId == countryId
orderby h.Name
select h).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetByCountryFromView type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public List <Distance> GetByRaceCourse(int?rcId)
{
List <Distance> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from d in new SQLinq <Distance>() where d.RaceCourseID == rcId select d)
.ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetByRaceCourse type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public T GetById(int?id)
{
T result = null;
if (id != null)
{
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Get <T>(id);
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetById type:" + typeof(T).ToString(), error);
}
finally
{
connection.Close();
}
}
}
return(result);
}
public override bool Insert(Race newEntity)
{
int result = 0;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
connection.Open();
var sql =
"INSERT INTO [dbo].[Race] ([MeetingId],[RaceNumber],[RaceName],[RaceTypeId],[DistanceId],[RaceJumpTimeLocal],[HK_RaceIndex],[RaceWinningTime],[RaceGoingId],[isTurf],[NumberOfRunners],[isDone],[isStarted])" +
" VALUES (@MeetingId,@RaceNumber,@RaceName,@RaceTypeId,@DistanceId,@RaceJumpTimeLocal,@HK_RaceIndex,@RaceWinningTime,@RaceGoingId,@isTurf,@NumberOfRunners,@isDone,@isStarted)";
try
{
result = connection.Execute(sql, new
{
newEntity.MeetingId,
newEntity.RaceNumber,
newEntity.RaceName,
RaceTypeId =
newEntity.RaceTypeId == 0 ? null : newEntity.RaceTypeId,
DistanceId =
newEntity.DistanceId == 0 ? null : newEntity.DistanceId,
newEntity.RaceJumpDateTimeUTC,
newEntity.HK_RaceIndex,
RaceWinningTime = newEntity.RaceWinningTime.ToString(),
newEntity.RaceGoingId,
newEntity.isTurf,
NumberOfRunners = newEntity.NumberOfRunners,
newEntity.isDone,
newEntity.isStarted
});
}
catch (Exception error)
{
RdLogger.Error("Error during execute Insert type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result > 0);
}
public override bool Update(Race entity)
{
int result = 0;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
connection.Open();
var sql =
"UPDATE [dbo].[Race] SET [MeetingId] = @MeetingId, [RaceNumber] = @RaceNumber, [RaceName] = @RaceName, [RaceTypeId] = @RaceTypeId " +
",[DistanceId] = @DistanceId,[RaceJumpDateTimeUTC] = @RaceJumpDateTimeUTC,[HK_RaceIndex] = @HK_RaceIndex,[RaceWinningTime] = @RaceWinningTime " +
",[RaceGoingId] = @RaceGoingId,[isTurf] = @isTurf,[NumberOfRunners] = @NumberOfRunners,[isDone] = @isDone, [isStarted] = @isStarted WHERE Id=@Id";
try
{
result = connection.Execute(sql, new
{
entity.Id,
entity.MeetingId,
entity.RaceNumber,
entity.RaceName,
RaceTypeId = entity.RaceTypeId == 0 ? null : entity.RaceTypeId,
DistanceId = entity.DistanceId == 0 ? null : entity.DistanceId,
entity.RaceJumpDateTimeUTC,
entity.HK_RaceIndex,
RaceWinningTime = entity.RaceWinningTime.ToString(),
entity.RaceGoingId,
entity.isTurf,
NumberOfRunners = entity.NumberOfRunners,
entity.isDone,
entity.isStarted
});
}
catch (Exception error)
{
RdLogger.Error("Error during execute Update type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result > 0);
}
public override List <RaceType> GetAll()
{
using (IDbConnection connection = _dbConnection.SqlConnection)
{
List <RaceType> result = null;
try
{
connection.Open();
result = connection.Query(from rt in new SQLinq <RaceType>() orderby rt.Name select rt).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetAll type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
return(result);
}
}
public List <HorseOdd> GetByRaceId(int?raceId)
{
List <HorseOdd> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from h in new SQLinq <HorseOdd>() where h.RaceId == raceId select h).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetByRaceId type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public virtual bool Update(T entity)
{
bool result = false;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Update(entity);
}
catch (Exception error)
{
RdLogger.Error("Error during execute Update type:" + typeof(T).ToString(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public virtual bool Insert(T newEntity)
{
int result = -1;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Insert(newEntity);
}
catch (Exception error)
{
RdLogger.Error("Error during execute Insert type:" + typeof(T).ToString(), error);
}
finally
{
connection.Close();
}
}
return(result > 0);
}
public virtual List <T> GetAll()
{
List <T> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result = connection.Query(from p in new SQLinq <T>() select p).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetAll type:" + typeof(T).ToString(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public List <DtoMeeting> SearchMeetings(int?countryId, string racecourse, DateTime?startDate, DateTime?endDate)
{
var sql = "SELECT m.[Id],ISNULL(c.Code,'') AS CountryCode,[MeetingDate],ISNULL(rc.Name,'') AS RaceCourseName,[WeatherId],[DefaultGoingId],[NumberOfRaces]" +
",[HK_isNightMeet],[CourseVariantId],[Z_MeetingCode],[isAbandoned],m.[MeetingCode] FROM [dbo].[Meeting] m " +
" LEFT JOIN Country c ON m.CountryId = c.Id LEFT JOIN RaceCourse rc ON m.RaceCourseId = rc.Id WHERE m.CountryId=" + countryId + " AND RaceCourseId=" + racecourse;
if (startDate != null && startDate != DateTime.MinValue)
{
sql += " AND MeetingDate>='" + startDate.Value.ToString(CultureInfo.InstalledUICulture.DateTimeFormat) + "'";
}
if (endDate != null && endDate != DateTime.MinValue)
{
sql += " AND MeetingDate<='" + endDate.Value.ToString(CultureInfo.InstalledUICulture.DateTimeFormat) + "'";
}
using (IDbConnection connection = _dbConnection.SqlConnection)
{
List <DtoMeeting> meetings = null;
try
{
connection.Open();
meetings =
connection.Query <DtoMeeting>(sql).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute SearchMeetings type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
return(meetings);
}
}
public List <Race> GetByMeetingId(int?meetingId)
{
List <Race> result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from r in new SQLinq <Race>() where r.MeetingId == meetingId select r).ToList();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetByMeetingId type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result);
}
public int GetCountryIdByName(string countryName)
{
Country result = null;
using (IDbConnection connection = _dbConnection.SqlConnection)
{
try
{
connection.Open();
result =
connection.Query(from c in new SQLinq <Country>() where c.Name == countryName select c)
.FirstOrDefault();
}
catch (Exception error)
{
RdLogger.Error("Error during execute GetCountryIdByName type:" + this.GetType(), error);
}
finally
{
connection.Close();
}
}
return(result == null ? -1 : result.Id);
}