using System;
using Fjeptri.DataAccess.DataHelper;
using System.Data.OracleClient;
using System.Data;
using System.IO;
using System.Windows.Forms;
using blank;
namespace WindowsApplication1
{
/// <summary>
/// Blob 的摘要說明。
/// </summary>
public class Blob
{
public Blob()
{
//
// TODO: 在此處添加構造函數邏輯
//
}
public void InsertBlob() //插入Blob內容
{
//獲取提交資料庫的檔內容。
OracleLob blob = this.GetTempBLobs();
//檔內容是否為空
if(blob.IsNull)
{return;}
//向資料庫提交內容。
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
OracleTransaction transaction =conn.BeginTransaction();
string Create="insert into tablewithlobs(a,b) values(tablewithlobs_seq.nextval,:Blob)";
OracleParameter[] param=new OracleParameter[1];
param[0]=new OracleParameter("Blob",OracleType.Blob);
param[0].Value = blob;
OracleHelper.ExecuteNonQuery(transaction,CommandType.Text,Create,param);
transaction.Commit();
conn.Close();
}
private OracleLob GetTempBLobs() //獲取提交資料庫的檔內容
{
//輸入檔內容
Stream stream = Stream.Null;
OpenFileDialog openFileDialog = new OpenFileDialog();
string fileName = openFileDialog.FileName;
openFileDialog.CheckFileExists = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
stream = openFileDialog.OpenFile();
}
else
{
return OracleLob.Null;
}
stream.Position =0 ;
byte[] byte_buffer = new byte[stream.Length];
for(int i=0;i<stream.Length;i++)
{
byte_buffer[i]=(byte)stream.ReadByte();
}
//創建臨時Blob變數
OracleType lobtype=OracleType.Blob;
string CreateTempBlob= "DECLARE A " + lobtype + "; "+
"BEGIN "+
"DBMS_LOB.CREATETEMPORARY(A, FALSE); "+
":LOC := A; "+
"END;";
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
OracleTransaction transaction = conn.BeginTransaction();
OracleParameter[] param=new OracleParameter[1];
param[0]=new OracleParameter("LOC",OracleType.Blob);
OracleHelper.CacheParameter(CreateTempBlob,param);
param[0].Direction = ParameterDirection.Output;
OracleHelper.ExecuteNonQuery(transaction,CommandType.Text,CreateTempBlob,param);
OracleLob tempLob = (OracleLob)param[0].Value;
//將檔內容傳入Blob變數
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(byte_buffer,0,byte_buffer.Length);
tempLob.Position = 0;
conn.Close();
return tempLob;
}
public void ReadBLob(decimal A) //讀出Blob內容
{
//從資料庫讀出內容
string Read = "select b from tablewithlobs where a =:a";
OracleParameter[] param=OracleHelper.GetCacheParameter(Read);
if(param==null)
{
param=new OracleParameter[1];
param[0]=new OracleParameter("a",OracleType.Number);
OracleHelper.CacheParameter(Read,param);
}
param[0].Value = A;
OracleDataReader dr = OracleHelper.ExecuteReader(ConfigInfo.GetConnectionString(),CommandType.Text,Read,param);
OracleLob blob = OracleLob.Null;
while(dr.Read())
{
blob = dr.GetOracleLob(0);
}
//打開保存內容的檔
string filename =string.Empty;
byte[] buffer=null;
SaveFileDialog saveFileDialog = new SaveFileDialog();
if(saveFileDialog.ShowDialog() == DialogResult.OK)
{
filename = saveFileDialog.FileName;
}
if(filename == string.Empty)
{
MessageBox.Show("請確定保存的檔路徑!");
return;
}
//將Blob類型的內容轉化成檔流類型
buffer = new byte[blob.Length];
blob.Read(buffer,0,buffer.Length);
//將內容寫入檔
FileStream fs= new FileStream(filename,FileMode.OpenOrCreate);
fs.Write(buffer,0,buffer.Length);
fs.Close();
}
public void UpdateBlob(decimal A) //更新Blob內容
{
//提交更新的檔內容
Stream stream = Stream.Null;
OpenFileDialog openFileDialog = new OpenFileDialog();
string fileName = openFileDialog.FileName;
openFileDialog.CheckFileExists = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
stream = openFileDialog.OpenFile();
}
else
{
return;
}
//獲取要更新的資料庫內容
string Update = "select b from tablewithlobs where a =:a for update";
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
conn.Open();
OracleTransaction transaction = conn.BeginTransaction();
OracleParameter[] param=OracleHelper.GetCacheParameter(Update);
if(param==null)
{
param=new OracleParameter[1];
param[0]=new OracleParameter("a",OracleType.Number);
OracleHelper.CacheParameter(Update,param);
}
param[0].Value = A;
OracleDataReader dr = OracleHelper.ExecuteReader(transaction,CommandType.Text,Update,param);
OracleLob blob = OracleLob.Null;
while(dr.Read())
{
blob = dr.GetOracleLob(0);
}
//向資料庫寫入更新內容
stream.Position =0 ;
byte[] byte_buffer = new byte[stream.Length];
for(int i=0;i<stream.Length;i++)
{
byte_buffer[i]=(byte)stream.ReadByte();
}
blob.Write(byte_buffer,0,byte_buffer.Length);
transaction.Commit();
conn.Close();
stream.Close();
}
public void DeleteBlob(decimal A) //刪除Blob內容
{
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
conn.Open();
OracleTransaction transaction = conn.BeginTransaction();
string Delete="delete tablewithlobs where a=:a";
OracleParameter[] param=OracleHelper.GetCacheParameter(Delete);
if(param==null)
{
param=new OracleParameter[1];
param[0]=new OracleParameter("a",OracleType.Number);
OracleHelper.CacheParameter(Delete,param);
}
param[0].Value=A;
OracleHelper.ExecuteNonQuery(transaction,CommandType.Text,Delete,param);
transaction.Commit();
conn.Close();
MessageBox.Show("刪除成功!");
}
}
}
http://blog.sina.com.cn/s/blog_4b9d73ad010008f2.html
http://topic.csdn.net/t/20041228/13/3682257.html
對oracle大物件(Blob,Clob)操作(增,刪,改)的C#代碼(一)
blob的操作
using System;
using Fjeptri.DataAccess.DataHelper;
using System.Data.OracleClient;
using System.Data;
using System.IO;
using System.Windows.Forms;
using blank;
namespace WindowsApplication1
{
/// <summary>
/// Blob 的摘要說明。
/// </summary>
public class Blob
{
public Blob()
{
//
// TODO: 在此處添加構造函數邏輯
//
}
public void InsertBlob() //插入Blob內容
{
//獲取提交資料庫的檔內容。
OracleLob blob = this.GetTempBLobs();
//檔內容是否為空
if(blob.IsNull)
{return;}
//向資料庫提交內容。
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
OracleTransaction transaction =conn.BeginTransaction();
string Create="insert into tablewithlobs(a,b) values(tablewithlobs_seq.nextval,:Blob)";
OracleParameter[] param=new OracleParameter[1];
param[0]=new OracleParameter("Blob",OracleType.Blob);
param[0].Value = blob;
OracleHelper.ExecuteNonQuery(transaction,CommandType.Text,Create,param);
transaction.Commit();
conn.Close();
}
private OracleLob GetTempBLobs() //獲取提交資料庫的檔內容
{
//輸入檔內容
Stream stream = Stream.Null;
OpenFileDialog openFileDialog = new OpenFileDialog();
string fileName = openFileDialog.FileName;
openFileDialog.CheckFileExists = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
stream = openFileDialog.OpenFile();
}
else
{
return OracleLob.Null;
}
stream.Position =0 ;
byte[] byte_buffer = new byte[stream.Length];
for(int i=0;i<stream.Length;i++)
{
byte_buffer[i]=(byte)stream.ReadByte();
}
//創建臨時Blob變數
OracleType lobtype=OracleType.Blob;
string CreateTempBlob= "DECLARE A " + lobtype + "; "+
"BEGIN "+
"DBMS_LOB.CREATETEMPORARY(A, FALSE); "+
":LOC := A; "+
"END;";
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
OracleTransaction transaction = conn.BeginTransaction();
OracleParameter[] param=new OracleParameter[1];
param[0]=new OracleParameter("LOC",OracleType.Blob);
OracleHelper.CacheParameter(CreateTempBlob,param);
param[0].Direction = ParameterDirection.Output;
OracleHelper.ExecuteNonQuery(transaction,CommandType.Text,CreateTempBlob,param);
OracleLob tempLob = (OracleLob)param[0].Value;
//將檔內容傳入Blob變數
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(byte_buffer,0,byte_buffer.Length);
tempLob.Position = 0;
conn.Close();
return tempLob;
}
public void ReadBLob(decimal A) //讀出Blob內容
{
//從資料庫讀出內容
string Read = "select b from tablewithlobs where a =:a";
OracleParameter[] param=OracleHelper.GetCacheParameter(Read);
if(param==null)
{
param=new OracleParameter[1];
param[0]=new OracleParameter("a",OracleType.Number);
OracleHelper.CacheParameter(Read,param);
}
param[0].Value = A;
OracleDataReader dr = OracleHelper.ExecuteReader(ConfigInfo.GetConnectionString(),CommandType.Text,Read,param);
OracleLob blob = OracleLob.Null;
while(dr.Read())
{
blob = dr.GetOracleLob(0);
}
//打開保存內容的檔
string filename =string.Empty;
byte[] buffer=null;
SaveFileDialog saveFileDialog = new SaveFileDialog();
if(saveFileDialog.ShowDialog() == DialogResult.OK)
{
filename = saveFileDialog.FileName;
}
if(filename == string.Empty)
{
MessageBox.Show("請確定保存的檔路徑!");
return;
}
//將Blob類型的內容轉化成檔流類型
buffer = new byte[blob.Length];
blob.Read(buffer,0,buffer.Length);
//將內容寫入檔
FileStream fs= new FileStream(filename,FileMode.OpenOrCreate);
fs.Write(buffer,0,buffer.Length);
fs.Close();
}
public void UpdateBlob(decimal A) //更新Blob內容
{
//提交更新的檔內容
Stream stream = Stream.Null;
OpenFileDialog openFileDialog = new OpenFileDialog();
string fileName = openFileDialog.FileName;
openFileDialog.CheckFileExists = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
stream = openFileDialog.OpenFile();
}
else
{
return;
}
//獲取要更新的資料庫內容
string Update = "select b from tablewithlobs where a =:a for update";
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
conn.Open();
OracleTransaction transaction = conn.BeginTransaction();
OracleParameter[] param=OracleHelper.GetCacheParameter(Update);
if(param==null)
{
param=new OracleParameter[1];
param[0]=new OracleParameter("a",OracleType.Number);
OracleHelper.CacheParameter(Update,param);
}
param[0].Value = A;
OracleDataReader dr = OracleHelper.ExecuteReader(transaction,CommandType.Text,Update,param);
OracleLob blob = OracleLob.Null;
while(dr.Read())
{
blob = dr.GetOracleLob(0);
}
//向資料庫寫入更新內容
stream.Position =0 ;
byte[] byte_buffer = new byte[stream.Length];
for(int i=0;i<stream.Length;i++)
{
byte_buffer[i]=(byte)stream.ReadByte();
}
blob.Write(byte_buffer,0,byte_buffer.Length);
transaction.Commit();
conn.Close();
stream.Close();
}
public void DeleteBlob(decimal A) //刪除Blob內容
{
OracleConnection conn = new OracleConnection(ConfigInfo.GetConnectionString());
conn.Open();
OracleTransaction transaction = conn.BeginTransaction();
string Delete="delete tablewithlobs where a=:a";
OracleParameter[] param=OracleHelper.GetCacheParameter(Delete);
if(param==null)
{
param=new OracleParameter[1];
param[0]=new OracleParameter("a",OracleType.Number);
OracleHelper.CacheParameter(Delete,param);
}
param[0].Value=A;
OracleHelper.ExecuteNonQuery(transaction,CommandType.Text,Delete,param);
transaction.Commit();
conn.Close();
MessageBox.Show("刪除成功!");
}
}
}