public bool RemoveBeerCategory(int idCategory, int idBeer)
{
QueryDB query = new QueryDB("DELETE FROM [BeerCategory] WHERE Id_Category = @IdCat AND Id_Beer= @IdBeer;");
query.AddParametre("@IdCat", idCategory);
query.AddParametre("@IdBeer", idBeer);
return(ConnectDB.ExecuteNonQuery(query) == 1);
}
public override ProductionCompany Update(long key, ProductionCompany entity)
{
QueryDB query = new QueryDB("UPDATE ProductionCompany SET Name = @name OUTPUT inserted.* WHERE Id_ProductionCompany = @Id");
query.AddParametre("@Id", key);
query.AddParametre("@name", entity.Name);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public MemberEntity GetByCredential(string email, string password)
{
QueryDB query = new QueryDB("RetrieveMember", true);
query.AddParametre("@Email", email);
query.AddParametre("@Password", password);
return(ConnectDB.ExecuteReader(query, ConvertDataReaderToEntity).SingleOrDefault());
}
public override Role Update(long key, Role entity)
{
QueryDB query = new QueryDB("UPDATE [Role] SET Name = @name OUTPUT inserted.* WHERE Id_Role = @Id");
query.AddParametre("@Id", key);
query.AddParametre("@name", entity.Name);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override MovieGenre Insert(MovieGenre entity)
{
QueryDB query = new QueryDB("INSERT INTO Movie_Genre ([Id_Movie],[Id_Genre]) OUTPUT inserted.* VALUES (@idMovie, @idGenre)");
query.AddParametre("@idMovie", entity.IdMovie);
query.AddParametre("@idGenre", entity.IdGenre);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override int Insert(BreweryEntity entity)
{
QueryDB query = new QueryDB("InsertBrewery", true);
query.AddParametre("@Name", entity.Name);
query.AddParametre("@Headquarter", entity.Headquarter);
query.AddParametre("@Country", entity.Country);
return((int)ConnectDB.ExecuteScalar(query));
}
public override int Insert(MemberEntity entity)
{
QueryDB query = new QueryDB("InsertMember", true);
query.AddParametre("@Username", entity.Username);
query.AddParametre("@Email", entity.Email);
query.AddParametre("@Password", entity.Password);
return((int)ConnectDB.ExecuteScalar(query));
}
public override MovieGenre Update(long key, MovieGenre entity)
{
QueryDB query = new QueryDB("UPDATE Movie_Genre SET [Id_Movie] = @idMovie, [Id_Genre] = @idGenre OUTPUT inserted.* WHERE Id_MovieGenre = @Id");
query.AddParametre("@Id", key);
query.AddParametre("@idMovie", entity.IdMovie);
query.AddParametre("@idGenre", entity.IdGenre);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override int Insert(BrandEntity entity)
{
QueryDB query = new QueryDB("InsertBrand", true);
query.AddParametre("@Name", entity.Name);
query.AddParametre("@Country", entity.Country);
query.AddParametre("@Creation_Year", entity.CreationYear);
return((int)ConnectDB.ExecuteScalar(query));
}
protected IEnumerable <TEntity> GetPagination(int offset, int limit, string orderColumn)
{
QueryDB query = new QueryDB($"SELECT * FROM [{TableName}] " +
$"ORDER BY [{orderColumn}] ASC " +
"OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY");
query.AddParametre("@offset", offset);
query.AddParametre("@limit", limit);
return(ConnectDB.ExecuteReader(query, ConvertDataReaderToEntity));
}
public override int Insert(BeerEntity entity)
{
QueryDB query = new QueryDB("InsertBeer", true);
query.AddParametre("@Name", entity.Name);
query.AddParametre("@Color", entity.Color);
query.AddParametre("@Degree", entity.Degree);
query.AddParametre("@Id_Brewery", entity.IdBrewery);
query.AddParametre("@Id_Brand", entity.IdBrand);
return((int)ConnectDB.ExecuteScalar(query));
}
public override Rating Insert(Rating entity)
{
QueryDB query = new QueryDB("INSERT INTO Rating ([Score],[Comment],[RatingDate],[Id_Movie],[Id_Member]) " +
"OUTPUT inserted.* VALUES (@score, @comment, @rating, @idMovie, @idMember)");
query.AddParametre("@score", entity.Score);
query.AddParametre("@comment", entity.Comment);
query.AddParametre("@rating", entity.RatingDate);
query.AddParametre("@idMovie", entity.IdMovie);
query.AddParametre("@idMember", entity.IdMember);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override Genre Update(long key, Genre entity)
{
string sql = "UPDATE Genre"
+ " SET [Name] = @name"
+ " OUTPUT inserted.Id_Genre, inserted.Name"
+ " WHERE [Id_Genre] = @id";
QueryDB query = new QueryDB(sql);
query.AddParametre("@id", entity.Id);
query.AddParametre("@name", entity.Name);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public Guid Insert(ImageData entity)
{
QueryDB query = new QueryDB("INSERT INTO [ImageData] ([OriginalName], [ImagePath], [Description])" +
" OUTPUT Inserted.Id" +
" VALUES (@OriginalName, @ImagePath, @Description)");
query.AddParametre("OriginalName", entity.OriginalName);
query.AddParametre("ImagePath", entity.ImagePath);
query.AddParametre("Description", entity.Description);
string id = DB.ExecuteScalar(query).ToString();
return(Guid.Parse(id));
}
public override bool Update(int id, CategoryEntity entity)
{
QueryDB query = new QueryDB("UpdateCategory", true);
query.AddParametre("@Id_Category", id);
query.AddParametre("@Name", entity.Name);
try
{
return(ConnectDB.ExecuteNonQuery(query) == 1);
}
catch (SqlException e)
{
return(false);
}
}
public override Role Insert(Role entity)
{
QueryDB query = new QueryDB("INSERT INTO [Role] ([Name]) OUTPUT inserted.* VALUES (@name)");
query.AddParametre("@name", entity.Name);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public ImageData Get(Guid id)
{
QueryDB query = new QueryDB("SELECT * FROM [ImageData] WHERE [Id] = @id");
query.AddParametre("id", id);
return(DB.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override Rating Get(long key)
{
QueryDB query = new QueryDB("SELECT * FROM Rating WHERE Id_Rating = @Id");
query.AddParametre("@Id", key);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public IEnumerable <Rating> GetByMovie(long idMovie)
{
QueryDB query = new QueryDB("SELECT * FROM Rating WHERE Id_Movie = @IdMovie");
query.AddParametre("@IdMovie", idMovie);
return(Connector.ExecuteReader(query, ConvertReaderToEntity));
}
public virtual TEntity Get(TKey id)
{
QueryDB query = new QueryDB($"SELECT * FROM [{TableName}] WHERE [{IdName}] = @Id");
query.AddParametre("@Id", id);
return(ConnectDB.ExecuteReader(query, ConvertDataReaderToEntity).SingleOrDefault());
}
public override bool Delete(long key)
{
QueryDB query = new QueryDB("DELETE FROM Rating WHERE Id_Rating = @Id");
query.AddParametre("@Id", key);
return(Connector.ExecuteNonQuery(query) == 1);
}
public override ProductionCompany Insert(ProductionCompany entity)
{
QueryDB query = new QueryDB("INSERT INTO ProductionCompany (Name) OUTPUT inserted.* VALUES (@name)");
query.AddParametre("@name", entity.Name);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override ProductionCompany Get(long key)
{
QueryDB query = new QueryDB("SELECT * FROM ProductionCompany WHERE Id_ProductionCompany = @Id");
query.AddParametre("@Id", key);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override bool Delete(long key)
{
QueryDB query = new QueryDB("DELETE FROM ProductionCompany WHERE Id_ProductionCompany = @Id");
query.AddParametre("@Id", key);
return(Connector.ExecuteNonQuery(query) == 1);
}
public bool Delete(Guid id)
{
QueryDB query = new QueryDB("DELETE FROM [ImageData] WHERE [Id] = @Id");
query.AddParametre("Id", id);
return(DB.ExecuteNonQuery(query) == 1);
}
public virtual bool Delete(TKey id)
{
QueryDB query = new QueryDB($"DELETE FROM [{TableName}] WHERE [{IdName}] = @Id");
query.AddParametre("@Id", id);
return(ConnectDB.ExecuteNonQuery(query) == 1);
}
public bool Update(Guid id, ImageData entity)
{
QueryDB query = new QueryDB("UPDATE [ImageData]" +
" SET [OriginalName] = @OriginalName," +
" [ImagePath] = @ImagePath," +
" [Description] = @Description" +
" WHERE [Id] = @Id");
query.AddParametre("Id", id);
query.AddParametre("OriginalName", entity.OriginalName);
query.AddParametre("ImagePath", entity.ImagePath);
query.AddParametre("Description", entity.Description);
int nbRow = DB.ExecuteNonQuery(query);
return(nbRow == 1);
}
public override int Insert(CategoryEntity entity)
{
QueryDB query = new QueryDB("InsertCategory", true);
query.AddParametre("@Name", entity.Name);
return((int)ConnectDB.ExecuteScalar(query));
}
public override Genre Get(long key)
{
QueryDB query = new QueryDB("SELECT Id_Genre, Name FROM Genre WHERE Id_Genre = @id");
query.AddParametre("@id", key);
return(Connector.ExecuteReader(query, ConvertReaderToEntity).SingleOrDefault());
}
public override bool Update(int id, BreweryEntity entity)
{
QueryDB query = new QueryDB("UpdateBrewery", true);
query.AddParametre("@Id_Brewery", id);
query.AddParametre("@Name", entity.Name);
query.AddParametre("@Headquarter", entity.Headquarter);
query.AddParametre("@Country", entity.Country);
try
{
return(ConnectDB.ExecuteNonQuery(query) == 1);
}
catch (SqlException e)
{
return(false);
}
}