private static void UpdateTableSchema(SQLiteConnectionED cn, string name, object schema)
        {
            HashSet <string> columns = new HashSet <string>();

            using (DbCommand cmd = cn.CreateCommand("PRAGMA table_info(@tablename)"))
            {
                cmd.AddParameterWithValue("@tablename", name);
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columns.Add(((string)reader["name"]).ToLower());
                    }
                }
            }

            EnumerateSchema(schema, (colname, coldef) =>
            {
                if (!columns.Contains(colname.ToLower()))
                {
                    string altercmd = $"ALTER TABLE {name} ADD COLUMN {colname} {coldef}";
                    System.Diagnostics.Trace.WriteLine(altercmd);
                    using (DbCommand cmd = cn.CreateCommand(altercmd))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            });
        }
Exemple #2
0
        private bool Add(SQLiteConnectionED cn)
        {
            using (DbCommand cmd = cn.CreateCommand("Insert into routes_expeditions (name, start, end) values (@name, @start, @end)"))
            {
                cmd.AddParameterWithValue("@name", Name);
                cmd.AddParameterWithValue("@start", StartDate);
                cmd.AddParameterWithValue("@end", EndDate);

                SQLiteDBClass.SQLNonQueryText(cn, cmd);

                using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from routes_expeditions"))
                {
                    Id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
                }

                using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
                {
                    cmd2.AddParameter("@routeid", DbType.String);
                    cmd2.AddParameter("@name", DbType.String);

                    foreach (var sysname in Systems)
                    {
                        cmd2.Parameters["@routeid"].Value = Id;
                        cmd2.Parameters["@name"].Value    = sysname;
                        SQLiteDBClass.SQLNonQueryText(cn, cmd2);
                    }
                }

                return(true);
            }
        }
Exemple #3
0
        private bool Update(SQLiteConnectionED cn)
        {
            using (DbCommand cmd = cn.CreateCommand("UPDATE routes_expeditions SET name=@name, start=@start, end=@end WHERE id=@id"))
            {
                cmd.AddParameterWithValue("@id", Id);
                cmd.AddParameterWithValue("@name", Name);
                cmd.AddParameterWithValue("@start", StartDate);
                cmd.AddParameterWithValue("@end", EndDate);
                SQLiteDBClass.SQLNonQueryText(cn, cmd);

                using (DbCommand cmd2 = cn.CreateCommand("DELETE FROM route_systems WHERE routeid=@routeid"))
                {
                    cmd2.AddParameterWithValue("@routeid", Id);
                    SQLiteDBClass.SQLNonQueryText(cn, cmd2);
                }

                using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
                {
                    cmd2.AddParameter("@routeid", DbType.String);
                    cmd2.AddParameter("@name", DbType.String);

                    foreach (var sysname in Systems)
                    {
                        cmd2.Parameters["@routeid"].Value = Id;
                        cmd2.Parameters["@name"].Value    = sysname;
                        SQLiteDBClass.SQLNonQueryText(cn, cmd2);
                    }
                }

                return(true);
            }
        }
        private static void UpdateDbSchema(SQLiteConnectionED cn, dynamic schema)
        {
            HashSet <string> tables  = new HashSet <string>();
            HashSet <string> indexes = new HashSet <string>();

            using (DbCommand cmd = cn.CreateCommand("SELECT name, type FROM sqlite_master"))
            {
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if ((string)reader["type"] == "table")
                        {
                            tables.Add((string)reader["name"]);
                        }
                        else if ((string)reader["type"] == "index")
                        {
                            indexes.Add((string)reader["name"]);
                        }
                    }
                }
            }

            EnumerateSchema((object)schema.Tables, (name, tblschema) =>
            {
                if (!tables.Contains(name))
                {
                    CreateTable(cn, name, tblschema);
                }
                else
                {
                    UpdateTableSchema(cn, name, tblschema);
                }
            });
            EnumerateSchema((object)schema.UniqueIndexes, (name, idxschema) =>
            {
                if (!indexes.Contains(name))
                {
                    string idxcmd = $"CREATE UNIQUE INDEX {name} ON {idxschema}";
                    System.Diagnostics.Trace.WriteLine(idxcmd);
                    using (DbCommand cmd = cn.CreateCommand(idxcmd))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            });
            EnumerateSchema((object)schema.Indexes, (name, idxschema) =>
            {
                if (!indexes.Contains(name))
                {
                    string idxcmd = $"CREATE INDEX {name} ON {idxschema}";
                    System.Diagnostics.Trace.WriteLine(idxcmd);
                    using (DbCommand cmd = cn.CreateCommand(idxcmd))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            });
        }
        private bool Add(SQLiteConnectionED 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 static void UpgradeUserDB108(SQLiteConnectionED conn)
        {
            string query1 = "ALTER TABLE Commanders ADD COLUMN JournalDir TEXT";

            PerformUpgrade(conn, 108, true, false, new[] { query1 }, () =>
            {
                try
                {
                    List <int> commandersToMigrate = new List <int>();
                    using (DbCommand cmd = conn.CreateCommand("SELECT Id, NetLogDir, JournalDir FROM Commanders"))
                    {
                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {
                            while (rdr.Read())
                            {
                                int nr            = Convert.ToInt32(rdr["Id"]);
                                object netlogdir  = rdr["NetLogDir"];
                                object journaldir = rdr["JournalDir"];

                                if (netlogdir != DBNull.Value && journaldir == DBNull.Value)
                                {
                                    string logdir = Convert.ToString(netlogdir);

                                    if (logdir != null && System.IO.Directory.Exists(logdir) && System.IO.Directory.EnumerateFiles(logdir, "journal*.log").Any())
                                    {
                                        commandersToMigrate.Add(nr);
                                    }
                                }
                            }
                        }
                    }

                    using (DbCommand cmd2 = conn.CreateCommand("UPDATE Commanders SET JournalDir=NetLogDir WHERE Id=@Nr"))
                    {
                        cmd2.AddParameter("@Nr", System.Data.DbType.Int32);

                        foreach (int nr in commandersToMigrate)
                        {
                            cmd2.Parameters["@Nr"].Value = nr;
                            cmd2.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Trace.WriteLine("UpgradeUser108 exception: " + ex.Message);
                }
            });
        }
        public static List <WantedSystemClass> GetAllWantedSystems()
        {
            try
            {
                using (SQLiteConnectionED cn = new SQLiteConnectionED())
                {
                    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);
            }
        }
Exemple #8
0
        public static long GetTotalDistances()
        {
            long value = 0;

            try
            {
                using (SQLiteConnectionED cn = new SQLiteConnectionED())
                {
                    using (DbCommand cmd = cn.CreateCommand("select Count(*) from Distances"))
                    {
                        using (DbDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                value = (long)reader["Count(*)"];
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
                System.Diagnostics.Trace.WriteLine(ex.StackTrace);
            }

            return(value);
        }
Exemple #9
0
        public static DistanceClass GetDistanceClass(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2)
        {
            if (s1 == null || s2 == null)
            {
                return(null);
            }

            try
            {
                using (SQLiteConnectionED cn = new SQLiteConnectionED())
                {
                    using (DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE (NameA = @NameA and NameB = @NameB) OR (NameA = @NameB and NameB = @NameA) limit 1"))
                    {
                        cmd.AddParameterWithValue("@NameA", s1.name);
                        cmd.AddParameterWithValue("@NameB", s2.name);

                        DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);

                        if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)     // if found.
                        {
                            DistanceClass dist = new DistanceClass(ds.Tables[0].Rows[0]);
                            return(dist);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
                System.Diagnostics.Trace.WriteLine(ex.StackTrace);
            }

            return(null);
        }
Exemple #10
0
        public static List <DistanceClass> GetDistancesByStatus(int status)
        {
            List <DistanceClass> ldist = new List <DistanceClass>();

            try
            {
                using (SQLiteConnectionED cn = new SQLiteConnectionED())
                {
                    using (DbCommand cmd = cn.CreateCommand("select * from Distances WHERE status='" + status.ToString() + "'"))
                    {
                        DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);

                        if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                DistanceClass dist = new DistanceClass(dr);
                                ldist.Add(dist);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
                System.Diagnostics.Trace.WriteLine(ex.StackTrace);
            }

            return(ldist);
        }
Exemple #11
0
        private bool Delete(SQLiteConnectionED cn)
        {
            using (DbCommand cmd = cn.CreateCommand("Delete From  Distances where ID=@id"))
            {
                cmd.AddParameterWithValue("@ID", id);

                SQLiteDBClass.SQLNonQueryText(cn, cmd);
                return(true);
            }
        }
        private bool Delete(SQLiteConnectionED cn)
        {
            using (DbCommand cmd = cn.CreateCommand("DELETE FROM wanted_systems WHERE id = @id"))
            {
                cmd.AddParameterWithValue("@id", id);

                SQLiteDBClass.SQLNonQueryText(cn, cmd);

                return(true);
            }
        }
Exemple #13
0
        public static bool Delete(DistancsEnum distsource)
        {
            using (SQLiteConnectionED cn = new SQLiteConnectionED())
            {
                using (DbCommand cmd = cn.CreateCommand("Delete from Distances where Status=@Status"))
                {
                    cmd.AddParameterWithValue("@Status", (int)distsource);
                    SQLiteDBClass.SQLNonQueryText(cn, cmd);
                }
            }

            return(true);
        }
Exemple #14
0
        public static List <SavedRouteClass> GetAllSavedRoutes()
        {
            List <SavedRouteClass> retVal = new List <SavedRouteClass>();

            try
            {
                using (SQLiteConnectionED cn = new SQLiteConnectionED())
                {
                    using (DbCommand cmd1 = cn.CreateCommand("select * from routes_expeditions"))
                    {
                        DataSet ds1 = SQLiteDBClass.SQLQueryText(cn, cmd1);

                        if (ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
                        {
                            using (DbCommand cmd2 = cn.CreateCommand("select * from route_systems"))
                            {
                                DataSet ds2 = SQLiteDBClass.SQLQueryText(cn, 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
            {
            }

            return(retVal);
        }
Exemple #15
0
        private bool Update(SQLiteConnectionED cn, DbTransaction tn = null)
        {
            using (DbCommand cmd = cn.CreateCommand("Update Distances  set NameA=@NameA, NameB=@NameB, Dist=@Dist, commandercreate=@commandercreate, CreateTime=@CreateTime, status=@status, id_edsm=@id_edsm  where ID=@id", tn))
            {
                cmd.AddParameterWithValue("@ID", id);
                cmd.AddParameterWithValue("@NameA", NameA);
                cmd.AddParameterWithValue("@NameB", NameB);
                cmd.AddParameterWithValue("@Dist", Dist);
                cmd.AddParameterWithValue("@CommanderCreate", CommanderCreate);
                cmd.AddParameterWithValue("@CreateTime", CreateTime);
                cmd.AddParameterWithValue("@Status", Status);
                cmd.AddParameterWithValue("@id_edsm", id_edsm);

                SQLiteDBClass.SQLNonQueryText(cn, cmd);
                return(true);
            }
        }
Exemple #16
0
        public static void FillVisitedSystems(List <VisitedSystemsClass> visitedSystems, bool usedb)
        {
            try
            {
                using (SQLiteConnectionED cn = new SQLiteConnectionED())
                {
                    DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE(NameA = @NameA and NameB = @NameB) OR(NameA = @NameB and NameB = @NameA) limit 1");

                    for (int i = 1; i < visitedSystems.Count; i++)                 // now we filled in current system, fill in previous system (except for last)
                    {
                        VisitedSystemsClass cur  = visitedSystems[i];
                        VisitedSystemsClass prev = visitedSystems[i - 1];
                        cur.prevSystem = prev.curSystem;

                        double dist = SystemClass.Distance(cur.curSystem, prev.curSystem); // Try the easy way

                        if (dist < 0 && usedb)                                             // failed, and use the db is allowed..
                        {
                            cmd.Parameters.Clear();
                            cmd.AddParameterWithValue("@NameA", cur.Name);
                            cmd.AddParameterWithValue("@NameB", prev.Name);

                            using (DbDataReader reader = cmd.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    DistanceClass dst = new DistanceClass(reader);
                                    dist = dst.Dist;
                                }
                            }
                        }

                        if (dist > 0)
                        {
                            cur.strDistance = dist.ToString("0.00");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
                System.Diagnostics.Trace.WriteLine(ex.StackTrace);
            }
        }
        private static void CreateTable(SQLiteConnectionED cn, string name, object schema)
        {
            List <string> columndefs = new List <string>();

            EnumerateSchema(schema, (colname, colschema) =>
            {
                columndefs.Add($"{colname} {colschema}");
            });

            string createstmt = $"CREATE TABLE {name} ({String.Join(",", columndefs)})";

            System.Diagnostics.Trace.WriteLine(createstmt);

            using (DbCommand cmd = cn.CreateCommand(createstmt))
            {
                cmd.ExecuteNonQuery();
            }
        }
Exemple #18
0
        public bool Store()
        {
            using (SQLiteConnectionED cn = new SQLiteConnectionED())
            {
                bool ret;
                ret = Store(cn);

                if (ret == true)
                {
                    using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Distances"))
                    {
                        id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
                    }

                    return(true);
                }

                return(ret);
            }
        }
Exemple #19
0
        private bool Store(SQLiteConnectionED cn, DbTransaction tn = null)
        {
            if (CommanderCreate == null)
            {
                CommanderCreate = "";
            }

            using (DbCommand cmd = cn.CreateCommand("Insert into Distances (NameA, NameB, Dist, CommanderCreate, CreateTime, Status, id_edsm) values (@NameA, @NameB, @Dist, @CommanderCreate, @CreateTime, @Status, @id_edsm)", tn))
            {
                cmd.AddParameterWithValue("@NameA", NameA);
                cmd.AddParameterWithValue("@NameB", NameB);
                cmd.AddParameterWithValue("@Dist", Dist);
                cmd.AddParameterWithValue("@CommanderCreate", CommanderCreate);
                cmd.AddParameterWithValue("@CreateTime", CreateTime);
                cmd.AddParameterWithValue("@Status", Status);
                cmd.AddParameterWithValue("@id_edsm", id_edsm);

                SQLiteDBClass.SQLNonQueryText(cn, cmd);
            }

            return(true);
        }
Exemple #20
0
        public static bool PutSettingString(string key, string strvalue , SQLiteConnectionED cn )
        {
            try
            {
                if (keyExists(key,cn))
                {
                    using (DbCommand cmd = cn.CreateCommand("Update Register set ValueString = @ValueString Where ID=@ID"))
                    {
                        cmd.AddParameterWithValue("@ID", key);
                        cmd.AddParameterWithValue("@ValueString", strvalue);

                        SQLNonQueryText(cn, cmd);

                        return true;
                    }
                }
                else
                {
                    using (DbCommand cmd = cn.CreateCommand("Insert into Register (ID, ValueString) values (@ID, @valint)"))
                    {
                        cmd.AddParameterWithValue("@ID", key);
                        cmd.AddParameterWithValue("@valint", strvalue);

                        SQLNonQueryText(cn, cmd);
                        return true;
                    }
                }
            }
            catch
            {
                return false;
            }
        }
Exemple #21
0
        public static bool keyExists(string sKey, SQLiteConnectionED cn)
        {
            try
            {
                using (DbCommand cmd = cn.CreateCommand("select ID from Register WHERE ID=@key"))
                {
                    cmd.AddParameterWithValue("@key", sKey);

                    DataSet ds = SQLQueryText(cn, cmd);

                    return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0);        // got a value, true
                }
            }
            catch
            {
            }

            return false;
        }
Exemple #22
0
        public static string GetSettingString(string key, string defaultvalue, SQLiteConnectionED cn)
        {
            try
            {
                using (DbCommand cmd = cn.CreateCommand("SELECT ValueString from Register WHERE ID = @ID"))
                {
                    cmd.AddParameterWithValue("@ID", key);
                    object ob = SQLScalar(cn, cmd);

                    if (ob == null)
                        return defaultvalue;

                    if (ob == System.DBNull.Value)
                        return defaultvalue;

                    string val = (string)ob;

                    return val;
                }
            }
            catch
            {
                return defaultvalue;
            }
        }
Exemple #23
0
        public static int GetSettingInt(string key, int defaultvalue, SQLiteConnectionED cn )
        {
            try
            {
                using (DbCommand cmd = cn.CreateCommand("SELECT ValueInt from Register WHERE ID = @ID"))
                {
                    cmd.AddParameterWithValue("@ID", key);

                    object ob = SQLScalar(cn, cmd);

                    if (ob == null)
                        return defaultvalue;

                    int val = Convert.ToInt32(ob);

                    return val;
                }
            }
            catch
            {
                return defaultvalue;
            }
        }
Exemple #24
0
 private static void ExecuteQuery(SQLiteConnectionED conn, string query)
 {
     using (DbCommand command = conn.CreateCommand(query))
         command.ExecuteNonQuery();
 }
        private static void UpdateTableSchema(SQLiteConnectionED cn, string name, object schema)
        {
            HashSet<string> columns = new HashSet<string>();

            using (DbCommand cmd = cn.CreateCommand("PRAGMA table_info(@tablename)"))
            {
                cmd.AddParameterWithValue("@tablename", name);
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columns.Add(((string)reader["name"]).ToLower());
                    }
                }
            }

            EnumerateSchema(schema, (colname, coldef) =>
            {
                if (!columns.Contains(colname.ToLower()))
                {
                    string altercmd = $"ALTER TABLE {name} ADD COLUMN {colname} {coldef}";
                    System.Diagnostics.Trace.WriteLine(altercmd);
                    using (DbCommand cmd = cn.CreateCommand(altercmd))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            });
        }
        private static void UpdateDbSchema(SQLiteConnectionED cn, dynamic schema)
        {
            HashSet<string> tables = new HashSet<string>();
            HashSet<string> indexes = new HashSet<string>();

            using (DbCommand cmd = cn.CreateCommand("SELECT name, type FROM sqlite_master"))
            {
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if ((string)reader["type"] == "table")
                        {
                            tables.Add((string)reader["name"]);
                        }
                        else if ((string)reader["type"] == "index")
                        {
                            indexes.Add((string)reader["name"]);
                        }
                    }
                }
            }

            EnumerateSchema((object)schema.Tables, (name, tblschema) =>
            {
                if (!tables.Contains(name))
                {
                    CreateTable(cn, name, tblschema);
                }
                else
                {
                    UpdateTableSchema(cn, name, tblschema);
                }
            });
            EnumerateSchema((object)schema.UniqueIndexes, (name, idxschema) =>
            {
                if (!indexes.Contains(name))
                {
                    string idxcmd = $"CREATE UNIQUE INDEX {name} ON {idxschema}";
                    System.Diagnostics.Trace.WriteLine(idxcmd);
                    using (DbCommand cmd = cn.CreateCommand(idxcmd))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            });
            EnumerateSchema((object)schema.Indexes, (name, idxschema) =>
            {
                if (!indexes.Contains(name))
                {
                    string idxcmd = $"CREATE INDEX {name} ON {idxschema}";
                    System.Diagnostics.Trace.WriteLine(idxcmd);
                    using (DbCommand cmd = cn.CreateCommand(idxcmd))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            });
        }
        private static void CreateTable(SQLiteConnectionED cn, string name, object schema)
        {
            List<string> columndefs = new List<string>();

            EnumerateSchema(schema, (colname, colschema) =>
            {
                columndefs.Add($"{colname} {colschema}");
            });

            string createstmt = $"CREATE TABLE {name} ({String.Join(",", columndefs)})";

            System.Diagnostics.Trace.WriteLine(createstmt);

            using (DbCommand cmd = cn.CreateCommand(createstmt))
            {
                cmd.ExecuteNonQuery();
            }
        }
Exemple #28
0
        private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date, bool removenonedsmids)
        {
            List <DistanceClass> toupdate = new List <DistanceClass>();
            List <DistanceClass> newpairs = new List <DistanceClass>();
            DateTime             maxdate  = DateTime.Parse(date, new CultureInfo("sv-SE"));

            bool emptydatabase = GetTotalDistances() == 0;            // if empty database, we can skip the lookup

            using (SQLiteConnectionED cn = new SQLiteConnectionED())  // open the db
            {
                int c = 0;

                DbCommand cmd = null;

                int lasttc = Environment.TickCount;

                try
                {
                    cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1");   // 1 return matching

                    while (jr.Read())
                    {
                        if (jr.TokenType == JsonToken.StartObject)
                        {
                            JObject jo = JObject.Load(jr);

                            DistanceClass dc = new DistanceClass(jo);

                            if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0)
                            {
                                maxdate = dc.CreateTime;
                            }

                            if (++c % 10000 == 0)
                            {
                                Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count());
                                lasttc = Environment.TickCount;
                            }

                            if (emptydatabase)                                                  // empty DB, just store..
                            {
                                newpairs.Add(dc);
                            }
                            else
                            {
                                cmd.Parameters.Clear();
                                cmd.AddParameterWithValue("id", dc.id_edsm);

                                using (DbDataReader reader1 = cmd.ExecuteReader())              // see if ESDM ID is there..
                                {
                                    if (reader1.Read())                                         // its there..
                                    {
                                        DistanceClass dbdc = new DistanceClass(reader1);

                                        // see if EDSM data changed..
                                        if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05)
                                        {
                                            dbdc.NameA = dc.NameA;
                                            dbdc.NameB = dc.NameB;
                                            dbdc.Dist  = dc.Dist;
                                            toupdate.Add(dbdc);
                                        }
                                    }
                                    else                                                                  // not in database..
                                    {
                                        //Console.WriteLine("Add new system " + system.name);
                                        newpairs.Add(dc);
                                    }
                                }
                            }
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("There is a problem using the EDSM distance file." + Environment.NewLine +
                                    "Please perform a manual EDSM distance sync (see Admin menu) next time you run the program ", "ESDM Sync Error");
                }
                finally
                {
                    if (cmd != null)
                    {
                        cmd.Dispose();
                    }
                }
            }

            using (SQLiteConnectionED cn2 = new SQLiteConnectionED())  // open the db
            {
                if (toupdate.Count > 0)
                {
                    using (DbTransaction transaction = cn2.BeginTransaction())
                    {
                        foreach (DistanceClass dc in toupdate)
                        {
                            dc.Update(cn2, transaction);
                        }

                        transaction.Commit();
                    }
                }

                if (newpairs.Count > 0)
                {
                    int count = 0;

                    while (count < newpairs.Count())
                    {
                        using (DbTransaction transaction = cn2.BeginTransaction())
                        {
                            while (count < newpairs.Count())
                            {
                                newpairs[count].Store(cn2, transaction);

                                if (++count % 10000 == 0)
                                {
                                    break;
                                }
                            }

                            Console.WriteLine("EDSM Dist Store Count " + count);
                            transaction.Commit();
                        }
                    }
                }

                if (removenonedsmids)                            // done on a full sync..
                {
                    Console.WriteLine("Delete old ones");
                    using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null"))
                    {
                        SQLiteDBClass.SQLNonQueryText(cn2, cmddel);
                    }
                }
            }

            date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
            return(toupdate.Count + newpairs.Count);
        }
Exemple #29
0
 public static void ExecuteQuery(SQLiteConnectionED conn, string query)
 {
     using (DbCommand command = conn.CreateCommand(query))
         command.ExecuteNonQuery();
 }