[ Team LiB ]Recipe 9.12 Reading and Writing Binary Data with Oracle
Problem
You need to read and write binary data from and to an Oracle database.
Solution
Use the techniques shown in the following example.
The sample code contains two event handlers:
Read Button.Click
Clears the controls on the form and builds a SQL statement to get the record for
the specified ID from the Oracle table TBL0912. A connection is created and a
command is built using the SQL statement and executed to build a DataReader.
The BLOB is retrieved from the DataReader and displayed in the PictureBox on
the form. The CLOB and NCLOB values are retrieved from the DataReader and
displayed in text boxes on the form.
Write Button.Click
Gets the ID from the TextBox on the form. A BLOB is retrieved from a user-
specified file and loaded into a Byte array. An Oracle DataAdapter is created and a
new table is created using the FillSchema( ) command. A CommandBuilder is
created from the DataAdapter. A new row is created where the BLOB value is set
from the file specified by the user and the CLOB, and NCLOB values are set from
the text boxes on the form. The new row is added to the table and the data updated
back to the source.
The C# code is shown in Example 9-15
.
Example 9-15. File: ReadWriteBinaryDataFromOracleForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Drawing;

ConfigurationSettings.AppSettings["Oracle_ConnectString"]);
OracleCommand cmd = new OracleCommand(sqlText, conn);
conn.Open( );

// Create the DataReader.
OracleDataReader dr = cmd.ExecuteReader( );
// Iterate over the collection of rows in the DataReader.
if(dr.Read( ))
{
// Retrieve the BLOB into a stream.
Byte[] blob = null;
if(!dr.IsDBNull(1))
blob = (Byte[])dr.GetOracleLob(1).Value;
MemoryStream ms = new MemoryStream(blob);
// Display the BLOB in the PictureBox.
blobPictureBox.Image = Image.FromStream(ms);
ms.Close( );

// Get the CLOB.
if(!dr.IsDBNull(2))
clobTextBox.Text = dr.GetOracleLob(2).Value.ToString( );

// Get the NCLOB.
if(!dr.IsDBNull(3))
nclobTextBox.Text = dr.GetOracleLob(3).Value.ToString( );
}
else
{
MessageBox.Show("No record found.", "Access Oracle LOB Data",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);


// Create a DataAdapter and table.
OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM " +
TABLENAME,
ConfigurationSettings.AppSettings["Oracle_ConnectString"]);
DataTable table = new DataTable( );
// Just get the schema.
da.FillSchema(table, SchemaType.Source);
OracleCommandBuilder cb = new OracleCommandBuilder(da);

// Create a row containing the new BLOB, CLOB, and NCLOB data.
DataRow row = table.NewRow( );
row[ID_FIELD] = id;
row[BLOBFIELD_FIELD] = blob;
if(clobTextBox.TextLength > 0)
row[CLOBFIELD_FIELD] = clobTextBox.Text;
if(nclobTextBox.TextLength > 0)
row[NCLOBFIELD_FIELD] = nclobTextBox.Text;
// Add the row to the table.
table.Rows.Add(row);

// Update the Oracle database using the DataAdapter.
try
{
da.Update(table);
}
catch(System.Exception ex)
{
MessageBox.Show(ex.Message, "Access Oracle LOB Data",
MessageBoxButtons.OK,

See Recipe 9.12
for a general discussion about reading and writing BLOB data from and
to a data source.
[ Team LiB ]