Subsonic operation instance

Subsonic operation instance

I. Query 1) Simple query  One method (decomposition constructor)  Subsonic.QueryQuery =Tpjworkday. Createquery (); Query. selectlist =Tpjworkday.Columns. Year +","+Tpjworkday.Columns. Yearmonth +","+Tpjworkday.Columns. Workdays; Query. addwhere (Tpjworkday.Columns. Year, yearnum ); Query. orderby = subsonic.Orderby. ASC (Tpjworkday.Columns. Yearmonth ); DatatableDt = query. executedataset (). Tables [0]; ReturnDT; Another method (LINQType) SqlqueryQuery =NewSelect(Tpjworkday.Columns. Year,Tpjworkday.Columns. Yearmonth,Tpjworkday.Columns. Workdays). From (Tpjworkday. Schema  ). Where (Tpjworkday.Columns. Year). isrelative to (yearnum). orderasc (Tpjworkday.Columns. Yearmonth ); DatatableDt = query. executedataset (). Tables [0];   2)LikeQuery Querybuilder. addwhere (Tbaseaddressbook.Columns. Name,Comparison. Like,"%"+This. Tbname. Text +"%");   3) Query a single value SqlqueryQuery =NewSelect(Tbasedepartment.Columns. Deptname). From (Tbasedepartment. Schema  ). Where (Tbasedepartment.Columns. Deptid). isrelative to (deptid ); ReturnQuery. executescalar (). tostring ();   4) Multi-table join query (the number of connected tables exceeds3It is best to use the view) By UnitIDGet user SqlqueryQuery =NewSelect(Tbaseuser. Useridcolumn,Tbaseuser. Usernamecolumn,Tbaseuser. Personnamecolumn,Tbaseuser. Phonecodecolumn,Tbaseuser. Emailcolumn) . From (Tbaseuser. Schema). innerjoin (Tlstuserindepartment. Useridcolumn,Tbaseuser. Useridcolumn) . Where (Tbaseuser. Enabledcolumn). isdue to (1) . And (Tlstuserindepartment. Deptidcolumn). isdue to (deptid) . Orderasc (Tbaseuser.Columns. Userid ); DatatableDt = query. executedataset (). Tables [0]; Note:Innerjoin(F2, F1. That is to say, when two tables are connected,FromThe table in the statement should be placed behind it.   5) Paging Query (Method 1,Query) // QueryMethod Paging,Sequence Number Column usageRowindex,OracleDatabase QueryQuery1 =NewQuery(Tbaseuser. Schema ); Query1.selectlist =Tbaseuser.Columns. Userid +","+Tbaseuser.Columns. Personname +","+Tbaseuser.Columns. Phonecode +","+Tbaseuser.Columns. Email +","+Tbaseuser.Columns. Userindex; Query1.addwhere (Tbaseuser.Columns. Issignedin,Comparison. Equals, 1 );//Logon status Query1.and (Tbaseuser.Columns. Enabled,Comparison. Equals, 1 );//Enable Query1.orderby =Orderby. ASC (Tbaseuser.Columns. Userindex ); IntSum = query1.getrecordcount ();//Used to calculate the total number of pages Query1.pagesize = 12;//Page size Query1.pageindex = pagenum-1;//Current page DatatableViewdata = query1.executedataset (). Tables [0]; If(Sum % 12 = 0) This. Lbpagecount. Text = (sum/12). tostring (); Else This. Lbpagecount. Text = (sum/12 + 1). tostring (); This. Lbpagenum. Text = pagenum. tostring ();   (Method 2,Sqlquery) // SqlqueryBy page. Use the serial number column.Row_numberOracleDatabase SqlqueryQuery =NewSelect(). From (Vwfsupervisor. Schema). Where (Vwfsupervisor.Columns. Userid). isattached to (createuserinfo (). userid). orderasc (Vwfsupervisor.Columns. Userid ); IntSum = query. getrecordcount ();//Total number of records before pagination Query. Paged (pagenum-1, 12 ); DatatableViewdata = query. executedataset (). Tables [0]; If(Sum % 12 = 0) This. Lbpagecount. Text = (sum/12). tostring ();  Else This. Lbpagecount. Text = (sum/12 + 1). tostring ();  This. Lbpagenum. Text = pagenum. tostring ();     Ii. insert, update, and delete Insert and update operations are based on objects.Save ()The update and insert operations are mainly reflected in the entity'sIsnewProperty, ifIsnew = true, The insert operation is executed, and the update operation is executed. When instantiating an object, suchTpjworkdayNewworkday =NewTpjworkday()At this timeNewworkday. isnew = true; When a parameter is input for instantiation, TpjattendancetimeAttendancetime =NewTpjattendancetime(Convert. Toint32 (Dt. Rows [0] ["Flowid"]);At this timeNewworkday. isnew = false.   1.Insert TpjworkdayNewworkday =NewTpjworkday(); Newworkday. Year =Convert. Toint32 (lbyear. Text ); Newworkday. yearmonth =Convert. Toint32 (This. Lbyear. Text) * 100 + (I + 1 ); Newworkday. Workdays = workstr; Newworkday. Save ();   NewInsert(Tlstuserinrole. Schema,False). Value (Tlstuserinrole. Useridcolumn, 2). Value (Tlstuserinrole. Roleidcolumn, 2) . Value (Tlstuserinrole. Createbycolumn, 3). Value (Tlstuserinrole. Createtimecolumn,Datetime. Now). Execute (); WhereFalseNot all fields. Default Value:True   2.Update The method for gradually updating a single field and passing in the primary key field value TpjattendancetimeAttendancetime =NewTpjattendancetime(Convert. Toint32 (Dt. Rows [0] ["Flowid"]); Attendancetime. begintime =Convert. Todatetime (tbbegintime. value ); Attendancetime. endtime =Convert. Todatetime (tbendtime. value ); Attendancetime. AM1 =Convert. Todatetime (dlam1.selectedvalue). to1_timestring (); Attendancetime. AM2 =Convert. Todatetime (dlam2.selectedvalue). to1_timestring (); Attendancetime. PM1 =Convert. Todatetime (dlpm1.selectedvalue). to1_timestring (); Attendancetime. PM2 =Convert. Todatetime (dlpm2.selectedvalue). to1_timestring (); Attendancetime. modifyby =Convert. Toint32 (request. querystring ["Userid"]); Attendancetime. modifytime = system.Datetime. Now; Attendancetime. Save ();   NewUpdate(Tpjinnernews. Schema). Set (Tpjinnernews. Newstitlecolumn). Similar ("123"). Where (Tpjinnernews. Newsidcolumn). isdue to (3 );   Composite primary key update method ,(Userid,Ascxid) NewUpdate(Tlstuserhomepage. Schema). Set (Tlstuserhomepage.Columns. Modifytime). Failed to (system.Datetime. Now). Set (Tlstuserhomepage.Columns. Modifyby). Modify to (3). Where (Tlstuserhomepage. Useridcolumn) . Isdue to (409). And (Tlstuserhomepage. Ascxidcolumn). isdue to (11). Execute ();   //Modify record,Get the object first, and then update TbasestatisticType =NewSelect(). From (Tbasestatistic. Schema). Where (Tbasestatistic.Columns. Typeid). issimilar (Int. Parse (geteqstring ("Typeid"))) . And (Tbasestatistic.Columns. Statid). isrelative (Int. Parse (This. Label3.text). executescalar (); Type. statname =This. Textbox1.text. Trim (); Type. stattabname =This. Label1.text; Type. statcolname =This. Label12.text; Type. statcolid =This. Label7.text; Type. datecolname =This. Label14.text; Type. wherepart =This. Label8.text; Type. Save ();   Update table set column = column +1 IntRecords =NewUpdate(Product. Schema) . Setexpression ("Unitprice"). Similar ("Unitprice * 3") . Where ("Productid"). Isdue to (1) . Execute ();   3.Delete QueryQ =Tpjworkday. Createquery (); Q. Where (Tpjworkday.Columns. Year,Convert. Toint32 (lbyear. Text )); Q. querytype =Querytype. Delete; Q. Execute ();   Tpjworkday. Delete (Tpjworkday.Columns. Yearmonth,"200901");       Iii. Transaction Processing Using(ShareddbconnectionscopeSP =NewShareddbconnectionscope()) { Using(TransactionscopeScope =NewTransactionscope()) { //Database Operations // You cannot use the auto-increment operation of the platform. Otherwise, an error is returned: {"Unable to loadDLL"Oramts. dll":The specified module cannot be found.(Exception fromHresult: 0x8007007e)."} // Caca. businesslogic. util. counterutil. increment ("entityinnernews ") Scope. Complete (); } }   List<Sqlquery> List =NewList<Sqlquery> (); List. Add (NewDelete(). From (Tbaseaddressbook. Schema). Where (Tbaseaddressbook.Columns. Serialno). isdue to (LBL. Text )); List. Add (NewDelete(). From (Tbaseaddressbookusual. Schema). Where (Tbaseaddressbookusual.Columns. Serialno). isdue to (LBL. Text )); Sqlquery. Executetransaction (list );   List<Insert> List = newList<Insert> ();   Stored Procedure: // Storedprocedure SPD = newstoredprocedure ("getdatatable "); // SPD. Command. addparameter ("@ userid", userid ); // SPD. Command. addoutputparameter ("@ dataresult "); // SPD. Execute ();       IV,SubsonicDirect executionSQLStatement(Webshell: When an unsolved problem occurs, write it directly.SQLStatement) SubsonicDirect executionSQLYou can use the following statements:   //RunSQLStatement, returnDatatable Querycommand qc = newquerycommand (strsql. tostring (), null );   Dt = dataservice. getdataset (QC). Tables [0];     Public void inline_simple () { Querycommandcmd = new inlinequery (). getcommand ("select productid from products "); Assert. istrue (CMD. commandsql = "Select productid fromproducts "); }   Public void inline_withcommands () { Querycommandcmd = new inlinequery () . Getcommand (@ "selectproductid from products Whereproductid = @ productid ", 1 );   Assert. istrue (CMD. Parameters [0]. parametername = "@ productid "); Assert. istrue (INT) cmd. Parameters [0]. parametervalue = 1 ); }   Public void inline_ascollection () { Productcollectionproducts = Newinlinequery () . Executeascollection <productcollection> ( @ "Selectproductid from products Whereproductid = @ productid ", 1 ); }     5. return values of common methods Execute (),Returns the number of affected records. // Executescalar (), ReturnsObject,(System. DecimalType value) Executesingle <T> (), ReturnTType object. For example, you can use this method to obtain object objects that meet the requirements. TdicitemItem =NewSelect(). From (Tdicitem. Schema). Where (Tdicitem.Columns. Dicitemid). isrelative to (2) . And (Tdicitem.Columns. Dictypeid). ispolicto (1030). executesingle <Tdicitem> ();   Executedataset (),Returns a record set.Dataset Executetypedlist <t> ();ReturnList <t>,Object List, such List <Product> Products = newSelect(). From <Product> () . Where (Product. Productidcolumn). isdue to (4) . Paged (1, 30) . Executetypedlist <Product> (); Transfer from othersArticle!