static void Main(string[] args)
{
Console.Clear();
WalkerRepository walkers = new WalkerRepository();
System.Collections.Generic.List <Walker> allWalkers = walkers.GetAllWalkers();
Console.WriteLine("Walkers:");
foreach (Walker walker in allWalkers)
{
Console.WriteLine($"{walker.WalkerName}");
}
Console.WriteLine();
Walker foundWalker = walkers.GetWalkerByNeighborhoodId(3);
Console.WriteLine("All walkers on the east side:");
Console.WriteLine($"{foundWalker.WalkerName}");
Console.WriteLine();
Walker newWalker = new Walker
{
WalkerName = "Holden Parker",
NeighborhoodId = 1
};
walkers.AddWalker(newWalker);
Console.WriteLine($"Added new walker: {newWalker.WalkerName}");
Console.WriteLine();
OwnerRepository owners = new OwnerRepository();
System.Collections.Generic.List <Owner> allOwners = owners.GetAllOwnersWithNeighborhood();
Console.WriteLine("All owners and their neighborhood:");
foreach (Owner owner in allOwners)
{
Console.WriteLine($"{owner.DogOwnerName}: {owner.Neighborhood.NeighborhoodName}");
}
Console.WriteLine();
Owner newOwner = new Owner
{
DogOwnerName = "Wily Metcalf",
DogOwnerAddress = "6256 Premier Dr",
NeighborhoodId = 2,
Phone = "615-555-5555"
};
owners.AddOwner(newOwner);
Console.WriteLine($"Added new owner: {newOwner.DogOwnerName}");
Console.WriteLine();
Owner updatedOwner = new Owner
{
Id = newOwner.Id,
DogOwnerName = newOwner.DogOwnerName,
DogOwnerAddress = newOwner.DogOwnerAddress,
NeighborhoodId = 3,
Phone = newOwner.Phone
};
owners.UpdateOwner(updatedOwner.Id, updatedOwner);
Console.WriteLine($"Updated {newOwner.DogOwnerName}'s NeighborhoodId from {newOwner.NeighborhoodId} to {updatedOwner.NeighborhoodId}");
Console.WriteLine();
Walker updatedWalker = new Walker
{
Id = newWalker.Id,
WalkerName = newWalker.WalkerName,
NeighborhoodId = 2
};
walkers.UpdateWalker(updatedWalker.Id, updatedWalker);
Console.WriteLine($"Updated {updatedWalker.WalkerName}'s NeighborhoodId from {newWalker.NeighborhoodId} to {updatedWalker.NeighborhoodId}");
}
/// <summary>
/// Returns a list of all departments in the database
/// </summary>
public List <Walker> GetAllWalkers()
{
// 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, WalkerName, NeighborhoodId FROM Walker";
// 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 departments we retrieve from the database.
List <Walker> walkers = new List <Walker>();
// 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 "DeptName" 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 walkerNameColumnPosition = reader.GetOrdinal("WalkerName");
string walkerNameColumnValue = reader.GetString(walkerNameColumnPosition);
int neighborhoodIdColumnPosition = reader.GetOrdinal("NeighborhoodId");
int neighborhoodIdColumnValue = reader.GetInt32(neighborhoodIdColumnPosition);
// Now let's create a new department object using the data from the database.
Walker walker = new Walker
{
Id = idValue,
WalkerName = walkerNameColumnValue,
NeighborhoodId = neighborhoodIdColumnValue
};
// ...and add that department object to our list.
walkers.Add(walker);
}
// We should Close() the reader. Unfortunately, a "using" block won't work here.
reader.Close();
// Return the list of departments who whomever called this method.
return(walkers);
}
}
}