internal static Response.GenericResponse <double> GetTotalAverageVotes()
{
Response.GenericResponse <double> response = new Response.GenericResponse <double>();
string queryGetC = "SELECT AVG(`Book-Rating`) FROM `bx-book-ratings`";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
conn.Open();
MySqlCommand commandGetC = new MySqlCommand(queryGetC, conn);
MySqlDataReader reader = commandGetC.ExecuteReader();
double C = 0.0;
if (reader.Read())
{
C = GetSafeField <double>(reader, 0);
}
else
{
throw new Exception("Error getting mean value.");
}
response.Content = C;
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.GenericResponse <Book> GetBook(string ISBN)
{
Response.GenericResponse <Book> response = new Response.GenericResponse <Book>();
string query = "SELECT * FROM `bx-books` WHERE ISBN = @ISBN";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
mySqlCommand.Parameters.AddWithValue("@ISBN", ISBN);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
Book Book;
if (reader.Read())
{
Book = GetBookFromReader(reader);
}
else
{
throw new Exception("Could not find a book with ISBN no: '" + ISBN + "'.");
}
response.Content = Book;
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}
public static Response.GenericResponse <int> GetVote(string userID, string bookISBN)
{
Response.GenericResponse <int> response = new Response.GenericResponse <int>();
string query = "SELECT `Book-Rating` FROM `bx-book-ratings` WHERE ISBN = @ISBN AND `User-ID` = @UserID";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
mySqlCommand.Parameters.AddWithValue("@ISBN", bookISBN);
mySqlCommand.Parameters.AddWithValue("@UserID", userID);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
if (reader.Read())
{
response.Content = GetSafeField <SByte>(reader, 0);
}
else
{
response.Content = -1;
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}
public static Response.GenericResponse <User> GetUser(string username, string password)
{
string queryLogin = "******";
Response.GenericResponse <User> response = new Response.GenericResponse <User>();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandLogin = new MySqlCommand(queryLogin, conn);
mySqlCommandLogin.Parameters.AddWithValue("@username", username);
mySqlCommandLogin.Parameters.AddWithValue("@password", password);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommandLogin.ExecuteReader();
if (reader.Read())
{
response.Content = GetUserFromReader(reader);
}
else
{
throw new Exception("No matching username and password combination.");
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.GenericResponse <List <Vote <UInt32> > > GetAllBookVotes(Book book)
{
string queryGetVotes = "SELECT `User-ID`, `Book-Rating` FROM `bx-book-ratings` WHERE `ISBN` = @ISBN";
Response.GenericResponse <List <Vote <UInt32> > > response = new Response.GenericResponse <List <Vote <UInt32> > > {
Content = new List <Vote <UInt32> >()
};
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandGetAllVotes = new MySqlCommand(queryGetVotes, conn);
mySqlCommandGetAllVotes.Parameters.AddWithValue("@ISBN", book.ISBN);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommandGetAllVotes.ExecuteReader();
while (reader.Read())
{
Vote <UInt32> vote = new Vote <UInt32>();
try
{
vote.Content = GetSafeField <UInt32>(reader, 0);
vote.Rating = GetSafeField <int>(reader, 1);
response.Content.Add(vote);
}
catch (Exception e)
{
response.ErrorText += e.Message + "\n";
continue;
}
}
if (response.Content.Count <= 0)
{
throw new Exception("No votes returned.");
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.GenericResponse <UInt32> AddUser(User user, string password)
{
string queryAddUser = "******";
Response.GenericResponse <UInt32> response = new Response.GenericResponse <UInt32>();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandAddUser = new MySqlCommand(queryAddUser, conn);
mySqlCommandAddUser.Parameters.AddWithValue("@Username", user.Username);
mySqlCommandAddUser.Parameters.AddWithValue("@Password", password);
mySqlCommandAddUser.Parameters.AddWithValue("@Location", user.Location);
mySqlCommandAddUser.Parameters.AddWithValue("@Age", user.Age);
try
{
conn.Open();
int rowsAffected = mySqlCommandAddUser.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error inserting into users table. Rows affected: " + rowsAffected);
}
string queryGetID = "SELECT MAX(`User-ID`) FROM `bx-users`";
MySqlCommand mySqlCommandGetID = new MySqlCommand(queryGetID, conn);
UInt32 getID = 0;
using (MySqlDataReader reader = mySqlCommandGetID.ExecuteReader())
{
if (reader.Read())
{
getID = GetSafeField <UInt32>(reader, 0);
}
}
response.Content = getID;
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.GenericResponse <List <Book> > GetAllBooks()
{
string queryGetBooks = "SELECT * FROM `bx-books`";
Response.GenericResponse <List <Book> > response = new Response.GenericResponse <List <Book> > {
Content = new List <Book>()
};
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandGetAllBooks = new MySqlCommand(queryGetBooks, conn);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommandGetAllBooks.ExecuteReader();
while (reader.Read())
{
try
{
response.Content.Add(GetBookFromReader(reader));
}
catch (Exception e)
{
response.ErrorText += e.Message + "\n";
continue;
}
}
if (response.Content.Count <= 0)
{
throw new Exception("No books returned.");
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.GenericResponse <List <Book> > GetNewsList()
{
Response.GenericResponse <List <Book> > response = new Response.GenericResponse <List <Book> >()
{
Content = new List <Book>()
};
string query = "SELECT * FROM `bx-books` ORDER BY AddedDate DESC LIMIT 5";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
while (reader.Read())
{
try
{
response.Content.Add(GetBookFromReader(reader));
}
catch (Exception e)
{
response.ErrorText += e.Message + "\n";
continue;
}
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}
public static Response.GenericResponse <List <Book> > GetRecommendList()
{
Response.GenericResponse <List <Book> > response = new Response.GenericResponse <List <Book> >()
{
Content = new List <Book>()
};
string query = "SELECT * FROM `bx-books` A NATURAL JOIN (SELECT `Book-Rating`,`ISBN` FROM `bx-book-ratings` WHERE `User-ID` = @UserID) B";
/*
* using (MySqlConnection conn = new MySqlConnection(connectionString))
* {
* MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
* mySqlCommand.Parameters.AddWithValue("@UserID", CommonLibrary.LoggedInUser.UserID);
*
* try
* {
* conn.Open();
* MySqlDataReader reader = mySqlCommand.ExecuteReader();
*
* while (reader.Read())
* {
* try
* {
* response.Content.Add(GetBookFromReader(reader));
* }
* catch (Exception e)
* {
* response.ErrorText += e.Message + "\n";
* continue;
* }
* }
* response.Success = true;
* }
* catch (Exception e)
* {
* response.ErrorText = e.Message;
* }
* }*/
return(response);
}
public static Response.GenericResponse <List <User> > GetAllUsers()
{
string queryGetBooks = "SELECT * FROM `bx-users` WHERE Username IS NOT NULL";
Response.GenericResponse <List <User> > response = new Response.GenericResponse <List <User> > {
Content = new List <User>()
};
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandGetAllUsers = new MySqlCommand(queryGetBooks, conn);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommandGetAllUsers.ExecuteReader();
while (reader.Read())
{
try
{
response.Content.Add(GetUserFromReader(reader));
}
catch (Exception e)
{
response.ErrorText += e.Message + "\n";
continue;
}
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.GenericResponse <List <Book> > GetPopularList()
{
Response.GenericResponse <List <Book> > response = new Response.GenericResponse <List <Book> >()
{
Content = new List <Book>()
};
string query = "SELECT * FROM `bx-books` AS B INNER JOIN (SELECT `ISBN`, COUNT(`ISBN`) AS NumberRating FROM `bx-book-ratings` GROUP BY `ISBN`) AS F ON B.`ISBN` = F.`ISBN` ORDER BY F.NumberRating DESC LIMIT 10";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataReader reader = mySqlCommand.ExecuteReader();
while (reader.Read())
{
try
{
response.Content.Add(GetBookFromReader(reader));
}
catch (Exception e)
{
response.ErrorText += e.Message + "\n";
continue;
}
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}