public bool Add(SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Insert into VisitedSystems (Name, Time, Unit, Commander, Source, edsm_sync, map_colour, X, Y, Z, id_edsm_assigned) values (@name, @time, @unit, @commander, @source, @edsm_sync, @map_colour, @x, @y, @z, @id_edsm_assigned)", tn))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@unit", Unit);
cmd.AddParameterWithValue("@commander", Commander);
cmd.AddParameterWithValue("@source", Source);
cmd.AddParameterWithValue("@edsm_sync", EDSM_sync);
cmd.AddParameterWithValue("@map_colour", MapColour);
cmd.AddParameterWithValue("@x", X);
cmd.AddParameterWithValue("@y", Y);
cmd.AddParameterWithValue("@z", Z);
cmd.AddParameterWithValue("@id_edsm_assigned", id_edsm_assigned);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from VisitedSystems"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return(true);
}
}
public static EDCommander Create(string name = null, string edsmName = null, string edsmApiKey = null, string journalpath = null,
bool toedsm = true, bool fromedsm = false, bool toeddn = true, bool toego = false, string egoname = null, string egoapi = null,
bool toinara = false, string inaraname = null, string inaraapikey = null)
{
EDCommander cmdr;
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
using (DbCommand cmd = conn.CreateCommand("INSERT INTO Commanders (Name,EdsmName,EdsmApiKey,JournalDir,Deleted, SyncToEdsm, SyncFromEdsm, SyncToEddn, NetLogDir, SyncToEGO, EGOName, EGOAPIKey, SyncToInara, InaraName, InaraAPIKey) " +
"VALUES (@Name,@EdsmName,@EdsmApiKey,@JournalDir,@Deleted, @SyncToEdsm, @SyncFromEdsm, @SyncToEddn, @NetLogDir, @SyncToEGO, @EGOName, @EGOApiKey, @SyncToInara, @InaraName, @InaraAPIKey)"))
{
cmd.AddParameterWithValue("@Name", name ?? "");
cmd.AddParameterWithValue("@EdsmName", edsmName ?? name ?? "");
cmd.AddParameterWithValue("@EdsmApiKey", edsmApiKey ?? "");
cmd.AddParameterWithValue("@JournalDir", journalpath ?? "");
cmd.AddParameterWithValue("@Deleted", false);
cmd.AddParameterWithValue("@SyncToEdsm", toedsm);
cmd.AddParameterWithValue("@SyncFromEdsm", fromedsm);
cmd.AddParameterWithValue("@SyncToEddn", toeddn);
cmd.AddParameterWithValue("@NetLogDir", ""); // Unused field, null out
cmd.AddParameterWithValue("@SyncToEGO", toego);
cmd.AddParameterWithValue("@EGOName", egoname ?? "");
cmd.AddParameterWithValue("@EGOApiKey", egoapi ?? "");
cmd.AddParameterWithValue("@SyncToInara", toinara);
cmd.AddParameterWithValue("@InaraName", inaraname ?? "");
cmd.AddParameterWithValue("@InaraApiKey", inaraapikey ?? "");
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = conn.CreateCommand("SELECT Id FROM Commanders WHERE rowid = last_insert_rowid()"))
{
int nr = Convert.ToInt32(cmd.ExecuteScalar());
}
using (DbCommand cmd = conn.CreateCommand("SELECT * FROM Commanders WHERE rowid = last_insert_rowid()"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
{
cmdr = new EDCommander(reader);
}
}
}
if (name == null)
{
using (DbCommand cmd = conn.CreateCommand("UPDATE Commanders SET Name = @Name WHERE rowid = last_insert_rowid()"))
{
cmd.AddParameterWithValue("@Name", cmdr.Name);
cmd.ExecuteNonQuery();
}
}
}
commanders[cmdr.Nr] = cmdr;
return(cmdr);
}
public EDCommander GetNewCommander(string name = null, string edsmName = null, string edsmApiKey = null, string journalpath = null)
{
EDCommander cmdr;
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
using (DbCommand cmd = conn.CreateCommand("INSERT INTO Commanders (Name,EdsmName,EdsmApiKey,JournalDir,Deleted, SyncToEdsm, SyncFromEdsm, SyncToEddn) VALUES (@Name,@EdsmName,@EdsmApiKey,@JournalDir,@Deleted, @SyncToEdsm, @SyncFromEdsm, @SyncToEddn)"))
{
cmd.AddParameterWithValue("@Name", name ?? "");
cmd.AddParameterWithValue("@EdsmName", edsmName ?? name ?? "");
cmd.AddParameterWithValue("@EdsmApiKey", edsmApiKey ?? "");
cmd.AddParameterWithValue("@JournalDir", journalpath ?? "");
cmd.AddParameterWithValue("@Deleted", false);
cmd.AddParameterWithValue("@SyncToEdsm", true);
cmd.AddParameterWithValue("@SyncFromEdsm", false);
cmd.AddParameterWithValue("@SyncToEddn", true);
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = conn.CreateCommand("SELECT Id FROM Commanders WHERE rowid = last_insert_rowid()"))
{
int nr = Convert.ToInt32(cmd.ExecuteScalar());
}
using (DbCommand cmd = conn.CreateCommand("SELECT * FROM Commanders WHERE rowid = last_insert_rowid()"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
{
cmdr = new EDCommander(reader);
}
}
}
if (name == null)
{
using (DbCommand cmd = conn.CreateCommand("UPDATE Commanders SET Name = @Name WHERE rowid = last_insert_rowid()"))
{
cmd.AddParameterWithValue("@Name", cmdr.Name);
cmd.ExecuteNonQuery();
}
}
}
LoadCommanders(); // refresh in-memory copy
return(cmdr);
}
// if cat is null, fdname only
public static MaterialCommodities GetCatFDName(string cat, string fdname, SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("select Id,Category,Name,FDName,Type,ShortName,Colour,Flags from MaterialsCommodities WHERE FDName=@name"))
{
cmd.AddParameterWithValue("@name", fdname);
if (cat != null)
{
cmd.CommandText += " AND Category==@cat";
cmd.AddParameterWithValue("@cat", cat);
}
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read()) // already sorted, and already limited to max items
{
return(new MaterialCommodities((long)reader[0], (string)reader[1], (string)reader[2], (string)reader[3], (string)reader[4], (string)reader[5], Color.FromArgb((int)reader[6]), (int)reader[7]));
}
else
{
return(null);
}
}
}
}
public static T GetLast <T>(int cmdrid, DateTime before)
where T : JournalEntry
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM JournalEntries WHERE CommanderId = @cmdrid AND EventTime < @time ORDER BY EventTime DESC"))
{
cmd.AddParameterWithValue("@cmdrid", cmdrid);
cmd.AddParameterWithValue("@time", before);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
JournalEntry ent = CreateJournalEntry(reader);
if (ent is T)
{
return((T)ent);
}
}
}
}
}
return(null);
}
static internal JObject GetJson(long journalid, SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("select EventData from JournalEntries where ID=@journalid", tn))
{
cmd.AddParameterWithValue("@journalid", journalid);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string EDataString = (string)reader["EventData"];
try
{
return(JObject.Parse(EDataString));
}
catch (Exception ex)
{
Trace.WriteLine($"Error parsing journal entry\n{EDataString}\n{ex.ToString()}");
return(null);
}
}
}
}
return(null);
}
public static void UpdateSyncFlagBit(long journalid, SyncFlags bit, bool value)
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
JournalEntry je = Get(journalid, cn);
if (je != null)
{
if (value)
{
je.Synced |= (int)bit;
}
else
{
je.Synced &= ~(int)bit;
}
using (DbCommand cmd = cn.CreateCommand("Update JournalEntries set Synced = @sync where ID=@journalid"))
{
cmd.AddParameterWithValue("@journalid", journalid);
cmd.AddParameterWithValue("@sync", je.Synced);
System.Diagnostics.Trace.WriteLine(string.Format("Update sync flag ID {0} with {1}", journalid, je.Synced));
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
}
}
}
public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from SystemNote"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(false);
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes.Add(sys);
}
return(true);
}
}
}
catch
{
return(false);
}
}
static public List <JournalEntry> GetAll(int commander = -999)
{
List <JournalEntry> list = new List <JournalEntry>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("select * from JournalEntries where CommanderID=@commander Order by EventTime ASC"))
{
if (commander == -999)
{
cmd.CommandText = "select * from JournalEntries Order by EventTime ";
}
cmd.AddParameterWithValue("@commander", commander);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(list);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
JournalEntry sys = JournalEntry.CreateJournalEntry(dr);
list.Add(sys);
}
return(list);
}
}
}
public static bool GetAllBookmarks()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from Bookmarks"))
{
DataSet ds = null;
ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(false);
}
bookmarks.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
BookmarkClass bc = new BookmarkClass(dr);
bookmarks.Add(bc);
}
return(true);
}
}
}
catch
{
return(false);
}
}
public void UpdateCommanders(List <EDCommander> cmdrlist)
{
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
using (DbCommand cmd = conn.CreateCommand("UPDATE Commanders SET Name=@Name, EdsmName=@EdsmName, EdsmApiKey=@EdsmApiKey, NetLogDir=@NetLogDir, JournalDir=@JournalDir, SyncToEdsm=@SyncToEdsm, SyncFromEdsm=@SyncFromEdsm, SyncToEddn=@SyncToEddn WHERE Id=@Id"))
{
cmd.AddParameter("@Id", DbType.Int32);
cmd.AddParameter("@Name", DbType.String);
cmd.AddParameter("@EdsmName", DbType.String);
cmd.AddParameter("@EdsmApiKey", DbType.String);
cmd.AddParameter("@NetLogDir", DbType.String);
cmd.AddParameter("@JournalDir", DbType.String);
cmd.AddParameter("@SyncToEdsm", DbType.Boolean);
cmd.AddParameter("@SyncFromEdsm", DbType.Boolean);
cmd.AddParameter("@SyncToEddn", DbType.Boolean);
foreach (EDCommander edcmdr in cmdrlist)
{
cmd.Parameters["@Id"].Value = edcmdr.Nr;
cmd.Parameters["@Name"].Value = edcmdr.Name;
cmd.Parameters["@EdsmName"].Value = edcmdr.EdsmName;
cmd.Parameters["@EdsmApiKey"].Value = edcmdr.APIKey != null ? edcmdr.APIKey : "";
cmd.Parameters["@NetLogDir"].Value = edcmdr.NetLogDir != null ? edcmdr.NetLogDir : "";
cmd.Parameters["@JournalDir"].Value = edcmdr.JournalDir != null ? edcmdr.JournalDir : "";
cmd.Parameters["@SyncToEdsm"].Value = edcmdr.SyncToEdsm;
cmd.Parameters["@SyncFromEdsm"].Value = edcmdr.SyncFromEdsm;
cmd.Parameters["@SyncToEddn"].Value = edcmdr.SyncToEddn;
cmd.ExecuteNonQuery();
}
LoadCommanders(); // refresh in-memory copy
}
}
}
static public List <VisitedSystemsClass> GetAll(int commander)
{
List <VisitedSystemsClass> list = new List <VisitedSystemsClass>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from VisitedSystems where commander=@commander Order by Time "))
{
cmd.AddParameterWithValue("@commander", commander);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(list);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
VisitedSystemsClass sys = new VisitedSystemsClass(dr);
list.Add(sys);
}
return(list);
}
}
}
static public List <JournalEntry> GetAll(int commander = -999)
{
Dictionary <long, TravelLogUnit> tlus = TravelLogUnit.GetAll().ToDictionary(t => t.id);
List <JournalEntry> list = new List <JournalEntry>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("select * from JournalEntries where CommanderID=@commander Order by EventTime ASC"))
{
if (commander == -999)
{
cmd.CommandText = "select * from JournalEntries Order by EventTime ";
}
cmd.AddParameterWithValue("@commander", commander);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(list);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
JournalEntry sys = JournalEntry.CreateJournalEntry(dr);
sys.beta = tlus.ContainsKey(sys.TLUId) ? tlus[sys.TLUId].Beta : false;
list.Add(sys);
}
return(list);
}
}
}
public static List <JournalEntry> GetByEventType(JournalTypeEnum eventtype, int commanderid, DateTime start, DateTime stop)
{
Dictionary <long, TravelLogUnit> tlus = TravelLogUnit.GetAll().ToDictionary(t => t.id);
List <JournalEntry> vsc = new List <JournalEntry>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM JournalEntries WHERE EventTypeID = @eventtype and CommanderID=@commander and EventTime >=@start and EventTime<=@Stop ORDER BY EventTime ASC"))
{
cmd.AddParameterWithValue("@eventtype", (int)eventtype);
cmd.AddParameterWithValue("@commander", (int)commanderid);
cmd.AddParameterWithValue("@start", start);
cmd.AddParameterWithValue("@stop", stop);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
JournalEntry je = CreateJournalEntry(reader);
je.beta = tlus.ContainsKey(je.TLUId) ? tlus[je.TLUId].Beta : false;
vsc.Add(je);
}
}
}
}
return(vsc);
}
private void UpdateSyncFlagBit(SyncFlags bit1, bool value1, SyncFlags bit2, bool value2, SQLiteConnectionUser cn, DbTransaction txn = null)
{
if (value1)
{
Synced |= (int)bit1;
}
else
{
Synced &= ~(int)bit1;
}
if (value2)
{
Synced |= (int)bit2;
}
else
{
Synced &= ~(int)bit2;
}
using (DbCommand cmd = cn.CreateCommand("Update JournalEntries set Synced = @sync where ID=@journalid", txn))
{
cmd.AddParameterWithValue("@journalid", Id);
cmd.AddParameterWithValue("@sync", Synced);
//System.Diagnostics.Trace.WriteLine(string.Format("Update sync flag ID {0} with {1}", Id, Synced));
cmd.ExecuteNonQuery();
}
}
public static List <JournalEntry> FindEntry(JournalEntry ent, JObject entjo = null) // entjo is not changed.
{
List <JournalEntry> entries = new List <JournalEntry>();
if (entjo == null)
{
entjo = GetJson(ent.Id);
}
entjo = RemoveEDDGeneratedKeys(entjo);
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM JournalEntries WHERE CommanderId = @cmdrid AND EventTime = @time AND TravelLogId = @tluid AND EventTypeId = @evttype ORDER BY Id ASC"))
{
cmd.AddParameterWithValue("@cmdrid", ent.CommanderId);
cmd.AddParameterWithValue("@time", ent.EventTimeUTC);
cmd.AddParameterWithValue("@tluid", ent.TLUId);
cmd.AddParameterWithValue("@evttype", ent.EventTypeID);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
JournalEntry jent = CreateJournalEntry(reader);
if (AreSameEntry(ent, jent, entjo))
{
entries.Add(jent);
}
}
}
}
}
return(entries);
}
static private void Delete(long idvalue, SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM JournalEntries WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", idvalue);
cmd.ExecuteNonQuery();
}
}
static public void Delete(long idvalue, SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM JournalEntries WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", idvalue);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM MaterialsCommodities WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
internal void UpdateJsonEntry(JObject jo, SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Update JournalEntries set EventData=@EventData where ID=@id", tn))
{
cmd.AddParameterWithValue("@ID", Id);
cmd.AddParameterWithValue("@EventData", jo.ToString());
cmd.ExecuteNonQuery();
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into MaterialsCommodities (Category,Name,FDName,Type,ShortName,Colour,Flags) values (@category,@name,@fdname,@type,@shortname,@colour,@flags)"))
{
cmd.AddParameterWithValue("@category", category);
cmd.AddParameterWithValue("@name", name);
cmd.AddParameterWithValue("@fdname", fdname);
cmd.AddParameterWithValue("@type", type);
cmd.AddParameterWithValue("@shortname", shortname);
cmd.AddParameterWithValue("@colour", colour.ToArgb());
cmd.AddParameterWithValue("@flags", flags);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from MaterialsCommodities"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into TravelLogUnit (Name, type, size, Path, CommanderID) values (@name, @type, @size, @Path, @CommanderID)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from TravelLogUnit"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return(true);
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM Bookmarks WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
bookmarks.RemoveAll(x => x.id == id); // remove from list any containing id.
return(true);
}
}
private bool ImportJump(ImportSystem sys)
{
DateTime eventtime = DateTime.SpecifyKind((DateTime)sys.Timestamp, DateTimeKind.Local).ToUniversalTime();
try
{
using (SQLiteConnectionUser conn = new SQLiteConnectionUser(true, EDDbAccessMode.Writer))
{
using (DbCommand exists = conn.CreateCommand("SELECT 1 FROM JournalEntries WHERE EventTypeId = 280 AND commanderId = @com AND EventTime between @low AND @high"))
{
AddParameterWithValue(exists, "@com", Commander);
AddParameterWithValue(exists, "@low", eventtime.AddSeconds(-10));
AddParameterWithValue(exists, "@high", eventtime.AddSeconds(10));
DbDataReader rdr = exists.ExecuteReader();
if (rdr.Read())
{
return(true);
}
}
}
using (SQLiteConnectionUser conn = new SQLiteConnectionUser(true, EDDbAccessMode.Writer))
{
using (DbTransaction txn = conn.BeginTransaction())
{
using (DbCommand cmd = conn.CreateCommand("Insert into JournalEntries (TravelLogId,CommanderId,EventTypeId,EventType,EventTime,EventData,EdsmId,Synced) " +
"values (@tli,@cid,@eti,@et,@etime,@edata,@edsmid,@synced)", txn))
{
AddParameterWithValue(cmd, "@tli", 0);
AddParameterWithValue(cmd, "@cid", Commander);
AddParameterWithValue(cmd, "@eti", 280); //EDDiscovery.EliteDangerous.JournalTypeEnum.FSDJump
AddParameterWithValue(cmd, "@et", "FSDJump");
JObject je = new JObject();
je["timestamp"] = eventtime.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'");
je["event"] = "FSDJump";
je["StarSystem"] = (sys.SysName);
AddParameterWithValue(cmd, "@etime", eventtime);
AddParameterWithValue(cmd, "@edata", je.ToString());
AddParameterWithValue(cmd, "@edsmid", 0);
AddParameterWithValue(cmd, "@synced", 0);
cmd.ExecuteNonQuery();
txn.Commit();
return(true);
}
}
}
}
catch
{ return(false); }
}
protected bool UpdateJson(JObject jo, SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Update JournalEntries set EventData=@EventData where ID=@id", tn))
{
cmd.AddParameterWithValue("@ID", Id);
cmd.AddParameterWithValue("@EventData", jo.ToString());
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
internal bool Add(JObject jo, SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Insert into JournalEntries (EventTime, TravelLogID, CommanderId, EventTypeId , EventType, EventData, Synced) values (@EventTime, @TravelLogID, @CommanderID, @EventTypeId , @EventStrName, @EventData, @Synced)", tn))
{
cmd.AddParameterWithValue("@EventTime", EventTimeUTC); // MUST use UTC connection
cmd.AddParameterWithValue("@TravelLogID", TLUId);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
cmd.AddParameterWithValue("@EventTypeId", EventTypeID);
cmd.AddParameterWithValue("@EventStrName", EventTypeStr);
cmd.AddParameterWithValue("@EventData", jo.ToString());
cmd.AddParameterWithValue("@Synced", Synced);
cmd.ExecuteNonQuery();
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from JournalEntries"))
{
Id = (long)cmd2.ExecuteScalar();
}
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into SystemNote (Name, Time, Note, journalid, edsmid) values (@name, @time, @note, @journalid, @edsmid)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@note", Note);
cmd.AddParameterWithValue("@journalid", Journalid);
cmd.AddParameterWithValue("@edsmid", EdsmId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from SystemNote"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
globalSystemNotes.Add(this);
return(true);
}
}
public static void UpdateCommanderID(long journalid, int cmdrid)
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("Update JournalEntries set CommanderID = @cmdrid where ID=@journalid"))
{
cmd.AddParameterWithValue("@journalid", journalid);
cmd.AddParameterWithValue("@cmdrid", cmdrid);
System.Diagnostics.Trace.WriteLine(string.Format("Update cmdr id ID {0} with map colour", journalid));
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
}
}
public static void Delete(int cmdrid)
{
commanders.Remove(cmdrid);
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
using (DbCommand cmd = conn.CreateCommand("UPDATE Commanders SET Deleted = 1 WHERE Id = @Id"))
{
cmd.AddParameterWithValue("@Id", cmdrid);
cmd.ExecuteNonQuery();
}
}
}
public void DeleteCommander(EDCommander cmdr)
{
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
using (DbCommand cmd = conn.CreateCommand("UPDATE Commanders SET Deleted = 1 WHERE Id = @Id"))
{
cmd.AddParameterWithValue("@Id", cmdr.Nr);
cmd.ExecuteNonQuery();
}
}
LoadCommanders(); // refresh in-memory copy
}
