public void RemoveSongsFromPool(Song song, int pool_id)
{
RemoveSongsFromPool(new Song[1] {
song
}, pool_id);
}
/// <summary>
/// Load all the songs from a pool, ordered and filtered if you want.
/// </summary>
/// <param name="poolname"></param>
/// <param name="order_by"></param>
/// <param name="filters"></param>
/// <returns></returns>
public List <Song> LoadSongs(string poolname, IEnumerable <string> order_by, IEnumerable <Filter> filters)
{
List <Song> result = new List <Song>();
using (SQLiteCommand sqc = new SQLiteCommand(connection))
{
// Process the filters to WHEREs
List <string> wheres = new List <string>();
foreach (Filter f in filters)
{
wheres.Add(((f.Not_Flag) ? "NOT " : "") + "LOWER(" + f.Key + ")" + f.Comparetype + "?");
sqc.Parameters.Add(new SQLiteParameter(f.Key, f.Value.ToLower()));
}
sqc.CommandText = "SELECT Songs.id AS SID, Songs.Artists, Songs.Title, Songs.Genres, Songs.TrackNr, Songs.Copyright, Songs.Comment, Songs.Composer, Songs.Conductor, Songs.Lyrics, Songs.BPM, Songs.Version, Songs.PlayCount, Songs.SkipCount, Songs.Rating, Albums.id AS AID, Albums.Name AS Album, Albums.AlbumArtists, Albums.TrackCount, Albums.Year FROM Poolsongs INNER JOIN Songs ON Poolsongs.idSong=Songs.id INNER JOIN Albums ON Albums.id=Songs.idAlbum WHERE Poolsongs.idPool=(SELECT id FROM Songpools WHERE Songpools.Name=:Poolname)" + String.Join(" AND ", wheres);
sqc.Parameters.Add(new SQLiteParameter("Poolname", poolname));
if (order_by.Count() > 0)
{
sqc.CommandText += " ORDER BY " + String.Join(", ", order_by);
}
// Process the result: Create song objects
using (SQLiteDataReader sqr = sqc.ExecuteReader())
{
while (sqr.Read())
{
Song tempsong = new Song();
for (int i = 0; i < sqr.FieldCount; i++)
{
if (!sqr.IsDBNull(i))
{
switch (sqr.GetName(i))
{
case "SID":
tempsong.id = Convert.ToUInt32(sqr.GetInt32(i));
break;
case "AID":
if (tempsong.Album == null)
{
tempsong.Album = new CAlbum();
}
tempsong.Album.id = Convert.ToUInt32(sqr.GetValue(i));
break;
case "Album":
if (tempsong.Album == null)
{
tempsong.Album = new CAlbum();
}
tempsong.Album.Name = (string)sqr.GetValue(i);
break;
case "AlbumArtists":
case "TrackCount":
case "Year":
if (tempsong.Album != null)
{
tempsong.Album[sqr.GetName(i)] = sqr.GetValue(i);
}
break;
default:
tempsong[sqr.GetName(i)] = sqr.GetValue(i);
break;
}
}
}
result.Add(tempsong);
}
}
return(result);
}
}