public static async Task <List <vSong> > GetTablogicalResultsAsync()
{
List <vSong> songs = null;
try
{
var context = new MyJukeboxEntities();
await Task.Run(() =>
{
songs = context.vSongs
.Where(s =>
(s.Genre.Contains(TreeViewLogicStates.Genre)) &&
(s.Catalog.Contains(TreeViewLogicStates.Catalog)) &&
(s.Album.Contains(TreeViewLogicStates.Album)) &&
(s.Artist.Contains(TreeViewLogicStates.Artist))
).ToList();
});
return(songs);
}
catch (Exception ex)
{
Debug.Print($"GetTablogicalResultsAsync: {ex.Message}");
return(null);
}
}
public static bool EditSaveFileinfoChanges(int id, MP3Record record)
{
try
{
var context = new MyJukeboxEntities();
var file = context.tFileInfoes.Find(id);
if (file.FileSize != record.FileSize)
{
file.FileSize = record.FileSize;
}
if (file.FileDate != record.FileDate)
{
file.FileDate = record.FileDate;
}
if (file.Duration != record.Duration)
{
file.Duration = record.Duration;
}
context.SaveChanges();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public static string GetSongFieldValuesByID(int id)
{
using (var context = new MyJukeboxEntities())
{
var entity = context.Set <tSong>().Find(id);
var entry = context.Entry(entity);
var currentPropertyValues = entry.CurrentValues;
List <Tuple <string, object> > list = currentPropertyValues.PropertyNames
.Select(name => Tuple.Create(name, currentPropertyValues[name]))
.ToList();
string result = "";
int count = 0;
foreach (var song in list)
{
if (count > 5)
{
break;
}
result += song.Item2.ToString() + ",";
count++;
}
return(result.Substring(0, result.Length - 1));
}
}
public static async Task RefillMD5Table()
{
List <tMD5> rec = new List <tMD5>();
using (var context = new MyJukeboxEntities())
{
await Task.Run(() =>
{
var result = context.tSongs
.OrderBy(s => s.ID)
.Select(s => new { s.ID, s.Pfad, s.FileName });
foreach (var s in result)
{
string hash = Common.MD5($"{s.Pfad}{s.FileName}");
Debug.Print($"ID_Song={s.ID}, md5={hash}");
rec.Add(new tMD5 {
ID_Song = s.ID, MD5 = hash
});
}
context.tMD5.AddRange(rec);
context.SaveChanges();
});
}
}
public async Task RefillMD5Table()
{
List <tMD5> rec = new List <tMD5>();
using (var context = new MyJukeboxEntities())
{
await Task.Run(() =>
{
var result = context.tSongs
.OrderBy(s => s.ID)
.Select(s => new { s.ID, s.Pfad, s.FileName });
foreach (var s in result)
{
string hash = Helpers.MD5($"{s.Pfad}\\{s.FileName}");
Console.WriteLine($"ID_Song={s.ID}, md5={hash}");
rec.Add(new tMD5
{
ID_Song = s.ID,
MD5 = hash
}
);
}
context.tMD5.AddRange(rec);
context.SaveChanges();
Console.WriteLine($"{rec} records added.");
});
}
}
private void FillTables()
{
using (var context = new MyJukeboxEntities())
{
Label label;
TextBox textBox;
var settings = DataGetSet.GetParameters();
foreach (var setting in settings)
{
label = new Label();
label.Name = $"label{setting.ID}";
label.ForeColor = System.Drawing.Color.White;
label.AutoSize = true;
label.Anchor = AnchorStyles.Left;
label.Text = $"{setting.Name}";
tableLayoutPanel.Controls.Add(label);
textBox = new AutoSizeTextBox();
textBox.Name = $"{setting.Name}";
textBox.AutoSize = true;
textBox.Text = $"{(setting.Value.Length == 0 ? " " : setting.Value)}";
if (setting.Editable == false)
{
textBox.Enabled = false;
}
tableLayoutPanel.Controls.Add(textBox);
}
}
}
private static int SetTestRecord(MP3Record mp3Record)
{
int lastSongID = -1;
if (MD5Exist(mp3Record.MD5, true) == false)
{
try
{
var context = new MyJukeboxEntities();
// tsongs_tst data
var songs = new tSongs_tst();
songs.Album = mp3Record.Album;
songs.Artist = mp3Record.Artist;
songs.Titel = mp3Record.Titel;
songs.Pfad = mp3Record.Path;
songs.FileName = mp3Record.FileName;
songs.ID_Genre = mp3Record.Genre;
songs.ID_Catalog = mp3Record.Catalog;
songs.ID_Media = mp3Record.Media;
context.tSongs_tst.Add(songs);
context.SaveChanges();
lastSongID = GetLastID("tSongs_tst");
// tmd5 data
//var md5 = new tMD5_tst();
//md5.MD5 = mp3Record.MD5;
//md5.ID_Song = lastSongID;
//context.tMD5_tst.Add(md5);
//context.SaveChanges();
// tfileinfo data
//var file = new tFileInfos_tst();
//file.FileDate = mp3Record.FileDate;
//file.FileSize = mp3Record.FileSize;
//file.ImportDate = DateTime.Now;
//file.ID_Song = lastSongID;
//context.tFileInfos_tst.Add(file);
//context.SaveChanges();
// tinfos data
//var info = new tInfos_tst();
//info.Sampler = mp3Record.IsSample;
//info.ID_Song = lastSongID;
//context.tInfos_tst.Add(info);
//context.SaveChanges();
return(1);
}
catch (Exception ex)
{
Debug.Print($"SetNewTestRecord_Error: {ex.Message}");
return(0);
}
}
else
{
return(0);
}
}
public async Task CreareNewDbFullathListAsync()
{
var context = new MyJukeboxEntities();
await Task.Run(() =>
{
var result = context.tSongs
.Select(s => new { s.Pfad, s.FileName })
.OrderBy(s => new { s.Pfad, s.FileName })
.ToList();
if (result == null)
{
return;
}
string documents = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\MyJukebox_tSongs.rpt";
if (File.Exists(documents))
{
File.Delete(documents);
}
using (StreamWriter sr = new StreamWriter(documents))
{
foreach (var s in result)
{
var fullpath = $"{s.Pfad}\\{s.FileName}";
sr.WriteLine(fullpath);
}
}
});
}
private static bool MD5Exist(string MD5, bool testmode = false)
{
object result = null;
var context = new MyJukeboxEntities();
if (testmode == false)
{
result = context.tMD5
.Where(m => m.MD5 == MD5).FirstOrDefault();
}
else
{
result = context.tMD5_tst
.Where(m => m.MD5 == MD5).FirstOrDefault();
}
if (result != null)
{
var md5 = (tMD5)result;
WriteLog($"title allready exist! SongID={md5.ID_Song}, MD5={md5.MD5})");
Debug.Print($"title allready exist! (MD5={result})");
return(true);
}
else
{
return(false);
}
}
public static Exception RemoveSongFromPlaylist(int idSong, int idPlaylist)
{
try
{
var context = new MyJukeboxEntities();
var playlist = context.tPlaylists
.Where(p => p.ID == idPlaylist)
.FirstOrDefault();
if (playlist == null)
{
throw new Exception($"Playlist {idPlaylist} not found!");
}
var entry = context.tPLentries
.Where(p => p.PLID == idPlaylist && p.SongID == idSong)
.FirstOrDefault();
if (entry == null)
{
throw new Exception($"Song not exist in the Playlist '{playlist.Name}'");
}
context.tPLentries.Remove(entry);
context.SaveChanges();
return(new Exception(null));
}
catch (Exception ex)
{
return(ex);
}
}
public static void DeleteSong(int id)
{
var context = new MyJukeboxEntities();
var songs = context.tSongs.First(s => s.ID == id);
context.tSongs.Remove(songs);
context.SaveChanges();
}
public static List <string> GetGenres()
{
List <string> genres = null;
var context = new MyJukeboxEntities();
genres = context.tGenres.Select(g => g.Name).ToList();
return(genres);
}
internal static List <tColumn> GetColumsWidth()
{
var context = new MyJukeboxEntities();
var result = context.tColumns
.Select(c => c).ToList();
return(result);
}
public static List <string> GetCatalogs()
{
List <string> catalogues = null;
using (var context = new MyJukeboxEntities())
{
catalogues = context.tCatalogs.Select(c => c.Name).ToList();
return(catalogues);
}
}
public static List <string> GetMedias()
{
List <string> medias = null;
using (var context = new MyJukeboxEntities())
{
medias = context.tMedias.Select(m => m.Type).ToList();
return(medias);
}
}
public static int GetGenreFromString(string genre)
{
List <int> genres;
var context = new MyJukeboxEntities();
genres = context.tGenres
.Where(g => g.Name == genre)
.Select(g => g.ID).ToList();
return(genres[0]);
}
internal static int AddNewPlaylist(string playlistname)
{
var context = new MyJukeboxEntities();
tPlaylist playlist = new tPlaylist {
Name = playlistname, Last = false
};
context.tPlaylists.Add(playlist);
context.SaveChanges();
return(playlist.ID);
}
public static void SaveParameters(List <Setting> settings)
{
var context = new MyJukeboxEntities();
foreach (Setting setting in settings)
{
var result = context.tSettings.SingleOrDefault(p => p.Name == setting.Name);
result.Value = setting.Value;
}
context.SaveChanges();
}
public static async Task <List <string> > GetGenresFullAsync()
{
List <string> genres = null;
var context = new MyJukeboxEntities();
await Task.Run(() =>
{
genres = context.tGenres
.Select(g => g.Name).ToList();
});
return(genres);
}
public static List <string> GetQueryList()
{
List <string> queries = null;
var context = new MyJukeboxEntities();
queries = context.tQueries
.Select(q => q.Query)
.OrderBy(q => q).ToList();
return(queries);
}
static SettingsDb()
{
var context = new MyJukeboxEntities();
var settings = context.tSettings
.Select(s => s).ToList();
Settings = new Dictionary <string, object>();
foreach (var s in settings)
{
Settings.Add(s.Name, s.Value);
}
}
public static List <SongList> GetSongs2(string catalog, string album)
{
var context = new MyJukeboxEntities();
string query = $"SELECT * FROM vSongs WHERE Catalog = '{catalog}' AND Album = '{album}' ";
ObjectContext objectContext = new ObjectContext("name=MyJukeboxEntities");
var songs = objectContext.ExecuteStoreQuery <SongList>(query).ToList();
return(songs);
}
public static void SetRating(int id, int rating)
{
var context = new MyJukeboxEntities();
var result = context.tInfos.SingleOrDefault(s => s.ID_Song == id);
if (result != null)
{
result.Rating = rating;
context.SaveChanges();
}
;
}
public static List <vSongs> GetSongs(string catalog, string album)
{
List <vSongs> songs = null;
var context = new MyJukeboxEntities();
songs = context.vSongs
.Select(s => s)
.Where(s => s.Catalog == catalog &&
s.Album == album)
.ToList();
return(songs);
}
public static async Task <List <string> > GetCatalogsAsync()
{
List <string> catalogues = null;
using (var context = new MyJukeboxEntities())
{
await Task.Run(() =>
{
catalogues = context.tCatalogs.Select(c => c.Name).ToList();
});
return(catalogues);
}
}
public static async Task <List <string> > GetMediaAsync()
{
List <string> medias = null;
using (var context = new MyJukeboxEntities())
{
await Task.Run(() =>
{
medias = context.tMedias.Select(m => m.Type).ToList();
});
return(medias);
}
}
internal static int GetLastselectedPlaylist()
{
var context = new MyJukeboxEntities();
var result = context.tPlaylists.SingleOrDefault(n => n.Last == true);
if (result != null)
{
return(result.ID);
}
else
{
return(1);
}
}
internal static int GetLastselectedPlaylistRow(int currentPlaylist)
{
var context = new MyJukeboxEntities();
var result = context.tPlaylists.SingleOrDefault(n => n.ID == currentPlaylist);
if (result != null && result.Row != null)
{
return((int)result.Row);
}
else
{
return(2);
}
}
public static int CreateCatalog(string catalog)
{
int idDbo = -1;
int idTst = -1;
var context = new MyJukeboxEntities();
#region create new catalog on [dbo]
var catalogDboExist = context.tCatalogs
.Where(c => c.Name == catalog)
.FirstOrDefault();
if (catalogDboExist == null)
{
context.tCatalogs
.Add(new tCatalog {
Name = catalog
});
context.SaveChanges();
idDbo = GetLastID("tCatalogs");
}
#endregion
#region create new catalog on [tst]
var catalogTstExist = context.tCatalogs
.Where(c => c.Name == catalog)
.FirstOrDefault();
if (catalogTstExist == null)
{
context.tCatalogs
.Add(new tCatalog {
Name = catalog
});
context.SaveChanges();
idTst = GetLastID("tCatalogs");
}
#endregion
if (idDbo == idTst)
{
return(idDbo);
}
else
{
return(-1);
}
}
public static void AddSongToPlaylist(int id, int playlistID)
{
var context = new MyJukeboxEntities();
var entry = new tPLentries()
{
PLID = playlistID,
SongID = id,
Pos = 1
};
context.tPLentries.Add(entry);
context.SaveChanges();
}
