/// <summary>
/// Insert advanced Reminder properties into the database
/// </summary>
/// <param name="avr">The avr object</param>
/// <returns></returns>
public static long InsertAVRProperties(AdvancedReminderProperties avr)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
if (db.AdvancedReminderProperties.Where(r => r.Remid == avr.Remid).Count() > 0)
{
//Exists already. update.
db.AdvancedReminderProperties.Attach(avr);
var entry = db.Entry(avr);
entry.State = System.Data.Entity.EntityState.Modified; //Mark it for update
db.SaveChanges();
db.Dispose();
}
else
{
if (db.AdvancedReminderProperties.Count() > 0)
{
avr.Id = db.AdvancedReminderProperties.Max(i => i.Id) + 1;
}
db.AdvancedReminderProperties.Add(avr);
db.SaveChanges();
db.Dispose();
}
}
return(avr.Id);
}
/// <summary>
/// Insert advanced Reminder HttpRequest into the database
/// </summary>
/// <param name="http">The avr object</param>
/// <returns></returns>
public static long InsertHttpRequest(HttpRequests http)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
if (db.HttpRequests.Where(r => r.reminderId == http.reminderId).Count() > 0)
{
//Exists already. update.
db.HttpRequests.Attach(http);
var entry = db.Entry(http);
entry.State = System.Data.Entity.EntityState.Modified; //Mark it for update
db.SaveChanges();
db.Dispose();
}
else
{
if (db.HttpRequests.Count() > 0)
{
http.Id = db.HttpRequests.Max(i => i.Id) + 1;
}
db.HttpRequests.Add(http);
db.SaveChanges();
db.Dispose();
}
}
return(http.Id);
}
/// <summary>
/// Marks an message as read.
/// </summary>
/// <param name="message">The message to be marked</param>
/// <returns></returns>
public static bool MarkMessageRead(Database.Entity.RemindMeMessages message)
{
try
{
ReadMessages msg = new ReadMessages();
msg.ReadMessageId = message.Id;
msg.MessageText = message.Message;
msg.ReadDate = message.DateOfCreation.Value.ToString();
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
if (db.ReadMessages.Count() > 0)
{
msg.Id = db.ReadMessages.Max(i => i.Id) + 1;
}
else
{
msg.Id = 0;
}
db.ReadMessages.Add(msg);
db.SaveChanges();
db.Dispose();
return(true);
}
}
catch
{
return(false);
}
}
/// <summary>
/// Gets the amount of button spaces for 1 button, right now that's the same for all, but maybe that could change in the future
/// </summary>
/// <returns></returns>
public static int GetButtonSpacing()
{
int spacing = -1;
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
int count = db.ButtonSpaces.Where(o => o.Id >= 0).Count();
if (count == 0)
{
ButtonSpaces spaceObj = new ButtonSpaces();
spaceObj.Id = 0;
spaceObj.Reminders = 5;
//spaceObj.MessageCenter = 5;
//more buttons possibly
spacing = 5;
db.ButtonSpaces.Add(spaceObj);
db.SaveChanges();
}
else
{
spacing = Convert.ToInt32((from b in db.ButtonSpaces select b.Reminders).SingleOrDefault());
}
db.Dispose();
}
return(spacing);
}
public static void DeleteAvrProperties(AdvancedReminderProperties prop)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
db.AdvancedReminderProperties.Attach(prop);
db.AdvancedReminderProperties.Remove(prop);
db.SaveChanges();
db.Dispose();
}
}
public static void UpdateButtonSpacing(ButtonSpaces btn)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
var count = db.ButtonSpaces.Where(o => o.Id >= 0).Count();
if (count == 1)
{
db.ButtonSpaces.Attach(btn);
var entry = db.Entry(btn);
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.ButtonSpaces.Add(btn);
db.SaveChanges();
db.Dispose();
}
}
}
/// <summary>
/// Update an existing hotkey combination in the SQLite database
/// </summary>
/// <param name="hotkey">The hotkey object</param>
private static void UpdateHotkey(Hotkeys hotkey)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
var count = db.Hotkeys.Where(o => o.Id >= hotkey.Id).Count();
if (count == 1)
{
db.Hotkeys.Attach(hotkey);
var entry = db.Entry(hotkey);
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.Hotkeys.Add(hotkey);
db.SaveChanges();
db.Dispose();
}
}
}
public static void UpdateHttpRequest(HttpRequestCondition cond)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
var count = db.HttpRequestCondition.Where(o => o.Id >= 0).Count();
if (count > 0)
{
db.HttpRequestCondition.Attach(cond);
var entry = db.Entry(cond);
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.HttpRequestCondition.Add(cond);
db.SaveChanges();
db.Dispose();
}
}
}
/// <summary>
/// Delete all conditions that are paired to a HttpRequest
/// </summary>
/// <param name="httpRequestId"></param>
public static void DeleteConditionsForHttpRequest(long httpRequestId)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
foreach (HttpRequestCondition cond in db.HttpRequestCondition.Where(r => r.RequestId == httpRequestId).ToList())
{
db.HttpRequestCondition.Attach(cond);
db.HttpRequestCondition.Remove(cond);
}
db.SaveChanges();
db.Dispose();
}
}
/// <summary>
/// Delete advanced reminder file(s)/folder(s) options (delete/open) for a specific reminder
/// </summary>
/// <param name="id">The ID of the avr record in the SQLite database</param>
public static void DeleteHttpRequestById(long id)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
foreach (HttpRequests http in db.HttpRequests.Where(r => r.Id == id).ToList())
{
db.HttpRequests.Attach(http);
db.HttpRequests.Remove(http);
}
db.SaveChanges();
db.Dispose();
}
}
/// <summary>
/// Removes a message that is marked as read from the local database
/// </summary>
/// <param name="onlineMessageId">the Id of the online message in the database</param>
public static void DeleteMessage(int onlineMessageId)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
ReadMessages readMess = db.ReadMessages.Where(rm => rm.ReadMessageId == onlineMessageId).FirstOrDefault();
if (readMess != null)
{
db.ReadMessages.Attach(readMess);
db.ReadMessages.Remove(readMess);
db.SaveChanges();
db.Dispose();
}
}
}
/// <summary>
/// Insert a hotkey combination into the SQLite database
/// </summary>
/// <param name="hotkey">The hotkey object</param>
public static void InsertHotkey(Hotkeys hotkey)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
if (db.Hotkeys.Count() > 0)
{
hotkey.Id = db.Reminder.Max(i => i.Id) + 1;
}
db.Hotkeys.Add(hotkey);
db.SaveChanges();
db.Dispose();
}
}
/// <summary>
/// Insert a hotkey combination into the SQLite database
/// </summary>
/// <param name="hotkey">The hotkey object</param>
public static void InsertTheme(Themes theme)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
if (db.Themes.Count() > 0)
{
theme.Id = db.Themes.Max(i => i.Id) + 1;
}
db.Themes.Add(theme);
db.SaveChanges();
db.Dispose();
}
}
/// <summary>
/// Delete advanced reminder file(s)/folder(s) options (delete/open) for a specific reminder
/// </summary>
/// <param name="id">The ID of the avr record in the SQLite database</param>
public static void DeleteAvrFilesFoldersById(long id)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
foreach (AdvancedReminderFilesFolders avr in db.AdvancedReminderFilesFolders.Where(r => r.Remid == id).ToList())
{
db.AdvancedReminderFilesFolders.Attach(avr);
db.AdvancedReminderFilesFolders.Remove(avr);
}
db.SaveChanges();
db.Dispose();
}
}
public static Themes DeleteThemeById(long id)
{
Themes theme = null;
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
theme = (from g in db.Themes select g).Where(i => i.Id == id).SingleOrDefault();
db.Themes.Attach(theme);
db.Themes.Remove(theme);
db.SaveChanges();
db.Dispose();
}
return(theme);
}
/// <summary>
/// Delete Avr properties of a specific reminder
/// </summary>
/// <param name="id">Id of the avr properties record in the SQLite database</param>
public static void DeleteAvrProperties(long id)
{
AdvancedReminderProperties prop = GetAVRProperties(id);
if (prop == null)
{
return;
}
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
db.AdvancedReminderProperties.Attach(prop);
db.AdvancedReminderProperties.Remove(prop);
db.SaveChanges();
db.Dispose();
}
}
/// <summary>
/// Insert a new Request condition into the db
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public static long InsertCondition(HttpRequestCondition condition)
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
/*if (db.HttpRequestCondition.Where(r => r.Id == condition.Id).Count() > 0)
* {
* throw new ArgumentException("Could not insert HttpRequestCondition with ID " + condition.Id + " because it already exists");
* }
* else*/
{
if (db.HttpRequestCondition.Count() > 0)
{
condition.Id = db.HttpRequests.Max(i => i.Id) + 1;
}
db.HttpRequestCondition.Add(condition);
db.SaveChanges();
db.Dispose();
}
}
return(condition.Id);
}
private static void RefreshDimensions()
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
int count = db.PopupDimensions.Where(o => o.Id >= 0).Count();
if (count == 0) //no dimensions yet.
{
dimensions = new PopupDimensions();
dimensions.FormWidth = DEFAULT_FORM_WIDTH;
dimensions.FormHeight = DEFAULT_FORM_HEIGHT;
dimensions.FontTitleSize = DEFAULT_FONT_TITLE_SIZE;
dimensions.FontNoteSize = DEFAULT_FONT_NOTE_SIZE;
UpdatePopupDimensions(dimensions);
}
else
{
dimensions = (from s in db.PopupDimensions select s).ToList().FirstOrDefault();
}
db.SaveChanges();
db.Dispose();
}
}
private static void RefreshDimensions()
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
int count = db.ListviewColumnSizes.Where(o => o.Id >= 0).Count();
if (count == 0) //no dimensions yet.
{
sizes = new ListviewColumnSizes();
sizes.Title = DEFAULT_TITLE_WIDTH;
sizes.Date = DEFAULT_DATE_WIDTH;
sizes.Repeating = DEFAULT_REPEAT_WIDTH;
sizes.Enabled = DEFAULT_ENABLED_WIDTH;
UpdateListviewSizes(sizes);
}
else
{
sizes = (from s in db.ListviewColumnSizes select s).ToList().FirstOrDefault();
}
db.SaveChanges();
db.Dispose();
}
}
/// <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 (RemindMeDbEntities db = new RemindMeDbEntities())
{
//every column that SHOULD exist
var reminderNames = typeof(Reminder).GetProperties().Select(property => property.Name).ToArray();
var settingNames = typeof(Settings).GetProperties().Select(property => property.Name).ToArray();
var songNames = typeof(Songs).GetProperties().Select(property => property.Name).ToArray();
var popupDimensionsNames = typeof(PopupDimensions).GetProperties().Select(property => property.Name).ToArray();
var lvColumnSizes = typeof(ListviewColumnSizes).GetProperties().Select(property => property.Name).ToArray();
var hotkeys = typeof(Hotkeys).GetProperties().Select(property => property.Name).ToArray();
var avrProperties = typeof(AdvancedReminderProperties).GetProperties().Select(property => property.Name).ToArray();
var avrFilesFolders = typeof(AdvancedReminderFilesFolders).GetProperties().Select(property => property.Name).ToArray();
var readMessages = typeof(ReadMessages).GetProperties().Select(property => property.Name).ToArray();
var buttonSpaces = typeof(ButtonSpaces).GetProperties().Select(property => property.Name).ToArray();
var themes = typeof(Themes).GetProperties().Select(property => property.Name).ToArray();
var http = typeof(HttpRequests).GetProperties().Select(property => property.Name).ToArray();
var httpCon = typeof(HttpRequestCondition).GetProperties().Select(property => property.Name).ToArray();
//var remindMeColorSchemes = typeof(RemindMeColorScheme).GetProperties().Select(property => property.Name).ToArray();
foreach (string column in reminderNames)
{
if (!HasColumn(column, "reminder"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE REMINDER ADD COLUMN " + column + " " + GetReminderColumnSqlType(column));
}
}
foreach (string column in settingNames)
{
if (!HasColumn(column, "settings"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE SETTINGS ADD COLUMN " + column + " " + GetSettingColumnSqlType(column));
}
}
foreach (string column in songNames)
{
if (!HasColumn(column, "songs"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE SONGS ADD COLUMN " + column + " " + GetSongColumnSqlType(column));
}
}
foreach (string column in popupDimensionsNames)
{
if (!HasColumn(column, "PopupDimensions"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE POPUPDIMENSIONS ADD COLUMN " + column + " " + GetPopupDimensionsColumnSqlType(column));
}
}
foreach (string column in lvColumnSizes)
{
if (!HasColumn(column, "ListviewColumnSizes"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE ListviewColumnSizes ADD COLUMN " + column + " " + GetLvColumnSizesColumnSqlType(column));
}
}
foreach (string column in hotkeys)
{
if (!HasColumn(column, "Hotkeys"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE Hotkeys ADD COLUMN " + column + " " + GetHotkeysColumnSqlType(column));
}
}
foreach (string column in avrProperties)
{
if (!HasColumn(column, "AdvancedReminderProperties"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE AdvancedReminderProperties ADD COLUMN " + column + " " + GetAvrPropertiesColumnSqlType(column));
}
}
foreach (string column in avrFilesFolders)
{
if (!HasColumn(column, "AdvancedReminderFilesFolders"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE AdvancedReminderFilesFolders ADD COLUMN " + column + " " + GetAvrFilesFoldersColumnSqlType(column));
}
}
foreach (string column in readMessages)
{
if (!HasColumn(column, "ReadMessages"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE ReadMessages ADD COLUMN " + column + " " + GetReadMessagesColumnSqlType(column));
}
}
foreach (string column in buttonSpaces)
{
if (!HasColumn(column, "ButtonSpaces"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE ButtonSpaces ADD COLUMN " + column + " " + GetButtonSpacesColumnSqlType(column));
}
}
foreach (string column in themes)
{
try
{
if (!HasColumn("[" + column + "]", "Themes"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE Themes ADD COLUMN [" + column + "] " + GetThemesColumnSqlType(column));
}
}
catch (Exception ex)
{
if (ex.Message == "SQL logic error\r\nnear \"Primary\": syntax error")
{
} //not important. Not sure why this error is thrown, but everything works.
else
{
throw ex;
}
}
}
foreach (string column in http)
{
if (!HasColumn(column, "HttpRequests"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE HttpRequests ADD COLUMN " + column + " " + GetHttpColumnSqlType(column));
}
}
foreach (string column in httpCon)
{
if (!HasColumn(column, "HttpRequestCondition"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE HttpRequestCondition ADD COLUMN " + column + " " + GetHttpConditionColumnSqlType(column));
}
}
db.SaveChanges();
db.Dispose();
}
}
public static void InsertDefaultThemes()
{
using (RemindMeDbEntities db = new RemindMeDbEntities())
{
Themes theme = new Themes();
theme.Mode = 1;
theme.ThemeName = "Dark-Red";
theme.IsDefault = 0;
theme.TextShade = (long)TextShade.WHITE;
theme.Primary = (long)Primary.Red700;
theme.DarkPrimary = (long)Primary.Red900;
theme.LightPrimary = (long)Primary.Red200;
theme.Accent = (long)Accent.Red100;
if (db.Themes.Count() > 0)
{
theme.Id = db.Themes.Max(i => i.Id) + 1;
}
else
{
theme.Id = 0;
}
db.Themes.Add(theme);
db.SaveChanges();
//----
theme = new Themes();
theme.Mode = 0;
theme.ThemeName = "(Default) Blue-Light";
theme.IsDefault = 1;
theme.TextShade = (long)TextShade.WHITE;
theme.Primary = (long)Primary.Indigo500;
theme.DarkPrimary = (long)Primary.Indigo700;
theme.LightPrimary = (long)Primary.Indigo100;
theme.Accent = (long)Accent.Pink200;
theme.Id = db.Themes.Max(i => i.Id) + 1;
db.Themes.Add(theme);
db.SaveChanges();
//----
theme = new Themes();
theme.Mode = 0;
theme.ThemeName = "(Default) Green-Light";
theme.IsDefault = 1;
theme.TextShade = (long)TextShade.WHITE;
theme.Primary = (long)Primary.Green600;
theme.DarkPrimary = (long)Primary.Green700;
theme.LightPrimary = (long)Primary.Green200;
theme.Accent = (long)Accent.Red100;
theme.Id = db.Themes.Max(i => i.Id) + 1;
db.Themes.Add(theme);
db.SaveChanges();
//----
theme = new Themes();
theme.Mode = 0;
theme.ThemeName = "(Default) BlueGrey-Light";
theme.IsDefault = 1;
theme.TextShade = (long)TextShade.WHITE;
theme.Primary = (long)Primary.BlueGrey800;
theme.DarkPrimary = (long)Primary.BlueGrey900;
theme.LightPrimary = (long)Primary.BlueGrey500;
theme.Accent = (long)Accent.LightBlue200;
theme.Id = db.Themes.Max(i => i.Id) + 1;
db.Themes.Add(theme);
db.SaveChanges();
//lastly, dispose
db.Dispose();
}
}
/// <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 (RemindMeDbEntities db = new RemindMeDbEntities())
{
//every column that SHOULD exist
var reminderNames = typeof(Reminder).GetProperties().Select(property => property.Name).ToArray();
var settingNames = typeof(Settings).GetProperties().Select(property => property.Name).ToArray();
var songNames = typeof(Songs).GetProperties().Select(property => property.Name).ToArray();
var popupDimensionsNames = typeof(PopupDimensions).GetProperties().Select(property => property.Name).ToArray();
var lvColumnSizes = typeof(ListviewColumnSizes).GetProperties().Select(property => property.Name).ToArray();
var hotkeys = typeof(Hotkeys).GetProperties().Select(property => property.Name).ToArray();
var avrProperties = typeof(AdvancedReminderProperties).GetProperties().Select(property => property.Name).ToArray();
var avrFilesFolders = typeof(AdvancedReminderFilesFolders).GetProperties().Select(property => property.Name).ToArray();
var readMessages = typeof(ReadMessages).GetProperties().Select(property => property.Name).ToArray();
var buttonSpaces = typeof(ButtonSpaces).GetProperties().Select(property => property.Name).ToArray();
//var remindMeColorSchemes = typeof(RemindMeColorScheme).GetProperties().Select(property => property.Name).ToArray();
foreach (string column in reminderNames)
{
if (!HasColumn(column, "reminder"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE REMINDER ADD COLUMN " + column + " " + GetReminderColumnSqlType(column));
}
}
foreach (string column in settingNames)
{
if (!HasColumn(column, "settings"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE SETTINGS ADD COLUMN " + column + " " + GetSettingColumnSqlType(column));
}
}
foreach (string column in songNames)
{
if (!HasColumn(column, "songs"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE SONGS ADD COLUMN " + column + " " + GetSongColumnSqlType(column));
}
}
foreach (string column in popupDimensionsNames)
{
if (!HasColumn(column, "PopupDimensions"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE POPUPDIMENSIONS ADD COLUMN " + column + " " + GetPopupDimensionsColumnSqlType(column));
}
}
foreach (string column in lvColumnSizes)
{
if (!HasColumn(column, "ListviewColumnSizes"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE ListviewColumnSizes ADD COLUMN " + column + " " + GetLvColumnSizesColumnSqlType(column));
}
}
foreach (string column in hotkeys)
{
if (!HasColumn(column, "Hotkeys"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE Hotkeys ADD COLUMN " + column + " " + GetHotkeysColumnSqlType(column));
}
}
foreach (string column in avrProperties)
{
if (!HasColumn(column, "AdvancedReminderProperties"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE AdvancedReminderProperties ADD COLUMN " + column + " " + GetAvrPropertiesColumnSqlType(column));
}
}
foreach (string column in avrFilesFolders)
{
if (!HasColumn(column, "AdvancedReminderFilesFolders"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE AdvancedReminderFilesFolders ADD COLUMN " + column + " " + GetAvrFilesFoldersColumnSqlType(column));
}
}
foreach (string column in readMessages)
{
if (!HasColumn(column, "ReadMessages"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE ReadMessages ADD COLUMN " + column + " " + GetReadMessagesColumnSqlType(column));
}
}
foreach (string column in buttonSpaces)
{
if (!HasColumn(column, "ButtonSpaces"))
{
db.Database.ExecuteSqlCommand("ALTER TABLE ButtonSpaces ADD COLUMN " + column + " " + GetButtonSpacesColumnSqlType(column));
}
}
/*This was testing a custom color scheme
* foreach (string column in remindMeColorSchemes)
* {
* if (!HasColumn(column, "RemindMeColorScheme"))
* db.Database.ExecuteSqlCommand("ALTER TABLE RemindMeColorScheme ADD COLUMN " + column + " " + GetRemindMeColorSchemesColumnSqlType(column));
* }*/
db.SaveChanges();
db.Dispose();
}
}
/// <summary>
/// Saves pending changes to the database, disposes it, and refreshes the local cache list
/// </summary>
/// <param name="db"></param>
private static void SaveAndCloseDataBase(RemindMeDbEntities db)
{
db.SaveChanges();
RefreshCacheList();
db.Dispose();
}