Reading and Writing BLOB and CLOB Data to Oracle Database using C#

i'm going to explan how to insert,update and Get data in blob and clob  column in Oracle database using C# asp.net or MVC.

I have used VS 2019 dot net Core

here i have used OracleCommand  for Insert Data and Update Data

Code is As- 

string _oraConn = "User Id=FINANCE;Password=" + password + ";" + "Data source="+ datasorce + "";

Converting Doc data in Byte[] for saving in Blob Field

var path = Path.Combine(Directory.GetCurrentDirectory(), "DocumentsFiles", "e53e2bd1-40ae-4cc3-be8a-fab1b5c6df4f.docx");
byte[] byteArray = null;
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read))
{
     byteArray = new byte[fs.Length];
     int iBytesRead = fs.Read(byteArray, 0, (int)fs.Length);
}

string commandText = "insert into TEST_BLOB(ID,NAME,PHONE,EMAIL,LARGE_DATA,LARGE_DATA2)values(:ID,:NAME,:PHONE,:EMAIL,:LARGE_DATA,:LARGE_DATA2)";
using (OracleConnection con = new OracleConnection(constr))
{
     OracleCommand command = new OracleCommand(commandText, con);
     command.Parameters.Add(":ID", OracleDbType.Int64);
     command.Parameters[":ID"].Value = 3;
     command.Parameters.Add(":NAME", OracleDbType.Varchar2);
     command.Parameters[":NAME"].Value = "shashi";
     command.Parameters.Add(":PHONE", OracleDbType.Varchar2);
     command.Parameters[":PHONE"].Value = "8800557612";
     command.Parameters.Add(":EMAIL", OracleDbType.Varchar2);
     command.Parameters[":EMAIL"].Value = "shashi@gmail.com";
     command.Parameters.Add(":LARGE_DATA", OracleDbType.Clob);
     command.Parameters[":LARGE_DATA"].Value = "Testing clob";
     command.Parameters.Add(":LARGE_DATA2", OracleDbType.Blob);
     command.Parameters[":LARGE_DATA2"].Value = byteArray;

     con.Open();
     Int32 rowsAffected = command.ExecuteNonQuery();
     con.Close();
}

Update Data Code -

string commandTextupd = "update  TEST_BLOB set NAME=:NAME,PHONE=:PHONE,EMAIL=:EMAIL,LARGE_DATA=:LARGE_DATA,LARGE_DATA2=:LARGE_DATA2 where ID=:ID";
using (OracleConnection con = new OracleConnection(constr))
{
    OracleCommand command = new OracleCommand(commandTextupd, con);                  
    command.Parameters.Add(":NAME", OracleDbType.Varchar2);
    command.Parameters[":NAME"].Value = "shashi Bhushan";
    command.Parameters.Add(":PHONE", OracleDbType.Varchar2);
    command.Parameters[":PHONE"].Value = "8800557612";
    command.Parameters.Add(":EMAIL", OracleDbType.Varchar2);
    command.Parameters[":EMAIL"].Value = "shashi@gmail.com";
    command.Parameters.Add(":LARGE_DATA", OracleDbType.Clob);
    command.Parameters[":LARGE_DATA"].Value = "Testing clob";
    command.Parameters.Add(":LARGE_DATA2", OracleDbType.Blob);
    command.Parameters[":LARGE_DATA2"].Value = byteArray;

    command.Parameters.Add(":ID", OracleDbType.Int64);
    command.Parameters[":ID"].Value = 3;

    con.Open();
    Int32 rowsAffected = command.ExecuteNonQuery();
    con.Close();
}

Note - Maintain sequence as update command text written other wise it will give error 

Get Data Code -

 var path2 = Path.Combine(Directory.GetCurrentDirectory(), "DocumentsFiles");
 path2 = path2 + "\\RaedData.docx";
 OracleConnection con1 = new OracleConnection(constr);
 OracleCommand cmdr = new OracleCommand("select * from TEST_BLOB where Id=3", con1);
 OracleDataAdapter da = new OracleDataAdapter(cmdr);
 DataTable dt = new DataTable();
 da.Fill(dt);
 if (dt.Rows.Count > 0)
 {
      string Name = dt.Rows[0]["Name"].ToString();
      string clobdata = dt.Rows[0]["LARGE_DATA"].ToString();

      byte[] byteArray1 = (Byte[])dt.Rows[0]["LARGE_DATA2"];
      using (FileStream fs = new FileStream(path2, FileMode.CreateNew, FileAccess.Write))
      {
          fs.Write(byteArray, 0, byteArray.Length);
      }
 }

 

Above i have get data in byte[] and byte data writen in .docx file in specified folder.

Leave a Comment