public int AddBlogSummary(BlogSummary blogSummary)
{
string sproc = "ins_add_blog_header";
try
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sproc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = blogSummary.Title;
cmd.Parameters.Add("@author", SqlDbType.NVarChar).Value = blogSummary.Author; //optional
cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = blogSummary.Image;
cmd.Parameters.Add("@summary", SqlDbType.NVarChar).Value = blogSummary.Summary;
cmd.Parameters.Add("@link", SqlDbType.NVarChar).Value = blogSummary.Link;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
return(1);
}
catch
{
throw;
}
}
//public async Task<List<BlogSummary>> GetPaginatedResult(int currentPage, int pageSize = 3) {
// var data = await GetData();
// return data.OrderBy(data => data.BlogId).Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
//}
//public async Task<int> GetCount() {
// var data = await GetData();
// return data.Count;
//}
//private async Task<List<BlogSummary>> GetData() {
// //var json = await File.ReadAllTextAsync(Path.Combine(_webHostEnvironment.ContentRootPath, "Data", "paging.json"));
// var json = this.GetAllBlogSummaries();
// //return JsonSerializer.Deserialize<List<BlogSummary>>(json);
// return JsonConvert.DeserializeObject<List<BlogSummary>>(json);
//}
public IEnumerable <BlogSummary> GetAllBlogSummaries(int pageNumber = 1)
{
string sproc = "sel_get_blog_headers";
try
{
List <BlogSummary> lstBlogSummaries = new List <BlogSummary>();
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sproc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@page_number", SqlDbType.Int).Value = pageNumber;
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader()){
while (rdr.Read())
{
BlogSummary blogSummary = new BlogSummary();
blogSummary.BlogId = Convert.ToInt32(rdr["BlogID"]);
blogSummary.Title = rdr["Title"].ToString();
blogSummary.Author = rdr["Author"].ToString();
blogSummary.Image = rdr["Image"].ToString();
blogSummary.Summary = rdr["Summary"].ToString();
blogSummary.Link = rdr["Link"].ToString();
blogSummary.Created = rdr["Created"].ToString();
lstBlogSummaries.Add(blogSummary);
}
conn.Close();
}
}
}
int pageSize = 3;
IEnumerable <BlogSummary> result;
if (pageNumber >= 1)
{
result = lstBlogSummaries.Skip((pageNumber - 1) * pageSize).Take(pageSize);
}
else
{
result = lstBlogSummaries.Skip((pageNumber) * pageSize).Take(pageSize);
}
return(result);
}
catch
{
throw;
}
}
public List <BlogSummary> GetInfo()
{
var blogPostService = DependencyResolver.Current.GetServices <IBlogPostService>().FirstOrDefault();
var list = blogPostService.Table.ToList().Select(x =>
{
BlogSummary model = new BlogSummary();
model.Author = "chuanbin";
model.Content = x.CommentText;
model.CTime = x.CreatedTime;
model.ID = x.Id;
model.Tittle = x.Tittle;
return(model);
}).ToList();
return(list);
}
public BlogSummary GetBlogSummaryById(int id)
{
string sproc = "sel_get_blog_header_by_blog_id";
try
{
BlogSummary blogSummary = new BlogSummary();
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sproc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@blog_id", SqlDbType.Int).Value = id;
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
blogSummary.BlogId = Convert.ToInt32(rdr["BlogID"]);
blogSummary.Title = rdr["Title"].ToString();
blogSummary.Author = rdr["Author"].ToString();
blogSummary.Image = rdr["Image"].ToString();
blogSummary.Summary = rdr["Summary"].ToString();
blogSummary.Link = rdr["Link"].ToString();
blogSummary.Created = rdr["Created"].ToString();
}
}
}
}
return(blogSummary);
}
catch
{
throw;
}
}
public int UpdateBlogSummary(BlogSummary blogSummary)
{
string sproc = "upd_update_blog_header";
try
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sproc, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@blog_id", SqlDbType.Int).Value = blogSummary.BlogId;
cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = blogSummary.Title;
if (blogSummary.Author.Length > 0)
{
cmd.Parameters.Add("@author", SqlDbType.NVarChar).Value = blogSummary.Author;
}
else
{
cmd.Parameters.Add("@author", SqlDbType.NVarChar).Value = "";
}
cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = blogSummary.Image;
cmd.Parameters.Add("@summary", SqlDbType.NVarChar).Value = blogSummary.Summary;
cmd.Parameters.Add("@link", SqlDbType.NVarChar).Value = blogSummary.Link;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
return(1);
}
catch
{
throw;
}
}
public int Edit([FromBody] BlogSummary blogSummary)
{
return(objBlog.UpdateBlogSummary(blogSummary));
//return 1;
}
public int Create([FromBody] BlogSummary blogSummary)
{
return(objBlog.AddBlogSummary(blogSummary));
//return 1;
}
