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