示例#1
0
        /// <summary>
        /// Gets all the entries from the database
        /// </summary>
        /// <returns> A list of objects of type Post</returns>
        public List<Post> GetAll()
        {
            List<Post> allPosts = new List<Post>();

           
                using (var conn = new SQLiteConnection(Connectionstring))
                {
                try
                {
                    conn.Open();

                    string query = $"SELECT * FROM {TableName} ORDER BY Date DESC";

                    // Read the rows in the table, 
                    using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                    {
                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Post post = new Post
                                {
                                    ID = Convert.ToInt32(reader["Id"]),
                                    Date = reader["Date"].ToString(),
                                    Title = reader["Title"].ToString(),
                                    Content = reader["Content"].ToString()
                                };

                                allPosts.Add(post);
                            }
                        }
                    }

                    conn.Close();
                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
            
            
            return allPosts;
        }
示例#2
0
        /// <summary>
        /// Edit an existing entry
        /// </summary>
        /// <param name="post"></param>
        /// <returns>a post object</returns>
        public async Task<Post> Update(Post post)
        {
            if (post == null)
            {
                return null;
            }

            bool result = false;

            try
            {

            using (var conn = new SQLiteConnection(Connectionstring))
            {
                conn.Open();
                string query = $"UPDATE {TableName} SET Title = '{post.Title}', Content = '{post.Content}' WHERE Id = {post.ID}";
                using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                {
                    await cmd.ExecuteNonQueryAsync();
                    result = true;
                }

                conn.Close();
            }
            }

            //useless catch, I need to handle it properly, either log or display a meaningfull message to the UI
            catch (SQLiteException ex)
            {
                Console.WriteLine(ex.ToString());
            }

            return result ? post : null;
        }
示例#3
0
        /// <summary>
        /// Get a post from an ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns>A post of id n </returns>
        public Post Get(int id)
        {
            Post post = new Post();

            using (var conn = new SQLiteConnection(Connectionstring))
            {
                try
                {
                    conn.Open();

                    //string query = $"select {id} from Posts";
                    string query = $"select * from {TableName} where Id = {id}";

                    //Todo There has to be a simpler way. Because I'm just fetching a single row
                    using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                    {
                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                post.ID = Convert.ToInt32(reader["Id"]);
                                post.Date = reader["Date"].ToString();
                                post.Title = reader["Title"].ToString();
                                post.Content = reader["Content"].ToString();
                            }
                        }
                    }
                    conn.Close();

                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
            return post;
        }
示例#4
0
        /// <summary>
        /// Create a new entry in the database.
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public Post Create(Post post)
        {

            if (post == null)
            {
                return null;
            }

            using (var conn = new SQLiteConnection(Connectionstring))
            {
                conn.Open();
                try
                {
                    string query = $"INSERT INTO {TableName} (Title, Content) VALUES ('{post.Title}', '{post.Content}')";
                    using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }

            return post.ID > 0 ? post : null;
        }
示例#5
0
 public dynamic createPostForm(dynamic parameters)
 {
     var post = new Post();
     return View["Views/Pages/Write", post];
 }