Read/Write RichEditBox to Sqlite3 for Universal Windows Platform using C#

Question

Seeking a couple of convenient methods to Save / Load RichEditBox Control Document to SQLite3 Blob Field (can it be in text field too?)

(if possible Without losing formatting. if cant, without formatting.)

its Windows IOT and Im using Sqlite3.PCL nuget

Answer 1

Here is a solution you can reference. I use Microsoft.Data.Sqlite nuget package and test it on Raspberry Pi 3 with Windows 10 IoT Core(version:10.0.16299.192).

The MainPage.xaml:

<StackPanel VerticalAlignment="Center" Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
    <RichEditBox x:Name="editor" Height="200" />
    <Button Content="Save" Click="Save_Click" />
    <Button Content="Load" Click="Load_Click" />
</StackPanel>

TheMainPage.xaml.cs:

    private async void SaveContent()
    {
        byte[] bytes;
        StorageFile file = await DownloadsFolder.CreateFileAsync("file.rtf", CreationCollisionOption.GenerateUniqueName);

        if (file != null)
        {
            // write to file
            using (Windows.Storage.Streams.IRandomAccessStream randAccStream =
                await file.OpenAsync(Windows.Storage.FileAccessMode.ReadWrite))
            {
                editor.Document.SaveToStream(Windows.UI.Text.TextGetOptions.FormatRtf, randAccStream);

                randAccStream.Seek(0);
                var dataReader = new DataReader(randAccStream);
                await dataReader.LoadAsync((uint)randAccStream.Size);
                bytes = new byte[randAccStream.Size];
                dataReader.ReadBytes(bytes);
            }


            SqliteConnection connection = new SqliteConnection("Filename=richEditBox.db");
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText =
            @"
            CREATE TABLE IF NOT EXISTS myTable (
                content BLOB
            )
            ";
            command.ExecuteNonQuery();

            var insertCommand = connection.CreateCommand();
            insertCommand.CommandText =
            @"
            INSERT INTO myTable (content)
            VALUES (@content)
            ";

            insertCommand.Parameters.Add("@content", SqliteType.Blob, bytes.Length);
            insertCommand.Parameters["@content"].Value = bytes;

            var task1 = insertCommand.ExecuteNonQueryAsync();
        }
    }

    private async void LoadContent()
    {
        object[] values = new object[2];

        SqliteConnection connection = new SqliteConnection("Filename=richEditBox.db");
        connection.Open();
        var retrieveCommand = connection.CreateCommand();
        retrieveCommand.CommandText =
            @"SELECT * from myTable";
        var reader = retrieveCommand.ExecuteReader();
        while (reader.Read())
        {
            System.Diagnostics.Debug.WriteLine(reader.GetValues(values));
        }
        var dataBytes = values[0] as byte[];
        var dataBuffer = dataBytes.AsBuffer();
        InMemoryRandomAccessStream randomAccessStream = new InMemoryRandomAccessStream();
        await randomAccessStream.WriteAsync(dataBuffer);
        randomAccessStream.Seek(0);
        editor.Document.LoadFromStream(Windows.UI.Text.TextSetOptions.FormatRtf, randomAccessStream);
    }

    private void Save_Click(object sender, RoutedEventArgs e)
    {
        SaveContent();
    }

    private void Load_Click(object sender, RoutedEventArgs e)
    {
        LoadContent();
    }