SQLite in-memory database - Loading sqlite db as memory db

[Prathapachandran]

I have an SQLite database and I need to load the database as in-memory database. Is it any way to do this? I am using ADO.NET Data Provider library for SQLite to do all the DB Operations. Please help.

[Andre Kraak]

Not familiar with SQLite myself, but a quick search yielded this documentation In-Memory Databases[^] from http://www.sqlite.org[^].

0100000101101110011001000111001011101001

[Prathapachandran]

I already read this documentation, but did't find any solution.. :(

[Luc Pattyn]

You could try this[^]. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Prathapachandran]

Thanks for the reply. But how to use it in C#? Do I need to write a wrapper like in the article http://www.switchonthecode.com/tutorials/csharp-tutorial-writing-a-dotnet-wrapper-for-sqlite[^]? I am doing all the db operations using ADO.NET sqlite provider libary (System.Data.SQLite). :(

[Luc Pattyn]

I don't know. If ADO.NET does not provide backup methods, and does not support SQL backup commands, then you will need to call native code that does perform backups for you. That would require some P/Invoke stuff, whether you would isolate that in a wrapper is up to you. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[jschell]

Prathapachandran.v wrote:

and I need to load the database as in-memory database

Why? And in terms of that is this going to be a read only database? If not then what happens when you write something to it? And how are you going to load it when your application starts?

[Prathapachandran]

In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost. Normally this concept is used for very fast database operations. we can use the below connection string to create an in-memory db in sqlite.

public const string sqliteConnectionString = "Data Source=:memory:";

visit the link http://www.sqlite.org/inmemorydb.html[^] for getting more idea about it.

[Prathapachandran]

Thanks for the input. I have written my own code to implement it. Please find the bellow steps. 1. Import the below Native methods

\[DllImport("System.Data.SQLite.dll")\]
public static extern int sqlite3\_backup\_init(IntPtr pTo, string toName, IntPtr pFrom, string fromName);

\[DllImport("System.Data.SQLite.dll")\]
public static extern int sqlite3\_backup\_step(int b, int n);

\[DllImport("System.Data.SQLite.dll")\]
public static extern int sqlite3\_backup\_finish(int backupObject);

\[DllImport("System.Data.SQLite.dll")\]
public static extern int sqlite3\_sleep(int interval);

\[DllImport("System.Data.SQLite.dll")\]
public static extern int sqlite3\_backup\_remaining(int remaining);

\[DllImport("System.Data.SQLite.dll")\]
public static extern int sqlite3\_backup\_pagecount(int pageCount);

2. Define the below constants

 public const int SQLITE\_OK = 0;
 public const int SQLITE\_BUSY = 5;
 public const int SQLITE\_LOCKED = 6;

3. Above APIs required database as IntPtr. But we have only a connection class [System.Data.SQLite.SQLiteConnection]. So we need to find out database handle from the connection object. I don't know whether it is a right way or not, anyway implement an extension method as shown below

    public static IntPtr GetConnectionHandle(this SQLiteConnection connection)
    {
        IntPtr returnPointer = IntPtr.Zero;
        FieldInfo\[\] fields = typeof(SQLiteConnection).GetFields(
                BindingFlags.NonPublic | BindingFlags.Instance |
                BindingFlags.DeclaredOnly);
        object sqLiteBase = null;
        object value = null;

        //Checks the valid connection states
        if (connection.State != System.Data.ConnectionState.Open &&
            connection.State != System.Data.ConnectionState.Fetching &&
            connection.State != System.Data.ConnectionState.Executing)
        {
            return returnPointer;
        }
        foreach (FieldInfo info in fields)
        {
            //Getting the object "internal SQLiteBase \_sql;" from the 
            //public class instance System.Data.SQLite.SQLiteConnection.
            if (string.Compare(info.Name, "\_sql", true) == 0)
            {                   
                sqLiteBase = info.GetValue(connection);
                break;

[jschell]

Prathapachandran.v wrote:

In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost.

I know what the term means.

Prathapachandran.v wrote:

we can use the below connection string to create an in-memory db in sqlite.

Do you know the difference between 'how' and 'why'? Because I asked why it was needed.