How to save images to Oracle and read images from Oracle

1. Save images to the Oracle database

The structure of the example Table news is: newsid number (10), title varchar2 (100), image (BLOB)

Method 1: Use the oraclecommandbuilder class (this class is used to automatically generate single table commands used to coordinate dataset changes and associated databases .)

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 is the file selection box, used as a server control
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 ()

Method 2: exploitation process

The pre-defined Oracle process is:

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;

The following code stores data into the database:

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 'defines a byte array used to read image streams.
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 ()

2. Read images from the Oracle database

Method 1: read data from the database and keep it on the server as a file

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" 'extracts image data blob from the database
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

'Save image data as a local file
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: the number of available bytes in the returned field. The first parameter is the column number starting from 0.

'Continue reading and writing while there are bytes beyond the size of the buffer.
Do While retval = buffersize
Bw. Write (outbyte)
Bw. Flush () 'clears all the buffers of the current writer so that all the buffered data is written to the basic device.

'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' File Format Conversion
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) 'adjust the display size
'Me. image1.height = new unit (350)
Me. image1.imageurl = "image/zkjhy.jpeg"