ASP implements ActiveRecord data query update v1.0

ASP implements ActiveRecord data query and update

introduction:

Students who have used the PHP framework ThinkPHP or CI framework all know that the data query ActiveRecord that comes with these frameworks is used to query data and update data. It is convenient.

Not only them, many PHP frameworks support ActiveRecord, which eliminates the need to write too many tedious original SQL query statements and makes project maintenance more convenient.

Now we also use it for simulation on Asp code. It has been successfully applied to the application of "AppCore based on AspBox framework".

The application core code is separated from AppCore.

================================================== ===============

[1]: Query data

a. Get the results:

View the output SQL statement: Dao.getSQL() usage

AliasDao.lastSQL()

e.g.

Response.WriteDao.T("media").Top(10).lastSQL

Dao.Query() usage

dimrs

dimtb_prefix:tb_prefix=Dao.tbPrefix'data table prefix

Setrs=Dao.Query("selecttop10id,namefrom@media").Result()

Equivalent to: Setrs=Dao.Query("selecttop10id,namefrom"tb_prefix"media").Result()

Dao.List() usage

dimlist

list=dao.t("media").select("id,name").top(10).List()

Note: list' returns a two-dimensional array

dimi,id,name

Fori=0ToUbound(list,2)

id=list(0,i)

name=list(1,i)

Response.Writeid":"name""

Next

Dao.Result() usage

Alias ​​Dao.GetRs() or Dao.Fetch()

dimRs

SetRs=Dao.T("media").select("id,name").top(10).Result()

ab.traceRs

DoWhilenotRs.eof

Response.WriteRs("id")":"Rs("name")""

Rs.MoveNext

Loop

Dao.Row(n) usage (n means to get the n+1th row of data) Dao.Row(0) means to get the first row of data

dimRs

SetRs=Dao.Query("selecttop10id,namefrom@media").Row(4)'Get the 5th row of data

IfNotRs.EofThen

Response.WriteRs("id")":"Rs("name")

EndIf

b. Query conditions:

The usage of Dao.T() is the same as the usage of Dao.From()

Dao.Select() usage is the same as Dao.Field() usage

Dao.Where() usage and Dao.Find() usage:

Note: The usage of Where is basically the same as the usage of Find, both of which filter data based on conditions.

Find usage is more flexible. You can use Dao.Find("1,3,5") to query data with automatic numbers (id) 1,3,5

Dao.Find("1,3,5") can also be written in the following way:

Dao.Find(Array(1,3,5))

Dao.Find("idin(1,3,5)")

Dao.Where("idin(1,3,5)")

Dao.Where("id=1orid=3orid=5")

Dao.Limit() usage:

Simulate mysql limit(offset,rows) usage

usage:

limit(0,0) takes all data (data from the beginning to the end of the first (0+1=1) data)

limit(0,1) takes 1 piece of data starting from (the 1st piece of data (0+1=1), that is: 1st to 1st piece) (1 piece in total)

limit(3,0) takes the data from the beginning to the end of the 4th (3+1=4) data, that is: the 4th to the last one)

limit(2,5) represents the 5 pieces of data starting from the 3rd piece of data (2+1=3), that is: 3rd to 7th piece) (5 pieces in total)

limit(1,2) represents the 2 pieces of data starting from the 2nd piece of data (1+1=2), that is: 2nd to 3rd pieces) (2 pieces in total)

limit(4,6) can be calculated like this: it means items 4+1=5 to 4+6=10) (6 items in total)

@Note: Limit usage can only be used for query, not for Rs data update!

Dimrs

Setrs=Dao.T("test").Where("pid=1").Limit(0,5).Fetch()

Attached, query example:

Dao.T("test").field("id,name").where("id>0").order("createtimedesc,iddesc")

Dao.T("test").select("id,name").top(10)

Dao.T("test").select("id,name").order("Rnd(ID)")

Dao.t("user").where("id<>{rq:id}andemail='{rq:email}'").find("id>5").result()

limit condition query

Setrs=Dao.T("media").field("id,name").limit(1,5).Fetch()

Complete query example:

dimsql

sql=dao.select("id,name").from("media").where("id>10").join("table2").on("table2.cid=user.cid").order ("iddesc,cidasc").group("id").having("id>5").union("select*fromtablexx").getSQL

===============

[2]: Modify data

Add data: Dao.Add()

Modify data: Dao.Update() or Dao.Set()

Delete data: Dao.Del() or Dao.Delete()

Smart save data (add or modify): Dao.Save()

Update a field value Dao.setField()

Dao.AffectedRows() or Dao.affRows() returns the number of affected rows

dimo_ds,arr(5),str

Dao.t("user").where("id=14").del()

Dao.t("user").delete("1,3,5")

Dao.t("user").where("id=13").set(Array("username:55"))

Dao.t("user").where("id=13").setField("name","aaa1111")

Dao.query("update@usersetusername='ttt'whereid<7").exec()

Dao.query("delete*from@userwhereid>7").exec()

response.writeDao.AffectedRows()'number of rows affected

Dao.t("user").add(Array("username:tttt","password:5fe84ad35fb5f95b","email:[email protected]"))

arr(0)="id>5"

arr(1)="time>#1986#"

arr(2)="oradd=555"

response.writedao.t("media").where(arr).lastSQL()

Seto_ds=Server.CreateObject("scripting.Dictionary")

o_ds("id")="15"

o_ds("name")="sss"

o_ds("ddds!=")="dsd"

response.writedao.t("media").where(o_ds).lastSQL()

response.writedao.t("media").Find(o_ds).lastSQL()