Deprecated
Data Access Layer (DAL) is deprecated. While the existing code keeps working, we recommend switching to Database API.
In the Enterprise Edition of ASPRunner.NET you can use multiple database connections in a single project. This article explains how you can access data from multiple databases in your events.
Method 1: using DAL functions
There are three options to refer to a table:
1. Finds the first matching table by its name in all connections, starting with the primary connection.
GlobalVars.dal.Table("table")
2. Finds the tables by the table name and schema name.
GlobalVars.dal.Table("table","schema")
3. Finds the table by the table name, schema name and connection name. The schema name can be left empty, and the last parameter is the connection name as it appears on Datasource tables screen.
GlobalVars.dal.Table("table","schema","connection")
A complete code example:
var rs = GlobalVars.dal.Table("carscars","","cars at localhost").QueryAll();
XVar data;
while (data = CommonFunctions.db_fetch_array(rs))
{
MVCFunctions.Echo(String.Format("{0}, {1} <br>", data["Make"], data["Model"]));
}
Method 2: using free form SQL Queries
1. Here is how to update all cars where make is Audi and the YearOfMake equals 2002:
GlobalVars.cman.byName( "cars at localhost" ).exec( "update carscars set yearofmake=2002 where make='Audi'" );
2. Example of a query returning data:
dynamic connection = GlobalVars.cman.byName( "cars at localhost" );
dynamic rs = connection.query( "select count(*) as c from cars where make='Audi'" );
XVar data = rs.fetchAssoc();
MVCFunctions.Echo( String.Format( "Number of Audi cars listed: {0}", data["c"]) );
See also:
•QueryResult object: fetchAssoc()
•Using DAL functions in projects with multiple database connections
