/// <summary>
/// Returns a single roommateChore with the given id.
/// </summary>
public RoommateChore GetById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT RoommateId, ChoreId FROM RoommateChore WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
RoommateChore roommateChore = null;
// If we only expect a single row back from the database, we don't need a while loop.
if (reader.Read())
{
roommateChore = new RoommateChore
{
Id = id,
RoommateId = reader.GetInt32(reader.GetOrdinal("RoommateId")),
ChoreId = reader.GetInt32(reader.GetOrdinal("ChoreId")),
};
}
reader.Close();
return(roommateChore);
}
}
}
/// <summary>
/// Updates the roommateChore
/// </summary>
public void Update(RoommateChore roommateChore)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE RoommateChore
SET RoommateId = @RoommateId,
ChoreId = @ChoreId
WHERE Id = @id";
cmd.Parameters.AddWithValue("@RoommateId", roommateChore.RoommateId);
cmd.Parameters.AddWithValue("@ChoreId", roommateChore.ChoreId);
cmd.Parameters.AddWithValue("@id", roommateChore.Id);
cmd.ExecuteNonQuery();
}
}
}
public void AssignChore(int roommateId, int choreId)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
RoommateChore roommateChore = new RoommateChore()
{
RoommateId = roommateId,
ChoreId = choreId
};
cmd.CommandText = @" INSERT INTO RoommateChore(ChoreId, RoommateId)
OUTPUT INSERTED.Id
VALUES (@choreId, @roommateId)";
cmd.Parameters.AddWithValue("@choreId", roommateChore.RoommateId);
cmd.Parameters.AddWithValue("@roommateId", roommateChore.ChoreId);
int id = (int)cmd.ExecuteScalar();
roommateChore.Id = id;
}
}
}
/// <summary>
/// Add a new roommateChore to the database
/// NOTE: This method sends data to the database,
/// it does not get anything from the database, so there is nothing to return.
/// </summary>
public void Insert(RoommateChore roommateChore)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
// These SQL parameters are annoying. Why can't we use string interpolation?
// ... sql injection attacks!!!
cmd.CommandText = @"INSERT INTO roommateChore (RoommateId, ChoreId)
OUTPUT INSERTED.Id
VALUES (@RoommateId, @ChoreId)";
cmd.Parameters.AddWithValue("@RoommateId", roommateChore.RoommateId);
cmd.Parameters.AddWithValue("@ChoreId", roommateChore.ChoreId);
int id = (int)cmd.ExecuteScalar();
roommateChore.Id = id;
}
}
// when this method is finished we can look in the database and see the new roommateChore.
}
public RoommateChore AssignChore(int RoommateID, int choreId)
{
using (SqlConnection conn = Connection)
{
RoommateChore roommateChore = new RoommateChore();
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO RoommateChore (RoommateId, ChoreId)
OUTPUT INSERTED.Id
VALUES (@RoomMateID, @ChoreID);";
cmd.Parameters.AddWithValue("@RoomMateID", RoommateID);
cmd.Parameters.AddWithValue("@ChoreID", choreId);
int id = (int)cmd.ExecuteScalar();
roommateChore.Id = id;
return(roommateChore);
}
}
}
public void InsertRC(Roommate roommate, Chore chore)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO RoommateChore (RoommateId, ChoreID)
OUTPUT INSERTED.Id
VALUES (@RoommateId, @ChoreID)";
cmd.Parameters.AddWithValue("@RoommateId", roommate.Id);
cmd.Parameters.AddWithValue("@ChoreID", chore.Id);
int id = (int)cmd.ExecuteScalar();
roommate.Id = id;
RoommateChore rmChore = new RoommateChore()
{
Id = id,
RoommateId = roommate.Id,
ChoreID = chore.Id
};
}
}
}
/// <summary>
/// Get a list of all Rooms in the database
/// </summary>
public List <RoommateChore> GetAll()
{
// We must "use" the database connection.
// Because a database is a shared resource (other applications may be using it too) we must
// be careful about how we interact with it. Specifically, we Open() connections when we need to
// interact with the database and we Close() them when we're finished.
// In C#, a "using" block ensures we correctly disconnect from a resource even if there is an error.
// For database connections, this means the connection will be properly closed.
using (SqlConnection conn = Connection)
{
// Note, we must Open() the connection, the "using" block doesn't do that for us.
conn.Open();
// We must "use" commands too.
using (SqlCommand cmd = conn.CreateCommand())
{
// Here we setup the command with the SQL we want to execute before we execute it.
cmd.CommandText = "SELECT Id, RoommateId, ChoreId FROM RoommateChore";
// Execute the SQL in the database and get a "reader" that will give us access to the data.
SqlDataReader reader = cmd.ExecuteReader();
// A list to hold the roommateChores we retrieve from the database.
List <RoommateChore> roommateChores = new List <RoommateChore>();
// Read() will return true if there's more data to read
while (reader.Read())
{
// The "ordinal" is the numeric position of the column in the query results.
// For our query, "Id" has an ordinal value of 0 and "Name" is 1.
int idColumnPosition = reader.GetOrdinal("Id");
// We user the reader's GetXXX methods to get the value for a particular ordinal.
int idValue = reader.GetInt32(idColumnPosition);
int RoommateIdPosition = reader.GetOrdinal("RoommateId");
int roommateIdValue = reader.GetInt32(RoommateIdPosition);
int ChoreIdPosition = reader.GetOrdinal("ChoreId");
int choreIdValue = reader.GetInt32(ChoreIdPosition);
// Now let's create a new roommateChore object using the data from the database.
RoommateChore roommateChore = new RoommateChore
{
Id = idValue,
RoommateId = roommateIdValue,
ChoreId = choreIdValue,
};
// ...and add that roommateChore object to our list.
roommateChores.Add(roommateChore);
}
// We should Close() the reader. Unfortunately, a "using" block won't work here.
reader.Close();
// Return the list of roommateChores who whomever called this method.
return(roommateChores);
}
}
}
static void Main(string[] args)
{
RoomRepository roomRepo = new RoomRepository(CONNECTION_STRING);
ChoreRepository choreRepo = new ChoreRepository(CONNECTION_STRING);
RoommateRepository roommateRepo = new RoommateRepository(CONNECTION_STRING);
bool runProgram = true;
while (runProgram)
{
string selection = GetMenuSelection();
switch (selection)
{
case ("Show all rooms"):
Console.Clear();
List <Room> rooms = roomRepo.GetAll();
foreach (Room r in rooms)
{
Console.WriteLine($"{r.Id} - {r.Name} Max Occupancy({r.MaxOccupancy})");
}
Console.Write("Press any key to continue");
Console.ReadKey();
break;
case ("Search for room"):
Console.Clear();
Console.Write("Room Id: ");
int id = int.Parse(Console.ReadLine());
Room room = roomRepo.GetById(id);
Console.WriteLine($"{room.Id} - {room.Name} Max Occupancy({room.MaxOccupancy})");
Console.Write("Press any key to continue");
Console.ReadKey();
break;
case ("Add a room"):
Console.Clear();
Console.Write("Room name: ");
string name = Console.ReadLine();
Console.Write("Max occupancy: ");
int max = int.Parse(Console.ReadLine());
Room roomToAdd = new Room()
{
Name = name,
MaxOccupancy = max
};
roomRepo.Insert(roomToAdd);
Console.Clear();
Console.WriteLine($"{roomToAdd.Name} has been added and assigned an Id of {roomToAdd.Id}");
Console.Write("Press any key to continue");
Console.ReadKey();
break;
case ("Show all chores"):
Console.Clear();
List <Chore> chores = choreRepo.GetAll();
foreach (Chore c in chores)
{
Console.WriteLine($"{c.Id} - {c.Name}");
}
Console.WriteLine("Press any key to continue");
Console.ReadLine();
break;
case ("Search for chore"):
Console.Clear();
Console.Write("Chore Id: ");
int choreId = int.Parse(Console.ReadLine());
Chore chore = choreRepo.GetById(choreId);
Console.WriteLine($"{chore.Id} - {chore.Name}");
Console.Write("Press any key to continue");
Console.ReadKey();
break;
case ("Add a chore"):
Console.Clear();
Console.Write("Chore Name: ");
string ChoreName = Console.ReadLine();
Chore choreToAdd = new Chore()
{
Name = ChoreName
};
choreRepo.Insert(choreToAdd);
Console.Clear();
Console.WriteLine($"{choreToAdd.Name} has been added and assigned an Id of {choreToAdd.Id}");
Console.WriteLine("Press any key to continue");
Console.ReadKey();
break;
case ("Get Unassigned Chores"):
Console.Clear();
PrintUnassignedChores(choreRepo);
Console.WriteLine("Press any key to continue");
Console.ReadKey();
break;
case ("Search Roommate"):
Console.Clear();
Console.Write("Roommate Id: ");
int roommateId = int.Parse(Console.ReadLine());
Roommate roommate = roommateRepo.GetById(roommateId);
Console.WriteLine($"{roommate.Firstname} - Rent Portion: {roommate.RentPortion} - Room: {roommate.Room.Name}");
Console.WriteLine("Press any key to continue");
Console.ReadKey();
break;
case ("Assign Chore"):
Console.Clear();
PrintUnassignedChores(choreRepo);
Console.Write("Select an option: ");
int chosenChoreId = int.Parse(Console.ReadLine());
Console.Clear();
PrintRoommates(roommateRepo);
Console.Write("Select an option: ");
int chosenRoommateId = int.Parse(Console.ReadLine());
Chore chosenChore = choreRepo.GetById(chosenChoreId);
Roommate chosenRoomate = roommateRepo.GetById(chosenRoommateId);
RoommateChore assignedChore = choreRepo.AssignChore(chosenRoommateId, chosenChoreId);
Console.Clear();
Console.WriteLine($"{chosenRoomate.Firstname} has been assigned: {chosenChore.Name} - id: {assignedChore.Id}");
Console.WriteLine("Press any key to continue");
Console.ReadKey();
break;
case ("Exit"):
runProgram = false;
break;
}
}
}
