Beispiel #1
0
        public List <Dog> GetAllDogs()
        {
            using (SqlConnection conn = Connection)
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT Id, [Name], OwnerId, Breed, Notes, ImageUrl FROM Dog";

                    SqlDataReader reader = cmd.ExecuteReader();

                    List <Dog> dogs = new List <Dog>();

                    while (reader.Read())
                    {
                        Dog d = new Dog()
                        {
                            Id       = reader.GetInt32(reader.GetOrdinal("Id")),
                            Name     = reader.GetString(reader.GetOrdinal("Name")),
                            OwnerId  = reader.GetInt32(reader.GetOrdinal("OwnerId")),
                            Breed    = reader.GetString(reader.GetOrdinal("Breed")),
                            Notes    = SQLHelpers.GetNullableString(reader, "Notes"),
                            ImageUrl = SQLHelpers.GetNullableString(reader, "ImageUrl")
                        };

                        dogs.Add(d);
                    }
                    reader.Close();
                    return(dogs);
                }
            }
        }
Beispiel #2
0
        public List <Dog> GetDogsByOwnerId(int ownerId)
        {
            using (SqlConnection conn = Connection)
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                        SELECT Id, [Name], Breed, Notes, ImageUrl, OwnerId 
                        FROM Dog
                        WHERE OwnerId = @ownerId
                    ";

                    cmd.Parameters.AddWithValue("@ownerId", ownerId);

                    SqlDataReader reader = cmd.ExecuteReader();

                    List <Dog> dogs = new List <Dog>();

                    while (reader.Read())
                    {
                        Dog d = new Dog()
                        {
                            Id       = reader.GetInt32(reader.GetOrdinal("Id")),
                            Name     = reader.GetString(reader.GetOrdinal("Name")),
                            OwnerId  = reader.GetInt32(reader.GetOrdinal("OwnerId")),
                            Breed    = reader.GetString(reader.GetOrdinal("Breed")),
                            Notes    = SQLHelpers.GetNullableString(reader, "Notes"),
                            ImageUrl = SQLHelpers.GetNullableString(reader, "ImageUrl")
                        };


                        // We could also null check optional columns like this

                        //if (reader.IsDBNull(reader.GetOrdinal("Notes")) == false)
                        //{
                        //    d.Notes = reader.GetString(reader.GetOrdinal("Notes"));
                        //}

                        //if (reader.IsDBNull(reader.GetOrdinal("ImageUrl")) == false)
                        //{
                        //    d.ImageUrl = reader.GetString(reader.GetOrdinal("ImageUrl"));
                        //}

                        dogs.Add(d);
                    }

                    reader.Close();

                    return(dogs);
                }
            }
        }
Beispiel #3
0
        public Dog GetDogById(int id)
        {
            using (SqlConnection conn = Connection)
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                        SELECT Id, [Name], OwnerId, Breed, Notes, ImageUrl 
                        FROM Dog
                        WHERE Id = @id";

                    cmd.Parameters.AddWithValue("@id", id);

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        Dog dog = new Dog()
                        {
                            Id       = reader.GetInt32(reader.GetOrdinal("Id")),
                            Name     = reader.GetString(reader.GetOrdinal("Name")),
                            OwnerId  = reader.GetInt32(reader.GetOrdinal("OwnerId")),
                            Breed    = reader.GetString(reader.GetOrdinal("Breed")),
                            Notes    = SQLHelpers.GetNullableString(reader, "Notes"),
                            ImageUrl = SQLHelpers.GetNullableString(reader, "ImageUrl")
                        };

                        reader.Close();
                        return(dog);
                    }

                    reader.Close();
                    return(null);
                }
            }
        }