public List <Trainer> GetAll()
{
using (var db = new SqlConnection(_connectionString))
{
var trainers = db.Query <Trainer>("Select Id,Name,YearsOfExperience,Specialty From Trainer");
foreach (var trainer in trainers)
{
var chickenRepo = new ChickenRepository();
var chickensForTrainer = chickenRepo.GetChickensForTrainer(trainer.Id);
trainer.Coop.AddRange(chickensForTrainer);
}
return(trainers.AsList());
}
}
// PRE-DAPPER
//public Trainer Get(string name)
//{
// using (var connection = new SqlConnection(_connectionString))
// {
// connection.Open();
// var cmd = connection.CreateCommand();
// cmd.CommandText = @"select *
// from Trainer
// where Trainer.Name = @trainerName"; // never use string interpolation for SQL commands, for security reasons
// cmd.Parameters.AddWithValue("trainerName", name); // uses a parameterized query
// var reader = cmd.ExecuteReader();
// if (reader.Read())
// {
// return GetTrainerFromDataReader(reader);
// }
// }
// return null; // returns null if there is no trainer
//}
public List <Trainer> GetAll()
{
// this using statement is essentially used so that the dispose statements at
// the end disposes everything from this block
//using (var connection = new SqlConnection(_connectionString))
using (var db = new SqlConnection(_connectionString))
{
db.Open();
// db.Query for results
// db.Execute if you don't need results
// make sure your SQL results match your class, or the results will be ignored
var trainers = db.Query <Trainer>("Select Name,Id,YearsOfExperience,Specialty From Trainer");
/* Pre-Dapper block
*
* //var cmd = connection.CreateCommand();
* ////cmd.CommandText = "Select * From Trainer";
* //cmd.CommandText = @"Select *
* // From Trainer";
*
*/
//cmd.ExecuteNonQuery
// returns how many results were changed (used when we don't need to see the results data)
//cmd.ExecuteScalar
// returns value of first cell of first row (used when trying to get the value of a calculation)
//cmd.ExecuteReader
// returns the values (used when we care about the results of our query, so we can iterate through them)
//var dataReader = cmd.ExecuteReader();
//var trainers = new List<Trainer>();
//// we use a while because it reads row by row until there are no rows with data, since
//// .Read() returns a bool to determine if there is a record on that row or not
//while (dataReader.Read())
//{
// // (int) is explicit casting, if int is incorrect it will immediately cause an exception
// var id = (int)dataReader["Id"];
// // "as string" is implicit casting, if the type is incorrect it'll return a null value
// var name = dataReader["Name"] as string;
// // Convert, converts your type unless they types can't be converted, like Guids
// var yearsOfExperience = Convert.ToInt32(dataReader["YearsOfExperience"]);
// // parse
// Enum.TryParse<Specialty>(dataReader["Specialty"].ToString(), out var specialty);
// var trainer = new Trainer
// {
// Specialty = specialty,
// Id = id,
// Name = name,
// YearsOfExperience = yearsOfExperience
// };
// trainers.Add(trainer);
//}
//// i believe these aren't actually needed in this situation since we built this block of code within a using statement
//connection.Dispose(); // very important to dispose of
//dataReader.Dispose();
//cmd.Dispose();
//return trainers;
// called RESULT MULTI-MAPPING
foreach (var trainer in trainers)
{
var chickenRepository = new ChickenRepository();
var chickens = chickenRepository.GetChickensForTrainer(trainer.Id);
trainer.Coop.AddRange(chickens);
}
// could also use .AsList() which doesn't loop through everything if it's already a list
return(trainers.ToList());
}
}