Oracle.DataAccess.Client – Insert BLOB ( File Object ) 方法

Oracle.DataAccess.Client – Insert BLOB ( File Object ) 方法

   Table Structure
 
   CREATE TABLE "APPUSER1"."BLOBTEST"
   (
      "ID" NUMBER NOT NULL ENABLE PRIMARY KEY,
      "NAME" NVARCHAR2(100) NOT NULL ENABLE,
      "PICTURE" BLOB NOT NULL ENABLE,
      "CAT" NUMBER NOT NULL ENABLE
   );
 
   Insert
 
   using System;
   using System.Text;
   using Oracle.DataAccess.Client;
   using System.Net;
   using System.Xml;
 
   static void Upload(int ID, String url, String Title, int cat)
   {
 
      try
      {
 
         String connectionString = "Data Source=(DESCRIPTION="
         + "(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.76)(PORT=1521))"
         + "(CONNECT_DATA=(SERVICE_NAME=orcl)));"
         + "User Id=APPUSER1;Password=Passw0rd;";
 
         StringBuilder strSql = new StringBuilder();
         strSql.Append("insert into BlogTest(");
         strSql.Append("ID,NAME,PICTURE,CAT)");
         strSql.Append(" values (");
         strSql.Append(ID.ToString() + ",'" + Title + "',:Picture," + cat.ToString() + ")");
 
         WebClient wc = new WebClient();
 
         byte[] bytes = wc.DownloadData(url);
 
         OracleConnection OrclConnection = new OracleConnection();
 
         OrclConnection.ConnectionString = connectionString;
 
         OracleCommand OrclCommand = OrclConnection.CreateCommand();
 
         OrclCommand.CommandText = strSql.ToString();
         OrclCommand.Parameters.Add(":Picture", OracleDbType.Blob).Value = bytes;
 
         OrclConnection.Open();
 
         OrclCommand.ExecuteNonQuery();
 
         OrclConnection.Close();
 
      }
      catch (Exception ex)
      {
         Console.Write("Error" + ex.Message);
         Console.ReadLine();
      }
 
   }
 
   Select
 
   using System;
   using System.Text;
   using Oracle.DataAccess.Client;
   using System.IO;
   using System.Net;
 
   static void Download()
   {
 
      try
      {
 
         String connectionString = "Data Source=(DESCRIPTION="
         + "(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.76)(PORT=1521))"
         + "(CONNECT_DATA=(SERVICE_NAME=orcl)));"
         + "User Id=APPUSER1;Password=Passw0rd;";
 
         byte[] bytes;
 
         OracleConnection OrclConnection = new OracleConnection();
 
         OrclConnection.ConnectionString = connectionString;
 
         OracleCommand OrclCommand = OrclConnection.CreateCommand();
 
         OrclConnection.Open();
 
         bytes = null;
 
         OrclCommand.CommandText = "SELECT ID, NAME, PICTURE, CAT from BLOBTEST WHERE ORDER BY ID DESC";
 
         OracleDataReader OrclDataReader = OrclCommand.ExecuteReader();
 
         String Folder;
         String ID;
 
         while (OrclDataReader.Read())
         {
 
            bytes = (byte[])OrclDataReader["PICTURE"];
            Folder = OrclDataReader["CAT"].ToString();
            ID = OrclDataReader["ID"].ToString();
 
            if(!Directory.Exists(@"C:\test\" + Folder)) Directory.CreateDirectory(@"C:\test\" + Folder);
 
            File.WriteAllBytes(@"C:\test\" + Folder + @"\" + ID + ".jpg", bytes);
 
         }
 
         OrclConnection.Close();
 
      }
      catch (Exception ex)
      {
         Console.Write("Error" + ex.Message);
         Console.ReadLine();
      }
 
   }