c# - Database seems to update during execution, then changes disappear

Question

I'm trying to create a recipe book in C# where I can easily search for what recipes I can make with what ingredients are on hand. This is mostly a learning exercise for me at this point.

Right now, I'm trying to test updating the tables in my database. It seems to work during one instance of the code, but the changes are gone the next time I run it.

So I have a table in my database like this:

I create a small list of example recipes from a json file

var js = new JsonImporter() { SourceDirectory = Path.Combine(Directory.GetCurrentDirectory(), "test_data") };
List<Recipe> recipes = js.ImportData<Recipe>("test_recipes.json");

Connect to my table and InsertAllOnSubmit:

DataContext db = new DataContext(connectionString);
Table<Recipe> db_recipes = db.GetTable<Recipe>();
db_recipes.InsertAllOnSubmit(recipes);

Show how many entries are in the table, submit the changes, refresh the number of entries, and show how many are in the table again:

MessageBox.Show(db_recipes.Count().ToString());
db.SubmitChanges();
db_recipes = db.GetTable<Recipe>();
MessageBox.Show(db_recipes.Count().ToString());

The first messagebox shows 1 (I have one row in the table that I entered manually via the Server Explorer), and the second message says 3. This is what I expect, since I am adding 2 entries.

However, once I run the code again, the message boxes again say 1 and 3, instead of always incrementing by 2. So it seems to me like the database is not saving. Can anybody help me figure out what's going on here?

question from:https://stackoverflow.com/questions/65893817/database-seems-to-update-during-execution-then-changes-disappear

Answer 1

You never said what database you use but I think it highly likely to be file based, either Access or SQL Server (in "attach the mdf file at runtime" mode)

Often with file based databases we end up setting them up so that there is a version of the db file in the project folder alongside the source code. This is copied to the output directory every time the project is built and the output directory contains the exe and the db. The exe modifies the db in the output folder. The next time it is built / run the blank db from the source code folder is copied over the top of the db that was modified in the output folder

Run your project, get the exe to modify the db, stop the exe and then search the whole project folder (using windows file search) or even the whole hard disk for the name of your db file. You'll figure out what's going on by looking at the file modified dates..

If it is a file based db thing, find the db in CS Solution explorer, click it and set its "copy to output" setting to "copy if newer" so that it only wipes the output db if eg the scheme has changed