public List <LibraryBook> GetAllLibraryBook(string bookType, string userID) { List <LibraryBook> retVal = new List <LibraryBook>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT librarybook.`id`, librarybook.`isbn`, librarybook.`title`, librarybook.`authorid`, librarybook.`pubid`, librarybook.`npages`, librarybook.`booktype`, librarybook.`qty`, librarybook.`classoneid`, librarybook.`classtwoid`, librarybook.`classthreeid`, librarybook.`active`,librarybook.bookdesc,subtitle,libraryauthor.fullname,librarypublisher.pubname FROM `librarybook` INNER JOIN libraryauthor ON librarybook.pubid = libraryauthor.id INNER JOIN librarypublisher ON librarybook.authorid = librarypublisher.id WHERE librarybook.active = 1 AND librarybook.`booktype`= @bookType"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@bookType", bookType); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { LibraryBook Mod = new LibraryBook(); Mod.ID = dr.GetInt32(0); Mod.BookISBN = dr.GetString(1); Mod.BookTitle = dr.GetString(2); Mod.AuthorID = dr.GetInt32(3); Mod.PubID = dr.GetInt32(4); Mod.BookPages = dr.GetInt32(5); Mod.BookType = dr.GetString(6); Mod.BookQty = dr.GetInt32(7); Mod.xIDclassOne = dr.GetInt32(8); Mod.xIDclassTwo = dr.GetInt32(9); Mod.xIDclassThree = dr.GetInt32(10); Mod.Active = dr.GetInt32(11); Mod.BookDesc = dr.GetString(12); Mod.SubTitle = dr.GetString(13); Mod.xTitleBlock = new LibraryClassificationService().GetLibraryClassOne(Mod.xIDclassOne, userID).TitleBlock; Mod.xTitleShelve = new LibraryClassificationService().GetLibraryClassTwo(Mod.xIDclassTwo, userID).TitleShelve; Mod.xTitleStack = new LibraryClassificationService().GetLibraryClassThree(Mod.xIDclassThree, userID).TitleStack; Mod.AuthorName = dr.GetString(14); Mod.PubName = dr.GetString(15); retVal.Add(Mod); } con.Close(); return(retVal); }
public bool AddBookAuthor(LibraryBook fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "INSERT INTO `librarybookauthor`(`bookid`, `authorid`) VALUES (@bookid, @authorid)"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@bookid", fp.BookID); cmd.Parameters.AddWithValue("@authorid", fp.AuthorID); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }
public bool AddPublisher(LibraryBook fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "INSERT INTO `librarypublisher`(`pubname`, `country`) VALUES (@pubname, @country)"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@pubname", fp.PubName); cmd.Parameters.AddWithValue("@country", fp.Country); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }
public LibraryBook GetLibraryBook(int Id, string userID) { LibraryBook Mod = new LibraryBook(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `isbn`, `title`, `authorid`, `pubid`, `npages`, `booktype`, `qty`, `classoneid`, `classtwoid`, `classthreeid`, `active`,bookdesc,subtitle FROM `librarybook` WHERE active = 1 AND id = @id"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@id", Id); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { Mod = new LibraryBook(); Mod.ID = dr.GetInt32(0); Mod.BookISBN = dr.GetString(1); Mod.BookTitle = dr.GetString(2); Mod.AuthorID = dr.GetInt32(3); Mod.PubID = dr.GetInt32(4); Mod.BookPages = dr.GetInt32(5); Mod.BookType = dr.GetString(6); Mod.BookQty = dr.GetInt32(7); Mod.xIDclassOne = dr.GetInt32(8); Mod.xIDclassTwo = dr.GetInt32(9); Mod.xIDclassThree = dr.GetInt32(10); Mod.Active = dr.GetInt32(11); Mod.BookDesc = dr.GetString(12); Mod.SubTitle = dr.GetString(13); Mod.xTitleBlock = new LibraryClassificationService().GetLibraryClassOne(Mod.xIDclassOne, userID).TitleBlock; Mod.xTitleShelve = new LibraryClassificationService().GetLibraryClassTwo(Mod.xIDclassTwo, userID).TitleShelve; Mod.xTitleStack = new LibraryClassificationService().GetLibraryClassThree(Mod.xIDclassThree, userID).TitleStack; //retVal.Add(Mod); } con.Close(); return(Mod); }
public bool UpdatePublisher(LibraryBook fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "UPDATE `librarypublisher` SET `pubname` =@pubname , `country` = @country WHERE id = @id"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@pubname", fp.PubName); cmd.Parameters.AddWithValue("@country", fp.Country); cmd.Parameters.AddWithValue("@id", fp.ID); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }