2014년 9월 30일 화요일

[예제]Oracle BLOB 예제(이미지 입출력)[닷넷C#교육/ADO.NET강좌/ASP.NET교육잘하는곳/C#,ASP.NET교육추천/닷넷실무교육/.NET,C#/ADO.NET교육/닷넷학원/ASP.NET실무교육]

ODP .Net을 이용하여 오라클 DB의 Table에 이미지를 BLOB로 저장 하고 물러오는 예제 입니다. 저도 외국 사이트에서 다운 받아서 테스트해 본겁니다. 아래 프로그램을 실행 하기 위해서는 Oracle Data Provider for .Net (ODP.Net)이 설치 되어 있어야 합니다. 아래 게시물을 확인하여 설치 바랍니다. 

실행 화면은 아래와 같습니다. 
 

----------------------- 
ManipulateOraBlobs.cs 
----------------------- 



// 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 List 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 = @"..\..\..\docs\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' 
// MessageBox.Show(empAdapter.UpdateCommand.CommandText); 
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()); 
} 
} 

 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… 오라클자바…12-272033
 [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취…오라클자바…12-111482
53 [평일주간]100%환급6건,안드로이드,자바,C#,스프링3.2,SQL,힌트/… 오라클자바…03-151244
52 [기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍 오라클자바…01-311399
51 [평일,기업100%환급]SQL기초에서 Schema Object까지 오라클자바…01-311260
50 [평일야간]HTML5, CSS3,Ajax, jQuery마스터과정 오라클자바…01-311086
49 [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정 오라클자바…01-191402
48 [평일주간,평일야간,주말]안드로이드개발자과정 오라클자바…01-111231
47 [평일야간,주말주간,주말야간]JAVA,Network&JSP&Spring,MyBatis,… 오라클자바…01-031731
46 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… 오라클자바…12-272033
45 [기업100%환급,평일주간]자바기초에서 JDBC, Servlet/JSP까지 오라클자바…12-191496
44 [평일야간, 주말]웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX,Jav… 오라클자바…12-141477
43 [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… 오라클자바…12-111482
42 [평일,기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 오라클자바…12-091185
41 [평일야간, 주말]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 오라클자바…12-011402
40 [기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍(평일주간(단기)… 오라클자바…12-011547
39 [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) 오라클자바…12-011055
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)