Hello,
See the following project source code on GitHub. The actual insert is hard-coded but that doesn't matter as using a OpenFileDialog to select an image works no different.
Here are parts of the code but not all of it, if you want to use it study the code, copy and use.
Class to read and insert records
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using static WorkingWithMsAccessImages.Classes.ConversionModule;
namespace WorkingWithMsAccessImages.Classes
{
public class Operations
{
public DataTable PictureDataTable { get; set; }
public DataTable CategoriesDataTable { get; set; }
public string ConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb";
public PictureItem AddImage(string fileName, int category, string description)
{
var results = new PictureItem() {Success = false};
if (!File.Exists(fileName))
{
results.ErrorMessage = "Failed to find file";
return results;
}
results.ImageBytes = FileImageBytes(fileName);
using (var cn = new OleDbConnection { ConnectionString = ConnectionString })
{
using (var cmd = new OleDbCommand { Connection = cn })
{
cmd.CommandText =
"INSERT INTO Pictures (Category,Picture,Description,BaseName,FileExtension) " +
"Values (@Category,@Picture,@Description,@BaseName,@FileExtension)";
cmd.Parameters.AddRange(new OleDbParameter[]
{
new OleDbParameter
{
ParameterName = "@Category",
DbType = DbType.Int32,
Value = category
},
new OleDbParameter
{
ParameterName = "@Picture",
OleDbType = OleDbType.Binary,
Value = results.ImageBytes
},
new OleDbParameter
{
ParameterName = "@Description",
DbType = DbType.String,
Value = description
},
new OleDbParameter
{
ParameterName = "@BaseName",
DbType = DbType.String,
Value = System.IO.Path.GetFileNameWithoutExtension(fileName).ToLower()
},
new OleDbParameter
{
ParameterName = "@FileExtension",
DbType = DbType.String,
Value = System.IO.Path.GetExtension(fileName).Replace(".", "").ToLower()
}
});
try
{
cn.Open();
var affected = cmd.ExecuteNonQuery();
if (affected == 1)
{
cmd.CommandText = "Select @@Identity";
results.Success = true;
results.Identifier = (int)cmd.ExecuteScalar();
return results;
}
else
{
return results;
}
}
catch (Exception ex)
{
results.ErrorMessage = ex.Message;
return results;
}
}
}
}
public void LoadImages()
{
using (var cn = new OleDbConnection { ConnectionString = ConnectionString })
{
using (var cmd = new OleDbCommand { Connection = cn })
{
cmd.CommandText = "SELECT Identifier, Category FROM Category ORDER BY Category";
CategoriesDataTable = new DataTable();
cn.Open();
CategoriesDataTable.Load(cmd.ExecuteReader());
DataRow dr = CategoriesDataTable.NewRow();
dr["Identifier"] = 0;
dr["Category"] = "ALL";
CategoriesDataTable.Rows.InsertAt(dr, 0);
cmd.CommandText =
"SELECT Identifier, Category, Picture, Description, " +
"BaseName,FileExtension,BaseName + '.' + FileExtension As FullFileName FROM Pictures;";
PictureDataTable = new DataTable();
PictureDataTable.Load(cmd.ExecuteReader());
PictureDataTable.Columns["Picture"].ColumnMapping = MappingType.Hidden;
}
}
}
public Tuple<string, byte[]> LoadSingleImage(int primaryKey)
{
byte[] imageBytes;
using (var cn = new OleDbConnection { ConnectionString = ConnectionString })
{
using (var cmd = new OleDbCommand { Connection = cn })
{
cmd.CommandText =
"SELECT Identifier, Category, Picture, Description," +
" BaseName,FileExtension,BaseName + '.' + FileExtension As FullFileName FROM Pictures WHERE Identifier = ?";
cmd.Parameters.AddWithValue("?", primaryKey);
var dt = new DataTable();
cn.Open();
dt.Load(cmd.ExecuteReader());
imageBytes = dt.Rows[0].Field<byte[]>("Picture");
var fileName = dt.Rows[0].Field<string>("FullFileName");
return new Tuple<string, byte[]>(fileName, imageBytes);
}
}
}
public Operations()
{
LoadImages();
}
}
}
Conversion helpers
using System;
using System.IO;
namespace WorkingWithMsAccessImages.Classes
{
public static class ConversionModule
{
/// <summary>
/// Saves bytes to a new image file
/// </summary>
/// <param name="pImageData"></param>
/// <param name="pFilePath"></param>
/// <returns></returns>
/// <remarks></remarks>
public static bool ConvertBytesToImageFile(byte[] pImageData, string pFilePath)
{
try
{
var fileStream = new FileStream(pFilePath, FileMode.OpenOrCreate, FileAccess.Write);
var binaryWriter = new BinaryWriter(fileStream);
binaryWriter.Write(pImageData);
binaryWriter.Flush();
binaryWriter.Close();
fileStream.Close();
binaryWriter = null;
fileStream.Dispose();
return true;
}
catch (Exception ex)
{
return false;
}
}
public static byte[] FileImageBytes(string pFileName)
{
var fileStream = new FileStream(pFileName, FileMode.Open, FileAccess.Read, FileShare.Read);
var imageStream = new StreamReader(fileStream);
byte[] byteArray = new byte[((int)(fileStream.Length - 1)) + 1];
fileStream.Read(byteArray, 0, (int)fileStream.Length);
return byteArray;
}
}
}
Return type for inserting image
namespace WorkingWithMsAccessImages.Classes
{
public class PictureItem
{
public int Identifier { get; set; }
public byte[] ImageBytes { get; set; }
public bool Success { get; set; }
public string ErrorMessage { get; set; }
}
}
using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using WorkingWithMsAccessImages.Classes;
namespace WorkingWithMsAccessImages
{
public partial class Form1 : Form
{
private readonly BindingSource _bindingSource = new BindingSource();
public Form1()
{
InitializeComponent();
_bindingSource.PositionChanged += BindingSourceOnPositionChanged;
Shown += OnShown;
}
private void OnShown(object sender, EventArgs e)
{
var ops = new Operations();
_bindingSource.DataSource = ops.PictureDataTable;
dataGridView1.DataSource = _bindingSource;
dataGridView1.ExpandColumns();
}
private void BindingSourceOnPositionChanged(object sender, EventArgs e)
{
if (_bindingSource.Current != null)
{
pictureBox1.Image = Image.FromStream(new MemoryStream(
((DataRowView)_bindingSource.Current).Row.Field<byte[]>("Picture")));
}
}
private void AddImageButton_Click(object sender, EventArgs e)
{
var ops = new Operations();
var results = ops.AddImage("ErrorForm.png", 0, "Test");
if (results.Success)
{
var values = ops.LoadSingleImage(results.Identifier);
var table = (DataTable) _bindingSource.DataSource;
table.Rows.Add(results.Identifier, 0, results.ImageBytes);
_bindingSource.MoveLast();
}
else
{
MessageBox.Show(!string.IsNullOrWhiteSpace(results.ErrorMessage) ?
$"Failed to add image\n{results.ErrorMessage}" :
"Failed to add image\n");
}
}
}
}
Update
Added simple update method
public PictureItem UpdateCurrentDescription(int primaryKey, string description)
{
var results = new PictureItem() { Success = false };
using (var cn = new OleDbConnection {ConnectionString = ConnectionString})
{
using (var cmd = new OleDbCommand {Connection = cn})
{
cmd.CommandText =
"UPDATE Pictures SET Description = @Description " +
"WHERE Identifier = @Identifier";
var descriptionParameter = new OleDbParameter
{
DbType = DbType.String,
ParameterName = "@Description",
Value = description
};
cmd.Parameters.Add(descriptionParameter);
var identifierParameter = new OleDbParameter
{
DbType = DbType.String,
ParameterName = "@Identifier",
Value = primaryKey
};
cmd.Parameters.Add(identifierParameter);
try
{
cn.Open();
int affected = cmd.ExecuteNonQuery();
if (affected == 1)
{
results.Success = true;
}
}
catch (Exception e)
{
results.ErrorMessage = e.Message;
return results;
}
}
}
return results;
}
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 7.000000000000001%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDZ%3C/text%3E%3C/svg%3E)