private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into Bookmarks (StarName, x, y, z, Time, Heading, Note, PlanetMarks) values (@sname, @xp, @yp, @zp, @time, @head, @note, @pmarks)"))
{
DateTime tme = TimeUTC;
if (TimeUTC < utcswitchover)
{
tme = TimeUTC.ToLocalTime();
}
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", tme);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
cmd.AddParameterWithValue("@pmarks", PlanetaryMarks?.ToJsonString());
cmd.ExecuteNonQuery();
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Bookmarks"))
{
id = (long)cmd2.ExecuteScalar();
}
return(true);
}
}
internal bool Add(SQLiteConnectionUser cn, DbTransaction tn = null)
{
FetchAll();
System.Diagnostics.Debug.WriteLine($"Add TLU {Path} {FileName}");
using (DbCommand cmd = cn.CreateCommand("Insert into TravelLogUnit (Name, type, size, Path, CommanderID) values (@name, @type, @size, @Path, @CommanderID)", tn))
{
cmd.AddParameterWithValue("@name", FileName);
cmd.AddParameterWithValue("@type", Type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
cmd.ExecuteNonQuery();
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from TravelLogUnit"))
{
ID = (long)cmd2.ExecuteScalar();
}
//System.Diagnostics.Debug.WriteLine("Update cache with " + ID);
cache[ID] = this;
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into wanted_systems (systemname) values (@systemname)"))
{
cmd.AddParameterWithValue("@systemname", system);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from wanted_systems"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into wanted_systems (systemname) values (@systemname)"))
{
cmd.AddParameterWithValue("@systemname", system);
cmd.ExecuteNonQuery();
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from wanted_systems"))
{
id = (long)cmd2.ExecuteScalar();
}
return(true);
}
}
public static List <SavedRouteClass> GetAllSavedRoutes()
{
List <SavedRouteClass> retVal = new List <SavedRouteClass>();
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true, mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader))
{
Dictionary <int, List <string> > routesystems = new Dictionary <int, List <string> >();
using (DbCommand cmd = cn.CreateCommand("SELECT routeid, systemname FROM route_systems ORDER BY id ASC"))
{
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int routeid = (int)(long)rdr[0];
string sysname = (string)rdr[1];
if (!routesystems.ContainsKey(routeid))
{
routesystems[routeid] = new List <string>();
}
routesystems[routeid].Add(sysname);
}
}
}
using (DbCommand cmd = cn.CreateCommand("SELECT id, name, start, end, Status FROM routes_expeditions"))
{
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int routeid = (int)(long)rdr[0];
List <string> syslist = routesystems.ContainsKey(routeid) ? routesystems[routeid] : new List <string>();
SavedRouteClass sys = new SavedRouteClass(rdr, syslist);
retVal.Add(sys);
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception " + ex.ToString());
}
return(retVal);
}
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);
}
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Update Bookmarks set StarName=@sname, x = @xp, y = @yp, z = @zp, Time=@time, Heading = @head, Note=@note, PlanetMarks=@pmarks where ID=@id"))
{
DateTime tme = TimeUTC;
if (TimeUTC < utcswitchover)
{
tme = TimeUTC.ToLocalTime();
}
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", tme);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
cmd.AddParameterWithValue("@pmarks", PlanetaryMarks?.ToJsonString());
cmd.ExecuteNonQuery();
return(true);
}
}
public static bool LoadBookmarks()
{
System.Diagnostics.Debug.Assert(gbl == null); // no double instancing!
gbl = new GlobalBookMarkList();
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);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
BookmarkClass bc = new BookmarkClass(dr);
gbl.globalbookmarks.Add(bc);
}
return(true);
}
}
}
catch
{
return(false);
}
}
public static List <WantedSystemClass> GetAllWantedSystems() // CAN return null
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from wanted_systems"))
{
DataSet ds = cn.SQLQueryText(cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
List <WantedSystemClass> retVal = new List <WantedSystemClass>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
WantedSystemClass sys = new WantedSystemClass(dr);
retVal.Add(sys);
}
return(retVal);
}
}
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception " + ex.ToString());
return(null);
}
}
public static List <WantedSystemClass> GetAllWantedSystems()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from wanted_systems"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
List <WantedSystemClass> retVal = new List <WantedSystemClass>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
WantedSystemClass sys = new WantedSystemClass(dr);
retVal.Add(sys);
}
return(retVal);
}
}
}
catch
{
return(null);
}
}
public static List <WantedSystemClass> GetAllWantedSystems() // CAN return null
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from wanted_systems"))
{
List <WantedSystemClass> retVal = new List <WantedSystemClass>();
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
WantedSystemClass sys = new WantedSystemClass(rdr);
retVal.Add(sys);
}
}
return(retVal);
}
}
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception " + ex.ToString());
return(null);
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM CaptainsLog WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", ID);
cn.SQLNonQueryText(cmd);
return(true);
}
}
static private bool Delete(SQLiteConnectionUser cn, long id)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM CaptainsLog WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
cmd.ExecuteNonQuery();
return(true);
}
}
public static void ClearEDSMID()
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("UPDATE SystemNote SET EdsmId=0"))
{
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
}
}
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 Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM wanted_systems WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
public static List <SavedRouteClass> GetAllSavedRoutes()
{
List <SavedRouteClass> retVal = new List <SavedRouteClass>();
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true, mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader))
{
using (DbCommand cmd1 = cn.CreateCommand("select * from routes_expeditions"))
{
DataSet ds1 = cn.SQLQueryText(cmd1);
if (ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
{
using (DbCommand cmd2 = cn.CreateCommand("select * from route_systems"))
{
DataSet ds2 = cn.SQLQueryText(cmd2);
foreach (DataRow dr in ds1.Tables[0].Rows)
{
DataRow[] syslist = new DataRow[0];
if (ds2.Tables.Count != 0)
{
syslist = ds2.Tables[0].Select(String.Format("routeid = {0}", dr["id"]), "id ASC");
}
SavedRouteClass sys = new SavedRouteClass(dr, syslist);
retVal.Add(sys);
}
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception " + ex.ToString());
}
return(retVal);
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into CaptainsLog (Commander, Time, SystemName, BodyName, Note, Tags, Parameters) values (@c,@t,@s,@b,@n,@g,@p)"))
{
cmd.AddParameterWithValue("@c", Commander);
cmd.AddParameterWithValue("@t", TimeUTC);
cmd.AddParameterWithValue("@s", SystemName);
cmd.AddParameterWithValue("@b", BodyName);
cmd.AddParameterWithValue("@n", Note);
cmd.AddParameterWithValue("@g", Tags);
cmd.AddParameterWithValue("@p", Parameters);
cmd.ExecuteNonQuery();
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from CaptainsLog"))
{
ID = (long)cmd2.ExecuteScalar();
}
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into Bookmarks (StarName, x, y, z, Time, Heading, Note) values (@sname, @xp, @yp, @zp, @time, @head, @note)"))
{
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Bookmarks"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
bookmarks.Add(this);
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into Bookmarks (StarName, x, y, z, Time, Heading, Note, PlanetMarks) values (@sname, @xp, @yp, @zp, @time, @head, @note, @pmarks)"))
{
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
cmd.AddParameterWithValue("@pmarks", PlanetaryMarks?.ToJsonString());
cn.SQLNonQueryText(cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Bookmarks"))
{
id = (long)cn.SQLScalar(cmd2);
}
return(true);
}
}
private bool AddToDbAndGlobal(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into SystemNote (Name, Time, Note, journalid) values (@name, @time, @note, @journalid)"))
{
cmd.AddParameterWithValue("@name", SystemName);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@note", Note);
cmd.AddParameterWithValue("@journalid", Journalid);
cmd.ExecuteNonQuery();
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from SystemNote"))
{
id = (long)cmd2.ExecuteScalar();
}
globalSystemNotes.Add(this);
Dirty = false;
return(true);
}
}
