2

I'm trying to read a Microsoft Access Database using C#. I'm using the OLE-DB classes. The Problem is that this code

OleDbDataReader reader = Command.ExecuteReader(); 
while (reader.Read())
{
    Console.WriteLine(reader.GetFieldType(0) + "\t" + reader.GetFieldType(1) + "\t" + reader.GetFieldType(2) +
            "\t" + reader.GetFieldType(3) + "\t" + reader.GetFieldType(4) + "\t" + reader.GetFieldType(5));
}

tells me, that the 5th field is from the datatype string. But it's an attached file. When I'm trying to read this string, it is empty. 

System.Int32    System.String   System.String   System.Int32    System.DateTime    System.String

Is there a way to read attached files from a database?

HerpDerpington
  • 3,751
  • 4
  • 27
  • 43

2 Answers2

4

I realise you asked for OleDb, but with DAO you could say something like:

    DBEngine dbe = new DBEngine();
    Database db = dbe.OpenDatabase(@"z:\docs\test.accdb", false, false, "");
    Recordset rs = db.OpenRecordset("SELECT TheAttachment FROM TheTable", 
        RecordsetTypeEnum.dbOpenDynaset, 0, LockTypeEnum.dbOptimistic);

    Recordset2 rs2 = (Recordset2)rs.Fields["TheAttachment"].Value;

    Field2 f2 = (Field2)rs2.Fields["FileData"];
    f2.SaveToFile(@"z:\docs\ForExample.xls");
    rs2.Close();
    rs.Close();

Reference: Programmatically managing Microsoft Access Attachment-typed field with .NET

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
1

It's a bit tricky, but you can't just query the attachment column, you'll only get the filename. You have to select the values from the attachment object in the attachment column, and pull the byte array (stored in filedata), then remove the header added to the file by Access:

var connection = new OleDbConnection(connectionString);
connection.Open();
var dt = new DataTable("Attachments");
var dataAdapter = new OleDbDataAdapter(@"select attachmentColumn.FileData as filedata, attachmentColumn.FileName as filename, attachmentColumn.FileType as filetype from tablename", connection);
dataAdapter.Fill(dt);

foreach (DataRow row in dt.Rows)
{
  var filename = row["filename"].ToString();
  if (string.IsNullOrWhiteSpace(filename)) continue;
  var filedata = (byte[]) row["filedata"];
  int header = (int) filedata[0];
  byte[] actualFile = new byte[filedata.Length - header];
  Buffer.BlockCopy(filedata, header, actualFile, 0, actualFile.Length);
  // do stuff with byte array!
  File.WriteAllBytes("C:\\" + filename, actualFile);
}

Keep in mind that uncompressed files will be compressed by Access. More about that here. Hope this works for you!

jmack2424
  • 11
  • 1