Loading...
 

Data Access Layer (DAL)

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 and transfer events from one project to another.

Each table in DAL is presented as PHP/ASP class, all fields are PHP/ASP variables declared in this class.

Variables/Arrays

PHP variable/arrayASP variable/arrayDescription
Table("TableName")Table("TableName")Provides access to a table. Example:
PHP
global $dal;
$tblCars = $dal->Table("Cars");
ASP
dal.Table("Cars")
Value["FieldName"]Value("FieldName")Provides access to the field values. Refers to the field to be updated or added. Example:
PHP
global $dal;
$tblCars = $dal->Table("Cars");
$tblCars->Value["Make"]="Volvo";
ASP
dal.Table("Cars").Value("Make")
If the field name contains only English alphabet letters, digits and underscores (doesn't contain spaces and special characters), starts with a letter and doesn't coincide with PHP/ASP reserved words, you can also use the following notation to access the field:
PHP
global $dal;
$tblCars = $dal->Table("Cars");
$tblCars->Make="Volvo";
ASP
dal.Table("Cars").Make
Param["FieldName"]Param("FieldName")Provides access to the field values. 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:
PHP
global $dal;
$tblUsers = $dal->Table("UsersTable");
$tblUsers->Param["FirstName"]="Bob";
$tblUsers->Value["FirstName"]="Jim";
$tblUsers->Update();
ASP
dal.Table("UsersTable").Param("FirstName")="Bob"
dal.Table("UsersTable").Value("FirstName")="Jim"
dal.Table("UsersTable").Update()
The corresponding SQL query:
SQL
Update UsersTable set 'FirstName'='Jim' where 'FirstName'='Bob'

Methods

MethodDescription
AddInserts a new record into the database.
CustomQueryRuns custom SQL query.
DeleteDeletes one or more records from the database.
FetchByIDSelects one or more records matching the condition.
QuerySelects records from database sorting data by orderby field or fields and return recordset.
QueryAllSelects all records.
TableNameReturns table name. This function is used for complex query with calculated fields or joined tables.
UpdateUpdates one or more records in the database.
UsersTableNameReturns properly formatted login table name.
whereAddAdds new AND condition to the existing WHERE clause.

Remarks
You can use shortened DAL notation:

ASP
set dalCars=dal.Table("Cars")
set data=dalCars.Query(strWhereClause,"")
Response.Write data("Make")

Examples

  • Before deleting a record check if related records exist
  • Dynamic SQL Query
  • Redirect to user info edit page
  • Show data from master table on detail view/edit/add page
  • Show list of customer orders
  • Update multiple tables