Example #1
0
        internal static bool updatePost(int id, Post post)
        {
            string queryString = @"
                Update Posts
                    set 
                    MetaTitle = @MetaTitle,
                    MetaDescription = @MetaDescription,
                    MetaKeywords = @MetaKeywords,
                    ModifiedDate = @ModifiedDate,
                    ModifiedBy = @ModifiedBy,
                where postId= @id";

            string connectionString = ConnectionStringManager.getConnectionString();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataReader reader = null;
                try
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Parameters.AddWithValue("@MetaTitle", post.MetaTitle ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@MetaDescription", post.MetaDescription ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@MetaKeywords", post.MetaKeywords ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
                    command.Parameters.AddWithValue("@ModifiedBy", post.ModifiedBy ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@id", id);
                    connection.Open();
                    reader = command.ExecuteReader();
                    reader.Close();
                    return(true);
                }
                catch (SqlException ex)
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    return(false);
                }
            }
        }
Example #2
0
        internal static IEnumerable <object> getUserPost(int id)
        {
            string queryString      = @"select * from posts where postedBy = @postedBy order by postId desc";
            string connectionString = ConnectionStringManager.getConnectionString();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@postedBy", id);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    if (reader.HasRows)
                    {
                        List <UserPost> posts = new List <UserPost>();
                        while (reader.Read())
                        {
                            UserPost post = new UserPost();
                            post.postId          = int.Parse(reader["postId"].ToString());
                            post.postTitle       = reader["postTitle"].ToString();
                            post.postContent     = HttpUtility.HtmlDecode(reader["postContent"].ToString());
                            post.postCreated     = DateTime.Parse(reader["postCreated"].ToString());
                            post.postCategory    = int.Parse(reader["postCategory"].ToString());
                            post.postApproved    = bool.Parse(reader["postApproved"].ToString());
                            post.postedBy        = int.Parse(reader["postedBy"].ToString());
                            post.MetaTitle       = reader["MetaTitle"].ToString();
                            post.MetaDescription = reader["MetaDescription"].ToString();
                            if (
                                reader["ModifiedDate"].ToString() != null &&
                                reader["ModifiedDate"].ToString() != ""
                                )
                            {
                                post.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString());
                            }

                            if (
                                reader["ModifiedBy"].ToString() != null &&
                                reader["ModifiedBy"].ToString() != ""
                                )
                            {
                                post.ModifiedBy = int.Parse(reader["ModifiedBy"].ToString());
                            }
                            post.Tags          = reader["Tags"].ToString();
                            post.MetaKeywords  = reader["MetaKeywords"].ToString();
                            post.postSource    = reader["postSource"].ToString();
                            post.OwnerOfSource = reader["OwnerOfSource"].ToString();
                            post.User          = UserManager.fetchUser(int.Parse(reader["postedBy"].ToString()));
                            posts.Add(post);
                        }
                        return(posts);
                    }
                    return(null);
                }

                catch (SqlException exception)
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    return(null);
                }

                finally
                {
                    reader.Close();
                }
            }
        }
Example #3
0
        internal static int createPost(Post post)
        {
            string queryString      = @"           
                insert into Posts(
	                postTitle, 
	                postContent, 
	                postCreated, 
	                postCategory, 
	                postedBy, 
	                Tags,
	                postSource, 
	                OwnerOfSource
                ) 
                values(
	                @postTitle, 
	                @postContent, 
	                SYSDATETIME(), 
	                @postCategory, 
	                @postedBy,
	                @tags,
                    @postSource,
                    @ownerOfSource
                );
                SELECT SCOPE_IDENTITY();
            ";
            string connectionString = ConnectionStringManager.getConnectionString();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataReader reader = null;
                try
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Parameters.AddWithValue("@postTitle", post.postTitle);
                    command.Parameters.AddWithValue("@postContent", post.postContent);
                    command.Parameters.AddWithValue("@postCategory", post.postCategory);
                    command.Parameters.AddWithValue("@postedBy", post.postedBy);
                    command.Parameters.AddWithValue("@tags", post.Tags);
                    command.Parameters.AddWithValue("@postSource", post.postSource);
                    command.Parameters.AddWithValue("@ownerOfSource", post.OwnerOfSource);
                    connection.Open();
                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        return(int.Parse(reader[0].ToString()));
                    }
                    reader.Close();
                    return(-1);
                }

                catch (SqlException exception)
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    return(-1);
                }
                finally
                {
                    reader.Close();
                }
            }
        }