Apart from system bodies, the game deletes the existing table and inserts the data from the program.
Does the delete for each table happen immediately prior to the insert, or do you do all the deletes at once and then all of the inserts?
I know you are opening and closing a connection for each insert. Are you reusing the same connection, or creating a new connection each time?
What would happen if you tried to insert a duplicate primary key? Would that possibly result in a silent failure that either ignores the duplicate row or overwrites the original with the duplicate?
Each table is a completely separate function. Open connection, delete table, open transaction, create all inserts, commit transaction, close connection. Move to next function.
Here is some example code
public void SaveSystems()
{
try
{
// establish a database connection
SQLiteConnection AuroraDB = new SQLiteConnection(GlobalValues.AuroraConnectionString);
AuroraDB.Open();
// delete the existing records
SQLiteCommand del = new SQLiteCommand("DELETE FROM FCT_System WHERE GameID = " + GameID, AuroraDB);
del.ExecuteNonQuery();
using (var cmd = new SQLiteCommand(AuroraDB))
{
using (SQLiteTransaction transaction = AuroraDB.BeginTransaction())
{
// insert new records
foreach (StarSystem obj in SystemList.Values)
{
cmd.CommandText =
@"INSERT INTO FCT_System (SystemID, SystemNumber, Age, AbundanceModifier, Stars, GameID, JumpPointSurveyPoints, SystemTypeID, DustDensity, SolSystem, NoSensorChecks )
VALUES ( @SystemID, @SystemNumber, @Age, @AbundanceModifier, @Stars, @GameID, @JumpPointSurveyPoints, @SystemTypeID, @DustDensity, @SolSystem, @NoSensorChecks)";
cmd.Parameters.AddWithValue("@SystemID", obj.SystemID);
cmd.Parameters.AddWithValue("@SystemNumber", obj.SystemNumber);
cmd.Parameters.AddWithValue("@Age", obj.Age);
cmd.Parameters.AddWithValue("@AbundanceModifier", obj.AbundanceModifier);
cmd.Parameters.AddWithValue("@Stars", obj.Stars);
cmd.Parameters.AddWithValue("@GameID", GameID);
cmd.Parameters.AddWithValue("@JumpPointSurveyPoints", obj.JumpPointSurveyPoints);
cmd.Parameters.AddWithValue("@SystemTypeID", obj.SystemTypeID);
cmd.Parameters.AddWithValue("@DustDensity", obj.DustDensity);
cmd.Parameters.AddWithValue("@SolSystem", obj.SolSystem);
cmd.Parameters.AddWithValue("@NoSensorChecks", obj.NoSensorChecks);
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
AuroraDB.Close();
}
catch (Exception error) { GlobalValues.ErrorHandler(error, 1430); return; }
}
