Unable to Save Image Data to SQLite Database in MAUI Application

Question

1. Problem Overview

   What you are trying to do: I am trying to save image binary data into a SQLite database named gazouByte.db3 when the "Save" button is pressed after selecting an image file using a file picker. Additionally, I want to display the list of saved images, including their "Id," "File Name," and "Extension," in a CollectionView on the MainPage when the "Get All GazouList" button is pressed. What issue you are facing: The image binary data is not being saved to the gazouByte.db3 database. When clicking the "Get All GazouList" button, only "got all file list" is displayed, and the "Id," "File Name," and "Extension" are not shown. Note that the table gazouByte has been confirmed to be created using "DB Browser for SQLite." [![There is no data in the browser.][1]][1]

2. Steps to Reproduce

   Specific steps to reproduce the issue: Start debugging in Visual Studio 2022 on a Windows machine. Click on "Select File" to open the file picker and choose an image file (jpg/png). Verify that the image preview is displayed under "Select File." Click the "Save File" button and confirm that "file saved" is displayed. Check the database using "DB Browser for SQLite"; the table is created, but no data is saved. enter image description here

using WorkReview.Models;
using System.Collections.Generic;
using Microsoft.Maui.Controls;
using WorkReview.ViewModels;
using System.Runtime.CompilerServices;

namespace WorkReview.Views
{
    public partial class MainPage : ContentPage
    {
        public MainPage()
        {
            InitializeComponent();
            BindingContext = new MainViewModel();
        }
        

        public void OnGetAllGazouClicked(object sender, EventArgs args)
        {
            List<GazouByte> gazouByte = App.GazouByteRepo.GetAllGazouBytes();
       
            statusMessage.Text = "got all file list";

        }


        public void OnFileSaveClicked(object sender, EventArgs args)
        {

            
            (BindingContext as MainViewModel).SaveGazouToDataBase(); //BindingVontextのインスタンスをもちいて.Save~メソッドを実行している
            statusMessage.Text = "file saved";

        }

        public async void OnFileSelectClicked(object sender, EventArgs args)
        {
            try
            {
                var result = await FilePicker.PickAsync();
                var fileExtension = result.ContentType;
                var fileName = result.FileName;
                if (result != null)
                {
                    if (result.FileName.EndsWith("jpg", StringComparison.OrdinalIgnoreCase) ||
                        result.FileName.EndsWith("png", StringComparison.OrdinalIgnoreCase))
                    {
                           
                        using (var stream = await result.OpenReadAsync())
                        {
                            using (var memoryStream = new MemoryStream())///using(var)はUsingステートメント,{}内の処理が終わったときにリソースが開放される
                            {
                                await stream.CopyToAsync(memoryStream);　//memoryStreamに保存されたデータをViewModelの該当箇所へ送る / send data to viewmodel
                                (BindingContext as MainViewModel).gazouExtension = fileExtension;
                                (BindingContext as MainViewModel).gazouName = fileName;
                                (BindingContext as MainViewModel).gazouBinary = memoryStream.ToArray();
                                var previewStream = new MemoryStream(memoryStream.ToArray());
                                userPreview.Source = ImageSource.FromStream(() => previewStream);
                            }
                        }
                    }
                    else
                    {
                        statusMessage.Text = "Unsupported file type.";
                    }
                }
            }
            catch (Exception ex)
            {
                statusMessage.Text = $"Error selecting file: {ex.Message}";
            }
        }
    }
}

Repositry here

using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WorkReview.Models;

namespace WorkReview.Models
{
    public class GazouByteRepositry
    {
        string _gazoudbPath;
        public string GazouStatusMessage { get; set; }

        private SQLiteConnection gazouconn;

        public GazouByteRepositry(string gazoudbPath)
        {
            _gazoudbPath = gazoudbPath;
        }

        private void Init() //dbに接続後テーブル作成 create table when connect db.3
        {
            if (gazouconn is not null)
                return;
            gazouconn = new SQLiteConnection(_gazoudbPath);

            gazouconn.CreateTable<GazouByte>();

        }

        public void AddNewGazouByte(string gazouName, byte[] gazouBinary, string gazouExtension)//画��データをDBへ挿入 add image data to db.3
        {
            ArgumentNullException.ThrowIfNull(gazouName, nameof(gazouExtension));
            ArgumentNullException.ThrowIfNull(gazouBinary, nameof(gazouBinary));
            ArgumentNullException.ThrowIfNull(gazouName, nameof(gazouName));
            int gazouResult = 0;
            try
            {
                Init();

                gazouResult = gazouconn.Insert(new GazouByte { GazouName = gazouName, GazouBinary = gazouBinary, GazouExtension = gazouExtension });
                Console.WriteLine($"Insert result: {gazouResult}");
                GazouStatusMessage = string.Format("{0} record(s) added (GazouName: {1})", gazouResult, gazouName);
            }
            catch (Exception ex)
            {
                GazouStatusMessage = string.Format("Failed to add {0}. Error: {1}", gazouName, ex.Message);
            }
        }
        public List<GazouByte> GetAllGazouBytes()//テーブルを取得 get table then create list for controll-view .MainPage
        {
            try
            {
                Init();
                return gazouconn.Table<GazouByte>().ToList();
            }
            catch (Exception ex)
            {
                GazouStatusMessage = string.Format("Failed to retrieve data .{0}", ex.Message);
            }
            return new List<GazouByte>();
        }


        

    }
}

Table is here

using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CommunityToolkit;

namespace WorkReview.Models;
[Table("gazouByte")]
public class GazouByte
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string GazouName { get; set; }
public byte[] GazouBinary { get; set; } //画像のバイナリ用 Binary fo image to db.3
public string GazouExtension { get; set; }

}

3. Expected Behavior

   When starting debugging, a table named gazouByte should be created in the app data. After selecting an image using the file picker, the selected image should be previewed under "Select File." When clicking the "Save File" button, the image data should be saved into the gazouByte.db3 database. Clicking the "Get All GazouList" button should display the "Id," "File Name," and "Extension" of the saved images in the CollectionView.

4. What I Have Tried

   Verified that exception handling in GazouByteRepositry.AddNewGazouByte method is correctly implemented. Confirmed that the table gazouByte is created using "DB Browser for SQLite." Noticed that a System.NullReferenceException occurs when pressing the "Save File" button again without selecting a new image.

5. Error Messages and Logs

   No error messages are displayed, but the image data is not saved.

6. Environment

   Windows 11 Home 23H2 Visual Studio Community 2022 Preview 17.11.0 Preview 4.0 Microsoft.Maui.Controls 8.0.80 Microsoft .NET SDK 8.0.107 (x64) SQlitePCLRaw.bundle_green 2.1.9 sqlite-net-pcl 1.9.172 CommunityToolkit.Mvvm 8.2.2

7. Additional Information

   The complete code can be found at the following URL: https://github.com/rasukuosu/WorkReview.git

Answer 1

After I debug your demo, I found that we could not save the data to the database correctly. Then I made some changes to your code and then the data was stored in the database smoothly and read out correctly.

You can refer to the following code:

GazouByteRepositry.cs

public class GazouByteRepositry
{
    string _gazoudbPath;
    public string GazouStatusMessage { get; set; }

    //private SQLiteConnection gazouconn;
    SQLiteAsyncConnection gazouconn;

    public GazouByteRepositry() { 
    }

    async Task Init()
    {
        if (gazouconn is not null)
            return;

        gazouconn = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
        var result = await gazouconn.CreateTableAsync<GazouByte>();
    }



    public async Task<int> AddNewGazouByte(string gazouName, byte[] gazouBinary, string gazouExtension)//画像データをDBへ挿入 add image data to db.3
    {
        ArgumentNullException.ThrowIfNull(gazouName, nameof(gazouExtension));
        ArgumentNullException.ThrowIfNull(gazouBinary, nameof(gazouBinary));
        ArgumentNullException.ThrowIfNull(gazouName, nameof(gazouName));
        //int gazouResult = 0;
        try
        {
            Init();

            GazouByte gazou=  new GazouByte { GazouName = gazouName, GazouBinary = gazouBinary, GazouExtension = gazouExtension };

            //gazouResult =  gazouconn.InsertAsync(gazou);
            return await gazouconn.InsertAsync(gazou);
            //Console.WriteLine($"Insert result: {gazouResult}");
            //GazouStatusMessage = string.Format("{0} record(s) added (GazouName: {1})", gazouResult, gazouName);
        }
        catch (Exception ex)
        {
           // GazouStatusMessage = string.Format("Failed to add {0}. Error: {1}", gazouName, ex.Message);
           return -1;
        }
    }
    public async Task<List<GazouByte>>  GetAllGazouBytes()//テーブルを取得 get table then create list for controll-view .MainPage
    {
        try
        {
            await Init();
            return await gazouconn.Table<GazouByte>().ToListAsync();
        }
        catch (Exception ex)
        {
            GazouStatusMessage = string.Format("Failed to retrieve data .{0}", ex.Message);
        }
        return new List<GazouByte>();
    }

Constants.cs

public static class Constants
{
    public const string DatabaseFilename = "gazouByte.db3";

    public const SQLite.SQLiteOpenFlags Flags =
        // open the database in read/write mode
        SQLite.SQLiteOpenFlags.ReadWrite |
        // create the database if it doesn't exist
        SQLite.SQLiteOpenFlags.Create |
        // enable multi-threaded database access
        SQLite.SQLiteOpenFlags.SharedCache;

    public static string DatabasePath =>
        Path.Combine(FileSystem.AppDataDirectory, DatabaseFilename);
}

MainPage.xaml.cs

public partial class MainPage : ContentPage
{
    GazouByteRepositry database;

    MainViewModel mainViewModel;

    public MainPage(GazouByteRepositry db)
    {
        InitializeComponent();
        this.database = db;

        mainViewModel = new MainViewModel(database);
        this.BindingContext = mainViewModel;
    }
    

    public async void  OnGetAllGazouClicked(object sender, EventArgs args)
    {
        // List<GazouByte> gazouByte = App.GazouByteRepo.GetAllGazouBytes();

        var items = await database.GetAllGazouBytes();

        foreach (var item in items)
            mainViewModel.Items.Add(item);

        statusMessage.Text = "got all file list";

    }


    public void OnFileSaveClicked(object sender, EventArgs args)
    {
        (BindingContext as MainViewModel).SaveGazouToDataBase(); 
        statusMessage.Text = "file saved";

    }
   //other code
}

MainPage.xaml

There are some problems with your code. For example, when loading data, the CollectionView will overlap with one of the buttons, and the ItemsSource of the CollectionView is not set correctly. I've made a slight modification to your code, you can refer to it.

   <Grid Padding="5" RowSpacing="10" ColumnSpacing="10" RowDefinitions="Auto, Auto, Auto, Auto, Auto, Auto,*">
    <Label x:Name="azouStatusMessage" TextColor="Wheat" Grid.Row="2"/>
    <Label x:Name="statusMessage" TextColor="red" Grid.Row="2"/>
    <Button Text="Get All GazouList" Grid.Row="3" Clicked="OnGetAllGazouClicked" />
    <CollectionView x:Name="productList" Grid.Row="1" ItemsSource="{Binding Items}">
        <CollectionView.ItemTemplate>
            <DataTemplate>
                <Grid ColumnDefinitions="*,2*">
                    <Label Text="{Binding Id}" TextColor="{StaticResource Primary}"/>
                    <Label Grid.Column="1" Text="{Binding GazouName }" TextColor="{StaticResource Primary}"/>
                </Grid>
            </DataTemplate>
        </CollectionView.ItemTemplate>
    </CollectionView>
    <Button Text="ファイルを選択する" Grid.Row="5" Clicked="OnFileSelectClicked"/>
    <Button Text="ファイルを保存する" Grid.Row="4" Clicked="OnFileSaveClicked"/>
    <Image x:Name="userPreview" Grid.Row="6">

    </Image>

</Grid>

MainViewModel.cs

public partial class MainViewModel : ObservableObject
{
    private GazouByteRepositry _gazouByteRepositry;

    public ObservableCollection<GazouByte> Items { get; set; } = new();


    public MainViewModel(GazouByteRepositry db)
    {
        // _gazouByteRepositry = new GazouByteRepositry("gazouByte.db3");
        _gazouByteRepositry = db;
       
    }

    [ObservableProperty]
    public string gazouName;

    [ObservableProperty]
    public string gazouPath;
    [ObservableProperty]
    public byte[] gazouBinary;
    [ObservableProperty]
    public string gazouExtension;


    public void SaveGazouToDataBase()//MainPageから渡された画像データをRepositryへ送る。
    {
        var gazouByte = new GazouByte
        {
            GazouName = gazouName,
            GazouBinary = gazouBinary,
            GazouExtension = gazouExtension

        };

        _gazouByteRepositry.AddNewGazouByte(GazouName, GazouBinary, GazouExtension);
       
    }

}

App.xaml.cs

public partial class App : Application
{
    public App()
    {
        InitializeComponent();

        MainPage = new AppShell();
    }
}

MauiProgram.cs

public static class MauiProgram
{
    public static MauiApp CreateMauiApp()
    {
        var builder = MauiApp.CreateBuilder();
        builder
            .UseMauiApp<App>()
            .ConfigureFonts(fonts =>
            {
                fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");
                fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");
            });


        // string dbPath = FileAccessHelper.GetLocalFilePath("gazouByte.db3");
        // builder.Services.AddSingleton< GazouByteRepositry> (s => ActivatorUtilities.CreateInstance< GazouByteRepositry> (s, dbPath));


        builder.Services.AddSingleton<MainPage>();
        builder.Services.AddSingleton<GazouByteRepositry>();
    
        return builder.Build();
    }
}