SQl部分:--一次接线图
drop sequence YW_YCJXT_ID
create sequence YW_YCJXT_ID
start with 1
increment by 1;
drop table YW_YCJXT
truncate table YW_YCJXT
create table YW_YCJXT
(
id numeric(5) primary key not null, ---接线图id
G3E_FNO numeric(10) not null, ---设备FNO
G3E_FID numeric(10) not null, ---设备FID
picsize numeric(10), ---接线图图片大小
pictype varchar2(20), ---图片类型
picname varchar2(50), ---接线图图片名称
description varchar2(500), --图片描述
adddate date, ---图片添加日期
modifydate date, ---图片上次修改日期
pic BLOB ---接线图
)
CREATE OR REPLACE PACKAGE YCJXT AS
TYPE MyCur IS REF CURSOR;
PROCEDURE Add_ycjxt(
p_g3e_fno YW_YCJXT.g3e_fno%type,
p_g3e_fid YW_YCJXT.g3e_fid%type,
p_picsize YW_YCJXT.picsize%type,
p_pictype YW_YCJXT.pictype%type,
p_picname YW_YCJXT.picname%type,
p_description YW_YCJXT.description%type,
p_pic IN BLOB);
procedure update_ycjxt(
p_id YW_YCJXT.id%type,
p_g3e_fno YW_YCJXT.g3e_fno%type,
p_g3e_fid YW_YCJXT.g3e_fid%type,
p_picsize YW_YCJXT.picsize%type,
p_pictype YW_YCJXT.pictype%type,
p_picname YW_YCJXT.picname%type,
p_description YW_YCJXT.description%type,
p_pic IN BLOB);
procedure del_ycjxt(
p_id YW_YCJXT.id%type);
procedure search_ycjxt(SeachCur OUT MyCur,
p_g3efno numeric);
procedure search_filter_ycjxt(SearchfilterCur OUT MyCur,
P_ID IN YW_YCJXT.id%TYPE);
END YCJXT;
/
CREATE OR REPLACE PACKAGE BODY YCJXT AS
-------------
--***添加***-
-------------
procedure Add_ycjxt(
p_g3e_fno YW_YCJXT.g3e_fno%type,
p_g3e_fid YW_YCJXT.g3e_fid%type,
p_picsize YW_YCJXT.picsize%type,
p_pictype YW_YCJXT.pictype%type,
p_picname YW_YCJXT.picname%type,
p_description YW_YCJXT.description%type,
p_pic IN BLOB) as
begin
insert into YW_YCJXT(id,g3e_fno,g3e_fid,picsize,pictype,picname,description,adddate,pic) values(YW_YCJXT_ID.NEXTVAL,p_g3e_fno,p_g3e_fid,p_picsize,p_pictype,p_picname,p_description,SYSDATE,p_pic);
commit;
end Add_ycjxt;
-------------
--***修改***-
-------------
procedure update_ycjxt(
p_id YW_YCJXT.id%type,
p_g3e_fno YW_YCJXT.g3e_fno%type,
p_g3e_fid YW_YCJXT.g3e_fid%type,
p_picsize YW_YCJXT.picsize%type,
p_pictype YW_YCJXT.pictype%type,
p_picname YW_YCJXT.picname%type,
p_description YW_YCJXT.description%type,
p_pic IN BLOB) as
Begin
update YW_YCJXT set g3e_fno=p_g3e_fno,g3e_fid=p_g3e_fid,picsize=p_picsize,modifydate=Sysdate,pictype=p_pictype,picname=p_picname,description=p_description,pic=p_pic where id=p_id;
commit;
end update_ycjxt;
-------------
--***删除***-
-------------
procedure del_ycjxt(
p_id YW_YCJXT.id%type) as
begin
delete from YW_YCJXT where g3e_fid=p_id;
commit;
end del_ycjxt;
--------------
--***查询***--
--------------
procedure search_ycjxt(
SeachCur OUT MyCur,
p_g3efno IN numeric)
IS
LocalSearchCur MyCur;
BEGIN
OPEN LocalSearchCur FOR
select id,g3e_fno,g3e_fid,picsize,pictype,picname,description,to_char(adddate,'yyyy/mm/dd'),to_char(modifydate) from yw_ycjxt where g3e_fno=p_g3efno order by adddate desc;
SeachCur := LocalSearchCur;
END search_ycjxt;
---------------------
--***查询某条接线图**--
---------------------
procedure search_filter_ycjxt(SearchfilterCur OUT MyCur,
P_ID IN YW_YCJXT.id%TYPE)
IS
LocalSearchFilterCur MyCur;
BEGIN
OPEN LocalSearchFilterCur FOR
select pictype,description from yw_ycjxt WHERE ID=P_ID;
SearchfilterCur := LocalSearchFilterCur;
END search_filter_ycjxt;
END YCJXT;
/
----------------------------------------------------------
----- ************ end *************- ------------------
class部分:
using System;
using System.Data;
using System.Data.OracleClient;
using Gis;
using System.IO;
using System.Web;
namespace YCJXT{
public class ycjxtmanage
{
logfile lfile;
public ycjxtmanage()
{
lfile = new logfile();
}
/// 功能:删除一次接线图 //
// 输入参数: p_id 图片ID ;DBConn: 连接参数
public void delimage(int p_id,string DBConn)
{
lfile.Msg("YCJXT.delimage:","Enter");
OracleConnection MyConn=new OracleConnection(DBConn);
OracleCommand MyCommand = new OracleCommand();
MyCommand.Connection=MyConn;
MyCommand.CommandText = "YCJXT.del_ycjxt";
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new OracleParameter("p_id", OracleType.Number)).Value = p_id;
MyConn.Open();
try
{
MyCommand.ExecuteNonQuery();
lfile.Msg("YCJXT.delimage:","Delete success.");
}
catch(Exception ex){
lfile.Msg("YCJXT.delimage:",ex.Message);
}
finally
{
MyConn.Close();
}
}
/
/// 功能:添加一次接线图 ///
// 输入参数 : p_g3e_fno 设备FNO ; p_g3e_fid 设备FID/
/ p_picsize 接线图图片大小 ; p_pictype 图片类型 ; p_picname 接线图图片名称//
/ p_description 图片描述 ; DBConn: 连接参数 //
//
public void addimage(int p_g3e_fno,int p_g3e_fid,int p_picsize,string p_pictype,HttpPostedFile p_picpath,string p_picname,string p_description,string DBConn)
{
lfile.Msg("YCJXT.addimage:","Enter");
// string fileTitle = p_picname.Split("/",1);
//Split(MyFile.Value,"/")(ubound(Split(MyFile.Value,"/")))
OracleConnection MyConn = new OracleConnection(DBConn);
Stream fileDataStream = p_picpath.InputStream;
//得到文件大小
int fileLength = p_picpath.ContentLength;
// Stream fs = File.OpenRead(p_picpath);
byte[] tempbuff = new byte[fileLength];
fileDataStream.Read(tempbuff,0,fileLength);
//fileDataStream.Close();
MyConn.Open();
OracleTransaction OraTrans;
OraTrans = MyConn.BeginTransaction();
OracleCommand MyCommand = new OracleCommand();
MyCommand = MyConn.CreateCommand();
MyCommand.Transaction = OraTrans;
MyCommand.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx,false,0); :tempblob:=xx; end;";
MyCommand.Parameters.Add(new OracleParameter("tempblob",OracleType.Blob)).Direction = ParameterDirection.Output;
MyCommand.ExecuteNonQuery();
OracleLob templob = (OracleLob)MyCommand.Parameters[0].Value;
templob.BeginBatch(OracleLobOpenMode.ReadWrite);
templob.Write(tempbuff,0,tempbuff.Length);
templob.EndBatch();
MyCommand.Parameters.Clear();
MyCommand.Connection = MyConn;
MyCommand.CommandText = "YCJXT.Add_ycjxt";
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new OracleParameter("p_g3e_fno",OracleType.Number)).Value = p_g3e_fno;
MyCommand.Parameters.Add(new OracleParameter("p_g3e_fid",OracleType.Number)).Value = p_g3e_fid;
MyCommand.Parameters.Add(new OracleParameter("p_picsize",OracleType.Number)).Value = Convert.ToInt32(p_picsize/1024);
MyCommand.Parameters.Add(new OracleParameter("p_pictype",OracleType.Char)).Value = p_pictype;
MyCommand.Parameters.Add(new OracleParameter("p_picname",OracleType.Char)).Value = p_picname;
MyCommand.Parameters.Add(new OracleParameter("p_description",OracleType.Char)).Value = p_description;
MyCommand.Parameters.Add(new OracleParameter("p_pic",OracleType.Blob)).Value = templob;
try
{
MyCommand.ExecuteNonQuery();
lfile.Msg("YCJXT.addimage:","Insert Success.");
OraTrans.Commit();
}
catch(Exception ex){
lfile.Msg("YCJXT.addimage:",ex.Message);
}
finally
{
MyConn.Close();
}
}
/
/// 功能:修改一次接线图 ///
// 输入参数 : p_id 图片ID ; p_g3e_fno 设备FNO ; p_g3e_fid 设备FID///
/ p_picsize 接线图图片大小 ; p_pictype 图片类型 ; p_picname 接线图图片名称//
/ p_description 图片描述 ; p_pic 接线图 ;DBConn: 连接参数 //
//
public void updateimage(int p_id,int p_g3e_fno,int p_g3e_fid,int p_picsize,string p_pictype,HttpPostedFile p_picpath,string p_picname,string p_description,string DBConn)
{
lfile.Msg("YCJXT.updateimage:","Enter");
OracleConnection MyConn = new OracleConnection(DBConn);
Stream fileDataStream = p_picpath.InputStream;
//得到文件大小
int fileLength = p_picpath.ContentLength;
// Stream fs = File.OpenRead(p_picpath);
byte[] tempbuff = new byte[fileLength];
fileDataStream.Read(tempbuff,0,fileLength);
//fileDataStream.Close();
MyConn.Open();
OracleTransaction OraTrans;
OraTrans = MyConn.BeginTransaction();
OracleCommand MyCommand = new OracleCommand();
MyCommand = MyConn.CreateCommand();
MyCommand.Transaction = OraTrans;
MyCommand.CommandText = "declare xx blob ; begin dbms_lob.createtemporary(xx,false,0); :tempblob := xx; end;";
MyCommand.Parameters.Add(new OracleParameter("tempblob",OracleType.Blob)).Direction = ParameterDirection.Output;
MyCommand.ExecuteNonQuery();
OracleLob templob = (OracleLob)MyCommand.Parameters[0].Value;
templob.BeginBatch(OracleLobOpenMode.ReadWrite);
templob.Write(tempbuff,0,tempbuff.Length);
templob.EndBatch();
MyCommand.Parameters.Clear();
MyCommand.Connection = MyConn;
MyCommand.CommandText = "YCJXT.update_ycjxt";
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new OracleParameter("p_id", OracleType.Number)).Value = p_id;
MyCommand.Parameters.Add(new OracleParameter("p_g3e_fno",OracleType.Number)).Value = p_g3e_fno;
MyCommand.Parameters.Add(new OracleParameter("p_g3e_fid",OracleType.Number)).Value = p_g3e_fid;
MyCommand.Parameters.Add(new OracleParameter("p_picsize",OracleType.Number)).Value = Convert.ToInt32(p_picsize/1024);
MyCommand.Parameters.Add(new OracleParameter("p_pictype",OracleType.Char)).Value = p_pictype;
MyCommand.Parameters.Add(new OracleParameter("p_picname",OracleType.Char)).Value = p_picname;
MyCommand.Parameters.Add(new OracleParameter("p_description",OracleType.Char)).Value = p_description;
MyCommand.Parameters.Add(new OracleParameter("p_pic",OracleType.Blob)).Value = templob;
try
{
MyCommand.ExecuteNonQuery();
lfile.Msg("YCJXT.updateimage:","update Success.");
}
catch(Exception ex){
lfile.Msg("YCJXT.updateimage:",ex.Message);
}
MyConn.Close();
}
// 显示某设备上的一次接线图 ///
// 输入参数:p_id ;DBConn 连接数据;//
//
public DataSet Showimage(int p_g3e_fno,int p_g3e_fid,string DBConn)
{
lfile.Msg("YCJXT.Showimage:","Enter");
OracleConnection MyConn = new OracleConnection(DBConn);
OracleCommand MyCommand = new OracleCommand();
MyCommand.Connection = MyConn;
MyCommand.CommandText = "YCJXT.search_ycjxt";
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new OracleParameter("p_g3efno",OracleType.Number)).Value = p_g3e_fno;
MyCommand.Parameters.Add(new OracleParameter("p_g3efid",OracleType.Number)).Value = p_g3e_fid;
MyCommand.Parameters.Add(new OracleParameter("SeachCur",OracleType.Cursor)).Direction = ParameterDirection.Output;
MyConn.Open();
DataSet ds;
ds = new DataSet();
OracleDataAdapter OraAdapter;
OraAdapter = new OracleDataAdapter(MyCommand);
try
{
OraAdapter.Fill(ds);
return ds;
}
// catch (Exception ex)
// {
// lfile.Msg("Showimage Failure:",ex.Message);
// }
finally
{
MyConn.Close();
lfile.Msg("YCJXT.Showimage:","End");
}
}
/
/ 显示一次接线图 //
// 输入参数:p_id ;DBConn 连接数据;//
//
public OracleDataReader ShowJXT(int p_id,string DBConn)
{
lfile.Msg("YCJXT.ShowJXT:","Enter");
OracleConnection MyConn = new OracleConnection(DBConn);
OracleCommand MyCommand = new OracleCommand();
MyCommand.Connection = MyConn;
MyCommand.CommandText = "YCJXT.search_filter_ycjxt";
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new OracleParameter("P_ID",OracleType.Number)).Value = p_id;
MyCommand.Parameters.Add(new OracleParameter("SearchfilterCur",OracleType.Cursor)).Direction = ParameterDirection.Output;
OracleDataReader OraDr;
MyConn.Open();
try
{
OraDr = MyCommand.ExecuteReader();
// if (OraDr.Read())
return OraDr;
}
//catch(Exception ex)
//{
// lfile.Msg("YCJXT.ShowJXT:",ex.Message);
//}
finally
{
// OraDr.Close();
MyConn.Close();
lfile.Msg("YCJXT.ShowJXT:","End");
}
//
}
}
}