private string BuildInsertStatement(Guest guest) {
return string.Format("INSERT INTO reservations VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')",
Guid.NewGuid().ToString(),
guest.Name,
DateTime.UtcNow.ToString(),
guest.Attending.ToString(),
guest.Underage.ToString());
}
private Guest[] HydrateGuests(SQLiteDataReader reader) {
var guests = new List<Guest>();
while (reader.Read()) {
var guest = new Guest();
guest.Name = reader.GetString(1);
guest.ReservationDate = DateTime.Parse(reader.GetString(2));
guest.Attending = bool.Parse(reader.GetString(3));
guest.Underage = bool.Parse(reader.GetString(4));
guests.Add(guest);
}
return guests.ToArray();
}
public ActionResult List(string orderBy) {
var guests = new Guest[0];
using (var connection = new SQLiteConnection("Data Source=" + MvcApplication.DatabaseFile + ";Version=3;")) {
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM reservations";
connection.Open();
using (var reader = command.ExecuteReader())
guests = HydrateGuests(reader);
connection.Close();
}
switch (orderBy.ToLower()) {
case "date":
guests = guests.OrderBy(x => x.Name).OrderByDescending(x => x.ReservationDate).ToArray();
break;
case "name":
default:
guests = guests.OrderBy(x => x.ReservationDate).OrderBy(x => x.Name).ToArray();
break;
}
ViewData["guests"] = guests;
return View();
}