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.