Question

In this chapter of the SQLite C# tutorial, we will work with image files. Note that some people oppose putting images into databases. Here we only show how to do it and we avoid the technical issues of whether to save images in databases or not.

sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);

For this example, we create a new table called Images. For the images, we use the BLOB data type, which stands for Binary Large Object.

Inserting images

In the first example, we are going to insert an image to the SQLite database.

using System;
using System.IO;
using System.Data;
using Mono.Data.Sqlite;

public class Example
{
  static void Main() 
  {

    string cs = "URI=file:test.db";    

    using(SqliteConnection con = new SqliteConnection(cs))
    {
      
      con.Open();

      byte[] data = null;

      try
      {
        data = File.ReadAllBytes("woman.jpg");
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.ToString());
      }
 
      SqliteCommand cmd = new SqliteCommand(con);
  
      cmd.CommandText = "INSERT INTO Images(Data) VALUES (@img)";
      cmd.Prepare();

      cmd.Parameters.Add("@img", DbType.Binary, data.Length);
      cmd.Parameters["@img"].Value = data;
      cmd.ExecuteNonQuery();
      
      con.Close();
    }
  }
}

We read an image from the current working directory and write it into the Images table of the SQLite test.db database.

byte[] data = null;

The image data will be stored in an array of bytes.

data = File.ReadAllBytes("woman.jpg");

The ReadAllBytes() method opens a binary file, reads the contents of the file into a byte array, and then closes the file.

cmd.CommandText = "INSERT INTO Images(Data) VALUES (@img)";
cmd.Prepare();

We prepare an SQL statement for inserting the array of bytes into the Data column of the Images table.

cmd.Parameters.Add("@img", DbType.Binary, data.Length);
cmd.Parameters["@img"].Value = data;
cmd.ExecuteNonQuery();

We bind the binary data to the prepared statement. Then the statement is executed. The image is written to the database table.

Reading images

In this section, we are going to perform the reverse operation. We will read an image from the database table.

using System;
using System.IO;
using Mono.Data.Sqlite;

public class Example
{

  static void Main() 
  {
    string cs = "URI=file:test.db";    

    using(SqliteConnection con = new SqliteConnection(cs))
    {      
      con.Open();

      SqliteCommand cmd = new SqliteCommand(con);   
      cmd.CommandText = "SELECT Data FROM Images WHERE Id=1";
      byte[] data = (byte[]) cmd.ExecuteScalar();

      try
      {         
        if (data != null)
        { 
          File.WriteAllBytes("woman2.jpg", data);
        } else 
        {
          Console.WriteLine("Binary data not read");
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.ToString());
      }      

      con.Close();
    }
  }
}

We read image data from the Images table and write it to another file, which we call woman2.jpg .

cmd.CommandText = "SELECT Data FROM Images WHERE Id=1";

This line selects the image data from the table.

byte[] data = (byte[]) cmd.ExecuteScalar();

We retrieve the binary data from the database table. The data is stored in an array of bytes.

if (data != null)
{ 
  File.WriteAllBytes("woman2.jpg", data);
} else 
{
  Console.WriteLine("Binary data not read");
}

The WriteAllBytes() method creates a new file, writes the specified byte array to the file, and then closes the file. If the target file already exists, it is overwritten. When the database table is empty and we run this example, we get a null. Therefore we check for the null value.

This part of the SQLite C# tutorial was dedicated to reading and writing images.