Operate blob data in Oracle via C#
oracle table:
CREATE TABLE "SCOTT"."TEST_BLOB"
( "NAME" VARCHAR2(200),
"PHOTO" BLOB
)
Operating Code:
private static void OprBlob()
{
var connStr = ConfigurationManager.ConnectionStrings["ora"].ConnectionString;
using (OracleConnection conn = new OracleConnection())
{
conn.ConnectionString = connStr;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"
begin
insert into test_blob values(:1,:2);
end;
";;
byte[] photo;
using (var fileStrm = new FileStream(@"D:\abc.png", FileMode.Open))
{
photo = new byte[fileStrm.Length];
fileStrm.Read(photo, 0, photo.Length);
}
cmd.Parameters.Add("name", OracleDbType.NVarchar2,"Phil",ParameterDirection.Input);
cmd.Parameters.Add("photo", OracleDbType.Blob, photo, ParameterDirection.Input);
cmd.ExecuteNonQuery();
}
using (OracleConnection conn = new OracleConnection())
{
conn.ConnectionString = connStr;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"
begin
select photo into :1 from test_blob where rownum =1;
end;
"; ;
var photoParam = cmd.Parameters.Add("p", OracleDbType.Blob, ParameterDirection.Output);
cmd.ExecuteNonQuery();
byte[] photo2 = (byte[])((Oracle.DataAccess.Types.OracleBlob) photoParam.Value).Value;
using (var fileStrm = new FileStream(@"D:\phil.png",FileMode.CreateNew))
{
fileStrm.Write(photo2,0,photo2.Length);
fileStrm.Flush(true);
}
}
}