VB.NET操作ACCESS数据库读取存入图片

一、准备工作

    在ACCESS数据库中，将字段声明为  OLE链接 类型

二、将 图片框中图片转化成 二进制数组的方法

        Dim MyStream As New System.IO.MemoryStream

        ' 将图片框中的图片以BMP形式存入内存流中

        Me.PictureBoxPeiTu.Image.Save(MyStream, System.Drawing.Imaging.ImageFormat.Bmp) 

        Dim MyBytes(MyStream.Length) As Byte    ' 声明数组

        MyBytes = MyStream.ToArray()                    ' 调用方法给数组赋值

三、 二进制数组转化成 图片框中图片的方法

        '这个例子是从DATASET中获取一项中的第4个数据项

        Dim Picturebyte = allData.Tables("timu").Rows(id - 1).Item(4)

        '用得到的BYTE数组创建内存流

        Dim ioStream As IO.MemoryStream = New IO.MemoryStream(Picturebyte, True)  

        '从流中得到BITMAP，注意这里要和上面的类型一致，不一样会报ArgumentException

        PictureBoxPeiTu.Image = Bitmap.FromStream(ioStream, True)                

-----------------------------------------------我是分割线--------------------------------------------

下面是详细的代码：

       这一部分是有关数据访问的类，主要的操作就是把数据存入刚刚说到的表中，还有一个是把表中数据读出

Imports System.Data.OleDb
Public Class AccessDBTools
    Private conn As OleDbConnection

    Public Sub New(ByVal dbPath As String)
        conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\" & dbPath)
    End Sub
    Public Sub insertIntoTable(ByVal tableName As String, ByRef options As Object())
        '生成SQL字符串，这个字符串可以按自己的需要自行更改，我这里是将4个字段插入数据
        Dim str As String = "INSERT INTO " & tableName & " (timu,daan,jiexi,pic)  VALUES("
        Dim comm As OleDbCommand
        For i As Integer = 0 To options.Count - 1
            If i < options.Count - 1 Then
                str = str & "?,"
            Else
                str = str & "?)"
            End If
        Next
        '到这里str会形成：insert into 表名 values(?,?,?,?)这样的字符串，？为占位符，可以加入数据
        conn.Open()
        comm = New OleDbCommand(str, conn)
        '这里的添加数据，这里我用的是OBJECT数组，由外部添加
        '
        '如果想自己添加的话，
        'comm.Parameters.Add(New OleDb.OleDbParameter)
        'comm.Parameters(第几个参数).Value = 值
        '
        For i As Integer = 0 To options.Count - 1
            comm.Parameters.Add(New OleDb.OleDbParameter)
            comm.Parameters(i).Value = options(i)
        Next
        comm.ExecuteNonQuery() 'sql写完了就开始执行  
        conn.Close()
        MsgBox("Save OK", MsgBoxStyle.Information)  '给个提示
    End Sub
    ''' <summary>
    ''' 这是一个查询，最简单的Select了，把所有数据都查询出来
    ''' </summary>
    ''' <param name="tableName"></param>
    ''' <returns>返回一个DataSet</returns>
    ''' <remarks></remarks>
    Public Function selectByTableName(ByVal tableName As String) As DataSet
        Dim str As String = "select * from " & tableName
        conn.Open()
        Dim cmd As New OleDb.OleDbCommand(str, conn)
        Dim RS As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
        Dim DT As New DataSet
        RS.Fill(DT, "timu")
        conn.Close()
        Return DT
    End Function
End Class
Imports System.Data.OleDb
Public Class AccessDBTools
    Private conn As OleDbConnection

    Public Sub New(ByVal dbPath As String)
        conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\" & dbPath)
    End Sub
    Public Sub insertIntoTable(ByVal tableName As String, ByRef options As Object())
        '生成SQL字符串，这个字符串可以按自己的需要自行更改，我这里是将4个字段插入数据
        Dim str As String = "INSERT INTO " & tableName & " (timu,daan,jiexi,pic)  VALUES("
        Dim comm As OleDbCommand
        For i As Integer = 0 To options.Count - 1
            If i < options.Count - 1 Then
                str = str & "?,"
            Else
                str = str & "?)"
            End If
        Next
        '到这里str会形成：insert into 表名 values(?,?,?,?)这样的字符串，？为占位符，可以加入数据
        conn.Open()
        comm = New OleDbCommand(str, conn)
        '这里的添加数据，这里我用的是OBJECT数组，由外部添加
        '
        '如果想自己添加的话，
        'comm.Parameters.Add(New OleDb.OleDbParameter)
        'comm.Parameters(第几个参数).Value = 值
        '
        For i As Integer = 0 To options.Count - 1
            comm.Parameters.Add(New OleDb.OleDbParameter)
            comm.Parameters(i).Value = options(i)
        Next
        comm.ExecuteNonQuery() 'sql写完了就开始执行  
        conn.Close()
        MsgBox("Save OK", MsgBoxStyle.Information)  '给个提示
    End Sub
    ''' <summary>
    ''' 这是一个查询，最简单的Select了，把所有数据都查询出来
    ''' </summary>
    ''' <param name="tableName"></param>
    ''' <returns>返回一个DataSet</returns>
    ''' <remarks></remarks>
    Public Function selectByTableName(ByVal tableName As String) As DataSet
        Dim str As String = "select * from " & tableName
        conn.Open()
        Dim cmd As New OleDb.OleDbCommand(str, conn)
        Dim RS As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
        Dim DT As New DataSet
        RS.Fill(DT, "timu")
        conn.Close()
        Return DT
    End Function
End Class

-----------------------------------------------我是分割线--------------------------------------------

窗体部分比较简单，如下图

这里面除了配图是图片框以外，其它都是RichTextBox，这个是我的功能需要，你可以自行修改。

这部分是窗口内部的代码：

Public Class FormMain
    Private accessTool As AccessDBTools = New AccessDBTools("data.accdb")
    Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
        Dim MyStream As New System.IO.MemoryStream
        '下面的方法把PictureBox中的Image属性存入一个流中
        Me.PictureBoxPeiTu.Image.Save(MyStream, System.Drawing.Imaging.ImageFormat.Bmp)
        '声明字节数组
        Dim MyBytes(MyStream.Length) As Byte
        '把流变成数组
        MyBytes = MyStream.ToArray()
        Dim options As Object() = {RichTextBoxTiMu.Rtf, RichTextBoxDaAn.Rtf, RichTextBoxJieXi.Rtf, MyBytes}
        '调用上个代码段类中的方法存入数据库
        accessTool.insertIntoTable("timu", options)
    End Sub
    ''' <summary>
    ''' 这个是我加的功能，点节图片框可以更换图片，可跳过
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub PictureBoxPeiTu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBoxPeiTu.Click
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            PictureBoxPeiTu.Image = Image.FromFile(OpenFileDialog1.FileName)
        End If
    End Sub
    ''' <summary>
    ''' 这个代码是输入ID来读取一行数据
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub ButtonRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRead.Click
        '调用上个代码段中ACCESS类来读取一个表的数据
        Dim allData As DataSet = accessTool.selectByTableName("timu")
        '获取输入的ID
        Dim id As Integer = CInt(TextBoxID.Text)
        'ID范围判断
        If id > allData.Tables("timu").Rows.Count Then
            MsgBox("你的ID超过最大的范围了！", MsgBoxStyle.Information)
        Else
            '将一行数据的前三个赋值到相应RichTextBox中
            RichTextBoxTiMu.Rtf = allData.Tables("timu").Rows(id - 1).Item(1).ToString()
            RichTextBoxDaAn.Rtf = allData.Tables("timu").Rows(id - 1).Item(2).ToString()
            RichTextBoxJieXi.Rtf = allData.Tables("timu").Rows(id - 1).Item(3).ToString()
            '取最后一个，即图片数据
            Dim Picturebyte = allData.Tables("timu").Rows(id - 1).Item(4)
            '用这个二进制数据创建一个流
            Dim ioStream As IO.MemoryStream = New IO.MemoryStream(Picturebyte, True)
            '用流转化成BITMAP并设置到图片框中去
            PictureBoxPeiTu.Image = Bitmap.FromStream(ioStream, True)
        End If
    End Sub
End Class