DataLink interface to build connection strings and use in Dynamics AX [Connection classes using X++]

Friends, As we all know that we can connect to external databases using Connection classes, System.Sql , LoginProperty Classes etc

Usually we need to build the connection string based on the provider and the Data source names.
Example :

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DynamicsAx1_Dev;Data Source=HYDSRV

DataLinks interface will help us to select the providers from the list of providers, and based on the provider, we can build our connection strings and connect to the required external systems.

Below job will help to open the DataLinks interface using COM and based on the selected provider , we can select our databases and build easily connection strings.

Please Note: Query in the below job is an example query on dummy table. You can write your own sql queries and execute accordingly. I will be connecting to SQL provider and will use CCADO classes to connect to DB and execute query.

static void SR_DataLinks(Args _args)
{
COM dataLink = new COM(‘DataLinks’);
COM adoconnection;
CCADOConnection connection;
CCADOCommand adoCom;

str commandStr;
str connectionString;
str value;
int idx;
;

connection = new CCADOConnection();
adoconnection = dataLink.promptNew();
if (adoconnection)
{
connectionString = adoconnection.connectionString();
//Connection String which you have selected as the DSN
info(connectionString);
}

Connection.open(connectionString);
adoCom = new CCADOCommand();

adoCom.commandText(“insert into tableName (CustAccount , CustName) values (‘400′,’Sreenath’)”);
adoCom.activeConnection(connection);
adoCom.execute();
connection.close();

// The below code will help you to prompt edit the connection
/*if (dataLink.promptEdit(adoconnection))
{
info(adoconnection.connectionString());
}*/
}

Once you run the job, you will get the DataLinks interface where you can select your provider from the list of providers as shown below

Once you click on next button, you will be asked to select the Data source, authentication and the database name. You can even test the connection by clicking on the Test connection button as shown below. On clicking on Ok Button, we can get the connection string as shown in the above job.

Connection string in the inflog outuput:

Note: Everytime it is not recommended to open the DataLinks screen, rather you can have a table and a form which will store connection strings and can have a button which will help to open the DataLinks form. Customizations and beautifications are left to you and can play with it.

Its always recommended or good practise to use Sql Execute permission classes before you exceute any SQL statements.

Good Day!!
Sreenath

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: