Query data from an external database
Collaborate with us
Edit this page on GitHub
Use the RuntimePublic.Db API to query data from an external database following this pattern:
- Retrieve a
DatabaseProvider
from theDatabaseAccess
instance. - Use the provider to create a transaction.
- Use the transaction to create a
Command
. - Use the
Command
to create command parameters. - Execute the
Command
. - Retrieve the
Command
results.
Example
In the following example there's a connection called Oracle DB Connection
accessing an external database. You define the connection in the environment management console (Service Center).
The code below gets the records of people with less than 20 years old from the PERSON
table in an external Oracle database using the previously obtained connection.
C# code sample
using OutSystems.RuntimePublic.Db; // [...] // Retrieve the DatabaseProvider for the external database. // You need to have a Database Connection called "Oracle DB Connection" configured // in Service Center. DatabaseProvider dbaProvider = DatabaseAccess.ForExternalDatabase("Oracle DB Connection"); // We will use a separate transaction to execute the query // A committable transaction can be committed and rolled back explicitly using (CommittableTransaction commitableTransaction = dbaProvider.GetCommittableTransaction()) { using (Command cmd = commitableTransaction.CreateCommand("SELECT AGE FROM PERSON WHERE AGE < 20")) { // Results are read using a standard IDataReader object using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader.GetInt32(0)); } } } }