SQL section: - One wiring diagram DROP SEQUENCE YW_YCJXT_ID
Create sequence yw_ycjxt_idstart with 1increment By 1;
DROP TABLE YW_YCJXTTRUNCATE TABLE YW_YCJXT
Create Table Yw_ycjxt (ID Numeric (5) Primary Key Not Null, --- Wiring Diagram ID G3E_FNO NUMERIC (10) Not Null, --- Device FNO G3e_fid Numeric (10) Not Null, --- Device FID PICSIZE NUMERIC (10 ), --- Wiring diagram Image Size Pictype Varchar2 (20), --- Picture Type Picname Varchar2 (50), --- Wiring Diagram Picture Name Description Varchar2 (500), - Picture Description Adddate Date, - Add Date ModifyDate Date, - Image Last Date of Modify Pic BLOB - Down Map)
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 --------------- *** Add *** -------------- 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) asbegin 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; ------- -------- *** Modify *** -------------- 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) asBegin update YW_YCJXT set g3e_fno = p_g3e_fno, g3e_fid = p_g3e_fid, picsize = p_picsize , modifydate = sysdate, pictype = p_pictype, picname = p_picn AME, DESCRIPTION = P_DESCRIPTION, PIC = p_pic where id = p_id; commit; end update_ycjxt;
--------------- *** Delete *** -------------- Procedure del_ycjxt (p_id yw_ycjxt.id% type) asbegin delete from yw_ycjxt WHERE G3E_FID = p_id; commit; end del_ycjxt;
---------------- *** Query *** ---------------- 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;
----------------------- *** Query a wiring map ** ----------------- ------ 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 ; / ------------------------------------------------ --------------- *********** * ************* - -------- ---------- Class section: using system; using system.data; using system.data.racleclient; using gis; using system.io; using system.Web; namespace ycjxt {public class ycjxtManage {logfile lfile Public ycjxtManage () {lfile = new logfile ();} /// function: delete a wiring diagram // // input parameter: p_id image ID; dbconn: connection parameters 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 ();}} / /// function: Add a wiring diagram // / / / / input parameter: p_g3e_fno device FNO; p_g3e_fid device FID / / P_PICSIZE wiring FIG image size; p_pictype picture type; p_picname wiring diagrams image name // / p_description image description; DBConn: connection parameter // // 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;
// get file size 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 = myco nn; 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 ("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."); Oracrans.commit ();} catch (exception ex) {lfile.msg "Ycjxt.addimage:", ex.Message);} finally {myconn.close ();}} / /// function: modify a wiring diagram // / / / input parameter: p_id image ID; p_g3e_fno device FNO; p_g3e_fid Device FID / / / P_PICSIZE wiring diagram picture size; p_pictype image type; p_picname wiring map picture name // / p_description image description; p_pic wiring diagram; dbconn: connection parameter // // 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;
// get file size 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.connecti on = 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: ", EXMESSAGE);} myconn.close ();}
/ / Displays a wiring diagram on a device // / / / / input parameter: p_id; dbconn connection data; // // 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 (Excep TION EX) // {// LFile.msg ("Showimage Failure:", EX.MESSAGE); //} Finally {MyConn.close (); LFile.msg ("Ycjxt.ShowImage:", "end"); }
