public ArticleItemModel GetPostById(int ID) { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString)) { connection.Open(); using (var command = new SqlCommand(String.Format("SELECT Post.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Post.Date, Post.Text, Post.Title FROM Post INNER JOIN UserInfo ON UserInfo.UserID = Post.UserID WHERE @ID = Post.PostID"))) { command.Connection = connection; command.Parameters.Add(new SqlParameter("ID", ID)); using (var reader = command.ExecuteReader()) { using (var connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString)) { connection2.Open(); using (var command2 = new SqlCommand(String.Format("SELECT Post.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText FROM Post INNER JOIN Comment ON Post.PostID = Comment.PostID INNER JOIN UserInfo ON UserInfo.UserID = Comment.UserID WHERE Post.PostID = @id ORDER BY Comment.CommentDate DESC"))) { command2.Connection = connection2; UserModel user = null; ArticleItemModel model = new ArticleItemModel(); if (reader.Read()) { int id = Convert.ToInt32(reader["PostID"]); if (id == 0) { return(null); } model.PostID = id; command2.Parameters.Add(new SqlParameter("id", id)); user = new UserModel(Convert.ToInt32(reader["UserID"]), reader["UserName"].ToString(), reader["Email"].ToString(), Convert.ToInt32(reader["Age"])); model.User = user; model.Date = DateTime.Parse(reader["Date"].ToString()); model.Text = reader["Text"].ToString(); model.Title = reader["Title"].ToString(); model.NewComment = new CommentItemModel(); model.NewComment.PostID = id; } using (var reader2 = command2.ExecuteReader()) { CommentModel commentModel = new CommentModel(); CommentItemModel commentItem = null; while (reader2.Read()) { user = new UserModel(Convert.ToInt32(reader["UserID"]), reader2["UserName"].ToString(), reader2["Email"].ToString(), Convert.ToInt32(reader2["Age"])); commentItem = new CommentItemModel(DateTime.Parse(reader2["CommentDate"].ToString()), reader2["CommentText"].ToString(), Convert.ToInt32(reader2["PostID"]), user); commentModel.Comments.Add(commentItem); } model.Comments = commentModel; model.Categories = new string[2]; model.Categories[0] = "Category2352"; model.Categories[1] = "Category31231"; return(model); } } } } } } }
public ArticleItemModel(int PostID, UserModel User, String Text, String Title, CommentModel Comments, String[] Categories) { this.PostID = PostID; Date = DateTime.Now; this.User = User; this.Text = Text; this.Title = Title; this.Comments = Comments; this.Categories = Categories; this.NewComment = new CommentItemModel(); this.NewComment.PostID = PostID; }
public ArticleModel GetPosts() { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString)) { connection.Open(); ArticleModel articleModel = new ArticleModel(); using (var command = new SqlCommand(String.Format("SELECT UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Post.PostID, Post.Date, Post.Text, Post.Title FROM Post INNER JOIN UserInfo ON UserInfo.UserID = Post.UserID ORDER BY Post.Date DESC"))) { command.Connection = connection; using (var reader = command.ExecuteReader()) { using (var connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString)) { connection2.Open(); using (var command2 = new SqlCommand(String.Format("SELECT Comment.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText FROM Post INNER JOIN Comment ON Post.PostID = Comment.PostID INNER JOIN UserInfo ON UserInfo.UserID = Comment.UserID WHERE @PostID = Comment.PostID ORDER BY Comment.CommentDate"))) { command2.Connection = connection2; while (reader.Read()) { UserModel user = null; ArticleItemModel model = new ArticleItemModel(); user = new UserModel(Convert.ToInt32(reader["UserID"]), reader["UserName"].ToString(), reader["Email"].ToString(), Convert.ToInt32(reader["Age"])); model.Date = DateTime.Parse(reader["Date"].ToString()); model.Text = reader["Text"].ToString(); model.Title = reader["Title"].ToString(); model.User = user; int postID = Convert.ToInt32(reader["PostID"]); if (command2.Parameters.Contains("PostID")) { command2.Parameters.RemoveAt("PostID"); } command2.Parameters.Add(new SqlParameter("postID", postID)); CommentModel commentModel = new CommentModel(); using (var reader2 = command2.ExecuteReader()) { CommentItemModel commentItem = null; while (reader2.Read()) { int commentPostID = Convert.ToInt32(reader2["PostID"]); if (postID == commentPostID) { user = new UserModel(Convert.ToInt32(reader2["UserID"]), reader2["UserName"].ToString(), reader2["Email"].ToString(), Convert.ToInt32(reader2["Age"])); commentItem = new CommentItemModel(DateTime.Parse(reader2["CommentDate"].ToString()), reader2["CommentText"].ToString(), postID, user); commentModel.Comments.Add(commentItem); } } model.Categories = new string[2]; model.Categories[0] = "Category2352"; model.Categories[1] = "Category31231"; model.Comments = commentModel; } articleModel.Articles.Add(model); } return(articleModel); } } } } } }