如何使用Microsoft Access数据库'；s附件数据类型

本文关键字：数据类型 何使用 Microsoft Access 数据库 | 更新日期: 2023-09-27 18:00:19

我正在尝试使用Microsoft access数据库的附件数据类型。但我不知道怎么用。

我想使用.Net Windows Form.将图像插入访问数据库

在SQL Server 2008中，图像数据类型和字节是兼容的。但我不知道如何将图像插入access数据库。

是否需要像SQL Server一样更改字节，或者可以直接插入到访问数据库中。

如何使用Microsoft Access数据库'；s附件数据类型

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();
    }
}

在此代码中，BlankDatabase.mdb是一个空的MS Access数据库文件。

[编辑]

当您将图像保存到数据库时，如上所示，您可以检索如上所示的图像字节：

您可以从以下图像字节构造Image：

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

以下是我用来从.net代码中的OleDB连接到附件字段类型为的microsoft访问数据库的文件附件

这个方法从我的表中的附件字段名称"Pic"中获取您想要的位于"顺序位置"的文件。。可以在附件字段中存储许多文件，因此必须指定所需的文件。。希望这能有所帮助（我将其用作web url，从访问数据库中的附件字段中获取图像，但COM调用在您的winform应用程序中是相同的）。。祝好运

        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;
    }
相关文章：