예제 #1
0
        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());
            }
        }
예제 #2
0
        // 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());
            }
        }