While the current debate on the nature of the Aether is being resolved, I have taken a break from coding functionality to look at the database situation. VB6 Aurora uses an Access database and writes to that DB frequently (One of the reasons for the slow performance).
C# Aurora (at the moment) also uses Access, although everything is loaded into memory at game start (about 10-12 seconds on my PC). I have, however, run into a problem.
My intention is to save at user request, rather than the continual save in VB6 Aurora. One option is to flag every object that is changed/deleted at any point and then update the DB based on those flags. However, that is open to errors (if I forget to flag something) and I have to remember to ignore any 'deleted' records. So I developed a cunning plan. I would backup the DB first, then simply remove all non-static data relating to the current game and replace with it whatever was currently held in memory. Even if this took a few minutes, it would still be far superior to the current situation of slow increments and I am not going to miss anything.
Yesterday I finally got around to implementing this plan and discovered you can't use Bulk Insert on an Access database. Aaagh!
So faced with going through all the existing code and flagging every change or simply changing the database, I have decided on the latter. Fortunately the program was set up with the intention of being flexible on the DB and all data access is handled within a single class. For example, one function accepts SQL and returns a data table so I can change the code in that function without affecting any of the code that calls it.
So now the question is, which database? Or do I even need a database? Another option is to save to XML or basic files.
I use SQL Server at work and there are a few different versions, including SQL Server Express, SQL Server Local DB and Compact Edition. For deployment purposes, the last of those options seems best but Microsoft have discontinued the product with no replacement apparent. The former two require SQL Server installs on the target PC, which is not ideal.
So I have been looking at SQLite. This seems to fulfil what I need and apparently I can write a series of records before committing the transaction, which should make things much faster. So, for those who know much more about this than me, a couple of questions.
1) Does SQlite seem like a good idea in this situation. If not, can you suggest an alternative?
2) If SQLite is suitable, which GUI should I choose? So far I have downloaded trial versions of:
a) DB Browser for SQLite (basic but free)
b) SQLiteManager (not keen as it flickered a LOT)
c) SQLite Expert ($100 but my favourite so far)
Any comments appreciated.