/********************************************************************************** * 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(); } ..... ..... } }
위 원본 페이지 : http://www.akadia.com/services/dotnet_orablobs.html |