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); } } }
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); } } }
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); } } }