public void UpdateDog(Dog dog)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE Dog
SET
[Name] = @name,
OwnerId = @ownerId,
Breed = @breed,
Notes = @notes,
ImageUrl = @imageUrl
WHERE Id = @Id";
cmd.Parameters.AddWithValue("@name", dog.Name);
cmd.Parameters.AddWithValue("@ownerId", dog.OwnerId);
cmd.Parameters.AddWithValue("@breed", dog.Breed);
cmd.Parameters.AddWithValue("@Id", dog.Id);
cmd.Parameters.AddWithValue("@notes", ReaderUtlis.GetNullableParameter(dog.Notes));
cmd.Parameters.AddWithValue("@imageUrl", ReaderUtlis.GetNullableParameter(dog.ImageUrl));
cmd.ExecuteNonQuery();
}
}
}
public List <Dog> GetDogByOwnerId(int ownerId)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT Id, Name, OwnerId, Breed, Notes, ImageUrl
FROM Dog
WHERE OwnerId = @Id";
cmd.Parameters.AddWithValue("@Id", ownerId);
SqlDataReader reader = cmd.ExecuteReader();
List <Dog> dogs = new List <Dog>();
while (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 = ReaderUtlis.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtlis.GetNullableString(reader, "ImageUrl")
};
dogs.Add(dog);
}
reader.Close();
return(dogs);
}
}
}
public Walker GetWalkerByEmail(string email)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT w.Id, w.Name, ImageUrl, Email, NeighborhoodId, n.Name as NeighborhoodName
FROM Walker w
JOIN Neighborhood n ON n.Id = NeighborhoodId
WHERE w.Email = @email";
cmd.Parameters.AddWithValue("@email", email);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Walker walker = new Walker
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Email = reader.GetString(reader.GetOrdinal("Email")),
ImageUrl = ReaderUtlis.GetNullableString(reader, "ImageUrl"),
NeighborhoodId = reader.GetInt32(reader.GetOrdinal("NeighborhoodId"))
};
walker.Neighborhood = new Neighborhood()
{
Name = reader.GetString(reader.GetOrdinal("NeighborhoodName"))
};
reader.Close();
return(walker);
}
else
{
reader.Close();
return(null);
}
}
}
}
public Dog GetDogById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT d.Id, d.Name, OwnerId, Breed, Notes, ImageUrl, o.Name AS OwnerName
FROM Dog d
JOIN Owner o ON o.Id = OwnerId
WHERE d.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 = ReaderUtlis.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtlis.GetNullableString(reader, "ImageUrl"),
Owner = new Owner()
{
Name = reader.GetString(reader.GetOrdinal("OwnerName"))
}
};
reader.Close();
return(dog);
}
else
{
reader.Close();
return(null);
}
}
}
}
public List <Walker> GetAllWalkers()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT w.Id, w.Name, ImageUrl, Email, NeighborhoodId, n.Name as NeighborhoodName
FROM Walker w
JOIN Neighborhood n ON n.Id = NeighborhoodId
";
SqlDataReader reader = cmd.ExecuteReader();
List <Walker> walkers = new List <Walker>();
while (reader.Read())
{
Walker walker = new Walker
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Email = reader.GetString(reader.GetOrdinal("Email")),
ImageUrl = ReaderUtlis.GetNullableString(reader, "ImageUrl"),
NeighborhoodId = reader.GetInt32(reader.GetOrdinal("NeighborhoodId"))
};
walker.Neighborhood = new Neighborhood()
{
Name = reader.GetString(reader.GetOrdinal("NeighborhoodName"))
};
walkers.Add(walker);
}
reader.Close();
return(walkers);
}
}
}
public void AddWalker(Walker walker)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Walker (Name, NeighborhoodId, ImageUrl)
OUTPUT INSERTED.ID
VALUES (@name, @neighborhoodId, @imageUrl)";
cmd.Parameters.AddWithValue("@name", walker.Name);
cmd.Parameters.AddWithValue("@neighborhoodId", walker.NeighborhoodId);
cmd.Parameters.AddWithValue("@imageUrl", ReaderUtlis.GetNullableParameter(walker.ImageUrl));
int id = (int)cmd.ExecuteScalar();
walker.Id = id;
}
}
}
public List <Dog> GetAllDogs()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT d.Id, d.Name, OwnerId, Breed, Notes, d.ImageUrl, o.Name as OwnerName
FROM Dog d
JOIN Owner o ON o.Id = OwnerId";
SqlDataReader reader = cmd.ExecuteReader();
List <Dog> dogs = new List <Dog>();
while (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 = ReaderUtlis.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtlis.GetNullableString(reader, "ImageUrl"),
Owner = new Owner
{
Id = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Name = reader.GetString(reader.GetOrdinal("OwnerName")),
}
};
dogs.Add(dog);
}
reader.Close();
return(dogs);
}
}
}
public void AddDog(Dog dog)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Dog ([Name], OwnerId, Breed, Notes, ImageUrl)
OUTPUT INSERTED.ID
VALUES(@name, @ownerId, @breed, @notes, @imageUrl)";
cmd.Parameters.AddWithValue("@name", dog.Name);
cmd.Parameters.AddWithValue("@ownerId", dog.OwnerId);
cmd.Parameters.AddWithValue("@breed", dog.Breed);
cmd.Parameters.AddWithValue("@notes", ReaderUtlis.GetNullableParameter(dog.Notes));
cmd.Parameters.AddWithValue("@imageUrl", ReaderUtlis.GetNullableParameter(dog.ImageUrl));
int id = (int)cmd.ExecuteScalar();
dog.Id = id;
}
}
}
public void UpdateWalker(Walker walker)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE Walker
SET
Name = @name,
NeighborhoodId = @neighborhoodId,
ImageUrl = @imageUrl
WHERE Id = @id";
cmd.Parameters.AddWithValue("@name", walker.Name);
cmd.Parameters.AddWithValue("@neighborhoodId", walker.NeighborhoodId);
cmd.Parameters.AddWithValue("@imageUrl", ReaderUtlis.GetNullableParameter(walker.ImageUrl));
cmd.Parameters.AddWithValue("@id", walker.Id);
cmd.ExecuteNonQuery();
}
}
}