public List <BXBookRating> GetAllBooksRatedByUser(int id)
{
IDbConnection connection;
DbProviderFactory factory;
GetConection(out connection, out factory);
string sqlSelect = "SELECT * FROM BXBookRatings WHERE UserID = @id";
IDbCommand command = factory.CreateCommand();
command.CommandText = sqlSelect;
command.Connection = connection;
// setting up 'id' parameter
DbParameter parameter = factory.CreateParameter();
parameter.Value = id;
parameter.ParameterName = "@id";
command.Parameters.Add(parameter);
List <BXBookRating> ratings = new List <BXBookRating>();
using (connection)
{
connection.Open();
IDataReader reader = command.ExecuteReader();
if (reader.FieldCount != 0)
{
while (reader.Read())
{
BXBookRating rating = new BXBookRating();
rating.UserID = Convert.ToInt32(reader[0].ToString());
rating.ISBN = reader[1].ToString();
rating.BookRating = Convert.ToInt32(reader[2].ToString());
ratings.Add(rating);
}
}
}
return(ratings);
}
public List <BXBookRating> GetTopRatedBooks()
{
IDbConnection connection;
DbProviderFactory factory;
GetConection(out connection, out factory);
string sqlSelect = "SELECT top 10 * " +
"from BXBookRatings " +
"order by BookRating desc";
IDbCommand command = factory.CreateCommand();
command.CommandText = sqlSelect;
command.Connection = connection;
List <BXBookRating> ratings = new List <BXBookRating>();
using (connection)
{
connection.Open();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
BXBookRating rating = new BXBookRating();
rating.UserID = Int32.Parse(reader[0].ToString());
rating.ISBN = reader[1].ToString();
rating.BookRating = Int32.Parse(reader[2].ToString());
ratings.Add(rating);
}
}
return(ratings);
}
