//Selecionar todos os registros
public List <MusicVO> GetAllMusic()
{
var listMusic = new List <MusicVO>();
using (connection = new Connection())
{
List <SqlParameter> parameters = new List <SqlParameter>();
var strQuery = @"SELECT * FROM TBMUSIC";
SqlDataReader reader = connection.ExecuteSelect(strQuery, parameters);
while (reader.Read())
{
var music = new MusicVO
{
Id = int.Parse(reader["Id_Music"].ToString()),
Title = reader["Title"].ToString(),
Genre = reader["Genre"].ToString(),
Duration = reader["Duration"].ToString(),
IdAlbum = int.Parse(reader["Id_Album"].ToString())
};
listMusic.Add(music);
}
reader.Close();
}
return(listMusic);
}
//Selecionar apenas por id
public MusicVO GetForId(int id)
{
using (connection = new Connection())
{
var music = new MusicVO();
List <SqlParameter> parameters = new List <SqlParameter>();
var strQuery = @"SELECT * FROM TBMUSIC WHERE Id_Music=@Id_Music";
var idMusic = new SqlParameter("Id_Music", id);
parameters.Add(idMusic);
SqlDataReader reader = connection.ExecuteSelect(strQuery, parameters);
while (reader.Read())
{
music.Id = int.Parse(reader["Id_Music"].ToString());
music.Title = reader["Title"].ToString();
music.Genre = reader["Genre"].ToString();
music.Duration = reader["Duration"].ToString();
music.IdAlbum = int.Parse(reader["Id_Album"].ToString());
}
return(music);
}
}
//Método de inserção
public void Insert(MusicVO music)
{
List <SqlParameter> parameters = new List <SqlParameter>();
var strQuery = @"INSERT INTO TBMUSIC(Title,Genre,Duration,Id_Album) VALUES(@Title,@Genre,@Duration,@Id_Album)";
var title = new SqlParameter("Title", music.Title);
var genre = new SqlParameter("Genre", music.Genre);
var duration = new SqlParameter("Duration", music.Duration);
var idAlbum = new SqlParameter("Id_Album", music.IdAlbum);
parameters.Add(title);
parameters.Add(genre);
parameters.Add(duration);
parameters.Add(idAlbum);
using (connection = new Connection()){
connection.ExecuteQry(strQuery, parameters);
}
}
//Método de alteração
public void Update(MusicVO music)
{
List <SqlParameter> parameters = new List <SqlParameter>();
var strQuery = @"UPDATE TBMUSIC SET Title=@Title,Genre=@Genre,Duration=@Duration WHERE Id_Music=@Id_Music";
var title = new SqlParameter("Title", music.Title);
var genre = new SqlParameter("Genre", music.Genre);
var duration = new SqlParameter("Duration", music.Duration);
var idMusic = new SqlParameter("Id_Music", music.Id);
parameters.Add(title);
parameters.Add(genre);
parameters.Add(duration);
parameters.Add(idMusic);
using (connection = new Connection()){
connection.ExecuteQry(strQuery, parameters);
}
}
//*******Métodos Auxiliares********//
//Inserir as músicas que estão nas listas cache no banco
private void InsertMusicBD(List <MusicModel> musicList, int idAlbum)
{
if (idAlbum == 0)
{
//Busca o ultimo ID salvo
idAlbum = albumBusiness.GetLastId();
}
//Percorre a lista de musica de cache para inserir no banco
foreach (var music in musicList)
{
var musicVO = new MusicVO()
{
Id = music.Id,
IdAlbum = idAlbum,
Title = music.Title,
Duration = music.Duration,
Genre = music.Genre
};
musicBusiness.Insert(musicVO);
}
}
public void Update(MusicVO music)
{
_musicRepository.Update(music);
}
public void Insert(MusicVO music)
{
_musicRepository.Insert(music);
}
