Working with Databases

Working with SQL Databases

In SQL databases you can store images in BLOB fields. To load an image from a BLOB field you should perform the following steps:

1. Put the content of the BLOB field into a binary array.
2. Create a System.IO.MemoryStream instance that uses the array.
3. Create a GraphicsMill.Bitmap instance using the stream from the previous step as a constructor parameter.

The System.IO.MemoryStream constructor does not copy an array, but works with it directly, so using this class does not cause memory overhead.

The following code loads an image with a given ID from an SQL database and saves the loaded image to an out.jpg file.

var connection = new System.Data.SqlClient.SqlConnection(connectionString);
var command = new System.Data.SqlClient.SqlCommand("SELECT Image FROM [Image] WHERE Image_ID=" + imageID, connection);

connection.Open();
byte[] imageData = (byte[])command.ExecuteScalar();
connection.Close();

using (var stream = new System.IO.MemoryStream(imageData))
{
    using (var bitmap = new Bitmap(stream))
    {
        bitmap.Save(@"Images\Output\out.jpg");
    }
}

Saving images to a database is quite similar to the loading process described above, by inversly performing the same steps:

1. Save image data to a System.IO.MemoryStream instance using the Bitmap.Save method
2. Get an internal array of bytes without copying all data via the MemoryStream.GetBuffer() method
3. Put the array generated in the previous step into a BLOB field.

The following code saves an in.jpg image to an SQL database.

using (var stream = new System.IO.MemoryStream())
{
    using (var bitmap = new Bitmap(@"Images\in.jpg"))
    {
        bitmap.Save(stream, new JpegSettings(70));
    }

    byte[] imageData = stream.GetBuffer();
    var parameter = new System.Data.SqlClient.SqlParameter("@Image", System.Data.SqlDbType.Image, imageData.Length);
    parameter.Value = imageData;

    var connection = new System.Data.SqlClient.SqlConnection(connectionString);
    var command = new System.Data.SqlClient.SqlCommand("INSERT INTO [Image](Image) VALUES (@Image)", connection);
    command.Parameters.Add(parameter);

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

Working with Access Databases

Microsoft Access 2007 and higher does not provide BLOB fields, but allows the saving of images in a database using attachment fields. To work with attachments you should use the Microsoft.Office.Interop.Access.Dao.dll library from Microsoft Office Assemblies. You can find more information about Microsoft Office Assemblies in the Office Primary Interop Assemblies MSDN article.

You cannot load/save an image from/to an Attachment field directly, but you can use the SaveToFile and LoadFromFile methods of the Microsoft.Office.Interop.Access.Dao.Field2 class. To load an image you should first save the image to a file via SaveToFile, and after that you can read the image and handle it using Graphics Mill. To save an image you should first save the image to a file (for example, via the Bitmap.Save method), and after that call the LoadFromFile method to save the image to a database.

Any Attachment field consists of three subfields: FileData, FileName, and FileType. You should work with the FileData subfield, not with a whole Attachment field to load/save an image from/to the Attachment field.

The following code gets an image with a given ID from a database and saves the loaded image to a file.

String dbPath = @"DB\Images.accdb";
var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
var db = dbe.OpenDatabase(dbPath, false, true, "");
var rst = db.OpenRecordset("Image");
var rstChild = rst.Fields["Image"].Value;
var fldImageData = (Microsoft.Office.Interop.Access.Dao.Field2)rstChild.Fields["FileData"];
string fileName = rstChild.Fields["FileName"].Value;

fldImageData.SaveToFile(@"Images\\" + fileName);

rstChild.Close();
rst.Close();

var bitmap = new Bitmap(@"Images\\" + fileName);

bitmap.Dispose();

The following code saves an out.jpg image (as an attachment) to an Access database.

String dbPath = @"DB\Images.accdb";
var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
var db = dbe.OpenDatabase(dbPath, false, false, "");
var rst = db.OpenRecordset("Image");
rst.AddNew();

var rstChild = rst.Fields["Image"].Value;
rstChild.AddNew();
var fldAttach = (Microsoft.Office.Interop.Access.Dao.Field2)rstChild.Fields["FileData"];
fldAttach.LoadFromFile(@"Images\Output\out.jpg");
rstChild.Update();
rstChild.Close();
rst.Update();
rst.Close();

See Also

Reference

• Bitmap Class

Manual

• Working with Files

Other

• Office Primary Interop Assemblies