I now write a stored procedure in Oracle as follows:
Create or replace procedure update_student_clob (
V_cmid in number, -- table's primary key ID
V_geometry in blob -- New graphic object
)
Is
Lobloc blob;
Query_str varchar2 (1000 );
Begin
-- Retrieve BLOB Object
Query_str: = 'selectSphoto From student where Studentid =: ID for Update ';
Execute immediate query_str into lobloc using v_cmid;
-- Update
Dbms_lob.write (lobloc, utl_raw.length (v_geometry), 1, v_geometry );
Commit;
End;
In C #,CodeParts are as follows:
1. Data Layer
Public override void runprocedure (string storedprocname, oracleparameter [] parameters)
{
Cmd. commandtext = storedprocname; // declare the name of the stored procedure
Cmd. commandtype = commandtype. storedprocedure;
Foreach (oracleparameter parameter in parameters)
{
Cmd. Parameters. Add (parameter );
}
Cmd. executenonquery (); // execute the Stored Procedure
}
2. Business Layer
// Used to store photos. stuentity is an entity class.
Public void addstudent (stuentity p_entity)
{
Dataaccess. dboperator m_ora = dboperator. createkginstance ();
Try
{
// FS creates a file stream
Filestream FS = new filestream (p_entity.photo, filemode. openorcreate );
// Creates a binary array.
Byte [] blob = new byte [fs. Length];
FS. Read (blob, 0, blob. Length );
FS. Close ();
// Studid is the primary key
String m_ SQL = "Update student set sphoto = empty_blob () WhereStudentid = 1 ";
M_ora.execute (m_ SQL );
Spexefor (1, blob );
}
Catch (exception ex)
{
Throw ex;
}
Finally
{
M_ora.close ();
}
}
Public void spexefor (INT p_studid, byte [] p_blob)
{
Dataaccess. dboperator m_ora = dboperator. createkginstance ();
Try
{
// Parameter declaration of Stored Procedure
Oracleparameter [] parameters = {
New oracleparameter ("v_cmid", oracletype. int32 ),
New oracleparameter ("v_geometry", oracletype. Blob, p_blob.length ),
};
Parameters [0]. value = p_studid;
Parameters [1]. value = p_blob;
M_ora.runprocedure ("update_student_clob", parameters );
}
Catch (exception E)
{
Throw E;
}
Finally
{
M_ora.close ();
}
}