|
Data Access Layer (DAL) is
created to simplify and formalize work with events. This will be
extremely helpful for those who don't know SQL. Also DAL allows for a
merge of events from different templates (see Add
template to project option) and transfer events from one project
to another.
Each table is presented as ASP class, all fields are ASP variables
declared in this class.
Naming convention
There are some rules how to define the field and tables names in the
event code:
-
field or table name is not changed in the code if field or table name
doesn't contain spaces any other other "bad" characters and
do not coincide with ASP reserved words. "Good" name
contains english alphabet letters, digits and underscores and starts
with a letter.
-
to access table with "bad" name use Table
array i.e. dal.Table("Order details")
-
to access field with "bad" name use Value
array i.e. dal.Cars.Value("Year of make")
Good name examlpes
Table Cars: dal.Cars
Field YearOfMake: dal.Cars.YearOfMake
"Bad" name examples
Table "Order Details": dal.Table("Order
Details")
Table Function (reserved word): dal.Table("Function")
Field
Year of make: dal.Cars.Value("Year of make")
Value() and Param()
Value() and
Param() arrays provide access to field values. Value("FieldName")
refers to field to be updated or added, Param("FieldName")
is used in the where clause of update query. This allows you to avoid
the confusion when the same field appears in the field list and in
WHERE clause.
Example:
dal.UsersTable.Param("FirstName")="Bob"
dal.UsersTable.Value("FirstName")="Jim"
dal.UsersTable.Update()
SQL: Update
UsersTable set `FirstName`='Jim' where `FirstName`='Bob'
Data Access
Layer functions:
Insert/Update/Delete
functions are used to add, edit or delete record(s) in the database.
1. Add() - inserts a new
record into the database
dal.EventsTable.event="First event"
dal.EventsTable.public="yes"
dal.EventsTable.Add()
Insert into EventsTable (event,public)
values ('First event','yes')
2. Update() - updates one or
more records in the database
dal.UsersTable.Param("ID")=32
dal.UsersTable.Value("First Name")="Jim"
dal.UsersTable.Update()
SQL: Update UsersTable set `First
Name`='Jim' where ID=32
3. Delete() - deletes one or
more records from the database
UsersTable.ID = 32
UsersTable.Delete()
SQL: Delete from UsersTable where ID=32
or
dal.UsersTable.FirstName="Bob"
dal.UsersTable.Email="test@test.com"
dal.UsersTable.Delete()
SQL: Delete from UsersTable where
Name='Bob' and Email='test@test.com'
Query/QueryAll/FetchByID
functions are used to select records from database. These functions
return a resource on success, or FALSE on error. To fetch a returned
row as an associative array use db_fetch_array function.
There are some rules how to define field and tables names in the
event code:
-
field or table name is not changed in the code if field or table name
doesn't contain spaces any other other "bad" characters and
do not coincide with ASP reserved words. "Good" name
contains english alphabet letters, digits and underscores and starts
with a letter.
-
all spaces and "bad" characters should be replaced with
undescores. to access field with "bad" name replace all
"bad" characters with underscores i.e. dal.Cars.Year_of_make
-
table name should have prefix tbl if table name coincides with ASP
reserved word.
|
Example:
select and print all orders for John Sample.
Code:
set rs = dal.OrdersTable.Query("Customer='John
Sample'","OrderID
DESC")
while not
rs.eof
Response.write
"Order " & rs("OrderID")
& " was placed " & rs("OrderDate")
& " by " & rs("Customer")
& "<br>"
rs.movenext
wend
Output:
Order 10456 was
placed 10/10/2008 by John Sample
Order 10512 was
placed 09/09/2008 by John Sample
Order 10689 was
placed 08/08/2008 by John Sample |
4. Query(where,
orderby) - selects records from database sorting data by
orderby field or fields and return recordsets
set rs = UsersTable.Query("Name
like '%Jim%'","Email DESC")
SQL: select * from UsersTable
where Name like '%Jim%' order by Email DESC
5. QueryAll() - selects all records
6. FetchByID()
- selects one or more records matching the condition
UsersTable.ID=32
set rs = UsersTable.FetchByID()
SQL: select * from UsersTable
where ID=32
7. TableName() - returns
table name. This function is used for complex query with calculated
fields or joined tables.
sql = "select
sum(UnitsInStock) as total, concat(Category,' ',ProductName) as
FullProductName from "
sql = sql
& dal.Products.TableName()
& " group by country"
|
