public static string RetrieveNameById(int id)
{
using (SqliteConnection db = OnelyDB.Open())
{
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "SELECT name FROM playlists WHERE id=@ID"
};
command.Parameters.AddWithValue("@ID", id);
var res = command.ExecuteReader();
if (!res.HasRows)
{
OnelyDB.Close(db);
return(null);
}
var name = String.Empty;
while (res.Read())
{
name = res.GetString(0);
}
return(name);
}
}
/// <summary>
/// Initializes the singleton application object. This is the first line of authored code
/// executed, and as such is the logical equivalent of main() or WinMain().
/// </summary>
public App()
{
this.InitializeComponent();
this.Suspending += OnSuspending;
OnelyDB.InitDb();
}
public static int Save(Playlist playlist, string name)
{
using (SqliteConnection db = OnelyDB.Open())
{
SqliteCommand command;
if (playlist.Name == null)
{
// Is name taken?
command = new SqliteCommand
{
Connection = db,
CommandText = "SELECT id FROM playlists WHERE name=@Name"
};
command.Parameters.AddWithValue("@Name", name);
var res = OnelyDB.ExecuteReader(command);
if (res.HasRows)
{
return(-1);
}
command = new SqliteCommand
{
Connection = db,
CommandText = "INSERT INTO playlists(name) VALUES (@Name)"
};
command.Parameters.AddWithValue("@Name", name);
OnelyDB.ExecuteReader(command);
playlist.Id = OnelyDB.GetLastInsertId(db);
playlist.Name = name;
}
else
{
if (name != playlist.Name)
{
command = new SqliteCommand
{
Connection = db,
CommandText = "UPDATE playlists SET name=@Name WHERE id=@ID"
};
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@ID", playlist.Id);
OnelyDB.ExecuteReader(command);
playlist.Name = name;
}
PlaylistItemStatic.DeleteBasedOnPlaylistId(playlist.Id, db);
}
int count = 0;
foreach (var item in playlist.Items)
{
PlaylistItemStatic.Save(item, playlist.Id, count, db);
count++;
}
OnelyDB.Close(db);
return(playlist.Id);
}
}
public static void DeleteBasedOnPlaylistId(int id, SqliteConnection db)
{
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "DELETE FROM playlist_items WHERE playlist_id=@ID"
};
command.Parameters.AddWithValue("@ID", id);
OnelyDB.ExecuteReader(command);
}
public static async Task <List <PlaylistItem> > RetrievePlaylistItemsByPlaylistId(int id)
{
using (SqliteConnection db = OnelyDB.Open())
{
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "SELECT audio_path, cover_path FROM playlist_items WHERE playlist_id=@ID ORDER BY sort_order ASC"
};
command.Parameters.AddWithValue("@ID", id);
var res = OnelyDB.ExecuteReader(command);
if (!res.HasRows)
{
return(null);
}
List <PlaylistItem> items = new List <PlaylistItem>();
List <AlbumCover> covers = new List <AlbumCover>();
while (res.Read())
{
var item = await LoadFromPath(res.GetString(0));
if (item != null)
{
items.Add(item);
var coverPath = res.GetString(1);
if ((item.Cover == null) && (!String.IsNullOrEmpty(coverPath)))
{
AlbumCover cover = GetExistingAlbumCover(coverPath, covers);
if (cover == null)
{
cover = await AlbumCover.FromPath(coverPath);
if (cover != null)
{
covers.Add(cover);
}
}
if (cover != null)
{
item.MainCover = cover;
}
}
}
}
covers.Clear();
OnelyDB.Close(db);
return(items);
}
}
public static void DeletePlaylistById(int id)
{
using (SqliteConnection db = OnelyDB.Open())
{
PlaylistItemStatic.DeleteBasedOnPlaylistId(id, db);
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "DELETE FROM playlists WHERE id=@ID"
};
command.Parameters.AddWithValue("@ID", id);
OnelyDB.ExecuteReader(command);
OnelyDB.Close(db);
}
}
public static void Save(PlaylistItem item, int PlaylistId, int sortOrder, SqliteConnection db)
{
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "INSERT INTO playlist_items(playlist_id, audio_path, cover_path, sort_order) VALUES (@PlaylistId, @Path, @CoverPath, @Sort)"
};
var coverPath = String.Empty;
if (item.MainCover != null)
{
coverPath = item.MainCover.CoverPath;
}
command.Parameters.AddWithValue("@PlaylistId", PlaylistId);
command.Parameters.AddWithValue("@Path", item.MusicPath);
command.Parameters.AddWithValue("@CoverPath", coverPath);
command.Parameters.AddWithValue("@Sort", sortOrder);
OnelyDB.ExecuteReader(command);
}
public static int GetDefaultPlaylistId()
{
using (SqliteConnection db = OnelyDB.Open())
{
int id = -1;
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "SELECT id FROM playlists WHERE name=@Default"
};
command.Parameters.AddWithValue("@Default", DefaultDBName);
var res = OnelyDB.ExecuteReader(command);
while (res.Read())
{
id = res.GetInt32(0);
}
return(id);
}
}
public static PlaylistReferenceCollection <PlaylistReference> GetSavedPlaylists()
{
using (SqliteConnection db = OnelyDB.Open())
{
PlaylistReferenceCollection <PlaylistReference> playlists = new PlaylistReferenceCollection <PlaylistReference>();
SqliteCommand command = new SqliteCommand
{
Connection = db,
CommandText = "SELECT id, name FROM playlists WHERE name != @Default ORDER BY name"
};
command.Parameters.AddWithValue("@Default", DefaultDBName);
var res = OnelyDB.ExecuteReader(command);
while (res.Read())
{
playlists.Add(new PlaylistReference(res.GetInt32(0), res.GetString(1)));
}
db.Close();
return(playlists);
}
}