form1.cs

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

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Text.RegularExpressions; 
using System.Data.OracleClient;

namespace ReadBlob
{
	/// <summary>
	/// Form1 的摘要说明。
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Button button1;
		private System.Windows.Forms.Button button2;
		private System.Windows.Forms.Button button3;
		private System.Windows.Forms.ImageList imageList1;
		private System.Data.OracleClient.OracleConnection oracleConnection1;
		private System.Windows.Forms.Button button4;
		private System.Windows.Forms.PictureBox pictureBox1;
		private System.Windows.Forms.CheckBox checkBox1;
		private System.Windows.Forms.CheckBox checkBox2;
		private System.Windows.Forms.TreeView treeView1;
		private System.Windows.Forms.TreeView treeView2;
		private System.Windows.Forms.TreeView treeView3;
		private System.ComponentModel.IContainer components;

		public Form1()
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗体设计器生成的代码
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.components = new System.ComponentModel.Container();
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));
			this.button1 = new System.Windows.Forms.Button();
			this.button2 = new System.Windows.Forms.Button();
			this.button3 = new System.Windows.Forms.Button();
			this.imageList1 = new System.Windows.Forms.ImageList(this.components);
			this.oracleConnection1 = new System.Data.OracleClient.OracleConnection();
			this.button4 = new System.Windows.Forms.Button();
			this.pictureBox1 = new System.Windows.Forms.PictureBox();
			this.checkBox1 = new System.Windows.Forms.CheckBox();
			this.checkBox2 = new System.Windows.Forms.CheckBox();
			this.treeView1 = new System.Windows.Forms.TreeView();
			this.treeView2 = new System.Windows.Forms.TreeView();
			this.treeView3 = new System.Windows.Forms.TreeView();
			this.SuspendLayout();
			// 
			// button1
			// 
			this.button1.Image = ((System.Drawing.Image)(resources.GetObject("button1.Image")));
			this.button1.Location = new System.Drawing.Point(144, 24);
			this.button1.Name = "button1";
			this.button1.Size = new System.Drawing.Size(80, 32);
			this.button1.TabIndex = 0;
			this.button1.Text = "button1";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// button2
			// 
			this.button2.Location = new System.Drawing.Point(88, 96);
			this.button2.Name = "button2";
			this.button2.Size = new System.Drawing.Size(104, 24);
			this.button2.TabIndex = 1;
			this.button2.Text = "button2";
			this.button2.Click += new System.EventHandler(this.button2_Click);
			// 
			// button3
			// 
			this.button3.Location = new System.Drawing.Point(32, 32);
			this.button3.Name = "button3";
			this.button3.Size = new System.Drawing.Size(96, 32);
			this.button3.TabIndex = 2;
			this.button3.Text = "button3";
			this.button3.Click += new System.EventHandler(this.button3_Click);
			// 
			// imageList1
			// 
			this.imageList1.ImageSize = new System.Drawing.Size(16, 16);
			this.imageList1.TransparentColor = System.Drawing.Color.Transparent;
			// 
			// oracleConnection1
			// 
			this.oracleConnection1.ConnectionString = "user id=mis_sys;data source=ktjgis;password=mis_sys";
			this.oracleConnection1.InfoMessage += new System.Data.OracleClient.OracleInfoMessageEventHandler(this.oracleConnection1_InfoMessage);
			// 
			// button4
			// 
			this.button4.Location = new System.Drawing.Point(80, 208);
			this.button4.Name = "button4";
			this.button4.Size = new System.Drawing.Size(80, 40);
			this.button4.TabIndex = 3;
			this.button4.Text = "button4";
			this.button4.Click += new System.EventHandler(this.button4_Click);
			// 
			// pictureBox1
			// 
			this.pictureBox1.Location = new System.Drawing.Point(48, 152);
			this.pictureBox1.Name = "pictureBox1";
			this.pictureBox1.Size = new System.Drawing.Size(104, 24);
			this.pictureBox1.TabIndex = 4;
			this.pictureBox1.TabStop = false;
			// 
			// checkBox1
			// 
			this.checkBox1.Location = new System.Drawing.Point(304, 48);
			this.checkBox1.Name = "checkBox1";
			this.checkBox1.Size = new System.Drawing.Size(112, 24);
			this.checkBox1.TabIndex = 5;
			this.checkBox1.Text = "checkBox1";
			// 
			// checkBox2
			// 
			this.checkBox2.Location = new System.Drawing.Point(328, 40);
			this.checkBox2.Name = "checkBox2";
			this.checkBox2.Size = new System.Drawing.Size(128, 16);
			this.checkBox2.TabIndex = 6;
			this.checkBox2.Text = "checkBox2";
			// 
			// treeView1
			// 
			this.treeView1.BorderStyle = System.Windows.Forms.BorderStyle.None;
			this.treeView1.CheckBoxes = true;
			this.treeView1.ImageIndex = -1;
			this.treeView1.Location = new System.Drawing.Point(264, 152);
			this.treeView1.Name = "treeView1";
			this.treeView1.Nodes.AddRange(new System.Windows.Forms.TreeNode[] {
																				  new System.Windows.Forms.TreeNode("节点0"),
																				  new System.Windows.Forms.TreeNode("节点1"),
																				  new System.Windows.Forms.TreeNode("节点2"),
																				  new System.Windows.Forms.TreeNode("节点3"),
																				  new System.Windows.Forms.TreeNode("节点4")});
			this.treeView1.Scrollable = false;
			this.treeView1.SelectedImageIndex = -1;
			this.treeView1.ShowLines = false;
			this.treeView1.Size = new System.Drawing.Size(36, 96);
			this.treeView1.TabIndex = 8;
			// 
			// treeView2
			// 
			this.treeView2.BorderStyle = System.Windows.Forms.BorderStyle.None;
			this.treeView2.CheckBoxes = true;
			this.treeView2.ImageIndex = -1;
			this.treeView2.Location = new System.Drawing.Point(280, 152);
			this.treeView2.Name = "treeView2";
			this.treeView2.Nodes.AddRange(new System.Windows.Forms.TreeNode[] {
																				  new System.Windows.Forms.TreeNode("节点0"),
																				  new System.Windows.Forms.TreeNode("节点1"),
																				  new System.Windows.Forms.TreeNode("节点2"),
																				  new System.Windows.Forms.TreeNode("节点3"),
																				  new System.Windows.Forms.TreeNode("节点4")});
			this.treeView2.SelectedImageIndex = -1;
			this.treeView2.ShowLines = false;
			this.treeView2.Size = new System.Drawing.Size(128, 96);
			this.treeView2.TabIndex = 9;
			// 
			// treeView3
			// 
			this.treeView3.BorderStyle = System.Windows.Forms.BorderStyle.None;
			this.treeView3.CheckBoxes = true;
			this.treeView3.ImageIndex = -1;
			this.treeView3.Location = new System.Drawing.Point(248, 152);
			this.treeView3.Name = "treeView3";
			this.treeView3.Nodes.AddRange(new System.Windows.Forms.TreeNode[] {
																				  new System.Windows.Forms.TreeNode("节点0"),
																				  new System.Windows.Forms.TreeNode("节点1"),
																				  new System.Windows.Forms.TreeNode("节点2"),
																				  new System.Windows.Forms.TreeNode("节点3"),
																				  new System.Windows.Forms.TreeNode("节点4")});
			this.treeView3.Scrollable = false;
			this.treeView3.SelectedImageIndex = -1;
			this.treeView3.ShowLines = false;
			this.treeView3.Size = new System.Drawing.Size(36, 96);
			this.treeView3.TabIndex = 10;
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(472, 273);
			this.Controls.Add(this.treeView3);
			this.Controls.Add(this.treeView1);
			this.Controls.Add(this.treeView2);
			this.Controls.Add(this.checkBox2);
			this.Controls.Add(this.checkBox1);
			this.Controls.Add(this.pictureBox1);
			this.Controls.Add(this.button4);
			this.Controls.Add(this.button3);
			this.Controls.Add(this.button2);
			this.Controls.Add(this.button1);
			this.Name = "Form1";
			this.Text = "Form1";
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void button1_Click(object sender, System.EventArgs e)
		{
			string conn_str = "Provider=OraOLEDB.Oracle.1;Password=mis_sys;Persist Security Info=True;User ID=mis_sys;Data Source=ktjgis;Extended Properties=";//Provider必須用OraOLEDB.Oracle.1
   
			OleDbDataAdapter da = new OleDbDataAdapter();
			DataSet ds = new DataSet();
			OleDbConnection conn = new OleDbConnection(conn_str);
			OleDbCommand cmd = new OleDbCommand("select BUTTONICON  from mis_base.fcltsort where rid = 5", conn);
			da.SelectCommand = cmd;
			cmd.Connection.Open();
			da.Fill(ds);

			byte[] img = new byte[0];
			DataRow dr;
			dr = ds.Tables[0].Rows[0];
			//OleDbCommand cmd = new OleDbCommand("select BUTTONICON  from mis_base.fcltsort where rid = 5", conn);
			OracleConnection cconn   = new OracleConnection("server=ktjgis;User ID=mis_sys;Password=mis_sys");
			using(cconn)
			{
				cconn.Open();
				OracleCommand ccmd = cconn.CreateCommand();
				ccmd.CommandText = "select BUTTONICON  from mis_base.fcltsort where rid = 5";
				OracleDataReader ddr = ccmd.ExecuteReader();
				ddr.Read();
				bool nn=ddr.IsDBNull(0);
				
				OracleLob tempLob = ddr.GetOracleLob(0);
				
				ddr.Close();
			}
			if (!ds.Tables[0].Rows[0]["BUTTONICON"].ToString().Equals(""))
			{
				img =  (byte[])dr["BUTTONICON"];
			}

			//tempLob = (OracleLob)dr["BUTTONICON"];

			Stream olefile ;//= (Stream)dr["BUTTONICON"];
			System.Drawing.Image im ;//= System.Drawing.Image.FromStream(olefile);
			im = (System.Drawing.Image)dr["BUTTONICON"];
			this.button3.Image = im;

			return;
			string olestring="";
			try
			{
				olefile.Seek(0, SeekOrigin.Begin); 
				Byte[] myByteArray = new Byte[6000];
				ASCIIEncoding myEncoding  = new ASCIIEncoding();			
				int intread=olefile.Read(myByteArray,0,6000);
				while(intread!=0)
				{			
					olestring=olestring+myEncoding.GetString(myByteArray,0,intread);
					intread=olefile.Read(myByteArray,0,intread);
				
				}	
			}
			catch
			{
				olestring="";
			} 
			//Response.ContentType= "image/*";
			//Response.BinaryWrite (img);

			conn.Close();

			
		}

		private void button2_Click(object sender, System.EventArgs e)
		{
			OracleLobData od = new OracleLobData();
			od.ReadBlob(0,"","","","");
		}

		private void button3_Click(object sender, System.EventArgs e)
		{
			string conn_str = "user id=mis_sys;data source=ktjgis;password=mis_sys";
   

			DataSet ds = new DataSet();
			OracleConnection conn = new OracleConnection(conn_str);
			conn.Open();
			OracleCommand cmd = new OracleCommand("select BUTTONICON  from mis_base.fcltsort where rid = 5", conn);
			//OracleCommand cmd = new OleDbCommand("select BUTTONICON  from mis_base.fcltsort where rid = 5", conn);
			//da.SelectCommand = cmd;


			int actual = 0;
			//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')";
			cmd.CommandText   = "select BUTTONICON  from mis_base.fcltsort where rid = 5";
			OracleDataReader reader = cmd.ExecuteReader();
			using(reader)
			{
				//Obtain the first row of data.
				reader.Read();
  
				//Obtain the LOBs (all 3 varieties).
				OracleLob BLOB = reader.GetOracleLob(1/*0:based ordinal*/);
				
				OracleLob CLOB  = reader.GetOracleLob(2/*0:based ordinal*/);
				OracleLob NCLOB = reader.GetOracleLob(3/*0:based ordinal*/);
				//Example - Reading binary data (in chunks).
				byte[] buffer = new byte[100];
				while((actual = BLOB.Read(buffer, 0/*buffer offset*/, buffer.Length/*count*/)) >0)
					Console.WriteLine(BLOB.LobType + ".Read(" + buffer + ", " + buffer.Length + ") => " + actual);
				//Example - Reading CLOB/NCLOB data (in chunks).
				//Note: You can read character data as raw Unicode bytes (using OracleLob.Read as in the above example).
				//However, because the OracleLob object inherits directly from the .NET stream object, 
				//all the existing classes that manipluate streams can also be used. For example, the 
				//.NET StreamReader makes converting the raw bytes into actual characters easier.
				StreamReader streamreader = new StreamReader(CLOB, Encoding.Unicode);
				char[] cbuffer = new char[100];
				while((actual = streamreader.Read(cbuffer, 0/*buffer offset*/, cbuffer.Length/*count*/)) >0)
					Console.WriteLine(CLOB.LobType + ".Read(" + new string(cbuffer, 0, actual) + ", " + cbuffer.Length + ") => " + actual);
				//Example - Reading data (all at once).
				//You could use StreamReader.ReadToEnd to obtain all the string data,or simply
				//call OracleLob.Value to obtain a contiguous allocation of all the data.
				//Console.WriteLine(NCLOB.LobType + ".Value => " + NCLOB.Value);
			}

		}

		private void oracleConnection1_InfoMessage(object sender, System.Data.OracleClient.OracleInfoMessageEventArgs e)
		{
		
		}

		private void button4_Click(object sender, System.EventArgs e)
		{
			string conn_str = "user id=mis_sys;data source=ktjgis;password=mis_sys";
   string sql ="select BUTTONICON  from mis_base.fcltsort where rid > 1";
			DataSet ds = new DataSet();
			OracleConnection conn = new OracleConnection(conn_str);
			//conn.Open();
			//调用方法如：ShowImage("select Photo from UserPhoto where UserNo='" + userno +"'");
			OracleCommand cmd = new OracleCommand(sql, conn);
			conn.Open();
			byte[] b= (byte[])cmd.ExecuteScalar();
			if (b.Length > 0)
			{
				MemoryStream stream = new MemoryStream(b, true);
				stream.Write(b, 0, b.Length);
				pictureBox1.Image = new Bitmap(stream);
				stream.Close();
			}
			conn.Close();
			

		}
	}
}