The Entity framework accesses the stored procedure of the MySQL database and gets the return value

Source: Internet
Author: User
Tags openid ticket

I. Creating a MySQL stored procedure

1,

CREATE PROCEDURE ' Insertalarminfo ' (inchBusinessindexint,inchProviderindexint,inchAlarmtypeint,            inchAlarmlevelint,inchDetecttime DateTime,inchAlarmdescription varchar ( -),inchAlarmphenomenonint,inchSubbusinesstypeint,            inchBusinessentityindex varchar ( -),inchTaskindex varchar ( -),inchEventindex varchar ( -)) BEGIN declare entityindex varchar ( -); ifDetecttime is  NULLThenSetDetecttime=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); SetEntityindex =concat ('A_CN', Right (Concat ('00000000', Currval ('Entity_seq')),8)); SET sql_safe_updates=0;ifBusinessentityindex isNotNULLTheninsert into M_configitemrelation (SOURCEINDEX, Targetindex, Relationtype, GROUPTYPE) VALUES (Entityindex, Businessentityindex,3,1);                 END IF; ifTaskindex isNotNULLThen inserts into M_configitemrelation (SOURCEINDEX, Targetindex, Relationtype, GROUPTYPE) VALUES (Entityindex, Taskindex,3,5); END IF; ifEventindex isNotNULLThen inserts into M_configitemrelation (SOURCEINDEX, Targetindex, Relationtype, GROUPTYPE) VALUES (Entityindex,eventindex,3, -); UPDATE m_event SET Dealtype=1WHERE Eventindex =Eventindex; END IF; UPDATE m_businessentityextend SET currentstatus=2WHERE Entityindex = businessentityindex and ENTITYTYPE =Subbusinesstype; SET sql_safe_updates=1;Selectentityindex;//is used here to return the data obtained by EF end

2,1 (This stored procedure call function)

CREATE PROCEDURE ' Insertworkorderinfo ' (inchPersonindex INTEGER,inchBusinessindex INTEGER,inchSystemstatus INTEGER,inchProviderindex INTEGER,inchAlarmindex VARCHAR ( -),inchOrderlevel INTEGER,inchExceptfixtime INTEGER,inchFixperson INTEGER,inchcurrentstatus INTEGER,inchOrderhandle VARCHAR ( -),inchHandletype INTEGER) BEGINSelectInsertworkorderinfo (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, Providerin Dex INTEGER, Alarmindex VARCHAR ( -), orderlevel Integer, exceptfixtime integer, Fixperson integer, Currentstatus integer, Orderhandle varc HAR ( -), Handletype INTEGER) RETURNS varchar ( -) CHARSET Utf8begin declare issmsnotify INTEGER; DECLARE isweixinnotify INTEGER; DECLARE Createtime DATETIME; DECLARE CTime DATETIME; Setissmsnotify=0; Setisweixinnotify=0; SetCtime=Now ();SetCreatetime=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

  PublicActionResult Editworkorder (formcollection collection) {Applog.info ("Editworkorder"); Try            {                stringOpenID = session["OpenID"].                ToString (); Applog.info ("to add OpenID when sending a ticket:"+OpenID); intPersonindex =DBAccess.Bussiness.GetStaffIndex (OpenID); intBusinessindex =convert.toint32 (session["Providerindex_businessindex"]. ToString (). Split ('/')[1]);//Road ... index                intSystemstatus =0; intProviderindex = Convert.ToInt32 (collection["providerlist"]);//Maintenance Units                stringAlarmindex = session["Alarmindex"]. ToString ();//Alarm number is provided above                intOrderlevel = Convert.ToInt32 (collection["Workorderlevel"]);//Priority Level                intFixperson = Convert.ToInt32 (collection["repairuserlist"]);//Maintenance Personnel                stringCurrentstatus = convert.tostring (collection["workorderstatuslist"]);//New                stringOrderhandle =string.                Empty; if(collection["Orderhandle"]!=NULL) {Orderhandle= collection["Orderhandle"];//Fault Classification Processing description                }                 stringHandletype = convert.tostring (collection["alarmhandletypelist"]);//Fault Handling ClassificationApplog.info ("Dispatch ticket parameter information:"+"--personindex:"+personindex+"--businessindex:"+businessindex+"--providerindex:"+providerindex+"--alarmindex:"+alarmindex+"--orderlevel:"+orderlevel+"--fixperson:"+fixperson+"--currentstatus:"+currentstatus+"--orderhandle:"+orderhandle+"--handletype:"+Handletype); Mysqlparameter[] Prams=Newmysqlparameter[ One]; prams[0] =NewMysqlparameter ("@personindex", Personindex); prams[1] =NewMysqlparameter ("@businessindex", Businessindex); prams[2] =NewMysqlparameter ("@systemstatus", Systemstatus); prams[3] =NewMysqlparameter ("@providerindex", Providerindex); prams[4] =NewMysqlparameter ("@alarmindex", Alarmindex); prams[5] =NewMysqlparameter ("@orderlevel", Orderlevel); prams[6] =NewMysqlparameter ("@exceptfixtime", -); prams[7] =NewMysqlparameter ("@fixperson", Fixperson); prams[8] =NewMysqlparameter ("@currentstatus", Currentstatus); prams[9] =NewMysqlparameter ("@orderhandle", Orderhandle); prams[Ten] =NewMysqlparameter ("@handletype", Handletype); using(Cnpsim_dbentities DbManager =Newcnpsim_dbentities ()) {                    varindex = dbmanager.database.sqlquery<string> ("Call Insertworkorderinfo (@personindex, @businessindex, @systemstatus, @providerindex, @alarmindex, @orderlevel, @ Exceptfixtime, @fixperson, @currentstatus, @orderhandle, @handletype)", prams); stringss=index. FirstOrDefault ().                    ToString (); strings =SS; returnRedirecttoaction ("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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.