从oracle中读取图片,存入图片至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"