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:\01.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();
