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