public static void DeleteSongFromDatabase(string musicKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute($"DELETE FROM MUSIC WHERE SongKey = '{musicKey}'");; } }
public static List <PlaylistModel> GetAllPlaylist() { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <PlaylistModel>("select * from Playlist", new DynamicParameters()); return(output.ToList()); } }
public static void DropPlaylistFromDatabase(string playlistKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute($"DELETE FROM PLAYLIST WHERE PlaylistKey = '{playlistKey}'");; cnn.Execute($"DELETE FROM SongInPlaylist WHERE PlaylistKey = '{playlistKey}'"); } }
public static void InsertSongToDatabase(SongModel song) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute("insert into Music(SongKey,SongName,SongLocation,Title,ContributingArtists,Album,Genre,Length,LyricPath) " + "values(@SongKey,@SongName,@SongLocation,@Title,@ContributingArtists,@Album,@Genre,@Length,@LyricPath)" , song); } }
public static void DeleteSongFromPlaylist(string songKey, string playlistKey) { string sql = $@"Delete from SongInPlaylist where SongKey = '{songKey}' and PlaylistKey = '{playlistKey}'"; using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute(sql); } }
public static void InsertNewPlaylistToDatabase(PlaylistModel playlist) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute("insert into Playlist(PlaylistKey,PlaylistName,Description,ImagePath) " + "values(@PlaylistKey,@PlaylistName, @Description,@ImagePath)" , playlist); } }
public static int GetAmountOfSongInPlaylist(string playlistKey) { string sql = $@"select count(*) from SongInPlaylist where PlaylistKey = '{playlistKey}'"; using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.ExecuteScalar(sql); return(Convert.ToInt32(output)); } }
public static void UpdatePlaylist(PlaylistModel playlist) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute("UPDATE Playlist " + "SET PlaylistName = @PlaylistName, ImagePath = @ImagePath, Description = @Description " + "WHERE PlaylistKey = @PlaylistKey", playlist); } }
public static void UpdateSong(SongModel song) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute("UPDATE Music " + "SET Title = @Title, Genre = @Genre, ContributingArtists = @ContributingArtists, Album = @Album, LyricPath = @LyricPath " + "WHERE SongKey = @SongKey", song); } }
public static void InsertSongToFavorite(string songKey) { string sql = $@"insert into SongInPlaylist (SongKey, PlaylistKey) values ('{songKey}', 'FAVORITE_PLAYLIST')"; using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute(sql); cnn.Execute("UPDATE Music " + "SET IsFavorite = 1 " + $"WHERE SongKey = '{songKey}'"); } }
public static void RemoveSongFromFavorite(string songKey) { string sql = $@"Delete from SongInPlaylist where SongKey = '{songKey}' and PlaylistKey = 'FAVORITE_PLAYLIST'"; using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute(sql); cnn.Execute("UPDATE Music " + "SET IsFavorite = 0 " + $"WHERE SongKey = '{songKey}'"); } }
public static List <SongModel> GetAllSongInPlaylist(string playlistKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <SongModel>( $@" select * from Music,SongInPlaylist where Music.SongKey = SongInPlaylist.SongKey and SongInPlaylist.PlaylistKey = '{playlistKey}' " , new DynamicParameters()); List <SongModel> list = output.ToList(); return(list); } }
public static void InsertSongToPlaylist(string songKey, string playlistKey) { string sql = $@"insert into SongInPlaylist (SongKey, PlaylistKey) values ('{songKey}', '{playlistKey}')"; try { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { cnn.Execute(sql); } } catch (Exception e) { return; } }
public static PlaylistModel GetPlaylist(string playlistKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <PlaylistModel>($"select * from Playlist where PlaylistKey = '{playlistKey}'", new DynamicParameters()); List <PlaylistModel> list = output.ToList(); if (list.Count > 0) { return(list[0]); } else { return(null); } } }
public static bool CheckSongExistInPlaylist(string SongKey, string PlaylistKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <SongModel>($@"select SongKey from SongInPlaylist where SongKey = '{SongKey}' and PlaylistKey = '{PlaylistKey}'", new DynamicParameters()); List <SongModel> list = output.ToList(); if (list.Count > 0) { return(true); } else { return(false); } } }
public static SongModel GetSongFromDatabase(string SongKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <SongModel>($@"select * from Music where SongKey = '{SongKey}' ", new DynamicParameters()); List <SongModel> list = output.ToList(); if (list.Count > 0) { return(list[0]); } else { return(null); } } }
public static bool CheckSongExistInDatabase(string SongName) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <SongModel>($@"select * from Music where SongName = '{SongName}' ", new DynamicParameters()); List <SongModel> list = output.ToList(); if (list.Count > 0) { return(true); } else { return(false); } } }
public static void CreateRequiredTable() { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { string sql = @" CREATE TABLE if not exists Music( SongKey TEXT PRIMARY KEY NOT NULL, SongName TEXT, SongLocation TEXT, Title TEXT, ContributingArtists TEXT, Album TEXT, Genre TEXT, Length TEXT, LyricPath TEXT, IsFavorite NUMERIC ); CREATE TABLE if not exists Playlist( PlaylistKey TEXT PRIMARY KEY NOT NULL, PlaylistName TEXT, Description TEXT, ImagePath TEXT, Amount NUMBER ); CREATE TABLE if not exists SongInPlaylist( SongKey TEXT, PlaylistKey TEXT, Description TEXT, FOREIGN KEY(SongKey) REFERENCES Music(SongKey), FOREIGN KEY(PlaylistKey) REFERENCES Playlist(PlaylistKey), PRIMARY KEY(SongKey,PlaylistKey) ); insert into Playlist(PlaylistKey,PlaylistName,Description,ImagePath) values('FAVORITE_PLAYLIST','Favorite Song','','FavoriteSongImage.jpg') "; cnn.Execute(sql); } }
public static List <SongModel> GetAllSongListWithPlaylistInfo(string playlistKey) { using (IDbConnection cnn = new SQLiteConnection(BaseDataProvider.LoadConnectionString())) { var output = cnn.Query <SongModel>( $@" SELECT * FROM Music LEFT JOIN ( select * from SongInPlaylist where PlaylistKey = '{playlistKey}' ) as AllSongInPL ON Music.SongKey = AllSongInPL.SongKey ORDER by PlaylistKey desc " , new DynamicParameters()); List <SongModel> list = output.ToList(); return(list); } }