存入圖片至ORACLE及從ORACLE讀取圖片方法整理

一、將圖片存入Oracle資料庫

樣本表NEWS的結構為：newsid number(10),title varchar2(100),image(blob)

方法1：利用OracleCommandBuilder類（該類用於自動產生用於協調 DataSet 的更改與關聯的資料庫的單表命令。）

        Dim cn As New OracleConnection("data source=site;uid=gf;pwd=macro;")
        cn.Open()

        Dim da As New OracleDataAdapter("select * from news", cn)
        Dim myCB As New OracleCommandBuilder(da)
        Dim ds As New DataSet("news")
        da.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Dim fs As Stream = File1.PostedFile.InputStream 'File1為檔案選擇框，作為伺服器控制項使用
        Dim mydata(fs.Length) As Byte
        fs.Read(mydata, 0, fs.Length)
        fs.Close()
        da.Fill(ds, "news")
        Dim myRow As DataRow = ds.Tables("news").NewRow
        myRow("newsid") = txtNewsID.Text
        myRow("title") = txtTitle.Text
        myRow("image") = mydata
        ds.Tables("news").Rows.Add(myRow)
        da.Update(ds, "news")

        cn.Close()

方法2：利用過程

事先定義的Oracle過程為：

CREATE OR REPLACE  PROCEDURE "GF"."NEWS_ADD" 
  (in_newsid in 
   news.newsid%type,
   in_title in news.title%type,
   in_imag in news.image%type)
   as
   begin
       insert into gf.news values(in_newsid,in_title,in_image);
   end;

下面是將資料存入資料庫的代碼：

        Dim cn As New OracleConnection("data source=site;uid=gf;pwd=macro;")
        cn.Open()

        Dim cmd As New OracleCommand("news_add", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New OracleParameter("in_newsid", OracleType.Number, 10))
        cmd.Parameters("in_newsid").Value = txtNewsID.Text.Trim
        cmd.Parameters.Add(New OracleParameter("in_title", OracleType.VarChar, 100))
        cmd.Parameters("in_title").Value = txtTitle.Text.Trim

        Dim fs As Stream = File1.PostedFile.InputStream
        Dim mydata(fs.Length) As Byte '定義一個位元組數組用於讀取圖片流
        fs.Read(mydata, 0, fs.Length)
        fs.Close()

        cmd.Parameters.Add(New OracleParameter("in_image", OracleType.Blob))
        cmd.Parameters("in_image").Value = mydata

        cmd.ExecuteNonQuery()

        cn.Close()

二、從Oracle資料庫中讀出圖片

方法1：從資料庫中讀取資料並以檔案形式保留在伺服器上

        Dim conn As New OracleClient.OracleConnection
        Dim cmd As New OracleClient.OracleCommand
        Dim myReader As OracleClient.OracleDataReader
        Dim sql As String
        Dim fl As File

        sql = "select * from news where newsid=3211"  '從資料庫中取出圖片資料 blob
        conn.ConnectionString = "Password=macro;User ID=gf;Data Source=site"
        cmd.CommandText = sql
        cmd.Connection = conn

        conn.Open()
        myReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
        myReader.Read()

        Label1.Text = myReader("title")

        Dim fs As FileStream                 ' Writes the BLOB to a file (*.bmp).
        Dim bw As BinaryWriter               ' Streams the binary data to the FileStream object.
        Dim bufferSize As Integer = 1000      ' The size of the BLOB buffer.
        Dim outbyte(bufferSize - 1) As Byte  ' The BLOB byte() buffer to be filled by GetBytes.
        Dim retval As Long                   ' The bytes returned from GetBytes.
        Dim startIndex As Long = 0           ' The starting position in the BLOB output.      
        Dim fpath As String

        '將圖片資料存成本地檔案
        fpath = Server.MapPath(Request.ApplicationPath) & "/Image/photo.bmp"
        fs = New FileStream(fpath, FileMode.OpenOrCreate, FileAccess.Write)
        bw = New BinaryWriter(fs)

        ' Reset the starting byte for a new BLOB.
        startIndex = 0

        ' Read bytes into outbyte() and retain the number of bytes returned.
        retval = myReader.GetBytes(2, startIndex, outbyte, 0, bufferSize) 'GetBytes返回欄位中的可用位元組數,第一個參數為從0開始的列序號

        ' Continue reading and writing while there are bytes beyond the size of the buffer.
        Do While retval = bufferSize
            bw.Write(outbyte)
            bw.Flush() '清理當前編寫器的所有緩衝區，使所有緩衝資料寫入基礎裝置。

            ' Reposition the start index to the end of the last buffer and fill the buffer.
            startIndex = startIndex + bufferSize
            retval = myReader.GetBytes(2, startIndex, outbyte, 0, bufferSize)
        Loop

        ' Write the remaining buffer.
        bw.Write(outbyte)
        bw.Flush()

        ' Close the output file.
        bw.Close()
        fs.Close()

        ' Close the reader and the connection.
        myReader.Close()
        conn.Close()

        Dim logo As Image               '檔案格式轉換
        logo = Image.FromFile(fpath)
        fpath = Server.MapPath(Request.ApplicationPath) & "/Image/zkjhy.jpeg"
        logo.Save(fpath, System.Drawing.Imaging.ImageFormat.Jpeg)

        'Me.Image1.Width = New Unit(300) '調整顯示大小
        'Me.Image1.Height = New Unit(350)
        Me.Image1.ImageUrl = "image/zkjhy.jpeg"