private async Task SetRecordDataAsync <T>(RecordType type, T data)
{
await ConnectDatabaseAsync();
Aes aes = Aes.Create();
aes.Key = key;
using (var inputStream = new MemoryStream())
using (var cryptoStream = new CryptoStream(inputStream, aes.CreateEncryptor(), CryptoStreamMode.Write))
using (var streamWriter = new StreamWriter(cryptoStream))
{
new JsonSerializer().Serialize(streamWriter, data);
await streamWriter.FlushAsync();
cryptoStream.FlushFinalBlock();
SqliteCommand command = new SqliteCommand(
"INSERT INTO cachedData (recordType, iv, data) VALUES (@type, @iv, zeroblob(@length))" +
"ON CONFLICT (recordType) DO UPDATE SET iv = @iv, data = zeroblob(@length)", connection);
command.Parameters.AddWithValue("@type", (int)type);
command.Parameters.AddWithValue("@iv", Convert.ToBase64String(aes.IV));
command.Parameters.AddWithValue("@length", inputStream.Length);
var updatedRows = await command.ExecuteNonQueryAsync();
if (updatedRows == 0)
{
throw new LocalCacheRequestFailedException($"Failed to update cache for type {type}.");
}
using (var blob = new SqliteBlob(connection, "cachedData", "data", (long)type))
{
inputStream.Seek(0, SeekOrigin.Begin);
await inputStream.CopyToAsync(blob);
}
}
}
private void TransferBlob(SqliteCommand commandFrom, string tableName, string columnName, long rowId)
{
using var reader = commandFrom.ExecuteReader();
while (reader.Read())
{
using var outputStream = new SqliteBlob(accessorTo.Connection, tableName, columnName, rowId);
using var inputStream = reader.GetStream(columnName);
inputStream.CopyTo(outputStream);
}
}
public override async Task <uint> SetLOB(Stream value, DBTransaction transaction)
{
var command = (SqliteCommand)transaction.AddCommand(@"insert into db_lob (lob_data) values (zeroblob($length));
select last_insert_rowid();");
command.Parameters.AddWithValue("$length", value.Length);
var oid = (long)await transaction.ExecuteQueryAsync(command);
// Open a stream to write the data
using (var blobStream = new SqliteBlob((SqliteConnection)transaction.Connection, "db_lob", "lob_data", oid))
{
await value.CopyToAsync(blobStream);
}
return((uint)oid);
}
static async Task Main()
{
var connection = new SqliteConnection("Data Source=StreamingSample.db");
connection.Open();
var createCommand = connection.CreateCommand();
createCommand.CommandText =
@"
CREATE TABLE data (
id INTEGER PRIMARY KEY AUTOINCREMENT,
value BLOB
)
";
createCommand.ExecuteNonQuery();
// 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
using (var inputStream = File.OpenRead("input.txt"))
{
// Start by inserting a row as normal. Use the zeroblob() function to allocate
// space in the database for 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 (var writeStream = new SqliteBlob(connection, "data", "value", rowid, writable: true))
{
Console.WriteLine("Writing the large object...");
// NB: Although SQLite doesn't support async, other types of streams do
await inputStream.CopyToAsync(writeStream);
}
}
using (var outputStream = Console.OpenStandardOutput())
{
// To stream the large object, you must select the rowid or one of its aliases as
// show here in addition to the large object's column. If you don't, the entire
// object will be loaded into memory
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))
{
Console.WriteLine("Reading the large object...");
await readStream.CopyToAsync(outputStream);
}
}
}
}
// Clean up
connection.Close();
File.Delete("StreamingSample.db");
}