How can I embed a SQLite Database in a .NET DLL and then use it from C#?

Question

I'm currently working on some evaluation work for a project that I'm planning.

I recently looked at solutions for a data storage mechanism for my application and while researching stumbled upon SQLite. I currently use SQLite with the System.Data.SQLite wrapper.

I really like the way it works but I have one problem with it that I couldn't get fixed and I also found no help concerning my problem on the internet.

I would like my SQLite Database to be embedded into one of my applications DLLs (ie. Title.Storage.dll) to be used within this DLL. Is this possible?

How can I access the database then?

It would be great if I could use something like:

SQLiteConnection con = new SQLiteConnection();
con.ConnectionString="DataSource=Title.Storage.storage.db3";
con.Open();

Thanks in advance and best regards,

3Fox

Answer 1

An assembly isn't for file storage, it's for code storage. While you can store files in an assembly, they are read only.

Answer 2

This is not possible as such. What you could do is embed the db in your dll project and dump it to certain location on the file system (may be AppData?) and read and write from there. Having db sit directly inside the executable (or dlls) may not be a good idea in the sense it bloats the size of the application besides being technically infeasible.

Having a single executable to distribute is a matter of taste which I like. In your case but the problems are more. It's not just about embedding the db file alone, what about the dlls associated with it? Steps are 2:

1) Add the necessary dlls (System.Data.SQLite?) associated with your db to your project as Embedded Resource (not necessarily a Resource file) and let the system automatically resolve the assembly conflicts. Catch it here how to do it.

2) Now either add your db file to your Resources of your project (and extract it)

static void DumpDatabase()
{
    var dbFullPath = Utility.GetDbFullPath(); //your path
    if (File.Exists(dbFullPath))
        return; //whatever your logic is

    File.WriteAllBytes(dbFullPath, Properties.Resources.myDb);
}

or even better do not embed the db as such in your project but write the logic to create database in your application. What if tomorrow you need to change the version of SQLite, say from 3 to 4? With the first approach you need to create a database for yourself and re-embed it in the project. But if you are writing the logic to create the db in your application then updating SQLite version is just a matter of changing the ADO.NET dll (the code remains the same). May be like this:

static void DumpDatabase()
{
    var dbFullPath = Utility.GetDbFullPath();
    if (File.Exists(dbFullPath))
        return; //whatever your logic is

    CreateDb(dbFullPath);
}

static void Create(string dbFullPath)
{
    SQLiteConnection.CreateFile(dbFullPath);

    string query = @"

    CREATE TABLE [haha] (.............)
    CREATE TABLE ..............";

    Execute(query);
}

And in the connection string add FailIfMissing=False;