/// <summary>
/// Gets articles
/// </summary>
/// <param name="startIndex">First article</param>
/// <param name="nbShow">Number article to show.</param>
/// <returns>Articles list from #startIndex to #startIndex + nbShow</returns>
public List<Article> GetArticles(int startIndex, int nbShow)
{
List<Article> lstArticles = new List<Article>();
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.pGetPublicArticles";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.AddParameterWithValue("nbstart_in", startIndex);
cmd.AddParameterWithValue("count_in", nbShow);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
Article article = new Article();
DatabaseHelper.FillModelFromReader<Article>(article, dr);
article.ShortContent = article.Content.GetEllipsis(
Math.Min(article.Content.Length - 1, 500),
false);
lstArticles.Add(article);
}
}
return lstArticles;
}
/// <summary>
/// Gets a specified article.
/// </summary>
/// <param name="articleId">Article's id</param>
/// <returns>Article object.</returns>
public Article GetArticle(Guid articleId)
{
Article article = new Article();
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.pGetArticle";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.AddParameterWithValue("articleid_in",
//NpgsqlTypes.NpgsqlDbType.Varchar,
articleId.ToString());
var dr = cmd.ExecuteReader();
if (dr.Read())
DatabaseHelper.FillModelFromReader<Article>(article, dr);
else
return article;
}
return article;
}
/// <summary>
/// Gets a single article
/// </summary>
/// <param name="articleId"></param>
/// <returns>Chosen article</returns>
public Article GetArticle(string articleTitle)
{
Article article = new Article();
bool exists = false;
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.pGetPublicArticle";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.AddParameterWithValue("articletitle_in", articleTitle);
var dr = cmd.ExecuteReader();
exists = dr.Read();
if (exists)
DatabaseHelper.FillModelFromReader<Article>(article, dr);
}
return article;
}
/// <summary>
/// Obtains all articles, even those not published (drafts)
/// </summary>
/// <param name="start">First article #</param>
/// <param name="count">Quantity to load.</param>
/// <returns>List of articles from #start to #(start+count)</returns>
public List<Article> GetArticles(int start, int count)
{
List<Article> lstArticles = new List<Article>();
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.pGetArticles";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.AddParameterWithValue("nbstart_in", start);
cmd.AddParameterWithValue("count_in", count);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
Article article = new Article();
DatabaseHelper.FillModelFromReader<Article>(article, dr);
lstArticles.Add(article);
}
}
return lstArticles;
}
/// <summary>
/// Saves the article in the database.
/// TODO: move into admin ?
/// </summary>
/// <param name="article">Article to save.</param>
public void SaveArticle(Article article)
{
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.pAddSetArticle";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.AddParameterWithValue("userid_in",
//NpgsqlTypes.NpgsqlDbType.Varchar,
article.AuthorId.ToString());
cmd.AddParameterWithValue("title_in",
//NpgsqlTypes.NpgsqlDbType.Varchar,
article.Title);
cmd.AddParameterWithValue("articleid_in",
//NpgsqlTypes.NpgsqlDbType.Varchar,
article.ArticleId.ToString());
cmd.AddParameterWithValue("lasteditiondate_in",
article.LastEditDate);
cmd.AddParameterWithValue("publisheddate_in",
article.PublishedDate);
cmd.AddParameterWithValue("seourl_in",
//NpgsqlTypes.NpgsqlDbType.Varchar,
article.SEOUrl);
cmd.AddParameterWithValue("content_in",
article.Content);
cmd.AddParameterWithValue("categoryid_in",
//NpgsqlTypes.NpgsqlDbType.Varchar,
article.CategoryId.ToString());
cmd.AddParameterWithValue("iscommentallowed_in", article.IsCommentAllowed);
cmd.AddParameterWithValue("isonline_in", article.PublishedDate < DateTime.MaxValue);
cmd.ExecuteNonQuery();
}
}