Page 1 of 1
Shared in-memory database
Posted: Thu 04 Apr 2013 16:53
by 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?
Re: Shared in-memory database
Posted: Fri 05 Apr 2013 14:30
by Pinturiccio
We will investigate the possibility of implementing URI formats in dotConnect for SQLite and notify you about the results as soon as possible.
Re: Shared in-memory database
Posted: Thu 18 Apr 2013 14:56
by 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
Re: Shared in-memory database
Posted: Thu 27 Oct 2016 07:57
by 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.
Re: Shared in-memory database
Posted: Fri 28 Oct 2016 13:23
by 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();
}
}
Re: Shared in-memory database
Posted: Wed 02 Nov 2016 10:23
by 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.
Re: Shared in-memory database
Posted: Mon 07 Nov 2016 15:47
by 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?
Re: Shared in-memory database
Posted: Mon 07 Nov 2016 16:06
by talbrecht
The file has been created in the working directory. With
DataSource=":memory:"
no such file is created.
Re: Shared in-memory database
Posted: Tue 08 Nov 2016 14:30
by 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.
Re: Shared in-memory database
Posted: Tue 08 Nov 2016 15:08
by 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.