我現在在Oracle中寫了個預存程序如下:
create or replace procedure update_student_clob(
v_cmid in number, --表的主鍵ID
v_geometry in blob --新的繪圖物件
)
is
lobloc blob;
query_str varchar2(1000);
begin
--取出blob對象
query_str :='select SPHOTOfrom student where STUDENTID= :id for update ';
EXECUTE IMMEDIATE query_str INTO lobloc USING v_cmid;
--更新
dbms_lob.write(lobloc, utl_raw.length(v_geometry),1, v_geometry);
commit;
end;
在C#中的代碼部分如下:
1.資料層
public override void RunProcedure(string storedProcName,OracleParameter[] parameters)
{
cmd.CommandText=storedProcName;//聲明預存程序名
cmd.CommandType=CommandType.StoredProcedure;
foreach(OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();//執行預存程序
}
2.商業層
//用於儲存照片,StuEntity 為實體類
public void AddStudent(StuEntity p_Entity)
{
DataAccess.DBOperator m_Ora = DBOperator.CreateKgInstance();
try
{
//fs為建立檔案流
FileStream fs=new FileStream(p_Entity.photo,FileMode.OpenOrCreate);
//建立了位元組
byte[] blob = new byte[fs.Length];
fs.Read(blob, 0, blob.Length);
fs.Close();
//studid為主鍵
string m_Sql="update student set sphoto=empty_blob() where STUDENTID=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
{
//預存程序的參數聲明
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();
}
}