public bool Add(SQLiteConnectionED 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);
}
}
static public List <VisitedSystemsClass> GetAll(int commander)
{
List <VisitedSystemsClass> list = new List <VisitedSystemsClass>();
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
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);
}
}
}
public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
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[sys.Name.ToLower()] = sys;
}
return(true);
}
}
}
catch
{
return(false);
}
}
public static bool GetAllBookmarks()
{
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
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 Add(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into SystemNote (Name, Time, Note) values (@name, @time, @note)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from SystemNote"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
globalSystemNotes[Name.ToLower()] = this;
return(true);
}
}
private bool Add(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into TravelLogUnit (Name, type, size, Path) values (@name, @type, @size, @Path)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
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(SQLiteConnectionED 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);
}
}
internal static bool Exist(string name, DateTime time)
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from VisitedSystems where name=@name and Time=@time Order by Time DESC Limit 1"))
{
cmd.AddParameterWithValue("@name", name);
cmd.AddParameterWithValue("@time", time);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
return(!(ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0));
}
}
}
private bool Add(SQLiteConnectionED 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 Update(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Update SystemNote set Name=@Name, Time=@Time, Note=@Note where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Note", Note);
cmd.AddParameterWithValue("@Time", Time);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
globalSystemNotes[Name.ToLower()] = this;
return(true);
}
}
public bool Update(SQLiteConnectionED cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Update TravelLogUnit set Name=@Name, Type=@type, size=@size, Path=@Path where ID=@id", tn))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
public static TravelLogUnit Get(string name)
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM TravelLogUnit WHERE Name = @name ORDER BY Id DESC"))
{
cmd.AddParameterWithValue("@name", name);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(new TravelLogUnit(reader));
}
}
}
}
return(null);
}
public static List <string> GetAllNames()
{
List <string> names = new List <string>();
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("SELECT DISTINCT Name FROM TravelLogUnit"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
names.Add((string)reader["Name"]);
}
}
}
}
return(names);
}
static public VisitedSystemsClass GetLast()
{
List <VisitedSystemsClass> list = new List <VisitedSystemsClass>();
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from VisitedSystems Order by Time DESC Limit 1"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
VisitedSystemsClass sys = new VisitedSystemsClass(ds.Tables[0].Rows[0]);
return(sys);
}
}
}
public static VisitedSystemsClass GetLast(int cmdrid, DateTime before)
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM VisitedSystems WHERE Commander = @commander AND Time < @before ORDER BY Time DESC LIMIT 1"))
{
cmd.AddParameterWithValue("@commander", cmdrid);
cmd.AddParameterWithValue("@before", before);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(new VisitedSystemsClass(reader));
}
}
}
}
return(null);
}
public static List <VisitedSystemsClass> GetAll(TravelLogUnit tlu)
{
List <VisitedSystemsClass> vsc = new List <VisitedSystemsClass>();
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM VisitedSystems WHERE Source = @source ORDER BY Time ASC"))
{
cmd.AddParameterWithValue("@source", tlu.id);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
vsc.Add(new VisitedSystemsClass(reader));
}
}
}
}
return(vsc);
}
private bool Update(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Update Bookmarks set StarName=@sname, x = @xp, y = @yp, z = @zp, Time=@time, Heading = @head, Note=@note where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
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);
bookmarks.RemoveAll(x => x.id == id); // remove from list any containing id.
bookmarks.Add(this);
return(true);
}
}
private bool Update(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Update VisitedSystems set Name=@Name, Time=@Time, Unit=@Unit, Commander=@commander, Source=@Source, edsm_sync=@edsm_sync, map_colour=@map_colour, X=@x, Y=@y, Z=@z, id_edsm_assigned=@id_edsm_assigned where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
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);
return(true);
}
}
static public List <TravelLogUnit> GetAll()
{
List <TravelLogUnit> list = new List <TravelLogUnit>();
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from TravelLogUnit"))
{
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)
{
TravelLogUnit sys = new TravelLogUnit(dr);
list.Add(sys);
}
return(list);
}
}
}