Считывание/запись BLOB-ов из/в Oracle с помощью C # .NET DataSet и DataReader
/**********************************************************************************
* 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();
}
...
}
}
