/// <summary>
/// This method will insert every missing column for each table into the database. Will only be called if HasallColumns() returns false. This means the user has an outdated .db file
/// </summary>
public static void InsertNewColumns()
{
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
//every column that SHOULD exist
var downloadHistoryNames = typeof(DownloadHistory).GetProperties().Select(property => property.Name).ToArray();
var settingNames = typeof(Settings).GetProperties().Select(property => property.Name).ToArray();
foreach (string column in downloadHistoryNames)
{
if (!HasColumn(column, "DownloadHistory"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE DownloadHistory ADD COLUMN " + column + " " + GetDownloadHistoryColumnSqlType(column));
}
}
foreach (string column in settingNames)
{
if (!HasColumn(column, "settings"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE SETTINGS ADD COLUMN " + column + " " + GetSettingColumnSqlType(column));
}
}
db.SaveChanges();
db.Dispose();
}
}
private static void RefreshSettings()
{
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
int count = db.Settings.Where(o => o.Id >= 0).Count();
if (count == 0)
{
settings = new Settings();
settings.AutomaticDownload = "false";
settings.MP3Path = Environment.GetFolderPath(Environment.SpecialFolder.MyMusic);
settings.VideoPath = Environment.GetFolderPath(Environment.SpecialFolder.MyVideos);
settings.SoundFile = "";
settings.PlaySound = "true";
settings.KeepMp4 = "false";
settings.ShowNotification = "true";
settings.CopyFromClipboard = "false";
UpdateSettings(settings);
}
else
{
settings = (from s in db.Settings select s).ToList().FirstOrDefault();
}
db.Dispose();
}
}
/// <summary>
/// Inserts all missing tables into the user's .db file
/// </summary>
public static void InsertMissingTables()
{
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
SQLiteConnection conn = new SQLiteConnection();
conn.ConnectionString = "data source = " + DB_FILE;
conn.Open();
SQLiteCommand tableDownloadHistory = new SQLiteCommand();
SQLiteCommand tableSettings = new SQLiteCommand();
tableDownloadHistory.CommandText = TABLE_DOWNLOAD_HISTORY;
tableSettings.CommandText = TABLE_SETTINGS;
tableDownloadHistory.Connection = conn;
tableSettings.Connection = conn;
if (!HasTable("DownloadHistory", db))
{
tableDownloadHistory.ExecuteNonQuery();
}
if (!HasTable("Settings", db))
{
tableSettings.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();
db.Dispose();
}
}
private static void RefreshCacheList()
{
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
localHistory = (from s in db.DownloadHistory select s).ToList();
db.Dispose();
}
}
/// <summary>
/// Checks wether the table x has column x
/// </summary>
/// <param name="columnName">The column you want to check on</param>
/// <param name="table">The table you want to check on</param>
/// <returns></returns>
public static bool HasColumn(string columnName, string table)
{
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
try
{
var t = db.Database.SqlQuery <object>("SELECT " + columnName + " FROM " + table).ToList();
db.Dispose();
return(true);
}
catch (SQLiteException ex)
{
db.Dispose();
//if (ex.Message.ToLower().Contains("no such column"))
//{
return(false);
//}
}
}
}
public static void UpdateSettings(Settings set)
{
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
db.Settings.Attach(set);
var entry = db.Entry(set);
entry.State = System.Data.Entity.EntityState.Modified; //Mark it for update
db.SaveChanges(); //push to database
db.Dispose();
}
else
{//The settings table is still empty
db.Settings.Add(set);
db.SaveChanges();
db.Dispose();
}
}
}
public static bool IsPlaySound()
{
string playSound = "";
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
playSound = (from g in db.Settings select g.PlaySound).SingleOrDefault();
db.Dispose();
}
else
{
RefreshSettings();
}
}
return(playSound == "true");
}
public static string GetSoundFile()
{
string file = "";
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
file = (from g in db.Settings select g.SoundFile).SingleOrDefault();
db.Dispose();
}
else
{
RefreshSettings();
}
}
return(file);
}
public static string GetMP3Path()
{
string path = "";
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
path = (from g in db.Settings select g.MP3Path).SingleOrDefault();
db.Dispose();
}
else
{
RefreshSettings();
}
}
return(path);
}
public static string GetVideoType()
{
string type = "";
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
type = (from g in db.Settings select g.VideoFormat).SingleOrDefault();
db.Dispose();
}
else
{
RefreshSettings();
}
}
return(type);
}
public static bool KeepMp4()
{
string keep = "";
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
keep = (from g in db.Settings select g.KeepMp4).SingleOrDefault();
db.Dispose();
}
else
{
RefreshSettings();
}
}
return(keep == "true");
}
public static bool IsShowNotification()
{
string notification = "";
using (Youtube2Mp3DatabaseEntities db = new Youtube2Mp3DatabaseEntities())
{
var count = db.Settings.Where(o => o.Id >= 0).Count();
if (count > 0)
{
notification = (from g in db.Settings select g.ShowNotification).SingleOrDefault();
db.Dispose();
}
else
{
RefreshSettings();
}
}
return(notification == "true");
}
/// <summary>
/// Saves pending changes to the database, disposes it, and refreshes the local cache list
/// </summary>
/// <param name="db"></param>
private static void SaveAndCloseDataBase(Youtube2Mp3DatabaseEntities db)
{
db.SaveChanges();
RefreshCacheList();
db.Dispose();
}