How to use Microsoft Access Database's Attachment Data Type?

Question

I'm trying to use Microsoft access database's attachment data type. but i don't know how to use it.

I want to insert image into access database using .Net Windows Form.

In SQL Server 2008 Image data type and byte is compatibility for that. but i don't know how to insert image into access database.

is there need to change byte like SQL Server or can directly insert into access database.

Answer 1

Here is what I use to get the file attachments from an OleDB connection in .net code to a microsoft access database with attachment field types:

This method gets the file you want in Ordinal Position from the attachment field name "Pic" in my table.. you can store many files in the attachment field, so you have to specify which of the files you want.. hope this helps ( i use this as web url to take the image from the attachment field in the access database, but the COM calls will be the same in your winform app)..good luck

        try
            {
                  //You get your file in a byteArray fileType is just the ordinal file position in the fileattachment field..ex. 1, 2, 3 (shown in the access listbox)
                Response.BinaryWrite(GetPicField(productID, fileType));
                Response.ContentType = "image/bmp";
            }

            catch 
            {
                //need to get missing product photo image here as well N/A
                Response.BinaryWrite(GetNA_Image());
                Response.ContentType = "image/bmp";
            }

    //getting from Database
    private byte[] GetPicField(string productID,int fileToShow)
    {
        DBEngine dbe = new DBEngine();
        Database db;
        Recordset rs;

        byte[] byteArray = null;

        dbe = new DBEngine();
        db = dbe.OpenDatabase(Application["DB_FileName"].ToString());
        rs = db.OpenRecordset("SELECT PIC FROM PRODUCT WHERE PRODUCTID = " + productID, RecordsetTypeEnum.dbOpenForwardOnly, 0, LockTypeEnum.dbPessimistic);

        if (rs.RecordCount > 0)
        {

            Recordset rs2 = (Recordset2)rs.Fields["Pic"].Value;
            int i = 1;

            while (i < fileToShow)
            {
                rs2.MoveNext();
                i++;
            }

          //get the thubmnail
           Field2 f2 = (Field2)rs2.Fields["FileData"]; //0 is first pic

            byteArray = f2.GetChunk(20, f2.FieldSize - 20);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(f2);
            rs2.Close();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(rs2);
            f2 = null;
            rs2 = null;

        }

        rs.Close();
        db.Close();

        System.Runtime.InteropServices.Marshal.ReleaseComObject(rs);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(dbe);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(db);

        rs = null;
        db = null;
        dbe = null;

        return byteArray;

    }

Answer 2

using (var connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BlankDatabase.mdb"))
{
    connection.Open();

    // Create table
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            CREATE TABLE FileTable (
                FileName VARCHAR(255),
                File IMAGE)
            ";
        command.ExecuteNonQuery();
    }

    var imageContent = File.ReadAllBytes(@"C:\logo.png");

    // upload image to the table
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            INSERT INTO FileTable (FileName, File)
            VALUES (@FileName, @File)
            ";
        command.Parameters.AddWithValue("@FileName", "Logo");
        command.Parameters.AddWithValue("@File", imageContent);
        command.ExecuteNonQuery();
    }

    // retreive image from the table
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            SELECT File
            FROM FileTable
            WHERE FileName = 'Logo'
            ";
        var readImageContent = (byte[])command.ExecuteScalar();
        File.WriteAllBytes(@"C:\logo1.png", readImageContent);
    }

    // alter image from the table
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            UPDATE FileTable
            SET File = @File
            WHERE FileName = 'Logo'
            ";
        command.Parameters.AddWithValue("@File", imageContent);
        command.ExecuteNonQuery();
    }

    // delete image from the table
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            DELETE FROM FileTable
            WHERE FileName = 'Logo'
            ";
        command.ExecuteNonQuery();
    }
}

In this code BlankDatabase.mdb is an empty MS Access database file.

[Edit]

When you saved image to the database, as shown above you can retrieve image bytes as shown above:

You can construct Image from image bytes like this:

var imageConverter = new ImageConverter();
pictureBox1.Image = (Image)imageConverter.ConvertFrom(fileContent);