/// <summary>
/// Insert data for User
/// </summary>
/// <param name="user">Info of user</param>
/// <returns></returns>
public User InsertDataForUsers(User user)
{
DbContextTransaction dbTransaction = null;
using (WebPersonalDB dbContext = new WebPersonalDB())
{
try
{
dbTransaction = dbContext.Database.BeginTransaction();
User newUser = dbContext.Users.Add(user);
dbContext.SaveChanges();
dbTransaction.Commit();
return(newUser);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
public List <Catalogic> GetMostUsedCatalogies()
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
return(dbContext.Database.SqlQuery <Catalogic>("sp_GetMostUsedCata").ToList());
}
}
/// <summary>
/// Get a user by ID
/// </summary>
/// <param name="idUser">ID of user</param>
/// <returns></returns>
public User GetUserByID(int idUser)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
return((from user in dbContext.Users
where user.IDUser == idUser
select user).FirstOrDefault());
}
}
public Article InsertDataForArticle(Article article)
{
DbContextTransaction dbTransaction = null;
using (WebPersonalDB dbContext = new WebPersonalDB())
{
try
{
dbTransaction = dbContext.Database.BeginTransaction();
List <Tag> listTagArticleAddNew = new List <Tag>();
if (!string.IsNullOrEmpty(article.Tag))
{
// Get all tag
List <Tag> tags = dbContext.Tags.ToList();
// Add tag
string[] listTagName = article.Tag.Split(',');
for (int i = 0; i < listTagName.Length; i++)
{
string tagName = listTagName[i];
Tag tagChoose = tags.Where(a => a.Name == tagName).FirstOrDefault();
if (tagChoose != null)
{
listTagArticleAddNew.Add(tagChoose);
}
else
{
Tag tagNew = new Tag()
{
Name = tagName,
Slug = "",
Description = ""
};
listTagArticleAddNew.Add(tagNew);
}
}
}
article.Tags = listTagArticleAddNew;
// Add new article
Article newArticle = dbContext.Articles.Add(article);
dbContext.SaveChanges();
dbTransaction.Commit();
return(newArticle);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
public PostDataModel GetArticleByID(int idArticle)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
#if DEBUG
dbContext.Database.Log = sql => Debug.Write(sql);
#endif
SqlParameter param = new SqlParameter("@IDArticle", idArticle);
return(dbContext.Database.SqlQuery <PostDataModel>("sp_GetArticleByID @IDArticle", param).FirstOrDefault());
}
}
public List <TagData> GetListTag()
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
return((from tag in dbContext.Tags
select new TagData()
{
IDTag = tag.IDTag,
Name = tag.Name
}).ToList());
}
}
public Catalogy EditCata(Catalogy cata, ref string errorMessage)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
#if DEBUG
dbContext.Database.Log = sql => Debug.Write(sql);
#endif
DbContextTransaction dbTransaction = null;
try
{
dbTransaction = dbContext.Database.BeginTransaction();
// Check exits
Catalogy oldCata = dbContext.Catalogies.Where(a => a.IDCata == cata.IDCata).FirstOrDefault();
if (oldCata == null)
{
errorMessage = "Catalogic isn't exists.";
return(null);
}
// Check name
Catalogy cataByName = dbContext.Catalogies.Where(a => a.Name == cata.Name).FirstOrDefault();
if (cataByName != null)
{
errorMessage = "Name is existed. Please select other name.";
return(null);
}
// Update cata
oldCata.Name = cata.Name;
oldCata.Slug = cata.Slug;
oldCata.Description = cata.Description;
oldCata.IDCataParent = cata.IDCataParent;
dbContext.SaveChanges();
dbTransaction.Commit();
return(oldCata);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
public List <InfoArticleModel> GetRelatePost(PostDataModel article)
{
List <InfoArticleModel> results = new List <InfoArticleModel>();
using (WebPersonalDB dbContext = new WebPersonalDB())
{
#if DEBUG
dbContext.Database.Log = sql => Debug.Write(sql);
#endif
SqlParameter numRecordParam = new SqlParameter("@NumRecord", 3);
SqlParameter idArticleParam = new SqlParameter("@IDArticle", article.IDArticle);
SqlParameter idCataParam = new SqlParameter("@IDCata", article.IDCata);
return(dbContext.Database.SqlQuery <InfoArticleModel>("sp_GetRelateArticle @NumRecord,@IDArticle,@IDCata", numRecordParam, idArticleParam, idCataParam).ToList());
}
}
public bool DeleteCatas(List <Catalogic> catas, ref string errorMessage)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
#if DEBUG
dbContext.Database.Log = sql => Debug.Write(sql);
#endif
DbContextTransaction dbTransaction = null;
try
{
dbTransaction = dbContext.Database.BeginTransaction();
List <Catalogy> listCataDelete = new List <Catalogy>();
foreach (Catalogic cata in catas)
{
// Check exists
Catalogy cataDelete = dbContext.Catalogies.Where(a => a.IDCata == cata.IDCata).FirstOrDefault();
if (cataDelete == null)
{
errorMessage = string.Format("Catalogy {0} isn't exists", "");
return(false);
}
listCataDelete.Add(cataDelete);
}
if (listCataDelete.Count > 0)
{
dbContext.Catalogies.RemoveRange(listCataDelete);
}
dbContext.SaveChanges();
dbTransaction.Commit();
return(true);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
public List <InfoArticleModel> GetListInfoArticle(int maxCount)
{
List <InfoArticleModel> results = new List <InfoArticleModel>();
using (WebPersonalDB database = new WebPersonalDB())
{
results = (from ar in database.Articles
orderby ar.DateOfWrite descending
select new InfoArticleModel()
{
ID = ar.IDArticle,
Name = ar.Title,
Description = ar.Description,
ImagePath = ar.ImagePath
}).Take(maxCount).ToList();
}
return(results);
}
public Tag AddNewTag(Tag tagNew, ref string errorMessage)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
DbContextTransaction dbTransaction = null;
try
{
dbTransaction = dbContext.Database.BeginTransaction();
// Check exist name
Tag tag = (from tg in dbContext.Tags
where tg.Name == tagNew.Name
select tg).FirstOrDefault();
if (tag != null)
{
errorMessage = "Tag isn't exists";
return(null);
}
Tag tagCreate = dbContext.Tags.Add(tagNew);
dbContext.SaveChanges();
dbTransaction.Commit();
return(tagCreate);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
public Catalogy InsertNewCata(Catalogy cata, ref string errorMessage)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
#if DEBUG
dbContext.Database.Log = sql => Debug.Write(sql);
#endif
DbContextTransaction dbTransaction = null;
try
{
dbTransaction = dbContext.Database.BeginTransaction();
// Check exists
Catalogy oldCata = dbContext.Catalogies.Where(a => a.Name == cata.Name).FirstOrDefault();
if (oldCata != null)
{
errorMessage = "Catalogic have been existed.";
return(null);
}
Catalogy cataNew = dbContext.Catalogies.Add(cata);
dbContext.SaveChanges();
dbTransaction.Commit();
return(cataNew);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
public bool DeleteTags(List <int> tagsDelete, ref string errorMessage)
{
using (WebPersonalDB dbContext = new WebPersonalDB())
{
DbContextTransaction dbTransaction = null;
try
{
dbTransaction = dbContext.Database.BeginTransaction();
// Check exist name
var listTagDelete = (from tg in dbContext.Tags
where tagsDelete.Contains(tg.IDTag)
select tg);
if (listTagDelete.Count() != tagsDelete.Count)
{
errorMessage = "Tags are deleted, not exists on database";
return(false);
}
// Delete tag
dbContext.Tags.RemoveRange(listTagDelete);
dbContext.SaveChanges();
dbTransaction.Commit();
return(true);
}
catch (Exception ex)
{
if (dbTransaction != null)
{
dbTransaction.Rollback();
dbTransaction = null;
}
throw ex;
}
}
}
