存入图片至ORACLE及从ORACLE读取图片方法整理

dikrhyskvm

　　一、将图片存入Oracle数据库
　　示例表NEWS的结构为：newsid number(10),titlevarchar2(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"