//del runner from race internal static void DelRunner(string firstName, string LastName, DateTime dob, int raceID) { BuildIfNotExsistDB(); bool alreadyOpen = _db.State != System.Data.ConnectionState.Closed; try { if (!alreadyOpen) { _db.Open(); } int id = GetRunnerID(firstName, LastName, dob); using (var cmd = new SQLiteCommand()) { cmd.Connection = _db; cmd.CommandText = "delete from RaceRunner where RunnerID=@id and RaceID=@RaceID;"; cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@RaceID", raceID); cmd.ExecuteNonQuery(); } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { if (!alreadyOpen) { _db.Close(); } } }
//delete a result from this race internal static void DelTimingRow(int raceID, string time) { BuildIfNotExsistDB(); using (var conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")) { try { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "delete from raceresults where RaceID=@RaceID AND Time=@time;"; cmd.Parameters.AddWithValue("@RaceID", raceID); cmd.Parameters.AddWithValue("@time", time); cmd.ExecuteNonQuery(); } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { conn.Close(); } } }
//update time of a runner internal static void UpdateTimingTime(int raceID, string bib, string oldTime, string newTime) { BuildIfNotExsistDB(); using (var conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")) { try { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "update raceresults set Time=@newTime where RaceID=@RaceID AND BibID=@Bib AND Time=@oldTime;"; cmd.Parameters.AddWithValue("@RaceID", raceID); cmd.Parameters.AddWithValue("@Bib", bib); cmd.Parameters.AddWithValue("@newTime", newTime); cmd.Parameters.AddWithValue("@oldTime", oldTime); cmd.ExecuteNonQuery(); } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { conn.Close(); } } }
//add bib:time result to db for this race internal static void AddTimeAndBib(int raceID, string bib, string time) { BuildIfNotExsistDB(); using (var conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")) { try { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "insert into raceresults(RaceID, BibID, Time) values(@RaceID, @Bib, @Time);"; cmd.Parameters.AddWithValue("@RaceID", raceID); cmd.Parameters.AddWithValue("@Bib", bib); cmd.Parameters.AddWithValue("@Time", time); cmd.ExecuteNonQuery(); } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { conn.Close(); } } }
//start the clock public override void StartRace() { if (_timer == null) { _timer = new TobyTimer(); } }
//add runner to db internal static void AddRunner(string FirstName, string LastName, DateTime DOB, string BibID, string sex, string Team, string Orginization, string RaceName, string Connection) { BuildIfNotExsistDB(); int raceID = GetRaceID(RaceName); using (var conn = new SQLiteConnection(Connection)) { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "Insert Into Runners(FirstName, LastName, DOB, Gender) Values(@FirstName, @LastName, @DOB, @Sex);"; cmd.Parameters.AddWithValue("@FirstName", FirstName); cmd.Parameters.AddWithValue("@LastName", LastName); cmd.Parameters.AddWithValue("@DOB", DOB.ToString("yyyy-MM-dd")); if (sex.Length > 0) { cmd.Parameters.AddWithValue("@Sex", sex.ToUpper().ToCharArray()[0]); } else { cmd.Parameters.AddWithValue("@Sex", 'N'); } //check to ensure that value was added, if not, break if (cmd.ExecuteNonQuery() == 0) { MessageBox.Show("No Runner added"); return; } cmd.CommandText = "Insert or ignore into RaceRunner(RunnerID, RaceID, BibID, Orginization, Team) Values(" + "(select RunnerID from Runners where FirstName=@FirstName AND LastName=@LastName Limit 1)," + "@Race," + "@BibID,@Orginization,@Team);"; cmd.Parameters.AddWithValue("@Race", raceID); cmd.Parameters.AddWithValue("@BibID", BibID); cmd.Parameters.AddWithValue("@Team", Team); cmd.Parameters.AddWithValue("@Orginization", Orginization); //check to ensure that value was added, if not, break if (cmd.ExecuteNonQuery() == 0) { MessageBox.Show("No Runner added to race"); return; } } conn.Close(); TobyTimer.BackupAfter60Seconds(); } }
//reuturn runner ID based on name and dob. return -1 if none is found internal static int GetRunnerID(string firstName, string LastName, DateTime dob) { BuildIfNotExsistDB(); int runnerID = -1; bool alreadyOpen = _db.State != System.Data.ConnectionState.Closed; try { if (!alreadyOpen) { _db.Open(); } using (var cmd = new SQLiteCommand()) { cmd.Connection = _db; cmd.CommandText = "select RunnerID from runners where FirstName=@firstName and LastName=@lastName and DOB=@dob;"; cmd.Parameters.AddWithValue("@firstName", firstName); cmd.Parameters.AddWithValue("@lastName", LastName); cmd.Parameters.AddWithValue("@dob", dob.ToString("yyyy-MM-dd")); SQLiteDataReader r = cmd.ExecuteReader(); if (r.HasRows) { r.Read(); runnerID = r.GetInt32(0); r.Dispose(); } else { Console.WriteLine("No rows found."); } } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { if (!alreadyOpen) { _db.Close(); } } return(runnerID); }
//returns -1 if no race is found public static int GetRaceID(string raceName) { BuildIfNotExsistDB(); int raceID; if (RaceIdMap.TryGetValue(raceName, out raceID)) { return(raceID); } //becouse if it wasnt found before, RaceID is now Default int (0) raceID = -1; using (var conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")) { try { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "select RaceID from Race where Name = @RaceName Limit 1;"; cmd.Parameters.AddWithValue("@RaceName", raceName); SQLiteDataReader r = cmd.ExecuteReader(); if (r.HasRows) { r.Read(); raceID = r.GetInt32(0); RaceIdMap.Add(raceName, raceID); r.Dispose(); } else { Console.WriteLine("No rows found."); } } TobyTimer.BackupAfter60Seconds(); } catch (Exception e) { MessageBox.Show(e.Message); } finally { conn.Close(); } return(raceID); } }
//same as above except we want to check if the bib exsist excluding runner internal static bool BibExistOutsideRunner(string bibID, int raceID, int runnerID) { BuildIfNotExsistDB(); bool results = false; bool alreadyOpen = _db.State != System.Data.ConnectionState.Closed; try { if (!alreadyOpen) { _db.Open(); } using (var cmd = new SQLiteCommand()) { cmd.Connection = _db; cmd.CommandText = "select * from RaceRunner where BibID=@id and RaceID=@RaceID and RunnerID<>@runnerID;"; cmd.Parameters.AddWithValue("@id", bibID); cmd.Parameters.AddWithValue("@RaceID", raceID); cmd.Parameters.AddWithValue("@runnerID", runnerID); var r = cmd.ExecuteReader(); results = r.HasRows; if (results) { //burn through results to prevent db lock while (r.Read()) { } } } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { if (!alreadyOpen) { _db.Close(); } } //if we get here then ya, it doesnt exsist return(results); }
//the inserting of every runner that happens on an asynchronous thread public static Task ProcessRunners(string[] FirstName, string[] LastName, DateTime[] DOB, char[] Genders, string[] BibID, string[] Team, string[] Orginization, string RaceName, string Connection, IProgress <ProgressReport> progress) { BuildIfNotExsistDB(); int raceID = GetRaceID(RaceName); int index = 1; //big assumption that all arrays are same size or atleast larger than the FirstName array int totalProcess = FirstName.Length; var progessReport = new ProgressReport(); return(Task.Run(() => { using (var conn = new SQLiteConnection(Connection)) { conn.Open(); using (var cmd = new SQLiteCommand()) { for (int i = 0; i < totalProcess; i++) { cmd.Connection = conn; cmd.CommandText = "Insert Into Runners(FirstName, LastName, DOB, Gender) Values(@FirstName, @LastName, @DOB, @Sex);"; cmd.Parameters.AddWithValue("@FirstName", FirstName[i]); cmd.Parameters.AddWithValue("@LastName", LastName[i]); cmd.Parameters.AddWithValue("@DOB", DOB[i].ToString("yyyy-MM-dd")); cmd.Parameters.AddWithValue("@Sex", Genders[i]); cmd.ExecuteNonQuery(); cmd.CommandText = "Insert into RaceRunner(RunnerID, RaceID, BibID, Orginization, Team) Values(" + "(select RunnerID from Runners where FirstName=@FirstName AND LastName=@LastName Limit 1)," + "@Race," + "@BibID,@Orginization,@Team);"; cmd.Parameters.AddWithValue("@Race", raceID); cmd.Parameters.AddWithValue("@BibID", BibID[i]); cmd.Parameters.AddWithValue("@Team", Team[i]); cmd.Parameters.AddWithValue("@Orginization", Orginization[i]); cmd.ExecuteNonQuery(); progessReport.PercentComplete = index++ *100 / totalProcess; progress.Report(progessReport); } } conn.Close(); TobyTimer.BackupAfter60Seconds(); } })); }
//update runner info internal static void UpdateRunner(int runnerId, string FirstName, string LastName, DateTime DOB, string BibID, string sex, string Team, string Orginization, string RaceName, string Connection) { BuildIfNotExsistDB(); int raceID = GetRaceID(RaceName); using (var conn = new SQLiteConnection(Connection)) { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "Update Runners set FirstName=@FirstName, LastName=@LastName, DOB=@DOB, Gender=@Sex where RunnerID=@runnerId;"; cmd.Parameters.AddWithValue("@FirstName", FirstName); cmd.Parameters.AddWithValue("@LastName", LastName); cmd.Parameters.AddWithValue("@DOB", DOB.ToString("yyyy-MM-dd")); cmd.Parameters.AddWithValue("@Sex", sex.ToUpper().ToCharArray()[0]); cmd.Parameters.AddWithValue("@runnerId", runnerId); //check to ensure that value was added, if not, break if (cmd.ExecuteNonQuery() == 0) { MessageBox.Show("No Runner changed"); return; } cmd.CommandText = "Update RaceRunner set BibID=@BibID, Orginization=@Orginization, Team=@Team where RunnerID=@runnerId"; cmd.Parameters.AddWithValue("@Race", raceID); cmd.Parameters.AddWithValue("@BibID", BibID); cmd.Parameters.AddWithValue("@Team", Team); cmd.Parameters.AddWithValue("@Orginization", Orginization); //check to ensure that value was added, if not, break if (cmd.ExecuteNonQuery() == 0) { MessageBox.Show("Runner race data not updated"); return; } } conn.Close(); TobyTimer.BackupAfter60Seconds(); } }
//returns list of duplicated bib id in race results for this race public static List <string> FindBadBibs(int raceID) { BuildIfNotExsistDB(); var badBibs = new List <string>(); using (var conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")) { try { conn.Open(); using (var cmd = new SQLiteCommand()) { //get list of all results in table that have multiple BibID entries cmd.Connection = conn; cmd.CommandText = "select BibID from(select count(BibID) as count, BibID from RaceResults where RaceID=@RaceID Group by (BibID)) as dictionary where dictionary.count>1;"; cmd.Parameters.AddWithValue("@RaceID", raceID + ""); var reader = cmd.ExecuteReader(); while (reader.Read()) { badBibs.Add(reader[0].ToString()); } reader.Dispose(); } TobyTimer.BackupAfter60Seconds(); } catch (Exception sqlError) { MessageBox.Show(sqlError.Message); } finally { conn.Close(); } } return(badBibs); }
//kill the clock public override void StopRace() { _timer = null; }
public void TestTobyTimer() { //Test public constructor with no parameters TobyTimer tobyTimer = new TobyTimer(); //Test it returns some form of a timespan Assert.IsNotNull(tobyTimer.Elapsed()); //Test public constructor when taking in a datetime parameter DateTime dt = DateTime.Now; TobyTimer tobyTimer1 = new TobyTimer(dt); Assert.IsNotNull(tobyTimer1.Elapsed()); }
//call on screen close. create backup private void StartScreen_FormClosing(object sender, FormClosingEventArgs e) { //Backup the database on close TobyTimer.TimeStampedBackup(true); }