public List <ADMINISTRATOR> List()
{
using (var db = new Libdb())
{
var query = db.ADMINISTRATOR.OrderBy(a => a.AdminId);
return(query.ToList());
}
}
// returns a list of books where the title contains the search string
public List <BOOK> GetSearchBookListFromDb(string searchString)
{
using (var db = new Libdb())
{
db.Configuration.LazyLoadingEnabled = false;
return(db.BOOK.Where(b => b.Title.Contains(searchString)).OrderBy(b => b.Title).ToList());
}
}
// returns a list of authors where the the name contains the searchString
public List <AUTHOR> GetSearchAuthorListFromDb(string searchString)
{
using (var db = new Libdb())
{
db.Configuration.LazyLoadingEnabled = false;
return(db.AUTHOR.Where(a => a.FirstName.Contains(searchString)).OrderBy(a => a.FirstName).ToList());
}
}
public ADMINISTRATOR Read(int AdminId)
{
using (var db = new Libdb())
{
db.ADMINISTRATOR.Load();
return(db.ADMINISTRATOR.Find(AdminId));
}
}
public List <BOOK> List()
{
using (var db = new Libdb())
{
// return
var query = db.BOOK.OrderBy(b => b.Title);
return(query.ToList());
}
}
public void Update(BOOK bookObj)
{
using (var db = new Libdb())
{
db.BOOK.Attach(bookObj);
db.Entry(bookObj).State = EntityState.Modified;
db.SaveChanges();
}
}
public void Update(AUTHOR authobj)
{
using (var db = new Libdb())
{
db.AUTHOR.Attach(authobj);
db.Entry(authobj).State = EntityState.Modified;
db.SaveChanges();
}
}
public List <AUTHOR> List() // retrieve all authors
{
using (var db = new Libdb())
{
// return
var query = db.AUTHOR.OrderBy(a => a.Aid);
return(query.ToList());
}
}
public void Add(AUTHOR authobj)
{
using (var db = new Libdb())
{
db.AUTHOR.Add(authobj);
db.Entry(authobj).State = EntityState.Added;
db.SaveChanges();
}
}
public int GetIdByUsername(string username)
{
using (var db = new Libdb())
{
var query = db.ADMINISTRATOR.SqlQuery("SELECT * FROM dbo.ADMINISTRATOR WHERE UserName = {0}", username);
int result = (int)query.First().AdminId;
return(result);
}
}
// returns list of authors based connected to the id=isbn
public List <AUTHOR> GetAuthorByIsbn(string id)
{
using (var db = new Libdb())
{
db.Configuration.LazyLoadingEnabled = false;
//return db.BOOK.Find("").AUTHOR.ToList();
return(db.BOOK.Include(x => x.AUTHOR).First(b => b.ISBN == id).AUTHOR.ToList());
}
}
public void Create(ADMINISTRATOR admin)
{
using (var db = new Libdb())
{
db.ADMINISTRATOR.Add(admin);
db.Entry(admin).State = EntityState.Added;
db.SaveChanges();
}
}
public void Update(ADMINISTRATOR adminobj)
{
using (var db = new Libdb())
{
db.ADMINISTRATOR.Attach(adminobj);
db.Entry(adminobj).State = EntityState.Modified;
db.SaveChanges();
}
}
public AUTHOR Read(int Aid) // Find author by id
{
using (var db = new Libdb())
{
var query = db.AUTHOR.Where(a => a.Aid == Aid);
return(query.SingleOrDefault());
//db.AUTHOR.Load();
//return db.AUTHOR.Find(Aid);
}
}
public BOOK Read(string id) // Find book by id
{
using (var db = new Libdb())
{
var query = db.BOOK.Where(b => b.ISBN == id);
return(query.SingleOrDefault());
//db.BOOK.Load();
//return db.BOOK.Find(id);
}
}
public void Delete(BOOK book)
{
using (var db = new Libdb())
{
var bk = db.BOOK.FirstOrDefault(b => b.ISBN == book.ISBN);
bk.AUTHOR.Clear();
db.BOOK.Remove(bk);
db.SaveChanges();
}
}
public void Delete(ADMINISTRATOR admin)
{
using (var db = new Libdb())
{
db.ADMINISTRATOR.Attach(admin);
db.ADMINISTRATOR.Remove(admin);
db.Entry(admin).State = EntityState.Deleted;
db.SaveChanges();
}
}
public void Remove(AUTHOR auth)
{
using (var db = new Libdb())
{
var au = db.AUTHOR.FirstOrDefault(a => a.Aid == auth.Aid);
au.BOOK.Clear();
db.AUTHOR.Remove(au);
db.SaveChanges();
}
}
/* Check for username-match using raw SQL */
public bool UsernameExists(string username)
{
using (var db = new Libdb())
{
var query = db.ADMINISTRATOR.SqlQuery("SELECT * FROM dbo.ADMINISTRATOR WHERE UserName = {0}", username).ToList();
if (query.Count != 0)
{
return(true);
}
else
{
return(false);
}
}
}
// returns list of books written by the author aid from db
public List <BOOK> GetBookByAid(int aid)
{
using (var db = new Libdb())
{
db.Configuration.LazyLoadingEnabled = false;
var author = db.AUTHOR.Include(b => b.BOOK).FirstOrDefault(a => a.Aid.Equals(aid));
if (author != null)
{
return(author.BOOK.ToList());
}
else
{
return(new List <BOOK>());
}
}
}
/* Check for username-match using raw SQL */
public bool DoHashMatch(string inHash, string inUsername)
{
using (var db = new Libdb())
{
var query = db.ADMINISTRATOR.SqlQuery("SELECT * FROM dbo.ADMINISTRATOR WHERE PassHash = {0} AND UserName = {1}", inHash, inUsername).ToList();
if (query.Count != 0)
{
return(true);
}
else
{
return(false);
}
}
}
public void Add(BOOK bk)
{
using (var db = new Libdb())
{
var authors = bk.AUTHOR;
bk.AUTHOR = new List <AUTHOR>();
db.BOOK.Add(bk);
foreach (var author in authors)
{
bk.AUTHOR.Add(author);
db.Entry(author).State = EntityState.Unchanged;
}
db.SaveChanges();
}
}
public bool DoesIsbnExist(string isbn)
{
using (var db = new Libdb())
{
db.Configuration.LazyLoadingEnabled = false;
//var book = db.BOOK.Include(x => x.ISBN).FirstOrDefault(x => x.ISBN.Equals(isbn));
var book = db.BOOK.FirstOrDefault(b => b.ISBN.Equals(isbn));
if (book == null)
{
return(false); // isbn does not exist on db
}
else
{
return(true); // isbn is not unique, so isbn is already on server
}
}
}
