Writing and reading images on a Microsoft Access database
If just a few days ago we talked about how to store binary files on SQL Server, today’s Microsoft Access turn. The process is similar, though for this example I’ve chosen to use image files as the binary data we are going to store and retrieve from the database.
Let’s say we have a simple Access database, with a single table called Images, with the following layout:
| Field Name | Type |
| imgID | int (Autonumeric Key) |
| FileName | Text(255) |
| ImageData | OLE Object |
imgID will store a unique autonumeric key for identifying each record. FileName will have the name of the image file, and ImageData will store the image itself, as binary data.
The procedure to insert images on the Access database is as follows:
private void SaveData(string fileName)
{
try
{
//Creamos un nuevo FileStream a partir del fichero parámetro
//
//We create a new FileStream from the file specified as parameter
FileStream fs = new FileStream(fileName, FileMode.Open);
//Declaramos un array de bytes del tamaño del FileStream
//
//We declare a new byte array as big as the FileStream
Byte[] data = new byte[fs.Length];
//E introducimos los datos del FileStream en el array
//
//And we enter the data from the FileStream into the array
fs.Read(data, 0, Convert.ToInt32(fs.Length));
fs.Close();
//para insertar el nuevo registro
//
//We create a new DataAdapter, DataSet, CommandBuilder and DataRow
//to perform the insertion of the new record
if(conn.State != ConnectionState.Open ) conn.Open();
OleDbDataAdapter adap =
new OleDbDataAdapter("SELECT * FROM Images", conn);
adap.MissingSchemaAction = MissingSchemaAction.AddWithKey;
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adap);
adap.InsertCommand = cmdBuilder.GetInsertCommand();
DataSet ds = new DataSet();
adap.Fill(ds);
DataRow dr = ds.Tables[0].NewRow();
dr["FileName"] = fileName;
dr["ImageData"] = data; //The byte array
ds.Tables[0].Rows.Add(dr);
adap.Update(ds);
conn.Close();
//el nuevo registro insertado
//
//This function refreshes a ListBox to show the newly inserted record
LoadData();
MessageBox.Show("Done!\nImage file: " + fileName +
" added to the database.");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
{
try
{
//Creamos un nuevo FileStream a partir del fichero parámetro
//
//We create a new FileStream from the file specified as parameter
FileStream fs = new FileStream(fileName, FileMode.Open);
//Declaramos un array de bytes del tamaño del FileStream
//
//We declare a new byte array as big as the FileStream
Byte[] data = new byte[fs.Length];
//E introducimos los datos del FileStream en el array
//
//And we enter the data from the FileStream into the array
fs.Read(data, 0, Convert.ToInt32(fs.Length));
fs.Close();
//Creamos un nuevo DataAdapter, DataSet, CommandBuilder and DataRow
//para insertar el nuevo registro
//
//We create a new DataAdapter, DataSet, CommandBuilder and DataRow
//to perform the insertion of the new record
if(conn.State != ConnectionState.Open ) conn.Open();
OleDbDataAdapter adap =
new OleDbDataAdapter("SELECT * FROM Images", conn);
adap.MissingSchemaAction = MissingSchemaAction.AddWithKey;
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adap);
adap.InsertCommand = cmdBuilder.GetInsertCommand();
DataSet ds = new DataSet();
adap.Fill(ds);
DataRow dr = ds.Tables[0].NewRow();
dr["FileName"] = fileName;
dr["ImageData"] = data; //The byte array
ds.Tables[0].Rows.Add(dr);
adap.Update(ds);
conn.Close();
//Esta funcion refresca un ListBox para que se refleje
//el nuevo registro insertado
//
//This function refreshes a ListBox to show the newly inserted record
LoadData();
MessageBox.Show("Done!\nImage file: " + fileName +
" added to the database.");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
Colorized by: CarlosAg.CodeColorizer
And the procedures to extract images from the database are like these:
/// <summary>
/// Coloca en el control Image la imagen recuperada de base de
/// datos por la función LoadImage, en base al índice único de
/// la imagen en la base de datos.
///
/// Sets in the Image control an image recovered from the database
/// via the LoadImage function, thanks to the unique identifier
/// of the image on the database.
/// </summary>
/// <param name="imgIndex">El identificador único de la imagen
///en la base de datos</param>
/// <param name="imgIndex">The unique identifier of the image
///on the database</param>
private void LoadImageControl(string imgIndex)
{
try
{
//Creamos un nuevo stream de memoria con el resultado
//de la función LoadImage, que devuelve un array de bytes
//
//We create a new MemoryStream with a byte array,
//returned by the LoadImage function
MemoryStream mem = new MemoryStream(LoadImage(imgIndex));
//Y establecemos que la imagen del control picImage es una nueva imagen,
//a partir del stream en memoria mem
//
//And we set the picImage control’s image is a new image,
//built from the memory stream mem
picImage.Image = Image.FromStream(mem);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
/// Carga la imagen de la base de datos en un array
//de bytes y devuelve dicho array
///
/// Loads the image from the database to a byte array and returns that array
/// </summary>
/// <param name="imgIndex">El ID unico de la imagen</param>
/// <param name="imgIndex">The unique ID of the image</param>
/// <returns>Devuelve la imagen en un array de bytes</returns>
/// <returns>The image on a byte array</returns>
private byte[] LoadImage(string imgIndex)
{
if(conn.State != ConnectionState.Open ) conn.Open();
sSQL = "SELECT ImageData FROM Images WHERE imgID = " + imgIndex;
OleDbCommand cmd = new OleDbCommand(sSQL, conn);
byte[] imgData = (byte[]) cmd.ExecuteScalar();
conn.Close();
return imgData;
}
/// Coloca en el control Image la imagen recuperada de base de
/// datos por la función LoadImage, en base al índice único de
/// la imagen en la base de datos.
///
/// Sets in the Image control an image recovered from the database
/// via the LoadImage function, thanks to the unique identifier
/// of the image on the database.
/// </summary>
/// <param name="imgIndex">El identificador único de la imagen
///en la base de datos</param>
/// <param name="imgIndex">The unique identifier of the image
///on the database</param>
private void LoadImageControl(string imgIndex)
{
try
{
//Creamos un nuevo stream de memoria con el resultado
//de la función LoadImage, que devuelve un array de bytes
//
//We create a new MemoryStream with a byte array,
//returned by the LoadImage function
MemoryStream mem = new MemoryStream(LoadImage(imgIndex));
//Y establecemos que la imagen del control picImage es una nueva imagen,
//a partir del stream en memoria mem
//
//And we set the picImage control’s image is a new image,
//built from the memory stream mem
picImage.Image = Image.FromStream(mem);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
/// <summary>
/// Carga la imagen de la base de datos en un array
//de bytes y devuelve dicho array
///
/// Loads the image from the database to a byte array and returns that array
/// </summary>
/// <param name="imgIndex">El ID unico de la imagen</param>
/// <param name="imgIndex">The unique ID of the image</param>
/// <returns>Devuelve la imagen en un array de bytes</returns>
/// <returns>The image on a byte array</returns>
private byte[] LoadImage(string imgIndex)
{
if(conn.State != ConnectionState.Open ) conn.Open();
sSQL = "SELECT ImageData FROM Images WHERE imgID = " + imgIndex;
OleDbCommand cmd = new OleDbCommand(sSQL, conn);
byte[] imgData = (byte[]) cmd.ExecuteScalar();
conn.Close();
return imgData;
}
Colorized by: CarlosAg.CodeColorizer
In any case, you can download a complete code example here, source code commented in English and Spanish. Please feel free to download it and play a little with it.
NOTE.- On a future entry I’ll tell you how to compact and repair a Microsoft Access database with C# code. Applications like the source code you can download from this post need that compact and repair capabilities, and they need it badly.
