Raw Sql

Lowe-level data access for mobile apps

Dynamics Mobile Apps can benefit from using the built-in relational database.

Both mobile apps and backend apps have access to a dedicated database, automatically managed by the system.

The developer can "define" the structure of the app's database by authoring Business Object Definition files. These files are then used by the system to automatically create or upgrade the schema of the database during the app deploy.

While the preferred method to access the relational database is by using Business Objects, sometimes it is vital for the app to use raw Sql queries to work with the data.

This is possible by using the SqlQueryService class.

WARNING: SqlQueryService class can be used in mobile apps only. Backend apps does not allow raw sql access to the database. Backend apps allow only access via the DbQuery class.

The SqlQueryService is a service, which means that the developer must not create it directly, but rather declare it as a member of the view or service and it will be automatically injected by the system.

Let's see a piece of code which uses SqlQueryService class.

import { SqlQueryService, Service } from '@dms';
import { Customer } from '@dms-bo';

@Service()
export class MyService {

  //declare the dependency  
  constructor(private SqlQueryService: SqlQueryService){
  }
  
  public async getFirstNameOfFirstCustomer(): Promise<string> {
     
      //get the actual phsycial name of the table for Customer
      const customerTableName = Customer.boTableName;
      //execute the sql
      const allCustomers = await this.SqlQueryService.execute(`SELECT [firstName] FROM ${customerTableName}`);
      //return the result
      if(allCustomers && allCustomers.length>0)
          return allCustomers[0].firstName;
      else
          return null;
  }
}

The code declares the SqlQueryService class as a member of MyService class in the constructor. This instructs the system that we want the SqlQueryService instance to be automatically created for us.

On line 14, we obtain the physical name of the table where the customer business objects are stored and then on line 16 we execute our Sql and waiting for the result.

Note that the execute method of SqlQueryService class returns array of flat objects of unknown type. The actual properties of the objects returned depends on the Sql expression.