In C#, is there any way to have an in-memory file linked as an in-memory SQLite database with System.Data.SQLite?

Question

What I want to do is something along the lines of the following:

using System.Data.SQLite;
using System.IO;

//My SQLite connection
SQLiteConnection myCon;

public void ReadAndOpenDB(string filename)
{
    FileStream fstrm = new FileStream(filename, FileMode.Open);
    byte[] buf = new byte[fstrm.Length];
    fstrm.Read(buf, 0, (int)fstrm.Length);
    MemoryStream mstrm = new MemoryStream(buf);

    //Do some things with the memory stream

    myCon = new SQLiteConnection(/*attach to my memory stream for reading*/);
    myCon.Open();

    //Do necessary DB operations
}

I do not plan on writing to the in-memory DB, but I need to be able to do some things with the file, in memory in my program, before connecting to it.

You could use either the SQLite in-memory DB and manipulate that via SQLite or write your memory stream to a temp file and open it. — Arne Claassen, Jun 01 '11 at 00:52
What kind of things do you need to "do with the file" before connecting to it? — RQDQ, Jun 07 '11 at 10:34
I want to be able to edit the stream. My reasons don't really matter unless the above method-in-question is possible. — Mike Webb, Jun 07 '11 at 23:21

score 3 · Answer 1 · answered Jun 06 '11 at 19:48

If you don't mind using Interop and going to CreateFile() directly (then wrapping the returned HANDLE in a FileStream) you could take a look at creating the file with FILE_ATTRIBUTE_TEMPORARY specified which will not write the file to disk as long as there is cache memory available and which will automatically delete the file when its handle is closed.

Specifying the FILE_ATTRIBUTE_TEMPORARY attribute causes file systems to avoid writing data back to mass storage if sufficient cache memory is available, because an application deletes a temporary file after a handle is closed. In that case, the system can entirely avoid writing the data. Although it does not directly control data caching in the same way as the previously mentioned flags, the FILE_ATTRIBUTE_TEMPORARY attribute does tell the system to hold as much as possible in the system cache without writing and therefore may be of concern for certain applications.

score 0 · Answer 2 · answered Mar 30 '18 at 13:13

Using Interop and CreateFile() is too complex. Here is a simple way to create file with FILE_ATTRIBUTE_TEMPORARY attribute which will use cache memory as much as possible:

var file = File.Create(path);
File.SetAttributes(path, File.GetAttributes(path) | FileAttributes.Temporary);

In C#, is there any way to have an in-memory file linked as an in-memory SQLite database with System.Data.SQLite?

2 Answers2