public bookModel getBookByID(int bookID)
{
bookModel bm = null;
SqlCommand command = new SqlCommand("select bookTitle, bookAuthor from bookSelection where bookID = " + bookID, conn);
conn.Open();
SqlDataReader rdr = command.ExecuteReader();
try
{
if (rdr.Read())
{
bm = new bookModel();
bm.bookTitle = rdr.GetString(0);
bm.bookAuthor = rdr.GetString(1);
}
rdr.Close();
conn.Close();
return(bm);
} catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
}
//Get specific years books
public List <bookModel> getBookAuthorTitle(int year)
{
List <bookModel> books = new List <bookModel>();
bookModel bm = null;
SqlCommand command = new SqlCommand("select dbo.bookSelection.bookAuthor,dbo.bookSelection.bookTitle from dbo.bookSelection WHERE year(dbo.bookSelection.bookChosenDate)='" + year + "'", conn);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
bm = new bookModel();
bm.bookAuthor = reader.GetValue(0).ToString();
bm.bookTitle = reader.GetValue(1).ToString();
books.Add(bm);
}
reader.Close();
conn.Close();
return(books);
} catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
}
public Book getBookFromApi()
{
OpenLibraryRequester ORequester = new OpenLibraryRequester();
bookModel bm = ORequester.GetInfos("0781441900");
if (bm != null)
{
Book b = new Book();
foreach (var item in bm.authors)
{
b.Author = b.Author + item.name + " ";
}
b.Title = bm.title;
foreach (var item in bm.subjects)
{
b.Category = b.Category + item.name + ",";
}
b.Description = bm.notes;
b.Image = bm.cover.large;
//b.ReleaseYear = int.Parse(bm.publish_date);
Insert(b);
return(b);
}
return(null);
}
public bookModel GetInfos(string isbn)
{
string Endpoint = "/books?bibkeys=ISBN:" + isbn + "&jscmd=data";
string json = base.Execute(Endpoint, "");
if (json != "")
{
string CleanJson = json.Replace("var _OLBookInfo =", "").Replace(@"\", "");
CleanJson = CleanJson.Remove(CleanJson.Length - 2);
CleanJson = CleanJson.Replace("ISBN:", "");
CleanJson = CleanJson.Substring(CleanJson.IndexOf(":") + 1);
bookModel lc = JsonConvert.DeserializeObject <bookModel>(CleanJson); //Using NewTonsoft nuget
return(lc);
}
else
{
return(null);
}
}
public bookModel getLowPageCount()
{
bookModel bm = new bookModel();
using (SqlCommand cmd = new SqlCommand("sp_getLowPageCount"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
bm.bookTitle = rdr.GetString(0);
bm.bookAuthor = rdr.GetString(1);
bm.bookPageLength = rdr.GetInt32(2);
rdr.Close();
conn.Close();
}
return(bm);
}
protected void draw_custom_table()
{
SQLFunctions conn = new SQLFunctions();
TableRow row;
TableCell cell;
tableContent.Rows.Clear();
resultsTally rt = evaluateTopBooks();
List <resultsTally> lrt = new List <resultsTally>();
row = new TableRow();
cell = new TableCell();
if (rt.book_id > -1)
{
cell.Text = "With " + rt.numberVotes + " number one votes, the clear #1 is : " + rt.bookTitle;
row.Cells.Add(cell);
}
else if (rt.book_id == -2)
{
lrt = returnTiedTopBooks();
cell.Text = "Tied with " + lrt[0].numberVotes + " number one votes, the two best books are : " + lrt[0].bookTitle + " and " + lrt[1].bookTitle;
row.Cells.Add(cell);
}
else
{
cell.Text = "There no was no clear #1 pick for best book!";
row.Cells.Add(cell);
//cell = new TableCell();
//List<resultsTally> lrt = evaluateTopBookPlurality();
//cell.Text = "With " + lrt[0].numberVotes + " the plurality of people voted #1 as : " + lrt[0].bookTitle;
}
tableContent.Rows.Add(row);
rt = new resultsTally();
rt = evaluateBottomBooks();
row = new TableRow();
cell = new TableCell();
if (rt.book_id > -1)
{
cell.Text = "With " + rt.numberVotes + " number seven votes, the most disliked book is : " + rt.bookTitle;
row.Cells.Add(cell);
}
else if (rt.book_id == -2)
{
lrt = new List <resultsTally>();
//lrt = returnTiedBottomBooks();
cell.Text = "Tied with " + lrt[0].numberVotes + " number seven votes, the two worst books are : " + lrt[0].bookTitle + " and " + lrt[1].bookTitle;
row.Cells.Add(cell);
}
else
{
cell.Text = "There no was no clear #1 pick for worst book!";
row.Cells.Add(cell);
}
tableContent.Rows.Add(row);
int pages = conn.getTotalPagesRead(2018);
if (pages > -1)
{
row = new TableRow();
cell = new TableCell();
cell.Text = "We have read " + pages + " number of pages in 2018";
row.Cells.Add(cell);
tableContent.Rows.Add(row);
}
bookModel highCount = conn.getHighPageCount();
if (highCount.bookPageLength > -1)
{
row = new TableRow();
cell = new TableCell();
cell.Text = "The high page count is " + highCount.bookTitle + " with " + highCount.bookPageLength + " pages.";
row.Cells.Add(cell);
tableContent.Rows.Add(row);
}
bookModel lowCount = conn.getLowPageCount();
if (highCount.bookPageLength > -1)
{
row = new TableRow();
cell = new TableCell();
cell.Text = "The low page count is " + lowCount.bookTitle + " with " + lowCount.bookPageLength + " pages.";
row.Cells.Add(cell);
tableContent.Rows.Add(row);
}
int pubdate = conn.getAvgBookPublishYear(2018);
if (pubdate > -1)
{
row = new TableRow();
cell = new TableCell();
cell.Text = "The average first published year is " + pubdate;
row.Cells.Add(cell);
tableContent.Rows.Add(row);
}
}
