c# - 在 Access 2010 中存储字节 []

Question

这么简单的任务:How to store a Byte[] in Access 2010?(整天在网上搜索这个。)

我必须在 access 2010 中使用“附件字段”，因为据我所知，没有其他可用的字段(varBinary、Image、..)。

我试过:(ConvertImageToByte 返回一个 Byte[])

 Cmd.CommandText = "UPDATE Clubs SET Field1 = @File WHERE Name = @Name";
 OleDbParameter para = new OleDbParameter("@File", OleDbType.VarBinary);
 para.Value = ConvertImageToByte(Logo);
 Cmd.ExecuteNonQuery();

异常:“UPDATE 或 DELETE 查询不能包含多值字段。”

我试过:

 DBEngine dbe = new DBEngine();
 Database db = dbe.OpenDatabase("database.accdb", false, false, "");
 String Command = "SELECT * FROM Clubs";
 Recordset rs = db.OpenRecordset(Command, RecordsetTypeEnum.dbOpenDynaset, 0, LockTypeEnum.dbOptimistic);
 rs.MoveFirst();
 rs.Edit();
 Recordset2 rs2 = (Recordset2)rs.Fields["Field1"].Value;
 rs2.AddNew();

 Field2 f2 = (Field2)rs2.Fields["FileData"];


 f2.LoadFromFile("file.png");
 rs2._30_Update();
 rs2.Close();

 rs._30_Update();
 rs.Close();

这行得通，但文件一直位于表格的第一行，我无法弄清楚如何获得正确的行。如果我尝试向 SELECT 语句添加 WHERE 子句，我会收到“参数太少。预期为 2”。异常。

如果有人知道将我的 Byte[](或图像)写入数据库并再次将其取出的方法，请告诉我!

请不要给我以下链接:

http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx#BM4.6

http://www.mikesdotnetting.com/Article/123/Storing-Files-and-Images-in-Access-with-ASP.NET

http://www.sitepoint.com/forums/showthread.php?t=666928

http://www.eggheadcafe.com/software/aspnet/35103540/multivalued-fields-in-access-2007-with-c-ado.aspx

Programmatically managing Microsoft Access Attachment-typed field with .NET

感谢您的帮助。

Answer 1

您可以使用 OLE 对象字段，这是匹配 varbinary(max) 的最佳选择。

一些注意事项:

''Reference: Microsoft ActiveX Data Object x.x Library
Dim strSQL As String
Dim strCN As String
Dim rs As dao.Recordset
Dim mstream As ADODB.Stream


strSQL = "SELECT Pix FROM Table1"
Set rs = CurrentDb.OpenRecordset(strSQL)

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\docs\project.jpg" ''FileName & FullPath

rs.AddNew
rs.Fields("Pix").Value = mstream.Read
rs.Update

rs.Close

编辑

要复制回磁盘，您可以再次使用 Stream:

Dim strSQL As String
Dim cn As New ADODB.Connection
Dim mstream As New ADODB.Stream


strSQL = "SELECT Pix FROM Table1"
Set rs = CurrentDb.OpenRecordset(strSQL)

mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs!Pix
mstream.SaveToFile "c:\docs\pixout.jpg", adSaveCreateOverWrite