I'm developing a front end for retro gaming. Upon boot I'm parsing a lot of xml files from another application so I end up with a List of "systems" each containing a list of "games". Parsing the xml data is really fast, but writing all this to the sqlite database is not. Currently it takes ~25 seconds (20.000 records) which may not be too bad, but hopefully I can get some ideas on how to make it even faster.
Ideally I want to be able to compare my object containing all the data with the sqlite database. If anything has changed in the xml files/parsed object the data should be updated or deleted from the database. Is there a better way of solving this then doing a initial import and then dump all of the database and compare it to the object? Basically the whole import code backwards...
This is my current code:
public static void PopulateDatabase(List<RetroBoxSystem> systems)
{
using (SQLiteConnection con = new SQLiteConnection("Data Source=RetroBox.db;Version=3;"))
{
con.Open();
using (SQLiteTransaction tr = con.BeginTransaction())
{
using (SQLiteCommand cmd = con.CreateCommand())
{
foreach (var system in systems)
{
cmd.CommandText = @"INSERT OR IGNORE INTO systems(system_id, name)
VALUES ((SELECT system_id FROM systems WHERE name = @name), @name)";
cmd.Parameters.Add(new SQLiteParameter("@name", system.Name));
cmd.ExecuteNonQuery();
}
}
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = @"INSERT OR IGNORE INTO games(game_id, system_id, name, description, cloneof, manufacturer, genre, rating, year)
VALUES ((SELECT game_id FROM games WHERE name = @name), (SELECT system_id FROM systems WHERE name = @system), @name, @description, @cloneof, @manufacturer, @genre, @rating, @year)";
foreach (var system in systems)
{
foreach (var g in system.GameList)
{
cmd.Parameters.Add(new SQLiteParameter("@system", system.Name));
cmd.Parameters.Add(new SQLiteParameter("@name", g.Name));
cmd.Parameters.Add(new SQLiteParameter("@description", g.Description));
cmd.Parameters.Add(new SQLiteParameter("@cloneof", g.CloneOf));
cmd.Parameters.Add(new SQLiteParameter("@manufacturer", g.Manufacturer));
cmd.Parameters.Add(new SQLiteParameter("@genre", g.Genre));
cmd.Parameters.Add(new SQLiteParameter("@rating", g.Rating));
cmd.Parameters.Add(new SQLiteParameter("@year", g.Year));
cmd.ExecuteNonQuery();
}
}
}
tr.Commit();
}
}
}
Aucun commentaire:
Enregistrer un commentaire