Oracle BLOB data type and C#

Oracle BLOB data type and C#

 

When I attempt to my customers tend to use Oracle Database or exist Oracle Database users they always ask “you have data type such as Large Object (BLOB), CLOB, and NCLOB how can we read or write in .Net”. I managed to find an article with complete working sample, from Mark Williams again at http://www.oracle.com/technology/oramag/oracle/05-nov/o65odpnet.html.

 

Take a quick look of the code.

 

private void btnDisplay_Click(object sender, System.EventArgs e)

    {

      // create and open connection

      // change for your environment

      string connStr = "User Id=pm; Password=pm; Data Source=orcllx; Pooling=false";

      OracleConnection con = new OracleConnection(connStr);

      try

      {

        con.Open();

      }

      catch (OracleException ex)

      {

        MessageBox.Show(ex.Message);

      }

 

      // statement to get a blob

      string sql = "select ad_composite from print_media where product_id=3106 and

                   ad_id=13001";

 

      // create command object

      // InitialLOBFetchSize

      //  defaults to 0

      OracleCommand cmd = new OracleCommand(sql, con);

 

      cmd.InitialLOBFetchSize = 8192;

 

      // create a datareader

      OracleDataReader dr = cmd.ExecuteReader();

 

      // read the single row result

      try

      {

        dr.Read();

      }

      catch (OracleException ex)

      {

        MessageBox.Show(ex.Message);

      }

 

      // use typed accessor to retrieve the blob

      OracleBlob blob = dr.GetOracleBlob(0);

 

      // create a memory stream from the blob

      MemoryStream ms = new MemoryStream(blob.Value);

 

      // set the image property equal to a bitmap

      // created from the memory stream

      pictureBox1.Image = new Bitmap(ms);

    }

  }

 

To me, I will pay attention to OracleBlob and MemoryStream object in the code above. The OracleDataReader will be like the DataReader that we are familiar in ADO.NET.

 

I actually run this code from my laptop and accessing to Oracle Database 10g Enterprise that I set up in a Oracle Enterprise Linux box, it works fine.