Operate blob data in Oracle via C#

时间:2021-12-05 22:55:21

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);
                }
            }

}