Using DAL functions in projects with multiple database connections

Categories ASP.NET, News, PHP, PHP Code Generator, PHP Form Generator, Tutorials

PHPRunner 8, ASPRunnerPro 9 and ASPRunner.NET 8 added an option to use multiple database connections in single project. This article explains how you can access data from multiple databases in your events.

Method 1: using DAL functions

PHPRunner

We now have three options to refer to a table

1. $dal->Table("table")

finds first matching table by it's name in all connections, starting with the primary connection.

2. $dal->Table("table","schema")

schema name helps to identify tables with identical names located in different schemas in databases like Oracle, Postgre and SQL Server.

3. $dal->Table("table","schema","connection")

Schema name can be left empty. Last parameter is connection name as it appears on 'Datasource tables' screen.

A complete code example:

$table = $dal->Table("cars","","cars at localhost");
$rs = $table->QueryAll();
while ($data = db_fetch_array($rs))
{
echo $data["Make"].", ".$data["Model"]."
"; }

ASPRunnerPro

We have 4 options to access a table object:

1. dal.Table("table")

finds first matching table by it's name in all connections, starting with the primary connection.

2. dal.TableSchema("table", "schema")

finds table by table name and schema name

3. dal.TableSchemaConn("table", "schema", "connection")

finds table by table name, schema name and connection name

4. dal.TableConn("table", "connection")

finds table by table name and connection name

A complete code example:

set data = dal.TableConn("carscars", "cars at localhost").QueryAll()
while not data.eof 
	Response.Write data("Make") & ", " & data("Model") & "
" data.MoveNext wend data.close : set data=nothing

ASPRunner.NET (C#)

We have three options to refer to a table

1. GlobalVars.dal.Table("table")

finds first matching table by it's name in all connections, starting with the primary connection.

2. GlobalVars.dal.Table("table","schema")

schema name helps to identify tables with identical names located in different schemas in databases like Oracle, Postgre and SQL Server.

3. GlobalVars.dal.Table("table","schema","connection")

Schema name can be left empty. Last parameter is connection name as it appears on 'Datasource tables' screen.

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} 
", data["Make"], data["Model"])); }

Method 2: using free form SQL Queries

Update all cars where make is 'Audi' making YearOfMake equals 2002

PHPRunner

$cman->byName("cars at localhost")->query("Update carscars set yearofmake=2002 where make='Audi'");

ASPRunnerPro

cman.byName_p1("cars at localhost").query_p1("Update carscars set yearofmake=2002 where make='Audi'")

1 thought on “Using DAL functions in projects with multiple database connections

  1. Nice tips, i cant find this in help/manual. I hope help/manual will be updated soon.

    Btw how to select custom query, and how to fetch them? because in multiple database always use primary connection.(in above only work to select all fields, not custom or more complex sql)

    Ex: SELECT SUM(Amount) FROM Orders WHERE OrderID=’1111′

Leave a Reply

Your email address will not be published. Required fields are marked *