Exemple #1
0
        public CommentModel getRecentComments()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(String.Format("SELECT TOP 3 Post.Title, Comment.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText FROM Comment INNER JOIN Post ON Post.PostID = Comment.PostID INNER JOIN UserInfo ON UserInfo.UserID = Comment.UserID ORDER BY Comment.CommentDate DESC")))
                {
                    command.Connection = connection;
                    using (var reader = command.ExecuteReader())
                    {
                        CommentModel     commentModel = new CommentModel();
                        CommentItemModel commentItem  = null;

                        UserModel user = null;
                        while (reader.Read())
                        {
                            user        = new UserModel(Convert.ToInt32(reader["UserID"]), reader["UserName"].ToString(), reader["Email"].ToString(), Convert.ToInt32(reader["Age"]));
                            commentItem = new CommentItemModel(DateTime.Parse(reader["CommentDate"].ToString()), reader["CommentText"].ToString(), Convert.ToInt32(reader["PostID"]), user, reader["Title"].ToString());
                            commentModel.Comments.Add(commentItem);
                        }

                        return(commentModel);
                    }
                }
            }
        }
Exemple #2
0
        public ArticleItemModel GetLastPost()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(String.Format("SELECT TOP 1 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 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 Post.PostID, UserInfo.UserID, UserInfo.UserName, UserInfo.Email, UserInfo.Age, Comment.CommentDate, Comment.CommentText, Comment.CommentID 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"]);
                                    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);
                                        commentItem.CommentID = Convert.ToInt32(reader2["CommentID"]);
                                        commentModel.Comments.Add(commentItem);
                                    }


                                    model.Comments      = commentModel;
                                    model.Categories    = new string[2];
                                    model.Categories[0] = "Category2352";
                                    model.Categories[1] = "Category31231";
                                    return(model);
                                }
                            }
                        }
                    }
                }
            }
        }
Exemple #3
0
        public void addComment(CommentItemModel comment)
        {
            int UserID = addUser(comment.User);

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["mssql"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(String.Format("INSERT INTO Comment(UserID, PostID, CommentText, CommentDate) VALUES(@UserID, @PostID, @CommentText, @CommentDate)")))
                {
                    command.Connection = connection;
                    command.Parameters.Add(new SqlParameter("UserID", UserID));
                    command.Parameters.Add(new SqlParameter("PostID", comment.PostID));
                    command.Parameters.Add(new SqlParameter("CommentText", comment.Text));
                    command.Parameters.Add(new SqlParameter("CommentDate", comment.Date));
                    command.ExecuteNonQuery();
                }
            }
        }
Exemple #4
0
        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);
                            }
                        }
                    }
                }
            }
        }