How to avoid SQLite-db being replaced during update of C# WPF app

Question

I got my app (wpf with a SQLite-db) to work using Squirrel. But when I deploy a new version, the db-file are replaced with an empty db.

I have tried to remove the db file in the newest nupkg, hoping the update will leave the existing db-file. But this will just cause the app to fail during runtime, due to the existing db was deleted by the automatic update.

All the other changes made to the app, Is updated and working.

I have followed this tutorial on youtube: https://youtu.be/W8Qu4qMJyh4

I expect the data stored in the SQLite-db in the app v. 1.0 still to be available in the app v. 2.0.

Answer 1

I assume your db file is saved within the same directory as your application file (.exe, .dll, etc). If that is the case, then you should save it on a different directory. By design, Squirrel creates a new install directory for each update.

from docs.. notice the MyApp.exe for each version app-1.0.0 and app-1.0.1. so you will end up with the same scenario for your db.sqlite if you place it under your application directory.

\%LocalAppData%\MyApp
   \packages
      MyApp-1.0.0.nupkg
      MyApp-1.0.1-delta.nupkg
      MyApp-1.0.1.nupkg   
   \app-1.0.0
      MyApp.exe
   \app-1.0.1
      MyApp.exe

Answer 2

I have a solution to this issue for Winforms. You can configure it out whatever you like. We need to backup the SQLite database file before your application has been updated and then We'll restore it at the first start up after update. To prevent that restore SQLite database file on every start up we will create a setting in application. Follow these

1. Right click your main class
2. Click Properties
3. Click Settings and create new setting like Name=updateDB, Type=bool, Scope=User, Value=True

Now we have created that check for update DB setting. After this we will call to BackupDatabase() function immediately after await Manager.UpdateApp();

private static void BackupDatabase()
    {
        // Get the current Database File
        string dbFile = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "database.db");

        // Set the temporary destination for backup file
        string destination = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\..\\database.db";

        // Backup current DB file to destination
        File.Copy(dbFile, destination, true);
    }

private async void btnUpdateApply_Click(object sender, EventArgs e)
    {
        await manager.UpdateApp();
        BackupDatabase();
    }

We have successfully backup db file. Now we should check updateDB's value to execute restore process in Program.cs. If the value is true then restore the old db file to new version of your application.

    private static void RestoreDB()
    {
        //Restore database after application update            
        string dest = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\database.db";
        string sourceFile = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\..\\database.db";

        // Check if we have settings that we need to restore
        if (!File.Exists(sourceFile))
            // Nothing we need to do
            return;

        // Create directory as needed
        try
        {
            Directory.CreateDirectory(Path.GetDirectoryName(dest));
        }
        catch (Exception) { }

        // Copy our backup file in place 
        try
        {
            File.Copy(sourceFile, dest, true);
        }
        catch (Exception) { }

        // Delete backup file
        try
        {
            File.Delete(sourceFile);
        }
        catch (Exception) { }
    }

 static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);

        if (Settings.Default.updateDB)
        {
            RestoreDB();
            Settings.Default.updateDB = false;
            Settings.Default.Save();
            Settings.Default.Reload();
        }

        Aplication.Run(new Form1());
    }