How can I build my SQLite connection string in a UWP App?

Question

In my Windows Form app, I was able to build my SQLite connection string like this (after adding the .db file to my project via Add > Existing Item):

string connStr = string.Format(@"Data source={0}F4F.db", Application.StartupPath);

So I tried the same thing in my UWP project, with this code:

string connStr = string.Format(@"Data source={0}Cartographer.db", Application.StartupPath);
SqliteConnection conn = new SqliteConnection(connStr);

...but there is no "StartupPath" property. What should I use instead?

These are the available members of the Application object:

Should I use "Current"?

UPDATE

In response to Peter's answer, and as an add-on to my comment to that answer, here is the code I have now:

public sealed partial class MainPage : Page
{
        StorageFolder folder = Windows.ApplicationModel.Package.Current.InstalledLocation;
        string connStrBase = @"Data source={0}Cartographer.db";
        string connStr = string.Empty;
        string path = string.Empty;    
        . . .
}

. . .

path = folder.Path;
connStr = string.Format(connStrBase, path);

...am I at least close to having it right?

Could you please tell me what the value of Application.StartupPath is like? ApplicationData.Current.LocalFolder represents your app’s local folder. You could try to use [Windows.ApplicationModel.Package.Current.InstalledLocation](https://learn.microsoft.com/en-us/windows/uwp/files/file-access-permissions#application-install-directory) to get the app’s install directory, like this: StorageFolder folder = Windows.ApplicationModel.Package.Current.InstalledLocation; string path = folder.Path; — YanGu, Dec 02 '20 at 02:11
In my previous app, the value of StartupPath is C:\Users\bclay\source\repos\F4F_Core\F4F_Core\bin\Release\netcoreapp3.1 (and using that works for that app, that's where my SQLite .db file is). But in my UWP app, StartupPath is not even available. — B. Clay Shannon-B. Crow Raven, Dec 02 '20 at 11:09
Is the database packaged with your app install? And do you need read-write access, or is it read-only? — Peter Torr - MSFT, Dec 02 '20 at 13:52
@PeterTorr-MSFT: Yes, the SQLite db will be installed with the app; in this case (my current app), it does need to be read-write (previous one ("Flix 4 Fams") was read-only). — B. Clay Shannon-B. Crow Raven, Dec 02 '20 at 13:57

Peter Torr - MSFT · Accepted Answer · 2020-12-09T07:53:18.573

For read-write access to a file packaged as part of your app, you will need to copy it to your ApplicationData.LocalFolder directory on first-run of your app, and then you can update it from there. Then you can use the LocalFolder.Path property to initialize your connection string with the correct location. The original file will be located inside your Package.InstalledLocation.

Here is a basic sample, using a helper class that you can use to copy any file from an arbitrary place in your package into a mirrored place in your local folder, and then return the resulting filename.

The sample is async in case you're copying large files during startup etc. but it can trivially be made sync if you only ever need files from your package directory. Also, even though the API is async, is 100% safe to block on it if you want (as illustrated below).

class LocalDataUtils
{
    static readonly string destinationDirectory = 
        ApplicationData.Current.LocalFolder.Path;
    static readonly string sourceDirectory = 
        Package.Current.InstalledPath;

    static Dictionary<string, TaskCompletionSource<string>> fileCompletionTasks = 
        new Dictionary<string, TaskCompletionSource<string>>();

    public static Task<string> GetFilenameAsync(string relativePath)
    {
        TaskCompletionSource<string> completion;

        if (!fileCompletionTasks.TryGetValue(relativePath, out completion))
        {
            lock (fileCompletionTasks)
            {
                // Check again in case there was a race.
                if (!fileCompletionTasks.TryGetValue(relativePath, out completion))
                {
                    completion = new TaskCompletionSource<string>();
                    fileCompletionTasks.Add(relativePath, completion);

                    // This is async in case you ever need to copy from locations 
                    // other than your local folders or you are copying files
                    // that might take a while.
                    // You can simplify and make it not-async if you like.
                    Task.Run(() => CopyFileAndGetFilename(relativePath, completion));
                }
            }
        }

        return completion.Task;
    }

    static void CopyFileAndGetFilename(string relativePath, 
        TaskCompletionSource<string> completion)
    {
        try
        {
            // Also a fun movie :)
            var finalDestination = Path.Combine(destinationDirectory, relativePath);

            if (!File.Exists(finalDestination))
            {
                var originalLocation = Path.Combine(sourceDirectory, relativePath);
                Directory.CreateDirectory(Path.GetDirectoryName(finalDestination));
                File.Copy(originalLocation, finalDestination);
            }

            completion.SetResult(finalDestination);
        }
        catch (Exception ex)
        {
            completion.SetException(ex);
        }
    }
}

Using it is pretty simple:

var name = await LocalDataUtils.GetFilenameAsync(@"Database\test.db");
var text = File.ReadAllText(name);
Debug.Write(text);

// Note: no 'await' here for illustrative purposes; subsequent calls
// for the same file will always return immediately.
var otherNameTask = LocalDataUtils.GetFilenameAsync(@"Database\test.db");
Debug.Assert(otherNameTask.IsCompletedSuccessfully);

// Don't call 'await' and instead block on the 'Result'
// This is not safe in general for async methods, but is fine in this case.
var name2 = LocalDataUtils.GetFilenameAsync(@"Assets\StoreLogo.png").Result;
var size = new FileInfo(name2).Length;
Debug.WriteLine($"{Path.GetFileName(name2)} is {size} bytes long.");

Thanks, Peter; now the question is, copy it from where? Where does it exist, programmatically speaking, when I have added it to my solution via Add New Existing Item? I know where it is on MY machine at design time (C:\Users\bclay\source\repos\CartographerYou\CartographerYou\Carographer.db), but this is obviously not where it will be when the user downloads the app. Please see my Update above. — B. Clay Shannon-B. Crow Raven, Dec 03 '20 at 06:08

How can I build my SQLite connection string in a UWP App?

UPDATE

1 Answers1