TableType::ExternalSQL

One very cool feature in NAV2016 is the table type “ExternalSQL”. In older versions you had to use a view in SQL for each table and set the property LinkedObject. Now you don’t have to do that any more.

Let’s take a look at it:

table

Here you can see how you can use TableType, ExternalName and ExternalSchema instead of LinkedObject. In NAV this table is called “My Table” but in SQL it’s called “mytable”.

You can also set ExternalName for fields. Below I want to use the field name “No.” although it’s called “number” in the SQL table.

field

 

The connection to the server, database and the user credentials is controlled with C/AL.

Here’s a sample:

UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName', 'Data Source='MyServerName';Initial Catalog='MyDatabaseName';User ID='MySQLUserID';Password='MyPassword');

SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

This code will first try to unregister any existing connection to the database I want to connect to. Then it will register using a connectionstring (which you of course shouldn’t hard code like this) and then it will set the connection as the default connection.

The connection needs to be established on each login so it would make sense to store server name, database name and credentials in a setup table and register the datasource on login.

According to the MSDN documentation any rollbacks will also take place in the external tables. I haven’t tested that myself though.

I’m on my first project using this feature so I’ll probably update this post later on when I’ve got some more time on this functionality.

12 Responses to “TableType::ExternalSQL”

  1. WordPress › Error

    There has been a critical error on your website.

    Learn more about debugging in WordPress.