CODEWITHSUNDEEP
sqliteuwpwindows-10entity-framework-core
Nick Goloborodko
Nick Goloborodko

Reputation: 3053

Including SQLite DB file with data in the UWP application

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!

Upvotes: 3

Views: 1380

Answers (2)

Wowo Ot
Wowo Ot

Reputation: 1529

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;

Upvotes: 1

Mamoru Satoh
Mamoru Satoh

Reputation: 2710

My recommendation is ...

  1. Include the .db file to your AppX package. To include it, add the .db file to Assets folder in your project.
  2. Then, at the first time launch of your app, you should copy the .db file from Assets to your app's local folder. Because, SQLite itself can't access to the Assets folder. It can only access to the your app's local or temporary folder.

I've answered for the similar question on MSDN forum. Please refer it too.

[UWP] SQLite.net Path question?

Upvotes: 6

Related Questions