How to store and retrieve image using MVVM pattern in WPF C#

Question

Please I am looking for a solid means to save and retrieve image to/fro SQL server database. I am using MVVM pattern in WPF. I have gone through everything I saw on Google concerning this issue, all I'm seeing is Image control being tied directly to code behind. Please I am not using code behind pattern, I am using pure MVVM pattern meaning my image control in the xaml should not know anything about the processes that is going on. What I need is a concrete means of converting the image in the image control to byte array and saving it to the database and also converting it back to Image format and display it on the view when the SqlDatareader goes to work, without tying the image control directly to code behind. How is this going to happen, is it in my viewmodel or my model? I am just confuse. Every other thing is ready, starting from textboxes to datetime picker. It's just this image stuff that is giving me headaches. I need help. If there are some materials on MVVM pattern for this process, I'll like to see them. Thanks in advance.

Answer 1

Hi,
try following demo. The ConnectionString to SQL Server is stored in Settings. The demo shows create Table, Insert Pictures to table and display the first Picture. If you have any questions, please, write and I can extend this demo.

XAML:

<Window x:Class="WpfApp1.Window025"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WpfApp025"
        mc:Ignorable="d"
        Title="Window025" Height="450" Width="800">
  <Window.DataContext>
    <local:ViewModel/>
  </Window.DataContext>
  <Window.Resources>
    <Style TargetType="Button">
      <Setter Property="Margin" Value="5"/>
    </Style>
  </Window.Resources>
  <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="Auto"/>
      <RowDefinition/>
    </Grid.RowDefinitions>
    <StackPanel Orientation="Horizontal">
      <Button Content="Create Table" Command="{Binding Cmd}" CommandParameter="Create"/>
      <Button Content="Fill Table" Command="{Binding Cmd}" CommandParameter="Fill"/>
      <Button Content="Show Picture" Command="{Binding Cmd}" CommandParameter="Show"/>
    </StackPanel>
    <Image Grid.Row="1" Source="{Binding ImageToShow}"/>
  </Grid>
</Window>

And Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using io = System.IO;
using dr = System.Drawing;
using System.ComponentModel;
using System.Runtime.CompilerServices;

namespace WpfApp025
{
  public class ViewModel : INotifyPropertyChanged
  {
    public BitmapImage ImageToShow { get; set; }
    public ICommand Cmd { get => new WpfApp1.Helper.RelayCommand(CmdExec); }

    private int nr = 1;
    private void CmdExec(object obj)
    {
      switch (obj.ToString())
      {
        case "Create":
          CreataTable();
          break;
        case "Fill":
          InsertPictures(@"c:\temp");
          break;
        case "Show":
          ShowImage(nr);
          break;
        default:
          break;
      }
    }

    private void CreataTable()
    {
      using (SqlConnection cn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL))
      {
        cn.Open();
        using (SqlCommand cmd = new SqlCommand { Connection = cn })
        {
          // delete previous table in SQL Server 2016 and above
          cmd.CommandText = "DROP TABLE IF EXISTS Table1;";
          cmd.ExecuteNonQuery();
          // Create Table
          cmd.CommandText = "CREATE Table [Table1]([ID] int Identity, [FolderName] nvarchar(255), [FileName] nvarchar(255), [FileType] nvarchar(10), [Picture] image, CONSTRAINT [PK_Table1] PRIMARY KEY ([ID]))";
          cmd.ExecuteNonQuery();
        }
      }
    }

    private void InsertPictures(string directorypath)
    {
      SqlParameter parFolderName, parFileName, parFileType, parPicture;
      using (SqlConnection cn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL))
      {
        cn.Open();
        using (SqlCommand cmd = new SqlCommand { Connection = cn })
        {
          cmd.CommandText = "INSERT INTO dbo.Table1 (FolderName, FileName, FileType, Picture) " +
                              "VALUES (@FolderName,@FileName,@FileType,@Picture); " +
                              "SELECT CAST(scope_identity() AS int); ";
          // define parameters once
          parFolderName = cmd.Parameters.Add("@FolderName", SqlDbType.VarChar, 255);
          parFileName = cmd.Parameters.Add("@FileName", SqlDbType.VarChar, 255);
          parFileType = cmd.Parameters.Add("@FileType", SqlDbType.VarChar, 10);
          parPicture = cmd.Parameters.Add("@Picture", SqlDbType.Image);
          foreach (string f in GetFilesRecursive(directorypath))
          {
            using (dr.Image img = dr.Image.FromFile(f))
            {
              int w = 800;
              int h = (int)(w * img.Height / img.Width);
              dr.Bitmap bmp = new dr.Bitmap(img, w, h);
              using (MemoryStream ms = new MemoryStream())
              {
                bmp.Save(ms, dr.Imaging.ImageFormat.Jpeg);
                parFolderName.Value = io.Path.GetDirectoryName(f);
                parFileName.Value = io.Path.GetFileNameWithoutExtension(f);
                parFileType.Value = io.Path.GetExtension(f);
                parPicture.Value = ms.ToArray();
                cmd.ExecuteNonQuery();
              }
            }
          }
        }
      }
    }

    private IEnumerable<string> GetFilesRecursive(string directorypath)
    {
      foreach (string file in Directory.GetFiles(directorypath, "*.jpg")) yield return file;
      foreach (var folder in Directory.GetDirectories(directorypath)) GetFilesRecursive(folder);
    }

    private void ShowImage(int ID)
    {
      using (SqlConnection cn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL))
      {
        cn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Table1 WHERE ID = @ID", cn))
        {
          cmd.Parameters.AddWithValue("@ID", ID);
          SqlDataReader rdr = cmd.ExecuteReader();
          if (rdr.Read())
          {
            ImageToShow = LoadBitmapImage((byte[])rdr["Picture"]);
            OnPropertyChanged(nameof(ImageToShow));
          }
        }
      }
    }

    private static BitmapImage LoadBitmapImage(byte[] imageData)
    {
      if (imageData == null || imageData.Length == 0) return null;
      var image = new BitmapImage();
      using (var mem = new MemoryStream(imageData))
      {
        mem.Position = 0;
        image.BeginInit();
        image.CreateOptions = BitmapCreateOptions.PreservePixelFormat;
        image.CacheOption = BitmapCacheOption.OnLoad;
        image.UriSource = null;
        image.StreamSource = mem;
        image.EndInit();
      }
      image.Freeze();
      return image;
    }

    #region PropertyChanged
    public event PropertyChangedEventHandler PropertyChanged;
    internal void OnPropertyChanged([CallerMemberName] string propName = "") =>
     PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propName));
    #endregion
  }
}