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();
                }

            }

        }

    }

}

posted on 2005-08-10 22:57  [Fenghua]  阅读(11291)  评论(3编辑  收藏  举报

导航