/// <summary> /// CalendarInfoの新規IDを取得する /// </summary> /// <returns></returns> internal int GetNewCalendarInfoId() { try { int result = 0; OnExecute = (SQLiteConnection con, SQLiteTransaction transaction) => { string sql = "SELECT CASE WHEN MAX(ID) IS NOT NULL THEN MAX(ID) + 1 ELSE 1 END FROM CALENDAR_INFO"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { result = int.Parse("" + reader.GetValue(0)); } } } }; Execute(false); return(result); } catch (Exception ex) { throw Program.ThrowException(ex); } }
public async Task UpdateExpenseCategory_PostsNotification() { //Arrange var messenger = new WeakReferenceMessenger(); var watcher = new MessageWatcher <DatabaseEvent <ExpenseCategory> >(); messenger.Register(watcher); AutoMocker mocker = new(); using var factory = mocker.WithDbScope(messenger); var expenseCategory = new ExpenseCategory { Account = new Account() }; using var setupContext = factory.Create(); setupContext.ExpenseCategories.Add(expenseCategory); await setupContext.SaveChangesAsync(); //Act using var actContext = factory.Create(); var category = await actContext.ExpenseCategories.FindAsync(expenseCategory.ID); category.CategoryName += "-Edited"; await actContext.SaveChangesAsync(); //Assert DatabaseEvent <ExpenseCategory>?message = watcher.Messages.Last(); Assert.AreEqual(expenseCategory.ID, message.Item.ID); Assert.AreEqual(EventType.Updated, message.Type); }
private async void OnDatabaseEvent(IONDatabase database, DatabaseEvent de) { if (!de.table.Equals(typeof(JobRow))) { return; } var index = IndexOfJob(JobRecordWithId(de.id)?.data); if (index < 0) { return; } switch (de.action) { case DatabaseEvent.EAction.Deleted: records.RemoveAt(index); NotifyItemRemoved(index); break; case DatabaseEvent.EAction.Inserted: var job = await ion.database.QueryForAsync <JobRow>(de.id); ion.PostToMain(() => { records.Insert(index, new JobRecord(job)); NotifyItemInserted(index); }); break; case DatabaseEvent.EAction.Modified: NotifyItemChanged(index); break; } }
/// <summary> /// UpdateIgnoreInfoを更新する /// </summary> /// <param name="version"></param> internal void CreateUpdateIgnoreInfo(string version) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { string sql = "REPLACE INTO UPDATE_IGNORE_INFO (IGNOREVERSION) VALUES (@VERSION)"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { try { command.Parameters.Add(new SQLiteParameter("@VERSION", version)); command.ExecuteNonQuery(); tran.Commit(); } catch (Exception) { tran.Rollback(); } } }; Execute(true); } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// CalendarInfoを削除する /// </summary> /// <param name="id"></param> internal void DeleteCalendarInfo(int id) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { string sql = "DELETE FROM CALENDAR_INFO WHERE ID = @ID"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { command.Parameters.Add(new SQLiteParameter("@ID", id)); command.ExecuteNonQuery(); } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } }; Execute(true); } catch (Exception ex) { throw ex; } }
/// <summary> /// 設定値情報を取得する /// </summary> /// <returns></returns> internal Dictionary <int, string> GetSettingInfoAll() { try { Dictionary <int, string> dic = new Dictionary <int, string>(); OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { string sql = "SELECT ID, VALUE FROM SETTING_INFO ORDER BY ID"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { int id = int.Parse("" + reader.GetValue(0)); string value = "" + reader.GetValue(1); dic.Add(id, value); } } } }; Execute(false); return(dic); } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// 設定値情報を作成する /// </summary> /// <param name="id"></param> /// <param name="value"></param> internal void CreateSettingInfo(int id, string value) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { string sql = "INSERT OR REPLACE INTO SETTING_INFO (ID, VALUE) VALUES(@ID,@VALUE)"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { command.Parameters.Add(new SQLiteParameter("@ID", id)); command.Parameters.Add(new SQLiteParameter("@VALUE", value)); command.ExecuteNonQuery(); } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw Program.ThrowException(ex); } }; Execute(true); } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// ExecuteLogを作成する /// </summary> /// <param name="calendarModel"></param> internal void CreateExecuteLog(ExecuteLogModel execModel) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { StringBuilder sql = new StringBuilder(); sql.Append("INSERT INTO EXECUTE_LOG ("); sql.Append("ID,"); sql.Append("CDATE,"); sql.Append("EXECID,"); sql.Append("EXECTYPE,"); sql.Append("EXECNAME,"); sql.Append("STARTTIME,"); sql.Append("ENDTIME,"); sql.Append("EXECTIME,"); sql.Append("DESCRIPTION,"); sql.Append("RESULT"); sql.Append(")VALUES("); sql.Append("@ID,"); sql.Append("@CDATE,"); sql.Append("@EXECID,"); sql.Append("@EXECTYPE,"); sql.Append("@EXECNAME,"); sql.Append("@STARTTIME,"); sql.Append("@ENDTIME,"); sql.Append("@EXECTIME,"); sql.Append("@DESCRIPTION,"); sql.Append("@RESULT"); sql.Append(")"); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con)) { try { command.Parameters.Add(new SQLiteParameter("@ID", execModel.Id)); command.Parameters.Add(new SQLiteParameter("@CDATE", execModel.Cdate)); command.Parameters.Add(new SQLiteParameter("@EXECID", execModel.ExecId)); command.Parameters.Add(new SQLiteParameter("@EXECTYPE", execModel.ExecType)); command.Parameters.Add(new SQLiteParameter("@EXECNAME", execModel.ExecName)); command.Parameters.Add(new SQLiteParameter("@STARTTIME", execModel.StartTime)); command.Parameters.Add(new SQLiteParameter("@ENDTIME", execModel.EndTime)); command.Parameters.Add(new SQLiteParameter("@EXECTIME", execModel.ExecTime)); command.Parameters.Add(new SQLiteParameter("@DESCRIPTION", execModel.Description)); command.Parameters.Add(new SQLiteParameter("@RESULT", execModel.Result)); command.ExecuteNonQuery(); tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } } }; Execute(true); } catch (Exception ex) { throw ex; } }
public async Task DeleteAccount_PostsNotification() { //Arrange var mocker = new AutoMocker().WithMessenger(); var watcher = new MessageWatcher <DatabaseEvent <Account> >(); mocker.Get <IMessenger>().Register(watcher); using var factory = mocker.WithDbScope(); var account1 = new Account(); using var setupContext = factory.Create(); setupContext.Accounts.Add(account1); await setupContext.SaveChangesAsync(); //Act using var actContext = factory.Create(); var account = await actContext.Accounts.FindAsync(account1.ID); actContext.Accounts.Remove(account); await actContext.SaveChangesAsync(); //Assert DatabaseEvent <Account>?message = watcher.Messages.Last(); Assert.AreEqual(account1.ID, message.Item.ID); Assert.AreEqual(EventType.Deleted, message.Type); }
/// <summary> /// データベース処理を実行する /// </summary> /// <param name="transaction"></param> public void Execute(bool transaction) { try { using (SQLiteConnection con = new SQLiteConnection(DATABASE_PATH)) { con.Open(); if (transaction) { using (SQLiteTransaction tran = con.BeginTransaction()) { OnExecute?.Invoke(con, tran); OnExecute = null; } } else { OnExecute?.Invoke(con, null); OnExecute = null; } } } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// 実行ログを削除する /// </summary> /// <param name="idList"></param> internal void DeleteExecuteLog(List <string> idList) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { string sql = "DELETE FROM EXECUTE_LOG WHERE EXECID IN ("; int i = 0; foreach (var id in idList) { i++; if (i == 1) { sql += "@EXECID" + i; } else { sql += ",@EXECID" + i; } } sql += ")"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { i = 0; foreach (var id in idList) { i++; command.Parameters.Add(new SQLiteParameter("@EXECID" + i, id)); } command.ExecuteNonQuery(); } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } }; Execute(true); } catch (Exception ex) { throw ex; } }
private void OnDatabaseEvent(IONDatabase db, DatabaseEvent e) { switch (e.action) { case DatabaseEvent.EAction.Deleted: if (e.table != typeof(JobRow)) { return; } if ((int)e.id == ion.preferences.job.activeJob) { RemoveActiveJob(); } break; } }
public void UpdateDataText(string newText) { var oldText = _databaseConnection.Data.Entity; _databaseConnection.Data.Entity = newText; var databaseEvent = new DatabaseEvent <TextUpdatedEvent>() { Event = new TextUpdatedEvent() { OldText = oldText, NewText = newText } }; _eventPublisher.Publish(databaseEvent); }
/// <summary> /// カレンダーインフォを作成する /// </summary> /// <param name="id"></param> /// <param name="name"></param> /// <param name="model"></param> /// <param name="cdate"></param> /// <param name="calendarType"></param> /// <param name="calendarInfoDic"></param> internal void CreateCalendarInfo(int id, string name, CalendarModel model, DateTime cdate, CalendarType calendarType, Dictionary <int, object[]> calendarInfoDic) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { string sql = "INSERT OR REPLACE INTO CALENDAR_INFO (ID, CALENDARNAME, CALENDARVALUE, CDATE, CALENDARTYPE) VALUES(@ID,@CALENDARNAME, @CALENDARVALUE, @CDATE, @CALENDARTYPE)"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { command.Parameters.Add(new SQLiteParameter("@ID", id)); command.Parameters.Add(new SQLiteParameter("@CALENDARNAME", name)); command.Parameters.Add(new SQLiteParameter("@CALENDARVALUE", JsonConvert.SerializeObject(model))); command.Parameters.Add(new SQLiteParameter("@CDATE", cdate)); command.Parameters.Add(new SQLiteParameter("@CALENDARTYPE", calendarType)); command.ExecuteNonQuery(); if (calendarInfoDic.ContainsKey(id)) { calendarInfoDic[id][0] = name; calendarInfoDic[id][1] = model; calendarInfoDic[id][2] = cdate; calendarInfoDic[id][3] = calendarType; } else { calendarInfoDic.Add(id, new object[] { name, model, cdate, calendarType }); } } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw Program.ThrowException(ex); } }; Execute(true); } catch (Exception ex) { throw Program.ThrowException(ex); } }
internal List <string[]> Get_Project_Macro_InfoAll(ExecDataType execDataType) { try { List <string[]> resultList = new List <string[]>(); OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { StringBuilder sql = new StringBuilder(); string name = ""; switch (execDataType) { case ExecDataType.PROJECT: name = "PROJECT"; break; case ExecDataType.MACRO: name = "MACRO"; break; } sql.Append("SELECT ID, " + name + "NAME, CDATE FROM " + name + "_INFO ORDER BY " + ((name != "PROJECT") ? name + "NAME" : "CDATE DESC")); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string id = "" + reader.GetValue(0); string pName = "" + reader.GetValue(1); string cdate = "" + reader.GetValue(2); resultList.Add(new string[] { id, pName, cdate }); } } } }; Execute(false); return(resultList); } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// プロジェクト又はマクロを削除する /// </summary> /// <param name="type"></param> /// <param name="projectId"></param> internal void Delete_Project_Macro_Info(ExecDataType type, string projectId) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { StringBuilder sql = new StringBuilder(); string name = ""; switch (type) { case ExecDataType.PROJECT: name = "PROJECT"; break; case ExecDataType.MACRO: name = "MACRO"; break; } sql.Append("DELETE FROM " + name + "_INFO WHERE ID = @ID"); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con)) { command.Parameters.Add(new SQLiteParameter("@ID", projectId)); command.ExecuteNonQuery(); tran.Commit(); } } catch (Exception ex) { tran.Rollback(); throw Program.ThrowException(ex); } }; Execute(true); } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// 指定したバージョンのUpdateIgnoreInfoが存在するかの判定 /// </summary> /// <param name="currentVersion"></param> /// <returns></returns> internal bool UpdateIgnoreInfoExists(string currentVersion) { try { bool hasRow = false; OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { string sql = "SELECT IGNOREVERSION FROM UPDATE_IGNORE_INFO WHERE IGNOREVERSION = @VERSION"; using (SQLiteCommand command = new SQLiteCommand(sql, con)) { command.Parameters.Add(new SQLiteParameter("@VERSION", currentVersion)); using (SQLiteDataReader reader = command.ExecuteReader()) { hasRow = reader.HasRows; } } }; Execute(false); return(hasRow); } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <summary> /// プロジェクト又はマクロのValue値を取得する /// </summary> /// <param name="name"></param> /// <param name="projectId"></param> /// <returns></returns> internal string Get_Project_Macro_Value(string name, string projectId) { try { string result = ""; OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { StringBuilder sql = new StringBuilder(); sql.Append("SELECT " + name + "VALUE FROM " + name + "_INFO WHERE ID = @ID"); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con)) { command.Parameters.Add(new SQLiteParameter("@ID", projectId)); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { result = "" + reader.GetValue(0); } } } } catch (Exception ex) { tran.Rollback(); throw Program.ThrowException(ex); } }; Execute(false); return(result); } catch (Exception ex) { throw Program.ThrowException(ex); } }
public void Receive(DatabaseEvent <ExpenseCategory> _) => LoadItemsAsync();
private Microsoft.VisualStudio.Modeling.ModelElement GetParentForDatabaseEvent(DatabaseEvent childElement) { return childElement.SubProcess; }
/// <summary> /// ProjectInfo又はMacroInfoを作成する /// </summary> /// <param name="model"></param> internal void Create_Project_Module_Info(ProjectModel model) { try { OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => { try { string jsonString = JsonConvert.SerializeObject(model, new JsonSerializerSettings() { ReferenceLoopHandling = ReferenceLoopHandling.Ignore, Formatting = Formatting.Indented }); StringBuilder sql = new StringBuilder(); string name = ""; switch (model.ExecDataType) { case ExecDataType.PROJECT: name = "PROJECT"; break; case ExecDataType.MACRO: name = "MACRO"; break; } sql.Append("DELETE FROM " + name + "_INFO WHERE ID = @ID"); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con)) { command.Parameters.Add(new SQLiteParameter("@ID", model.ProjectId)); command.ExecuteNonQuery(); } sql.Clear(); sql.Append("INSERT INTO " + name + "_INFO("); sql.Append("ID"); sql.Append("," + name + "NAME"); sql.Append("," + name + "VALUE"); sql.Append(",DESCRIPTION"); sql.Append(",CDATE"); sql.Append(")VALUES("); sql.Append("@ID"); sql.Append(",@NAME"); sql.Append(",@VALUE"); sql.Append(",@DESCRIPTION"); sql.Append(",@CDATE"); sql.Append(")"); using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con)) { command.Parameters.Add(new SQLiteParameter("@ID", model.ProjectId)); command.Parameters.Add(new SQLiteParameter("@NAME", model.Name)); command.Parameters.Add(new SQLiteParameter("@VALUE", jsonString)); command.Parameters.Add(new SQLiteParameter("@DESCRIPTION", model.Description)); command.Parameters.Add(new SQLiteParameter("@CDATE", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"))); command.ExecuteNonQuery(); } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } }; Execute(true); } catch (Exception ex) { throw ex; } }
private void OnDatabaseEvent(IONDatabase db, DatabaseEvent e) { }
public void Receive(DatabaseEvent <Account> _) => LoadItemsAsync();