C# 读写 Oracle BLOB 数据

 

using System;
using System.Data.OracleClient;
using System.IO;

namespace fenghua.Data.Oracle
{
    /**//// <summary>
    /// Class1 的摘要说明。
    /// </summary>
    public class OracleLobData
    {
        private OracleConnection conn;
        public OracleLobData()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /**//*
         * 在调用此函数之前需要写插入一个字符串到 BLOB 中比如：
         * Select some data.
         * Table Schema:
         *        "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
         *        "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
         * 否则程序会在 OracleLob tempLob    = reader.GetOracleLob(0) 处出错。
         */
        /**//// <summary>
        /// 文件写入到 Oracle Blob 字段中。
        /// </summary>
        /// <param name="idData">id 值</param>
        /// <param name="fileName">文件名</param>
        /// <param name="id">id 键</param>
        /// <param name="blob">blob 键</param>
        /// <param name="tableName">表名</param>
        public void WriteBlob(int idData, string fileName, string id, string blob, string tableName)
        {
            string connString = "server=oratest;User ID=kttest;Password=test";
            using(conn = new OracleConnection(connString))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();

                    // 利用事务处理（必须）
                    OracleTransaction transaction = cmd.Connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    // 获得 OracleLob 指针
                    cmd.CommandText = "select " + blob + " from " + tableName + " where " + id + " = " + idData + " FOR UPDATE";
                    OracleDataReader reader = cmd.ExecuteReader();
                    using(reader)
                    {
                        //Obtain the first row of data.
                        reader.Read();
                        //Obtain a LOB.
                        OracleLob tempLob    = reader.GetOracleLob(0);

                        // 将文件写入 BLOB 中
                        FileStream fs = new FileStream(fileName,FileMode.Open);
                        tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
                        int length = 10485760;
                        byte[] Buffer = new byte[length];
                        int i;
                        while((i = fs.Read(Buffer,0,length)) > 0)
                        {
                            tempLob.Write(Buffer,0,i);
                        }
                        fs.Close();
                        tempLob.EndBatch();
                        cmd.Parameters.Clear();
                    }
                    // 提交事务
                    transaction.Commit();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /**//// <summary>
        /// 读取 Oracle Blob 到文件中。
        /// </summary>
        /// <param name="idData">id 值</param>
        /// <param name="fileName">文件名</param>
        /// <param name="id">id 键</param>
        /// <param name="blob">blob 键</param>
        /// <param name="tableName">表名</param>
        public void ReadBlob(int idData,string fileName, string id, string blob, string tableName)
        {
            string connString = "server=oratest;User ID=kttest;Password=test";
            using(conn = new OracleConnection(connString))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();

                    // 利用事务处理（必须）
                    OracleTransaction transaction = cmd.Connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    // 获得 OracleLob 指针
                    string sql = "select " + blob + " from " + tableName + " where " + id + " = " + idData;
                    cmd.CommandText = sql;
                    OracleDataReader dr = cmd.ExecuteReader();
                    dr.Read();
                    OracleLob tempLob = dr.GetOracleLob(0);
                    dr.Close();

                    // 读取 BLOB 中数据，写入到文件中
                    FileStream fs = new FileStream(fileName,FileMode.Create);
                    int length = 1048576;
                    byte[] Buffer = new byte[length];
                    int i;
                    while((i = tempLob.Read(Buffer,0,length)) > 0)
                    {
                        fs.Write(Buffer,0,i);
                    }
                    fs.Close();
                    tempLob.Clone();
                    cmd.Parameters.Clear();

                    // 提交事务
                    transaction.Commit();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
}