Subsonic operation instance

Source: Internet
Author: User
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];


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];



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 ();


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 ();


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.



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



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 ();



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!

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: 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.