c# winform 操作oracle資料庫的Blob欄位，把圖片儲存到資料庫，儲存圖片到資料庫

轉自：http://heisetoufa.iteye.com/blog/262798

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.IO;
using System.Data.OleDb;

namespace ImageSaveOracleBlobDataBase
{
 public partial class Form1 : Form
 {
  /// <summary>
  /// 作者：黑色頭髮
  /// C# WinForm操作oracle資料庫的Blob欄位
  /// blog:http://heisetoufa.iteye.com
  /// 靜網:http://www.heisetoufa.cn
  /// </summary>

  public Form1()
  {
   InitializeComponent();
  }

  #region 儲存圖片到oracle資料庫

  private void button1_Click(object sender, EventArgs e)
  {
   string cnnstr = "provider=OraOLEDB.Oracle;data source=zlkj_kk;User Id=kk;Password=kk;";
   OleDbConnection con = new OleDbConnection(cnnstr);
   try
   {
    con.Open();
   }
   catch
   { }
   OleDbCommand cmd = new OleDbCommand(cnnstr, con);

   cmd.CommandType = CommandType.Text;
   cmd.CommandText = cnnstr;

   string imgPath = @"d:\aa\a.jpg";//圖片檔案所在路徑
   FileStream file = new FileStream(imgPath, FileMode.Open, FileAccess.Read);
   Byte[] imgByte = new Byte;//把圖片轉成 Byte型 二進位流
   file.Read(imgByte, 0, imgByte.Length);//把二進位流讀入緩衝區
   file.Close();

   cmd.CommandText = " insert into kk.kkzp ( xh,zp ) values ('17',:zp) ";//正常sql語句插入資料庫

   cmd.Parameters.Add("zp", System.Data.OleDb.OleDbType.Binary, imgByte.Length);
   cmd.Parameters[0].Value = imgByte;

   try
   {
    cmd.ExecuteNonQuery();
    MessageBox.Show("插入成功");
   }
   catch (System.Exception e1)
   {
    MessageBox.Show(e1.Message);
   }
  }

  #endregion

  #region 修改圖片

  private void button2_Click(object sender, EventArgs e)
  {
   string cnnstr = "provider=OraOLEDB.Oracle;data source=zlkj_kk;User Id=kk;Password=kk;";
   OleDbConnection con = new OleDbConnection(cnnstr);

   string imgPath = @"d:\aa\b.jpg";//圖片檔案所在路徑
   FileStream file = new FileStream(imgPath, FileMode.Open, FileAccess.Read);
   Byte[] ddd = new Byte;//把圖片轉成 Byte型 二進位流
   file.Read(ddd, 0, ddd.Length);//把二進位流讀入緩衝區
   file.Close();

   string strSQL = "update kk.kkzp set zp=:zp where xh = '16' ";
   try
   {
    con.Open();
   }
   catch
   { }
   OleDbCommand cmd = new OleDbCommand(cnnstr, con);

   cmd.CommandType = CommandType.Text;
   cmd.CommandText = strSQL;
   cmd.Parameters.Add("zp", System.Data.OleDb.OleDbType.Binary, ddd.Length).Value = ddd;
   cmd.ExecuteNonQuery();
   MessageBox.Show("修改成功");
  }

  #endregion

  #region 讀出資料庫裡圖片

  private void button3_Click(object sender, EventArgs e)
  {
   string strSQL = "select xh,zp from kk.kkzp where xh = 16 ";
   string cnnstr = "provider=OraOLEDB.Oracle;data source=zlkj_kk;User Id=kk;Password=kk;";
   OleDbConnection con = new OleDbConnection(cnnstr);
   try
   {
    con.Open();
   }
   catch
   { }
   OleDbCommand cmd = new OleDbCommand(strSQL, con);
   System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();
   while (dr.Read())
   {
    if (dr["zp"] != DBNull.Value)//照片欄位裡有值才能進到方法體顯示圖片，否則清空pb  
    {
     MemoryStream ms = new MemoryStream((byte[])dr["zp"]);//把照片讀到MemoryStream裡  
     Image imageBlob = Image.FromStream(ms, true);//用流建立Image  
     pictureBox1.Image = imageBlob;//輸出圖片  
     pictureBox1.Image.Save("d:\\abcc.jpg", pictureBox1.Image.RawFormat);
    }
    else//照片欄位裡沒值，清空pb  
    {
     pictureBox1.Image = null;
    }  
   }
  }

  #endregion

  #region 使文本編輯變得可見

  private void button4_Click(object sender, EventArgs e)
  {
   textBox1.Visible = true;
   button5.Visible = true;
   button6.Visible = true;
   button7.Visible = true;
  }

  #endregion

  #region 儲存文本

  private void button5_Click(object sender, EventArgs e)
  {
   string cnnstr = "provider=OraOLEDB.Oracle;data source=zlkj_kk;User Id=kk;Password=kk;";
   OleDbConnection con = new OleDbConnection(cnnstr);
   try
   {
    con.Open();
   }
   catch
   { }
   OleDbCommand cmd = new OleDbCommand(cnnstr, con);

   cmd.CommandType = CommandType.Text;
   cmd.CommandText = cnnstr;

   string txvalue = this.textBox1.Text.Trim();

   //將textBox1.Text轉換為二進位類型
   byte[] expbyte = System.Text.Encoding.Unicode.GetBytes(txvalue);

   cmd.CommandText = " insert into kk.kkzp ( xh,zp ) values ('18',:zp) ";

   cmd.Parameters.Add("zp", System.Data.OleDb.OleDbType.Binary, expbyte.Length);
   cmd.Parameters[0].Value = expbyte;

   try
   {
    cmd.ExecuteNonQuery();
    MessageBox.Show("插入結束");
   }
   catch (System.Exception e1)
   {
    MessageBox.Show(e1.Message);
   }
  }

  #endregion

  #region 修改文本

  private void button6_Click(object sender, EventArgs e)
  {
   string cnnstr = "provider=OraOLEDB.Oracle;data source=zlkj_kk;User Id=kk;Password=kk;";
   OleDbConnection con = new OleDbConnection(cnnstr);

   byte[] ddd;
   ddd = System.Text.Encoding.Unicode.GetBytes(this.textBox1.Text);
   string strSQL = "update kk.kkzp set zp=:zp where xh = 18 ";
   try
   {
    con.Open();
   }
   catch
   { }
   OleDbCommand cmd = new OleDbCommand(cnnstr, con);

   cmd.CommandType = CommandType.Text;
   cmd.CommandText = strSQL;
   cmd.Parameters.Add("zp", System.Data.OleDb.OleDbType.Binary, ddd.Length).Value = ddd;
   cmd.ExecuteNonQuery();
   MessageBox.Show("OK!");
  }

  #endregion

  
  #region 讀出文本

  private void button7_Click(object sender, EventArgs e)
  {
   string strSQL = "select xh,zp from kk.kkzp where xh = 18 ";
   string cnnstr = "provider=OraOLEDB.Oracle;data source=zlkj_kk;User Id=kk;Password=kk;";
   OleDbConnection con = new OleDbConnection(cnnstr);
   try
   {
    con.Open();
   }
   catch
   { }
   OleDbCommand cmd = new OleDbCommand(strSQL, con);
   System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();
   while (dr.Read())
   {

    //讀取BLOB資料時也要將其轉換迴文本類型
    string dd = dr["zp"].ToString();
    byte[] ooo = (byte[])dr["zp"];
    string str;
    str = System.Text.Encoding.Unicode.GetString(ooo);
    this.textBox1.Text = str;
   }
  }

  #endregion

  #region 退出

  private void button8_Click(object sender, EventArgs e)
  {
   Application.Exit();
  }

  #endregion

  #region 單擊狀態列

  private void toolStripStatusLabel2_Click(object sender, EventArgs e)
  {
   System.Diagnostics.Process.Start("http://heisetoufa.iteye.com");
  }

  #endregion
 }
}