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:
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.
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”