I. Creating a MySQL stored procedure
1,
CREATE PROCEDURE ' Insertalarminfo ' (in Businessindex int, in Providerindex int., in Alarmtype int, in Alarmlevel Int,in detecttime DateTime, in alarmdescription varchar (+), in Alarmphenomenon int, in Subbusinesstype int, I n businessentityindex varchar (+), in Taskindex varchar (a), in Eventindex varchar ()) BEGIN declare entityindex varchar ( 50); If Detecttime is a null then set Detecttime=now (); END IF; INSERT into M_alarm (Businessindex, Providerindex, Alarmtype, Alarmlevel, Detecttime, Alarmdescription, HANDLESTATUS, Alarmphenomenon, ENTITYTYPE) VALUES (Businessindex,providerindex, Alarmtype, Alarmlevel, Detecttime, alarmdescription , 3, Alarmphenomenon, Subbusinesstype); Set Entityindex =concat (' A_cn ', Right (concat (' 00000000 ', Currval (' Entity_seq ')), 8)); SET sql_safe_updates = 0;if Businessentityindex is not null Theninsert into M_configitemrelation (SOURCEINDEX, Targetindex , Relationtype, GROUPTYPE) VALUES (Entityindex,businessentityindex, 3, 1); END IF; If Taskindex is isn't null then INSERT into M_configitemrelation (SOURCEINDEX, Targetindex, Relationtype, GROUPTYPE) VALUES (Entityindex, Taskindex, 3, 5); END IF; If Eventindex is isn't null then INSERT into M_configitemrelation (SOURCEINDEX, Targetindex, Relationtype, GROUPTYPE) VALUES (Entityindex,eventindex, 3, 14); UPDATE m_event SET dealtype = 1 WHERE eventindex = Eventindex; END IF; UPDATE m_businessentityextend SET currentstatus = 2 WHERE entityindex = businessentityindex and ENTITYTYPE = Subbusinesst Ype SET sql_safe_updates = 1;select entityindex;//is used here to return the data obtained by EF end
2,1 (This stored procedure call function)
CREATE PROCEDURE ' Insertworkorderinfo ' (in personindex integers, in businessindex integer,in systemstatus Integer,in providerindex Integer, in alarmindex VARCHAR (+), in Orderlevel Integer, in Exceptfixtime Integer,in Fixperson Integer, in Currentstatus Integer, in orderhandle VARCHAR (+), in Handletype integer) Beginselect Insertworkorderinfo (Personindex,businessindex,systemstatus,
Providerindex,alarmindex,orderlevel,exceptfixtime,fixperson,currentstatus,orderhandle,handletype); END
2,2 (defining functions)
CREATE FUNCTION ' insertworkorderinfo ' (Personindex integer, Businessindex integer, Systemstatus integer, Providerindex Integer, Alarmindex VARCHAR (+), orderlevel integer, exceptfixtime integer, Fixperson integer, Currentstatu s integer, Orderhandle varchar (+), Handletype INTEGER) RETURNS varchar CHARSET utf8begin declare issmsn Otify INTEGER; DECLARE isweixinnotify INTEGER; DECLARE Createtime DATETIME; DECLARE CTime DATETIME; Set issmsnotify=0; Set isweixinnotify=0; Set Ctime=now (); Set Createtime=now (); INSERT into M_workorder (Businessindex, Systemstatus, Providerindex, Alarmindex, Orderlevel, Exceptfixtime, FIXPERSON, Currentstatus, Issmsnotify, Isweixinnotify, Createtime, Orderhandle, Handletype) VALUES (Businessindex, Systemstatus, Providerindex, Alarmindex, Orderlevel, Exceptfixtime, Fixperson,currentstatus, Issmsnotify,isweixinnotify, CTime, Orderhandle, Handletype); INSERT into M_orderhandle (Orderindex, Handlestatus, Handletime, HandleperSON, Handledescription, Handletype) VALUES (concat (' S_cn ', Right (concat (' 00000000 ', Currval (' Entity_seq ')), 8), Currentstatus, Ctime,personindex, Orderhandle, Handletype); RETURN concat (' S_cn ', Right (concat (' 00000000 ', Currval (' Entity_seq ')), 8)); END
Two EF Calls
Public ActionResult Editworkorder (FormCollection collection) {applog.info ("Editworkorder"); try {string OpenID = session["OpenID"]. ToString (); Applog.info ("OpenID when adding a ticket:" + OpenID); int Personindex =dbaccess.bussiness.getstaffindex (OpenID); int Businessindex =convert.toint32 (session["Providerindex_businessindex"]. ToString (). Split ('/') [1]);//road ... index int systemstatus = 0; int providerindex = Convert.ToInt32 (collection["providerlist"]);//maintenance Unit String alarmindex = session["Alarmin Dex "]. ToString ();//alarm number is provided above int orderlevel = Convert.ToInt32 (collection["Workorderlevel"]);//Priority level int Fixperson = Convert.ToInt32 (collection["repairuserlist"]);//maintenance staff string currentstatus = Convert.tostri Ng (collection["workorderstatuslist"]);//Add String Orderhandle =string. Empty; if (collection["Orderhandle"]!=null) {orderhandle = collection["Orderhandle"];//Fault classification processing description } String handletype = Convert.ToString (collection["alarmhandletypelist"]);//Fault handling classification Ap Plog.info ("Dispatch ticket parameter Info:" + "--personindex:" +personindex+ "--businessindex:" +businessindex+ "--providerindex:" + providerindex+ "--alarmindex:" +alarmindex+ "--orderlevel:" +orderlevel+ "--fixperson:" +fixperson+ "--currentstatus: "+currentstatus+"--orderhandle: "+orderhandle+"--handletype: "+handletype); mysqlparameter[] prams = new MYSQLPARAMETER[11]; Prams[0] = new Mysqlparameter ("@personindex", Personindex); PRAMS[1] = new Mysqlparameter ("@businessindex", Businessindex); PRAMS[2] = new Mysqlparameter ("@systemstatus", systemstatus); PRAMS[3] = new Mysqlparameter ("@providerindex", Providerindex); PRAMS[4] = new Mysqlparameter ("@alarmindex", Alarmindex);PRAMS[5] = new Mysqlparameter ("@orderlevel", orderlevel); PRAMS[6] = new Mysqlparameter ("@exceptfixtime", 24); PRAMS[7] = new Mysqlparameter ("@fixperson", Fixperson); PRAMS[8] = new Mysqlparameter ("@currentstatus", currentstatus); PRAMS[9] = new Mysqlparameter ("@orderhandle", Orderhandle); PRAMS[10] = new Mysqlparameter ("@handletype", Handletype); using (cnpsim_dbentities DbManager = new Cnpsim_dbentities ()) {var index = Dbmanager.da Tabase. Sqlquery<string> ("Call Insertworkorderinfo" (@personindex, @businessindex, @systemstatus, @providerindex, @ Alarmindex, @orderlevel, @exceptfixtime, @fixperson, @currentstatus, @orderhandle, @handletype) ", prams); String Ss=index. FirstOrDefault (). ToString (); string s = SS; Return redirecttoaction ("Successinfo", "Backinfo", new {str1 = "successfully added dispatch ticket", str2 = "",url = "Http://wx115.cnpsim.com/Business/index"}); }} catch (Exception ex) {return null; } }
The Entity framework accesses the stored procedure of the MySQL database and gets the return value