class1.cs

csharp 读取oracle 数据库表中blob字段的例子。

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

namespace ReadBlob
{
	///
	/// Class1 的摘要说明。
	/// 
	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) 处出错。
		 */
		///
		/// 文件写入到 Oracle Blob 字段中。
		/// 
		///id 值
		///文件名
		///id 键
		///blob 键
		///表名
		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();
				}
			}
		}

		///
		/// 读取 Oracle Blob 到文件中。
		/// 
		///id 值
		///文件名
		///id 键
		///blob 键
		///表名
		public void ReadBlob(int idData,string fileName, string id, string blob, string tableName)
		{
			string connString = "server=ktjgis;User ID=mis_sys;Password=mis_sys";
			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;
					string sql = "select meta from gis_fun.newmetainfo where no = '301' and scaleid = 111";
					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();
				}
			}
		}
	}
}