odp.net 读写oracle blob字段

DEVELOPER: ODP.NET Serving Winning LOBs：

http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65odpnet-085139.html

Data Provider for .NET Developer's Guide：

https://docs.oracle.com/database/121/ODPNT/OracleBlobClass.htm#ODPNT4035

从blob字段读取一个图片文件的代码：

 1        OracleConnection con = new OracleConnection(connStr);
 2         con.Open();
 3 
 4         // statement to get a blob
 5         string sql = "select yz from sysusers where yhdh='123'";
 6 
 7         // create command object
 8         // InitialLOBFetchSize
 9         //  defaults to 0
10         OracleCommand cmd = new OracleCommand(sql, con);
11 
12         // create a datareader
13         using (OracleDataReader dr = cmd.ExecuteReader())
14         {
15             // read the single row result
16             dr.Read();
17             // use typed accessor to retrieve the blob
18             OracleBlob blob = dr.GetOracleBlob(0);
19 
20             // create a memory stream from the blob
21             using (MemoryStream ms = new MemoryStream(blob.Value))
22             {
23                 // set the image property equal to a bitmap
24                 // created from the memory stream
25                 pictureBox1.Image = new Bitmap(ms);
26             }
27         }

保存文件到blob字段的代码：

 1         con.Open();
 2 
 3         // 利用事务处理（必须）
 4         OracleTransaction transaction = con.BeginTransaction();
 5         string sql="select yz from sysusers where yhdh='123' FOR UPDATE";
 6         OracleCommand cmd = new OracleCommand(sql, con);
 7         
 8         using ( OracleDataReader reader = cmd.ExecuteReader())
 9         {
10             //Obtain the first row of data.
11             reader.Read();
12             //Obtain a LOB.
13             OracleBlob blob = reader.GetOracleBlob(0);
14             blob.Erase();
15             // 将文件写入 BLOB 中
16             byte[] Buffer;
17             FileStream fs = new FileStream(@"d:1.jpg", FileMode.Open);
18             using (MemoryStream ms = new MemoryStream())
19             {
20                 int b;
21                 while ((b = fs.ReadByte()) != -1)
22                 {
23                     ms.WriteByte((byte)b);
24                 }
25                 Buffer = ms.ToArray();
26             }
27 
28             // Begin ChunkWrite to improve performance
29             // Index updates occur only once after EndChunkWrite
30             blob.Position=0;
31             blob.BeginChunkWrite();  //启用BeginChunkWrite不是必须，只与性能有关
32             blob.Write(Buffer, 0,Buffer .Length);  //从字节数据写入blob字段
33             blob.EndChunkWrite();
34             blob.Close();
35         }
36         // 提交事务
37         transaction.Commit();
38         con.Close();