Четверг, 28.03.2024, 21:05

  • Полезные Скрипты Oracle
Главная Книги Быстрый Поиск О Сайте

Oracle
PL/SQL
Решения для Oracle
ПОИСК
Быстрый поиск
 Считывание / запись BLOB-ов

Считывание/запись 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();
        }
       ...
    }
}

 





Copyright sql.ucoz.com © 2024
Оцените сайт

Всего ответов: 51