C#操作Oracle的blob

最新推荐文章于 2021-04-10 12:18:41 发布

weixin_30355437

最新推荐文章于 2021-04-10 12:18:41 发布

阅读量137

点赞数

文章标签： c#

原文链接：http://www.cnblogs.com/rayhuang/archive/2010/02/26/1674415.html

版权

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("刪除成功！");
   }
}
}