Overview
ODP.NET offers faster and reliable access to Oracle Database by using Oracle Native APIs. ODP.NET provides features to access any .NET application. The data access through ODP.NET can be done using a collection of classes contained in Oracle.DataAccess assembly.
DataSet is a major component of ADO.NET. It is an in-memory cache of the data retrieved from the database. OracleDataAdapter represents a set of data commands and a database connection that is used to fill the DataSet based on the query given.
OracleCommand represents SQL statements to execute against datasource. OracleConnection is used to build the database connection.
OracleDataReader is a read-only, forward-only recordset. ExecuteReader method of OracleCommand is used to create OracleDataReader. OracleBLOB is an OracleType specific to ODP.NET for accessing BLOB data from Oracle databases.
Example
The purpose of this sample application is to demonstrate:
- How to perform DML operations on DataSet for LOB (Large Objects) columns like images,
sound files etc. through ODP.NET using C#.
- How to fetch BLOB data using OracleBLOB (ODP.NET type) and OracleDataReader
through ODP.NET using C#The scenario for this sample application is to insert or update new photos for the employees in the "EMP" table. When this application is run, a drop down list populated with employee data from database is displayed. The user can select the employee for which he/she wishes to insert/update a photo and her/his job title. To insert/update the photo and job title the user can enter text for the job and select an image for the photo by clicking on 'Browse' button. To commit changes the user can click on 'Save' button.
![]()
/**********************************************************************************
* This sample application shows how to perform DML operations on a DataSet for LOB
* (Large Objects) columns like images, sound files etc. through ODP.NET using C#.
*
* The connection to database is made using Oracle Data Provider for .Net (ODP .Net).
* DataSet is an in-memory cache of data that contains data filled by an
* OracleDataAdapter.
* An OracleDataAdapter serves as a bridge between the DataSet and the data source.
* The connection to database is made using OracleConnection object.
*
* The scenario for this sample application is to insert or update new photos for
* the employees in the "EMP" table.
* When this application is run, a drop down list populated with employee data
* from database is displayed. The user can select the employee for which he/she
* wishes to insert/update a Photo and her/his Job Title.
*
* To insert/update the Photo and Job Title the user can enter text for
* Job and select image for the photo by clicking on 'Browse' button.
* To commit changes the user can click on 'Save' button.
**********************************************************************************/
// Standard Namespaces referenced in this sample application
using System;
using System.Drawing;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
// Namespace for ODP.Net classes
using Oracle.DataAccess.Client;
// Namespace for ODP.Net Types
using Oracle.DataAccess.Types;
namespace Akadia.OraBlobs
{
// ManipulateOraBlobs class inherits Window's Form
public class ManipulateOraBlobs : System.Windows.Forms.Form
{
private System.Windows.Forms.Button saveBtn;
private System.Windows.Forms.Button closeBtn;
private System.Windows.Forms.Button browseBtn;
private Container components = null;
// Variable for storing the image name, path chosen from file dialog
private String _strImageName = "";
// To store value of current Employee ID
private String _curEmpNo = "";
// To store existing Employee Job Title
private String _strExistText = "";
// To store existing Employee values
private int _empID = 0;
private int _imageLength;
private byte[] _imageData;
private System.Windows.Forms.Label lblJob;
private System.Windows.Forms.Label lblEmpName;
private System.Windows.Forms.ComboBox cboEmpName;
private System.Windows.Forms.TextBox txtEmpJob;
private System.Windows.Forms.PictureBox picEmpPhoto;
// For database connection
private OracleConnection _conn;
// Constructor
public ManipulateOraBlobs()
{
// Creates the UI required for this application
InitializeComponent();
}
// ***************************************************************
// Entry point to this sample application
// ***************************************************************
static void Main()
{
// Instantiating this class
ManipulateOraBlobs oraBlobs = new ManipulateOraBlobs();
// Get database connection
if (oraBlobs.getDBConnection())
{
// Populate Employee Names in the ComboBox
oraBlobs.populateComboBox();
// When this application is run, "ManipulateOraBlobs' form is run
Application.Run(oraBlobs);
}
}
// *******************************************************************
// Get the database connection using the parameters given.
// Note: Replace the datasource parameter with your datasource value
// in ConnectionParams.cs file.
// *******************************************************************
private Boolean getDBConnection()
{
try
{
// Connection Information
string connectionString =
// Username
"User Id=" + ConnectionParams.Username +
// Password
";Password=" + ConnectionParams.Password +
// Replace with your datasource value (TNSNames)
";Data Source=" + ConnectionParams.Datasource ;
// Connection to datasource, using connection parameters given above
_conn = new OracleConnection(connectionString);
// Open database connection
_conn.Open();
return true;
}
// Catch exception when error in connecting to database occurs
catch (Exception ex)
{
// Display error message
MessageBox.Show(ex.ToString());
return false;
}
}
// ***********************************************************************
// Populate Employee Names in the ComboBox with data from the "EMP"
// table. 'EmpName' is displayed in the List, whereas the actual value
// stored is 'EmpNo'.
// ***********************************************************************
void populateComboBox()
{
// To fill DataSet and update datasource
OracleDataAdapter empAdapter;
// In-memory cache of data
DataSet empDataSet;
// No selection
// The starting position of text selected in the text box.
txtEmpJob.SelectionStart = 0;
try
{
// Instantiate OracleDataAdapter to create DataSet
empAdapter = new OracleDataAdapter();
// Fetch Product Details
empAdapter.SelectCommand = new OracleCommand
("SELECT empno, ename FROM emp ORDER BY ename ASC",_conn);
// Instantiate a DataSet object
empDataSet = new DataSet("empDataSet");
// Fill the DataSet
empAdapter.Fill(empDataSet, "emp");
// Employee Name is shown in the list displayed
cboEmpName.DisplayMember = empDataSet.Tables["emp"].Columns["ename"].ToString();
// Employee Id is the actual value contained in the list
cboEmpName.ValueMember = empDataSet.Tables["emp"].Columns["empno"].ToString();
// Assign DataSet as a data source for the Combo Box
cboEmpName.DataSource = empDataSet.Tables["emp"].DefaultView;
}
catch(Exception ex)
{
// Display error message
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
}
// *******************************************************************
// This method is called on the click event of the 'Browse' button,
// The purpose of this method is to display a File-Dialog, from
// which the user can choose the desired photo for the employee.
// The chosen image gets displayed in the Picture Box.
// *******************************************************************
private void browseBtn_Click(object sender, System.EventArgs e)
{
try
{
// Instantiate File Dialog box
FileDialog fileDlg = new OpenFileDialog();
// Set the initial directory
fileDlg.InitialDirectory =
"E:\\MyDotNet\\MyWinFormsTutorial\\OraEmpWithBlob\\doc\\images" ;
// Filter image(.jpg, .bmp, .gif) files only
fileDlg.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif";
// Restores the current directory before closing
fileDlg.RestoreDirectory = true ;
// When file is selected from the File Dialog
if(fileDlg.ShowDialog() == DialogResult.OK)
{
// Store the name of selected file into a variable
_strImageName = fileDlg.FileName;
// Create a bitmap for selected image
Bitmap newImage= new Bitmap(_strImageName);
// Fit the image to the size of picture box
picEmpPhoto.SizeMode = PictureBoxSizeMode.StretchImage;
// Show the bitmap in picture box
picEmpPhoto.Image = (Image)newImage;
}
// No Image chosen
fileDlg = null;
}
catch(System.ArgumentException ex)
{
// Display error message, if image is invalid
_strImageName = "";
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
catch(Exception ex)
{
// Display error message
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
}
// *******************************************************************************
// This method is called on the click event of the 'Save' button,
// It calls "updateData" method for data updation of Job and Photos.
// *******************************************************************************
private void saveBtn_Click(object sender, System.EventArgs e)
{
this.updateData();
}
// *****************************************************************************
// This method is called from the click event of Save button and
// SelectedIndexChanged event of Products DropDown list.
//
// The purpose of this method is to demonstrate DML operations on a Data Set for
// LOB(Large Object)data. The functionalitity of this method is to insert
// a new employee photo or update an existing one.
//
// The flow of this method is as follows:
// 1. Instantiate an OracleDataAdapter object with the query for 'emp'
// table.
// 2. Configure the schema to match with Data Source. Set Primary Key information.
// 3. OracleCommandBuilder automatically generates the command for loading data
// for the given query.
// 4. The Dataset is filled with data that is loaded through OracleDataAdapter.
// 5. Create a DataRow in a DataTable contained in the DataSet for a new
// photo or find the current DataRow for the existing photo.
// 6. Convert new the photo image into a byte array.
// 7. Assign the corresponding values to the columns in the Data Row.
// 8. Add the Data Row to the Data Set for a new photo or end the edit
// operation for existing photo.
// 9. Update the database with the Data Set values. Hence adding/updating
// 'emp' table data.
// *************************************************************************
private void updateData()
{
try
{
// Check if Image or Text is changed.
if (_strImageName != "" || _strExistText != txtEmpJob.Text)
{
// Change the default cursor to 'WaitCursor'(an HourGlass)
this.Cursor = Cursors.WaitCursor;
// Change the default cursor to 'WaitCursor'(an HourGlass)
this.Cursor = Cursors.WaitCursor;
// To fill Dataset and update datasource
OracleDataAdapter empAdapter;
// In-memory cache of data
DataSet empDataSet;
// Data Row contained in Data Table
DataRow empRow;
// FileStream to get the Employee Photo
FileStream fs;
// Get Image Data from the Filesystem if User has loaded a Photo
// by the 'Browse' button
if (_strImageName != "")
{
fs = new FileStream(@_strImageName, FileMode.Open,FileAccess.Read);
_imageLength = (int)fs.Length;
// Create a byte array of file stream length
_imageData = new byte[fs.Length];
// Read block of bytes from stream into the byte array
fs.Read(_imageData,0,System.Convert.ToInt32(fs.Length));
// Close the File Stream
fs.Close();
}
// Instantiate an OracleDataAdapter object with the
// appropriate query
empAdapter = new OracleDataAdapter(
"SELECT empno, ename, job, photo" +
" FROM emp WHERE empno = " + _curEmpNo, _conn);
// Instantiate a DataSet object
empDataSet= new DataSet("emp");
// Create an UPDATE command as a template for the
// OracleDataAdapter.
empAdapter.UpdateCommand = new OracleCommand
("UPDATE emp SET " +
"job = :iJOB, "+
"photo = :iPHOTO " +
"WHERE empno = :iEMPNO", _conn);
// Add the Parameters for the UPDATE Command
empAdapter.UpdateCommand.Parameters.Add(":iJOB",
OracleDbType.Varchar2, 9, "job");
empAdapter.UpdateCommand.Parameters.Add(":iPHOTO",
OracleDbType.Blob, _imageLength, "photo");
empAdapter.UpdateCommand.Parameters.Add(":iEMPNO",
OracleDbType.Int16, 0, "empno");
// Configure the schema to match with the Data Source.
// AddWithKey sets the Primary Key information to complete the
// schema information
empAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Configures the schema to match with Data Source
empAdapter.FillSchema(empDataSet, SchemaType.Source, "emp");
// Fills the DataSet with 'EMP' table data
empAdapter.Fill(empDataSet,"emp");
// Get the current Employee ID row for updation
DataTable empTable = empDataSet.Tables["emp"];
empRow = empTable.Rows.Find(_curEmpNo);
// Start the edit operation on the current row in
// the 'emp' table within the dataset.
empRow.BeginEdit();
// Assign the value of the Job Title
empRow["job"] = txtEmpJob.Text;
// Assign the value of the Photo if not empty
if (_imageData.Length != 0)
{
empRow["photo"] = _imageData;
}
// End the editing current row operation
empRow.EndEdit();
// Update the database table 'EMP'
empAdapter.Update(empDataSet,"emp");
// Reset variables
_strImageName = "";
_strExistText = txtEmpJob.Text;
// Set the wait cursor to default cursor
this.Cursor = Cursors.Default;
// Display message on successful data updatation
MessageBox.Show("Data saved successfully");
}
else
{
MessageBox.Show("Select Photo or change Job Title for the Employee");
}
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
}
// ***********************************************************************
// This method is called when an Item is selected from 'cboEmpName'
// drop down list. The purpose of this method is to demonstrate how to
// fetch BLOB lob as an OracleLOB (ODP .Net Data Type) using an
// OracleDataReader.
// The flow of the method is as follows:
// 1. Clear the contents of Job-Title and Photo.
// 2. Populate OracleDataReader with data from 'EMP' table, through
// ExecuteReader method of OracleCommand object. The data is fetched
// based on the Emplyoyy selected from 'cboEmpName' list.
// 3. Assign value for Job-Title from the OracleDataReader.
// 4. The Image(BLOB) is read into a Byte array, then used to construct
// MemoryStream and passed to PictureBox.
// ***********************************************************************
private void cboEmpName_SelectedIndexChanged(object sender, System.EventArgs e)
{
// For fetching read only rows from datasource
OracleDataReader oraImgReader;
// For executing SQL statements against datasource
OracleCommand oraImgCmd;
// To store MessageBox result
DialogResult x;
// If Image orText is changed then promt user to save.
if (_strImageName != "" || _strExistText != txtEmpJob.Text)
{
// MessageBox prompting user whether he/she wishes to save changes made
x = MessageBox.Show("Do you want to save changes ?",
"Save Dialog",MessageBoxButtons.YesNo);
// If the user wishes to save changes
if (x == DialogResult.Yes)
{
// Call the method for insertion or updation
updateData();
// Reset variable
_empID = int.Parse(cboEmpName.GetItemText(cboEmpName.SelectedValue));
}
// If the user doesn't wish to save changes
else
{
// Reset variables
_strImageName ="";
_empID = int.Parse(cboEmpName.GetItemText(cboEmpName.SelectedValue));
}
}
try
{
// Initializing, clear contents
txtEmpJob.Text ="";
picEmpPhoto.Image = null;
_strImageName = "";
_curEmpNo ="";
_strExistText="";
// Fetch Product Details using OracleCommand
// for the selected Product from the Combobox
string strSelectedId = cboEmpName.GetItemText(cboEmpName.SelectedValue);
oraImgCmd = new OracleCommand(
"SELECT " +
"empno, " +
"job, " +
"photo " +
"FROM emp " +
"WHERE empno = " + strSelectedId ,_conn);
// Set OracleConnection for this instance of OracleCommand
oraImgCmd.Connection = _conn;
// Set Command type as text
oraImgCmd.CommandType = CommandType.Text;
// Sends the CommandText to the Connection
// and builds an OracleDataReader
oraImgReader = oraImgCmd.ExecuteReader();
// Read data
// Returns true if another row exists; otherwise, returns false.
Boolean recordExist = oraImgReader.Read();
// If data exists
if (recordExist)
{
// Store current Employee value
if (!oraImgReader.IsDBNull(0))
{
_curEmpNo = oraImgReader.GetInt32(0).ToString();
}
// Assign Job-Title to the Text Box
if (oraImgReader.GetValue(1).ToString() != "")
{
_strExistText = oraImgReader.GetString(1);
txtEmpJob.Text = _strExistText;
}
// If Photo exists in the Database, load it into the PictureBox
if (oraImgReader.GetValue(2).ToString() != "")
{
// Fetch the BLOB data through OracleDataReader using OracleBlob type
OracleBlob blob = oraImgReader.GetOracleBlob(2);
// Create a byte array of the size of the Blob obtained
Byte[] byteArr = new Byte[blob.Length];
// Read blob data into byte array
int i = blob.Read(byteArr,0,System.Convert.ToInt32(blob.Length));
// Get the primitive byte data into in-memory data stream
MemoryStream memStream = new MemoryStream(byteArr);
// Attach the in-memory data stream to the PictureBox
picEmpPhoto.Image = Image.FromStream(memStream);
// Fit the image to the PictureBox size
picEmpPhoto.SizeMode = PictureBoxSizeMode.StretchImage;
}
// close the OracleDataReader
oraImgReader.Close();
}
// Reset variable
_empID = int.Parse(cboEmpName.GetItemText(cboEmpName.SelectedValue));
}
// Catch exception when accessing arrary element out of bound
catch (System.IndexOutOfRangeException rangeException)
{
// Do nothing
rangeException.ToString();
}
catch (Exception ex)
{
// Display error message
System.Windows.Forms.MessageBox.Show( ex.ToString());
}
}
// **********************************************************************
// This method is called on the click event of the 'Close' button.
// The purpose of this method is to close the database connection,
// the form 'ManipulateOraBlobs' and then exit out of the application.
// **********************************************************************
private void closeBtn_Click(object sender, System.EventArgs e)
{
_conn.Close();
this.Close();
Application.Exit();
}
.....
.....
}
}