public List <Dog> GetAllDogs()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT dog.Id, dog.[Name], dog.OwnerId, dog.Breed, dog.Notes, dog.ImageUrl, Owner.[Name] AS Owner
FROM Dog
LEFT JOIN Owner ON dog.OwnerId = Owner.Id";
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")),
Breed = reader.GetString(reader.GetOrdinal("Breed")),
Notes = ReaderUtils.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl"),
OwnerId = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Owner = new Owner()
{
Id = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Name = reader.GetString(reader.GetOrdinal("Owner"))
}
};
dogs.Add(dog);
}
reader.Close();
return(dogs);
}
}
}
//gets therapist info to be used to verify therapist for client registration
public TherapistConfirmationInfo GetByCounselorCode(string cCode)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT u.Id, u.FirstName, u.LastName, u.NickName, u.AvatarId, u.FavColor,
t.Verified, t.Company,
a.Image, a.Name
FROM [User] u
JOIN Therapist t ON u.Id = t.UserId
JOIN Avatar a ON a.Id = u.AvatarId
WHERE t.Code = @code
";
cmd.Parameters.AddWithValue("@code", cCode);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
TherapistConfirmationInfo therapistConfirmationInfo = new TherapistConfirmationInfo
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
NickName = reader.GetString(reader.GetOrdinal("NickName")),
AvatarId = reader.GetInt32(reader.GetOrdinal("AvatarId")),
FavColor = ReaderUtils.GetNullableString(reader, "FavColor"),
Verified = reader.GetBoolean(reader.GetOrdinal("Verified")),
Company = reader.GetString(reader.GetOrdinal("Company")),
Code = cCode
};
Avatar avatar = new Avatar
{
Id = therapistConfirmationInfo.AvatarId,
Image = reader.GetString(reader.GetOrdinal("Image")),
Name = reader.GetString(reader.GetOrdinal("Name"))
};
therapistConfirmationInfo.Avatar = avatar;
reader.Close();
return(therapistConfirmationInfo);
}
else
{
reader.Close();
return(null);
}
}
}
}
public Walker GetWalkerById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Walker.Id AS WalkerId, Walker.[Name] AS WalkerName, Email, ImageUrl, Walker.NeighborhoodId, Neighborhood.Id AS NeighborId , Neighborhood.[Name] AS Neighborhood
FROM Walker
LEFT JOIN Neighborhood ON Walker.NeighborhoodId = Neighborhood.Id
WHERE Walker.Id = @id
";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Walker walker = new Walker
{
Id = reader.GetInt32(reader.GetOrdinal("WalkerId")),
Name = reader.GetString(reader.GetOrdinal("WalkerName")),
Email = reader.GetString(reader.GetOrdinal("Email")),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl"),
NeighborhoodId = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Neighborhood = new Neighborhood()
{
Id = reader.GetInt32(reader.GetOrdinal("NeighborId")),
Name = reader.GetString(reader.GetOrdinal("Neighborhood"))
}
};
reader.Close();
return(walker);
}
else
{
reader.Close();
return(null);
}
}
}
}
public List <Dog> GetAllDogs()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT d.Id, d.[Name], d.Breed, d.Notes, d.ImageUrl, d.OwnerId, o.[Name] as Owner
FROM Dog d
JOIN Owner o ON d.OwnerId = o.Id
";
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")),
Breed = reader.GetString(reader.GetOrdinal("Breed")),
Notes = ReaderUtils.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl"),
OwnerId = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Owner = new Owner()
{
Name = reader.GetString(reader.GetOrdinal("Owner"))
}
};
// method to check if something is actually in notes
//ReaderUtils utils = new ReaderUtils();
//dog.Notes = Utils.GetNullableString(reader, "Notes");
//dog.ImageUrl = Utils.GetNullableString(reader, "ImageUrl");
dogs.Add(dog);
}
reader.Close();
return(dogs);
}
}
}
public Dog GetDogById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT d.Id, d.[Name], d.Breed, d.Notes, d.ImageUrl, d.OwnerId, o.[Name] as Owner
FROM Dog d
JOIN Owner o ON d.OwnerId = o.Id
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")),
Breed = reader.GetString(reader.GetOrdinal("Breed")),
Notes = ReaderUtils.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl"),
OwnerId = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Owner = new Owner()
{
Name = reader.GetString(reader.GetOrdinal("Owner"))
}
};
reader.Close();
return(dog);
}
else
{
reader.Close();
return(null);
}
}
}
}
//returns a list of clients' posts for a therapist
public List <Post> GetPostsByTherapistId(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT p.Id, p.UserId, p.CreateDate, p.MoodId,
p.Content, p.EditTime, p.Flagged, p.TherapistId,
p.ViewTime, p.Comment, p.Deleted
FROM Post p
JOIN UserRelationship ur ON p.UserId = ur.UserId
WHERE ur.TherapistId = id
";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
List <Post> posts = new List <Post>();
while (reader.Read())
{
Post post = new Post
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
CreateDate = reader.GetDateTime(reader.GetOrdinal("CreateDate")),
MoodId = reader.GetInt32(reader.GetOrdinal("MoodId")),
Content = ReaderUtils.GetNullableString(reader, "Content"),
EditTime = ReaderUtils.GetNullableDateTime(reader, "EditTime"),
Flagged = reader.GetBoolean(reader.GetOrdinal("Flagged")),
TherapistId = ReaderUtils.GetNullableInt(reader, "TherapistId"),
ViewTime = ReaderUtils.GetNullableDateTime(reader, "ViewTime"),
Comment = ReaderUtils.GetNullableString(reader, "Ccomment"),
Deleted = reader.GetBoolean(reader.GetOrdinal("Deleted"))
};
posts.Add(post);
}
reader.Close();
return(posts);
}
}
}
public List <Dog> GetDogsByOwnerId(int ownerId)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT Dog.Id, Dog.[Name], Breed, Notes, ImageUrl, Dog.OwnerId, Owner.[Name] AS Owner
FROM Dog
LEFT JOIN Owner ON Dog.OwnerId = Owner.Id
WHERE OwnerId = @ownerId
";
cmd.Parameters.AddWithValue("@ownerId", 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")),
Breed = reader.GetString(reader.GetOrdinal("Breed")),
OwnerId = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Notes = ReaderUtils.GetNullableString(reader, "notes"),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl"),
Owner = new Owner()
{
Id = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Name = reader.GetString(reader.GetOrdinal("Owner"))
}
};
dogs.Add(dog);
}
reader.Close();
return(dogs);
}
}
}
public List <Walker> GetAllWalkers()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT walker.Id, walker.[Name], Email, ImageUrl, walker.NeighborhoodId, Neighborhood.[Name] as Neighborhood
FROM Walker
JOIN Neighborhood ON walker.NeighborhoodId = Neighborhood.Id
";
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 = ReaderUtils.GetNullableString(reader, "ImageUrl"),
NeighborhoodId = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Neighborhood = new Neighborhood()
{
Id = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Name = reader.GetString(reader.GetOrdinal("Neighborhood"))
}
};
walkers.Add(walker);
}
reader.Close();
return(walkers);
}
}
}
public List <Comment> GetCommentByPostId(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT c.Id, c.PostId, c.UserProfileId, c.Subject, c.Content, c.CreateDateTime,
p.Id, p.Title, p.Content, p.ImageLocation, p.CreateDateTime, p.PublishDateTime,
p.IsApproved, p.CategoryId, p.UserProfileId,
u.Id, u.DisplayName, u.FirstName, u.LastName, u.Email,
u.CreateDateTime, u.ImageLocation, u.UserTypeId,
ut.Id, ut.Name
FROM Comment c
LEFT JOIN Post p ON p.Id = c.PostId
LEFT JOIN UserProfile u ON u.Id = p.UserProfileId
LEFT JOIN UserType ut ON ut.Id = u.UserTypeId
WHERE c.PostId = @id
";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
List <Comment> comments = new List <Comment>();
while (reader.Read())
{
Comment comment = new Comment
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
PostId = reader.GetInt32(reader.GetOrdinal("PostId")),
UserProfileId = reader.GetInt32(reader.GetOrdinal("UserProfileId")),
Subject = reader.GetString(reader.GetOrdinal("Subject")),
Content = reader.GetString(reader.GetOrdinal("Content")),
CreateDataTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime")),
Post = new Post()
{
Id = reader.GetInt32(reader.GetOrdinal("PostId")),
Title = reader.GetString(reader.GetOrdinal("Title")),
Content = reader.GetString(reader.GetOrdinal("Content")),
ImageLocation = ReaderUtils.GetNullableString(reader, "ImageLocation"),
CreateDateTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime")),
PublishDateTime = ReaderUtils.GetNullableDateTime(reader, "PublishDateTime"),
IsApproved = reader.GetBoolean(reader.GetOrdinal("IsApproved")),
CategoryId = reader.GetInt32(reader.GetOrdinal("CategoryId")),
},
UserProfile = new UserProfile()
{
Id = reader.GetInt32(reader.GetOrdinal("UserProfileId")),
DisplayName = reader.GetString(reader.GetOrdinal("DisplayName")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
Email = reader.GetString(reader.GetOrdinal("Email")),
CreateDateTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime")),
ImageLocation = ReaderUtils.GetNullableString(reader, "ImageLocation"),
UserTypeId = reader.GetInt32(reader.GetOrdinal("UserTypeId")),
UserType = new UserType()
{
Id = reader.GetInt32(reader.GetOrdinal("UserTypeId")),
Name = reader.GetString(reader.GetOrdinal("Name"))
}
}
};
comments.Add(comment);
}
reader.Close();
return(comments);
}
}
}
public Dog GetDogById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT d.Id,
d.Name AS DogName,
OwnerId,
Breed,
Notes,
ImageUrl,
Email,
o.Name AS OwnerName,
Address,
NeighborhoodId,
Phone,
n.Name AS NeighborhoodName
FROM Dog d
LEFT JOIN Owner o ON o.Id = d.OwnerId
LEFT JOIN Neighborhood n ON n.Id = o.NeighborhoodId
WHERE d.Id = @id";
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
Dog dog = null;
if (reader.Read())
{
dog = new Dog()
{
Id = id,
Name = reader.GetString(reader.GetOrdinal("DogName")),
OwnerId = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Owner = new Owner()
{
Id = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Email = reader.GetString(reader.GetOrdinal("Email")),
Name = reader.GetString(reader.GetOrdinal("OwnerName")),
Address = reader.GetString(reader.GetOrdinal("Address")),
NeighborhoodId = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Neighborhood = new Neighborhood()
{
Id = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Name = reader.GetString(reader.GetOrdinal("NeighborhoodName"))
},
Phone = reader.GetString(reader.GetOrdinal("Phone"))
},
Breed = reader.GetString(reader.GetOrdinal("Breed")),
Notes = ReaderUtils.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl")
};
}
reader.Close();
return(dog);
}
}
}
public List <Dog> GetAllDogs()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT d.Id,
d.Name AS DogName,
OwnerId,
Breed,
Notes,
ImageUrl,
Email,
o.Name AS OwnerName,
Address,
NeighborhoodId,
Phone,
n.Name AS NeighborhoodName
FROM Dog d
LEFT JOIN Owner o ON o.Id = d.OwnerId
LEFT JOIN Neighborhood n ON n.Id = o.NeighborhoodId";
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("DogName")),
OwnerId = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Owner = new Owner()
{
Id = reader.GetInt32(reader.GetOrdinal("OwnerId")),
Email = reader.GetString(reader.GetOrdinal("Email")),
Name = reader.GetString(reader.GetOrdinal("OwnerName")),
Address = reader.GetString(reader.GetOrdinal("Address")),
NeighborhoodId = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Neighborhood = new Neighborhood()
{
Id = reader.GetInt32(reader.GetOrdinal("NeighborhoodId")),
Name = reader.GetString(reader.GetOrdinal("NeighborhoodName"))
},
Phone = reader.GetString(reader.GetOrdinal("Phone"))
},
Breed = reader.GetString(reader.GetOrdinal("Breed")),
Notes = ReaderUtils.GetNullableString(reader, "Notes"),
ImageUrl = ReaderUtils.GetNullableString(reader, "ImageUrl")
};
dogs.Add(dog);
}
reader.Close();
return(dogs);
}
}
}
