Problem with connection to local database

[ghost]

I downloaded database which application is connected to my computer to work with it locally. I was trying to change paths and names in AppSettings, but I faced with issues like "unable to open database file" or, in some cases, "no such table", and all tables are empty. Path is absolutely correct and file has all permissions. How I can establish local connection right?

[KevHoff2020]

Hi, This is a cool application, and I was immediately able to get it working using VS 2019 on Windows 10 (latest and greatest).

I tried to update it to the latest frameworks by using nuget and installing the latest stable release for each of the installed packages, and I was able to get it to compile, however when I run it now I'm facing this issue SQLite Error 14: 'unable to open database file'

EntityFramework 3.1.2 .netcore.UniversalWindowsPlatform 6.2.10 netstandard.library 2.0.3

Sorry I can't be of more help -Kevin

[mylastsong18]

I modeled it after the AppLog database since its used locally so not a whole lot of code changes were needed

AppSettings.cs

static public readonly string DatabasePath = "Database";
static public readonly string DatabaseName = $"{DB_NAME}.{DB_VERSION}.db";
static public readonly string DatabasePattern = $"{DB_NAME}.{DB_VERSION}.pattern.db";
static public readonly string DatabaseFileName = Path.Combine(DatabasePath, DatabaseName);

// These aren't used anymore but you can always keep them around in case you want to revert the functionality
// static public readonly string DatabasePatternFileName = Path.Combine(DatabasePath, DatabasePattern);
// static public readonly string DatabaseUrl = $"{DB_BASEURL}/{DatabaseName}";

Startup.cs

await EnsureLocalDbAsync(); // ADD THIS
await EnsureLogDbAsync();
//await EnsureDatabaseAsync(); // NO LONGER NEEDED 
await ConfigureLookupTables();

static private async Task EnsureLocalDbAsync()
{
    var localFolder = ApplicationData.Current.LocalFolder;
    var databaseFolder = await localFolder.CreateFolderAsync(AppSettings.DatabasePath, CreationCollisionOption.OpenIfExists);
    if (await databaseFolder.TryGetItemAsync(AppSettings.DatabaseName) == null)
    {
        var sourceDatabaseFile = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Assets/Database/DatabaseName.Version.db"));
        var targetDatabaseFile = await databaseFolder.CreateFileAsync(AppSettings.DatabaseName, CreationCollisionOption.ReplaceExisting);
        await sourceDatabaseFile.CopyAndReplaceAsync(targetDatabaseFile);
    }
}

---

As for @KevHoff2020's comment i have been able to get to AppCenter 2.6.4 and EntityFrameworkCore 2.2.6. If you use them together then you must remember that they're both implementing a SQLite library so versions must match. Since i plan to continue using AppCenter i haven't explored just upgrading EFCore but i did notice the same error message when i was on 3.1.2. If i end up figuring anything else out i will definitely share my findings

[EtherGhost]

How is the database created? What defines it? It doesn't seem to matter if models and contexts are removed, the created database looks the same anyway? I'm probably blind but I can't find in it the code.

Edit: Found it! "Summary: // Asynchronously ensures that the database for the context exists. If it exists, // no action is taken. If it does not exist then the database and all its schema // are created. If the database exists, then no effort is made to ensure it is compatible // with the model for this context. // Note that this API does not use migrations to create the database. In addition, // the database that is created cannot be later updated using migrations. If you // are targeting a relational database and using migrations, you can use the DbContext.Database.Migrate() // method to ensure the database is created and all migrations are applied."

[wcswanson]

Certicate not trusted when connecting to SQL Server.

This connection string works in a console app:

string connString = @"Data Source=WIN-10-PRO-LEAN\SQLEXPRESS;Initial Catalog=VanArsdelDb;Integrated Security=SSPI;TrustServerCertificate=True";

I did generate a certificate using OpenSsl. Then it was added to the certicates in mmc tool. Then it was added to SQL Server Network Configuration, SQL EXPRSS.

But when I run the Inventory sample, settings to connect to the database using the above connection string, I am still getting the error connection error like certicate is not trusted.

What can be done to solve this issue?

MSSQL 15 SQLEXPRESS upgraded from Windows 10 Pro 15.0.2101.7 Visual Studio 2022