public static IEntityList <PonyCompetition> SelectForCompetition(int competitionId, bool orderByAssessment) { IEntityList <PonyCompetition> result = new EntityList <PonyCompetition>(); using (SQLiteConnection connection = OpenConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(connection)) { StringBuilder sb = new StringBuilder() .Append("SELECT ") .Append("pc.Id, pc.CompetitionId, pc.PonyId, pc.SortIndex, ") .Append("pc.Assessment, pc.Comment, ") .Append("s.FirstName, s.LastName, s.Club, p.Name as Pony ") .Append("FROM EO_PonyCompetition AS pc ") .Append("INNER JOIN EO_Pony AS p ON p.Id = pc.PonyId ") .Append("INNER JOIN EO_Starter AS s ON s.Id = p.StarterId ") .Append("WHERE pc.CompetitionId = ? "); if (orderByAssessment) { sb.Append("ORDER BY pc.Assessment DESC;"); } else { sb.Append("ORDER BY pc.SortIndex;"); } cmd.CommandText = sb.ToString(); cmd.Parameters.Add(new SQLiteParameter { Value = competitionId }); _log.Debug(CreateLogString(cmd)); using (SQLiteDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { result.Add(new PonyCompetition { Id = rdr.GetInt32(0), CompetitionId = rdr.GetInt32(1), PonyId = rdr.GetInt32(2), SortIndex = rdr.GetInt32(3), Assessment = rdr.GetNullableDecimal(4), Comment = rdr.GetNullableString(5), FirstName = rdr.GetNullableString(6), LastName = rdr.GetNullableString(7), Club = rdr.GetNullableString(8), Pony = rdr.GetNullableString(9) }); } } } } return(result); }
public static IEntityList <PonyCompetition> Select(int ponyId) { IEntityList <PonyCompetition> result = new EntityList <PonyCompetition>(); using (SQLiteConnection connection = OpenConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(connection)) { StringBuilder sb = new StringBuilder() .Append("SELECT Id, PonyId, CompetitionId, SortIndex, Assessment, Comment ") .Append("FROM EO_PonyCompetition ") .Append("WHERE PonyId = ?"); cmd.CommandText = sb.ToString(); cmd.Parameters.Add(new SQLiteParameter { Value = ponyId }); _log.Debug(CreateLogString(cmd)); using (SQLiteDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { result.Add(new PonyCompetition { Id = rdr.GetInt32(0), PonyId = rdr.GetInt32(1), CompetitionId = rdr.GetInt32(2), SortIndex = rdr.GetInt32(3), Assessment = rdr.GetNullableDecimal(4), Comment = rdr.GetNullableString(5) }); } } } } return(result); }
public static IEntityList <Starter> Select() { IEntityList <Starter> result = new EntityList <Starter>(); using (SQLiteConnection connection = OpenConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(connection)) { StringBuilder sb = new StringBuilder() .Append("SELECT Id, FirstName, LastName, Birthdate, Club, Comment, Costs, Paied ") .Append("FROM EO_Starter ") .Append("ORDER BY LastName, FirstName;"); cmd.CommandText = sb.ToString(); _log.Debug(CreateLogString(cmd)); using (SQLiteDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { result.Add(new Starter { Id = rdr.GetInt32(0), FirstName = rdr.GetNullableString(1), LastName = rdr.GetNullableString(2), Birthdate = rdr.GetNullableDateTime(3), Club = rdr.GetNullableString(4), Comment = rdr.GetNullableString(5), Costs = rdr.GetNullableDecimal(6), Paied = rdr.GetBoolean(7) }); } } } } return(result); }