public void Initialize()
{
competitor = new Competitor();
resultZero = new MultisportResult();
resultZero.Section = 0;
resultZero.TimeOfDay = new TimeSpan(7, 0, 0);
competitor.Results.Add(resultZero);
resultOne = new MultisportResult();
resultOne.Section = 1;
resultOne.TimeOfDay = new TimeSpan(7, 30, 0);
competitor.Results.Add(resultOne);
}
private void FillViewModel()
{
using (SqlCeConnection connection = new SqlCeConnection(String.Concat(@"Data Source=""", ViewModel.FilePath, @"""")))
using (SqlCeCommand command = new SqlCeCommand("SELECT Id, Bib, LastName, Code FROM Competitors", connection))
using (SqlCeCommand resultsCommand = new SqlCeCommand("SELECT Id, Section, TimeOfDay, State FROM MultisportResults WHERE Bib = @Bib", connection))
{
connection.Open();
using (SqlCeDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = (int)reader[0];
Competitor competitor = ViewModel.Competitors.FirstOrDefault(x => x.Id == id);
if (competitor == null)
{
competitor = new Competitor();
competitor.Id = (int)reader[0];
ViewModel.Competitors.Add(competitor);
}
competitor.Bib = (int)reader[1];
competitor.LastName = (string)reader[2];
competitor.Code = (string)reader[3];
resultsCommand.Parameters.Clear();
resultsCommand.Parameters.Add(new SqlCeParameter("Bib", competitor.Bib));
SqlCeDataReader resultsReader = resultsCommand.ExecuteReader();
while (resultsReader.Read())
{
int resultsId = (int)resultsReader[0];
MultisportResult result = competitor.Results.FirstOrDefault(x => x.Id == resultsId);
if (result == null)
{
result = new MultisportResult();
result.Id = resultsId;
competitor.Results.Add(result);
}
result.Bib = competitor.Bib;
result.Section = (int)resultsReader[1];
result.TimeOfDay = TimeSpan.FromMilliseconds((int)resultsReader[2] / 10);
result.State = (ResultState)(int)resultsReader[3];
}
}
}
}
}
private void SendToWebsite()
{
if (!String.IsNullOrEmpty(ViewModel.ConnectionString))
{
try {
using (SqlConnection conn = new SqlConnection(ViewModel.ConnectionString))
using (SqlCommand selectCommand = new SqlCommand("SELECT CrewId FROM dbo.Crews WHERE BroeCrewId = @BroeId", conn))
using (SqlCommand command = new SqlCommand(@"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.Results SET TimeOfDay = @TimeOfDay WHERE CrewId = @CrewId AND TimingPointId = @TimingPointId;
IF @@ROWCOUNT = 0
BEGIN
INSERT dbo.Results(CrewId,TimeOfDay,TimingPointId) SELECT @CrewId,@TimeOfDay,@TimingPointId;
END
COMMIT TRANSACTION; ", conn))
using (SqlCommand statusCommand = new SqlCommand("UPDATE dbo.Crews SET Status = @ResultStatus WHERE CrewId = @CrewId", conn))
{
conn.Open();
SqlParameter broeParameter = new SqlParameter("@BroeId", SqlDbType.Int);
selectCommand.Parameters.Add(broeParameter);
SqlParameter timeOfDay = new SqlParameter("@TimeOfDay", SqlDbType.Time);
SqlParameter timingPointId = new SqlParameter("@TimingPointId", SqlDbType.Int);
SqlParameter crewId = new SqlParameter("@CrewId", SqlDbType.Int);
SqlParameter statusCrewId = new SqlParameter("@CrewId", SqlDbType.Int);
SqlParameter resultStatus = new SqlParameter("@ResultStatus", SqlDbType.Int);
command.Parameters.Add(timeOfDay);
command.Parameters.Add(timingPointId);
command.Parameters.Add(crewId);
statusCommand.Parameters.Add(statusCrewId);
statusCommand.Parameters.Add(resultStatus);
foreach (Competitor competitor in ViewModel.Competitors)
{
if (competitor.Code == null || (competitor.StartTime == null && competitor.State == ResultState.Ok))
{
continue;
}
// Get the CrewId
broeParameter.Value = Int32.Parse(competitor.Code, CultureInfo.InvariantCulture);
crewId.Value = (int)selectCommand.ExecuteScalar();
statusCrewId.Value = crewId.Value;
// Sync start times (point Id 1)
if (competitor.StartTime != null)
{
timeOfDay.Value = competitor.StartTime.TimeOfDay;
timingPointId.Value = 1;
command.ExecuteNonQuery();
}
// Sync Barnes times (point Id 2)
MultisportResult sectionResult = competitor.SectionTime(1);
if (sectionResult != null)
{
timeOfDay.Value = sectionResult.TimeOfDay;
timingPointId.Value = 2;
command.ExecuteNonQuery();
}
// Sync Hammersmith times (point Id 3)
sectionResult = competitor.SectionTime(2);
if (sectionResult != null)
{
timeOfDay.Value = sectionResult.TimeOfDay;
timingPointId.Value = 3;
command.ExecuteNonQuery();
}
// Sync finish times (point Id 4)
sectionResult = competitor.FinishTime;
if (sectionResult != null && sectionResult.TimeOfDay > new TimeSpan(0, 0, 0))
{
timeOfDay.Value = sectionResult.TimeOfDay;
timingPointId.Value = 4;
command.ExecuteNonQuery();
}
resultStatus.Value = (int)competitor.State;
statusCommand.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
ViewModel.IsConnecting = false;
MessageBox.Show(ex.Message);
}
}
}
