private void FillTags(long taskID) { using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection)) { var command = new System.Data.SQLite.SQLiteCommand("SELECT tags.name FROM tasks_tags INNER JOIN tags ON tasks_tags.tag_id=tags.ID WHERE tasks_tags.task_id=@id", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", taskID)); var reader = command.ExecuteReader(); var builder = new System.Text.StringBuilder(); while (reader.Read()) { builder.AppendFormat("[{0}]", reader[0]); } TagsTextBox.Text = builder.ToString(); } }
private void FillDeadline(System.Data.DataRow row) { FillEventsComboBox(); if (!row.IsNull("deadline_id")) { long deadlineID = (long) row["deadline_id"]; using (var automatic = new AutomaticOpenClose(connection)) { var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID)); var reader = command.ExecuteReader(); if (reader.Read()) { DeadlineDatePicker.SelectedDate = reader.GetDateTime(0); if (reader.IsDBNull(1)) { DeadlineIsSelectedDateRadioButton.IsChecked = true; } else { DeadlineIsEventRadioButton.IsChecked = true; EventsComboBox.SelectedValue = reader.GetInt64(1); } } } } }
private void Save() { // -------------------------------------------------- // Deadline using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection)) { var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate)); var reader = command.ExecuteReader(); if (reader.Read()) { row["deadline_id"] = reader.GetInt64(0); } else { row["deadline_id"] = System.DBNull.Value; } } taskDataAdapter.Update(dataSet, "event"); // -------------------------------------------------- // Tags using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection)) { var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text)); var whereInTuple = Util.SqlParametersList(currentTags); var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>(); { var command = new System.Data.SQLite.SQLiteCommand(@" SELECT events_tags.ID, events_tags.event_id, events_tags.tag_id, tags.name FROM events_tags LEFT JOIN tags ON tags.ID = events_tags.tag_id WHERE events_tags.event_id=@id AND tags.name IN(" + whereInTuple.Item1 + @") UNION ALL SELECT NULL, NULL, ID, name FROM tags WHERE ID NOT IN(SELECT tag_id FROM events_tags WHERE event_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id)); foreach (var parameter in whereInTuple.Item2) command.Parameters.Add(parameter); var reader = command.ExecuteReader(); while (reader.Read()) { var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, ""); System.Nullable<long> tasksTagsID = null; System.Nullable<long> taskID = null; System.Nullable<long> tagID = null; string name = ""; if (!reader.IsDBNull(0)) tasksTagsID = reader.GetInt64(0); if (!reader.IsDBNull(1)) taskID = reader.GetInt64(1); if (!reader.IsDBNull(2)) tagID = reader.GetInt64(2); if (!reader.IsDBNull(3)) name = reader.GetString(3); rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name)); } } // delete all old tasks_tags not need for new tags { var oldTasksTagsIDs = new System.Collections.Generic.List<long>(); foreach (var tuple in rows) { if (tuple.Item1.HasValue) oldTasksTagsIDs.Add(tuple.Item1.Value); } var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs); var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM events_tags WHERE event_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id)); foreach (var parameter in whereInTuple2.Item2) command.Parameters.Add(parameter); command.ExecuteNonQuery(); } // link existing new tags foreach (var tuple in rows) { if (!tuple.Item1.HasValue && tuple.Item3.HasValue) { var tagID = tuple.Item3.Value; long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", tagID)); } } // create and link new tags { var newTags = new System.Collections.Generic.List<string>(); foreach (var tagName in currentTags) { bool found = false; foreach (var row in rows) { if (row.Item4 == tagName) { found = true; break; } } if (!found) { long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName)); long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", newTagID)); } } } // Alerts foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows) { if (row.RowState == System.Data.DataRowState.Added) row["event_id"] = id; } alertsDataAdapter.Update(dataSet, "alerts"); dataSet.Tables["alerts"].Clear(); alertsDataAdapter.Fill(dataSet, "alerts"); } // -------------------------------------------------- // Clean state IsDirty = false; try { this.parent.MassReloadEvents(); } catch { } }
private bool Save() { System.Data.SQLite.SQLiteTransaction transaction = null; using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection)) { try { transaction = connection.BeginTransaction(); // -------------------------------------------------- // Deadline if (DeadlineIsSelectedDateRadioButton.IsChecked.Value) { var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate)); var reader = command.ExecuteReader(); if (reader.Read()) { row["deadline_id"] = reader.GetInt64(0); } } else if (DeadlineIsEventRadioButton.IsChecked.Value) { row["deadline_id"] = (EventsComboBox.SelectedValue != null) ? EventsComboBox.SelectedValue : System.DBNull.Value; } else { row["deadline_id"] = System.DBNull.Value; } taskDataAdapter.Update(dataSet, "task"); // -------------------------------------------------- // Tags var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text)); var whereInTuple = Util.SqlParametersList(currentTags); var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>(); { var command = new System.Data.SQLite.SQLiteCommand(@" SELECT tasks_tags.ID, tasks_tags.task_id, tasks_tags.tag_id, tags.name FROM tasks_tags LEFT JOIN tags ON tags.ID = tasks_tags.tag_id WHERE tasks_tags.task_id=@id AND tags.name IN(" + whereInTuple.Item1 + @") UNION ALL SELECT NULL, NULL, ID, name FROM tags WHERE ID NOT IN(SELECT tag_id FROM tasks_tags WHERE task_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id)); foreach (var parameter in whereInTuple.Item2) command.Parameters.Add(parameter); var reader = command.ExecuteReader(); while (reader.Read()) { var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, ""); System.Nullable<long> tasksTagsID = null; System.Nullable<long> taskID = null; System.Nullable<long> tagID = null; string name = ""; if (!reader.IsDBNull(0)) tasksTagsID = reader.GetInt64(0); if (!reader.IsDBNull(1)) taskID = reader.GetInt64(1); if (!reader.IsDBNull(2)) tagID = reader.GetInt64(2); if (!reader.IsDBNull(3)) name = reader.GetString(3); rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name)); } } // delete all old tasks_tags not need for new tags { var oldTasksTagsIDs = new System.Collections.Generic.List<long>(); foreach (var tuple in rows) { if (tuple.Item1.HasValue) oldTasksTagsIDs.Add(tuple.Item1.Value); } var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs); var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM tasks_tags WHERE task_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id)); foreach (var parameter in whereInTuple2.Item2) command.Parameters.Add(parameter); command.ExecuteNonQuery(); } // link existing new tags foreach (var tuple in rows) { if (!tuple.Item1.HasValue && tuple.Item3.HasValue) { var tagID = tuple.Item3.Value; long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", tagID)); } } // create and link new tags { var newTags = new System.Collections.Generic.List<string>(); foreach (var tagName in currentTags) { bool found = false; foreach (var row in rows) { if (row.Item4 == tagName) { found = true; break; } } if (!found) { long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName)); long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", newTagID)); } } } // -------------------------------------------------- // Alerts foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows) { if (row.RowState == System.Data.DataRowState.Added) row["task_id"] = id; } alertsDataAdapter.Update(dataSet, "alerts"); dataSet.Tables["alerts"].Clear(); alertsDataAdapter.Fill(dataSet, "alerts"); // -------------------------------------------------- // Sub-Tasks foreach (System.Data.DataRow row in dataSet.Tables["sub_tasks"].Rows) { if (row.RowState == System.Data.DataRowState.Added) row["child_of"] = id; } subTasksDataAdapter.Update(dataSet, "sub_tasks"); dataSet.Tables["sub_tasks"].Clear(); subTasksDataAdapter.Fill(dataSet, "sub_tasks"); // -------------------------------------------------- // Clean state IsDirty = false; transaction.Commit(); } catch (System.Data.SQLite.SQLiteException e) { if (transaction != null) transaction.Rollback(); switch (e.ErrorCode) { case System.Data.SQLite.SQLiteErrorCode.Constraint: Util.ShowFieldMustBeUniqueMessage(this, Util.ExtractColumnName(e.Message)); break; } } } try { this.parent.MassReloadTasks(); } catch { } return !IsDirty; }
private void FillDeadline(System.Data.DataRow row) { if (!row.IsNull("deadline_id")) { long deadlineID = (long)row["deadline_id"]; using (var automatic = new AutomaticOpenClose(connection)) { var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection); command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID)); var reader = command.ExecuteReader(); if (reader.Read()) { DeadlineDatePicker.SelectedDate = reader.GetDateTime(0); } } } }