Blob I/O

Note

The SqliteBlob class was added in version 3.0.

You can reduce memory usage while reading and writing large objects by streaming the data into and out of the database. This can be especially useful when parsing or transforming the data.

Start by inserting a row as normal. Use the zeroblob() SQL function to allocate space in the database to hold the large object. The last_insert_rowid() function provides a convenient way to get its rowid.

var insertCommand = connection.CreateCommand();
insertCommand.CommandText =
@"
    INSERT INTO data(value)
    VALUES (zeroblob($length));

    SELECT last_insert_rowid();
";
insertCommand.Parameters.AddWithValue("$length", inputStream.Length);
var rowid = (long)insertCommand.ExecuteScalar();

After inserting the row, open a stream to write the large object using SqliteBlob.

using (var writeStream = new SqliteBlob(connection, "data", "value", rowid))
{
    // NB: Although SQLite doesn't support async, other types of streams do
    await inputStream.CopyToAsync(writeStream);
}

To stream the large object out of the database, you must select the rowid or one of its aliases as shown here in addition to the large object's column. If you don't select the rowid, the entire object will be loaded into memory. The object returned by GetStream() will be a SqliteBlob when done correctly.

var selectCommand = connection.CreateCommand();
selectCommand.CommandText =
@"
    SELECT id, value
    FROM data
    LIMIT 1
";
using (var reader = selectCommand.ExecuteReader())
{
    while (reader.Read())
    {
        using (var readStream = reader.GetStream(1))
        {
            await readStream.CopyToAsync(outputStream);
        }
    }
}