Including SQLite DB file with data in the UWP application

user_715267 0
Viewed 1145

I'm trying to include a SQLite file with the UWP application. SQLite file has a number of tables in it populated with data.

The database (for now, at least) is read-only and I'm planning to use EF Core to access the data.

So i have two questions:

  1. What is the correct way to bundle this file with the application.
  2. How do I compose the file path to access that file from UWP applciation?

Setting .sqlite file's "Copy to output directory" setting to "Copy always" seems to copy the file to bin folder but I can't seem to figure out the path to access it. When setting the SQLite conenction string to "Filename=databasename.sqlite" - it seems to create a blank DB file elsewhere, as tryign to access any of the tables throws a table not found exception.

Thanks in advance!

windows-10 entity-framework-core uwp sqlite
2 Answers

What worked for me based on the answer in the link by @Mamoru Satoh

In the Assets Folder I changed the database extention to .png otherwise it can't be copied then in the distination the database name extention is .db

    public async Task getDataBase()
    {
        StorageFile dbFile = (StorageFile) await ApplicationData.Current.LocalFolder.TryGetItemAsync("TestDb.db");
        try
        {
            if (null == dbFile)
            {
                // first time ... copy the .db file from assets to local  folder
                var localFolder = ApplicationData.Current.LocalFolder;
                var originalDbFileUri = new Uri("ms-appx:///Assets/TestDb.png");

                var originalDbFile = await StorageFile.GetFileFromApplicationUriAsync(originalDbFileUri);

                if (null != originalDbFile)
                {
                    dbFile = await originalDbFile.CopyAsync(localFolder, "TestDb.db", NameCollisionOption.ReplaceExisting);
                }
            }
        }
        catch (Exception ex)
        {
        }

    }

If you are using Entity framework for the database you copied from the Assets Folder

public class Emp
{
    [Key]
    public int EmpID { get; set; }
    public string EmpName { get; set; }
    public int EmpDept { get; set; }
}

public class DataContext : DbContext
{
    public DbSet<Emp> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=TestDb.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Emp>().ToTable("Emp");
    }
}

you will need to run the getDataBase() function when you start the application

and you can query from the database the same old way

  DataContext context = new DataContext();
  string name = (await context.Employees.OrderBy(a => a.EmpName).FirstOrDefaultAsync()).EmpName;
user_81950760
Related