使用 Oracle 托管驱动程序读取和写入LONG RAW

示例：使用 Oracle 托管驱动程序读取和写入长 RAW

这演示了如何使用 ODP.NET 托管驱动程序从/到表中写入（）长原始数据。insertselect

 

该示例由两个 C# 文件组成：和 。insert-from-file.csselect-to-file.cs

 

insert-from-file.cs读取二进制文件的内容并将其插入到表中。二进制文件的名称硬编码为Snake_River_（5mb）.jpg。我选择这个文件是因为我想要一个大于4兆字节的文件。

 

write-to-file.cs读取写入的内容并将其写入文件 。同样，这个名字是硬编码的。long rawextracted.bin

创建表

为了运行该示例，需要一个表。

 

 

create table table_with_long_raw (
    id   integer primary key,
    bin  long raw
);

Github 存储库 。NET-API， path： /Oracle/ManagedDataAccess/long-raw/create-table.sql

 

从文件插入.cs

 

//
// csc  -r:c:\oracle\18c\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll insert-from-file.cs
//

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.IO;

class Prg {

   private static void Main() {

      OracleConnection ora = new OracleConnection("user id=rene;password=rene;data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA18)))");
      ora.Open();

   //
   // Read file content into byte array:
   //
      FileStream fs = new FileStream("Snake_River_(5mb).jpg", FileMode.Open, FileAccess.Read);
      Console.WriteLine("File size: {0}", fs.Length);
      byte[] data = new byte[fs.Length];
      fs.Read(data,0,System.Convert.ToInt32(fs.Length));
      fs.Close();

   //
   // Bind parameters:
   //
      OracleCommand stmt = new OracleCommand();
      stmt.CommandText = "insert into table_with_long_raw values(:id, :bin)";
      stmt.Connection  =  ora;
      stmt.CommandType =  CommandType.Text;

      stmt.Parameters.Add("id" , OracleDbType.Int32  ).Value = 42;
      stmt.Parameters.Add("bin", OracleDbType.LongRaw).Value = data;

      stmt.ExecuteNonQuery();
   }
}

Github 存储库 。NET-API， path： /Oracle/ManagedDataAccess/long-raw/insert-from-file.cs

 

选择到文件.cs

 

//
// csc  -r:c:\oracle\18c\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll select-to-file.cs
//

using System;
using System.Data;
using System.IO;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

class Prg {

   static void Main() {

      OracleConnection ora = new OracleConnection("user id=rene;password=rene;data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA18)))");
      ora.Open();

      OracleCommand    stmt = new OracleCommand("select bin from table_with_long_raw where id = :id", ora);
      stmt.CommandType      = CommandType.Text;

      stmt.Parameters.Add("id", OracleDbType.Int32).Value = 42;

   //
   // Following line is kind of crucial: it specifies that
   // the entire length of the long raw needs to be read:
   //
      stmt.InitialLONGFetchSize  = -1;

      OracleDataReader rdr = stmt.ExecuteReader();

      rdr.Read();
      OracleBinary bin = rdr.GetOracleBinary(0);
      File.WriteAllBytes("extracted.bin", bin.Value);
   }
}

Github 存储库 。NET-API， path： /Oracle/ManagedDataAccess/long-raw/选择到文件.cs

 

 

 示例：使用 Oracle 托管驱动程序读取和写入长 RAW (renenyffenegger.ch)

 

注意，以下代码使用using System.Data.OracleClient;有效，而使用Oracle自己提供的Oracle.ManagedDataAccess居然无效。

以下是实测有效代码：

            using (var conn = new OracleConnection("Data Source=xxxx:1521/xxxx;User ID=xxx;Password=xxxx"/*_unitOfWork.Db.CurrentConnectionConfig.ConnectionString*/))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(sql, conn))
                {
                    cmd.Parameters.Add(":serialNumber", serialNumber);
                    OracleDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        string fileName = reader.GetString(reader.GetOrdinal("filename"));
                        var blob = reader.GetOracleBinary(reader.GetOrdinal("file"));

                        if (!blob.IsNull)
                        {
                            //File.WriteAllBytes(@"F:\MyProjects\xxx\xxx\bin\" + fileName, blob.Value);
                            return new Tuple<string, byte[]>(fileName, blob.Value);
                        }
                        return new Tuple<string, byte[]>(fileName, null);
                    }
                    return new Tuple<string, byte[]>("", null);
                }
            }