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

DPlug 41 Reputation points
2020-12-21T00:11:48.31+00:00

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.

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,185 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,565 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
7,002 questions
XAML
XAML
A language based on Extensible Markup Language (XML) that enables developers to specify a hierarchy of objects with a set of properties and logic.
559 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Peter Fleischer (Freelancer) 17,571 Reputation points
    2020-12-21T11:11:23.92+00:00

    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
      }
    }
    
    No comments