从 Access 数据库中的附件字段中提取文件 [英] Extracting files from an Attachment field in an Access database
问题描述
我们正在开展一个项目，需要将存储在 Access 数据库中的数据迁移到缓存数据库.Access 数据库包含数据类型为 Attachment 的列；一些元组包含多个附件.我可以通过使用 .FileName 获取这些文件的文件名，但我不确定如何确定一个文件何时结束而另一个文件在 .FileData 中开始.
We are working on a project where we need to migrate data stored in an Access database to a cache database. The Access database contains columns with a data type of Attachment; some of the tuples contain multiple attachments. I am able to obtain the filenames of these files by using .FileName, but I'm unsure how to determine when one file ends and another starts in .FileData.
我正在使用以下内容来获取此数据:
I am using the following to obtain this data:
System.Data.OleDb.OleDbCommand command= new System.Data.OleDb.OleDbCommand();
command.CommandText = "select [Sheet1].[pdf].FileData,* from [Sheet1]";
command.Connection = conn;
System.Data.OleDb.OleDbDataReader rdr = command.ExecuteReader();
推荐答案
(我对这个问题的原始回答具有误导性.它适用于随后使用 Adobe Reader 打开的 PDF 文件，但并不总是有效适用于其他类型的文件.以下是更正的版本.)
不幸的是，我们无法使用 OleDb 直接检索 Access Attachment 字段中的文件内容.Access 数据库引擎将一些元数据添加到文件的二进制内容中，如果我们通过 OleDb 检索 .FileData，则包含该元数据.
Unfortunately we cannot directly retrieve the contents of a file in an Access Attachment field using OleDb. The Access Database Engine prepends some metadata to the binary contents of the file, and that metadata is included if we retrieve the .FileData via OleDb.
为了说明，使用 Access UI 将名为Document1.pdf"的文档保存到附件字段.该 PDF 文件的开头如下所示:
To illustrate, a document named "Document1.pdf" is saved to an Attachment field using the Access UI. The beginning of that PDF file looks like this:
如果我们使用以下代码尝试将PDF文件解压到磁盘
If we use the following code to try and extract the PDF file to disk
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
cmd.CommandText =
"SELECT Attachments.FileData " +
"FROM AttachTest " +
"WHERE Attachments.FileName='Document1.pdf'";
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
byte[] fileData = (byte[])rdr[0];
using (var fs = new FileStream(
@"C:UsersGordDesktopFromFileData.pdf",
FileMode.Create, FileAccess.Write))
{
fs.Write(fileData, 0, fileData.Length);
fs.Close();
}
}
}
然后生成的文件将包含文件开头的元数据(在本例中为 20 个字节)
then the resulting file will include the metadata at the beginning of the file (20 bytes in this case)
Adobe Reader 能够打开此文件，因为它足够强大，可以忽略文件中可能出现在 '%PDF-1.4' 签名之前的任何垃圾".不幸的是，并非所有文件格式和应用程序都对文件开头的无关字节如此宽容.
Adobe Reader is able to open this file because it is robust enough to ignore any "junk" that may appear in the file before the '%PDF-1.4' signature. Unfortunately not all file formats and applications are so forgiving of extraneous bytes at the beginning of the file.
唯一官方™从 Access 中的 Attachment 字段中提取文件的方法是使用 ACE DAO Field2 对象的 .SaveToFile 方法，如下所示:
The only Official™ way of extracting files from an Attachment field in Access is to use the .SaveToFile method of an ACE DAO Field2 object, like so:
// required COM reference: Microsoft Office 14.0 Access Database Engine Object Library
//
// using Microsoft.Office.Interop.Access.Dao; ...
var dbe = new DBEngine();
Database db = dbe.OpenDatabase(@"C:UsersPublicDatabase1.accdb");
Recordset rstMain = db.OpenRecordset(
"SELECT Attachments FROM AttachTest WHERE ID=1",
RecordsetTypeEnum.dbOpenSnapshot);
Recordset2 rstAttach = rstMain.Fields["Attachments"].Value;
while ((!"Document1.pdf".Equals(rstAttach.Fields["FileName"].Value)) && (!rstAttach.EOF))
{
rstAttach.MoveNext();
}
if (rstAttach.EOF)
{
Console.WriteLine("Not found.");
}
else
{
Field2 fld = (Field2)rstAttach.Fields["FileData"];
fld.SaveToFile(@"C:UsersGordDesktopFromSaveToFile.pdf");
}
db.Close();
请注意，如果您尝试使用 Field2 对象的 .Value，您仍然会在字节序列的开头获得元数据；.SaveToFile 过程是将其删除的原因.
Note that if you try to use the .Value of the Field2 object you will still get the metadata at the beginning of the byte sequence; the .SaveToFile process is what strips it out.
这篇关于从 Access 数据库中的附件字段中提取文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
