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);
}
