C# Working with Oracle BLOB
Author: Willie
Date: 2012/09/05
目的：利用 C# 寫入 Oracle DB 儲存圖片，來測試SmartQuery做Web的顯示
使用的額外 Component 是 Devert ，當然也可以安裝ODAC 來做開發，本次使用的是WebForm的程式故開發，再搭配SmartQuery的web做顯示。這個文件裡重點是要如何利用 Oracle DB Connection Object Insert BLOB資料，詳情請見如下程式範例。
Visual Studio : 2010
.Net Framework: 3.5
SmartQuery Ver.: 2011.12.31 版
 |
| DotNet - WinForm 畫面 |
using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//Oracle
using Devart.Data.Oracle;
namespace BLOB
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// Click File(s) Button
/// </summary>
private void btFileSelect_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
txtFilePath.Text = openFileDialog1.FileName;
}
}
/// <summary>
/// Click Submit
/// </summary>
private void btSubmit_Click(object sender, EventArgs e)
{
string strConn = "User Id=[Id];Password=[pwd];Server=[server];Direct=True;Sid=[server sid]";
string strSQL = "INSERT INTO REPORT_TEST (EMPNO,IMAGE) " +
"VALUES('" + txtEMPNO.Text + "',:blob2db ) ";
string strFilePath = @txtFilePath.Text;
if (strFilePath != "")
{
FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.Read);
byte[] ImageData = new byte[fs.Length];
fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
OracleConnection con = new OracleConnection(strConn);
OracleCommand cmd = new OracleCommand(strSQL, con);
cmd.CommandType = CommandType.Text;
// Bind the parameter as OracleDbType.Blob to command for inserting image
OracleParameter p = cmd.Parameters.Add("blob2db", OracleDbType.Blob);
p.Direction = ParameterDirection.Input;
// Assign Byte Array to Oracle Parameter
p.Value = ImageData;
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Success");
}
catch (Exception ex){
MessageBox.Show("Error:" + ex.Message);
}
finally {
con.Close();
}
}
else {
MessageBox.Show("File is required!");
}
}
}
}
 |
| SmartQuery - 設計畫面 |
 |
| SmartQuery 的 Report 顯示 |
Reference
working with OLEDB
http://bytes.com/topic/oracle/answers/645917-woking-blobs-oledb
DotNet Insert BLOB into Oracle Database (ODAC)
http://stackoverflow.com/questions/4902250/insert-blob-in-oracle-database-with-c-sharp
