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);
}
