Shared in-memory database

[mjegen]

SQlite now supports the ability to connect to the same in-memory database from multiple connections but the only way to do this appears to be by using a URI filename.

http://www.sqlite.org/inmemorydb.html

However, dotConnect for SQLite does not appear to support this format in the connection string.

Is there a way to do this with the dotConnect for SQLite provider?

[Pinturiccio]

We will investigate the possibility of implementing URI formats in dotConnect for SQLite and notify you about the results as soon as possible.

[Pinturiccio]

We have implemented the support of the URI format for the connection string.
The new build of dotConnect for SQLite 4.6.224 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=26907

[talbrecht]

I'm currently using dotConnect 5.6 but I cannot find any information about how to use the URI format. SQLiteConnectionStringBuilder does not provide a FullUri property like System.Data.SQLite does.

Can anybody post a small example about how to use such URIs with dotConnect?

Especially I need support for multple in-memory database (with shared cache) in format:

"file:memdb1?mode=memory&cache=shared"

as described in http://www.sqlite.org/inmemorydb.html.

[Pinturiccio]

sqlite3.dll engine uses in memory databases like it is described in the following topic: http://www.sqlite.org/inmemorydb.html

Your connection string can be like the following:

Code: Select all

"DataSource=:memory:"
"DataSource=:memory:?cache=shared"
"DataSource=file::memory:"
"DataSource=file::memory:?cache=shared"
"DataSource=file:memdb1?mode=memory&cache=shared"

Here is the small example of using in memory databases with dotConnect for SQLite:

Code: Select all

class Program
{
    static void Main(string[] args)
    {
        SQLiteConnection conn = new SQLiteConnection(@"DataSource=file::memory:?cache=shared");
        conn.Open();
        SQLiteCommand comm=new SQLiteCommand(@"CREATE TABLE DEPT (
DEPTNO INTEGER PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13))", conn);
        comm.ExecuteNonQuery();
        SQLiteScript script = new SQLiteScript(@"INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');", conn);
        script.Execute();
        SQLiteConnection conn2 = new SQLiteConnection(@"DataSource=file::memory:?cache=shared");
        conn2.Open();
        SQLiteCommand cmd = new SQLiteCommand("select * from dept", conn2);
        SQLiteDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
                Console.Write(reader.GetValue(i) + "\t");
            Console.WriteLine();
        }
        conn.Close();
        conn2.Close();
    }
}

[talbrecht]

Thanks a lot! It works like a charm.

By using

"DataSource=file:memdb1?mode=memory&cache=shared"

a file named 'file' with 0 bytes is created always.

It's a minor side effect, though - I just want to let you know.

[Pinturiccio]

Thank you for the provided information. However, we could not find such a file. Could you please specify in which folder it is created?

[talbrecht]

The file has been created in the working directory. With

DataSource=":memory:"

no such file is created.

[Pinturiccio]

We have used the following connection string in the previous time, but we can't see any created file in working directory.

Code: Select all

"DataSource=file:memdb1?mode=memory&cache=shared"

dotConnect for SQLite does not create any files, and if some file appears, than it is created by the sqlite3.dll engine. So we can't change this behaviour.

[talbrecht]

Ok, I've re-checked this. I can confirm now that it's not a fault of dotConnect!

In my test project the sqlite3.dll was not copied to the output folder so that it was loaded from system directory. That sqlite3.dll was replaced somewhere in the past with SqlCipher 3.40 (SQLite 3.11) which creates the 'file' file. As soon as I had copied the dotConnect version of sqlite3.dll to the output folder no such file was created.