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