Querying an external SQL database using .NET Interop

In one of the Dynamics AX implementations I have worked on, there was a requirement to query an external SQL database in order to integrate with an information system. Although the preferred way of integration is web services, in my case there were no services exposed by that system which AX could consume. Therefore the only option I had was to query the database directly.

I used the .NET Framework classes to connect and query the SQL database. See the code below for an example of how you can use classes in the System.Data.SqlClient namespace. You’ll need to replace the Database_Name and Server_Name placeholders within the connection string to make it work. Note that depending on how your SQL security is configured, you might be required to provide user ID and password in the connection string.

static void ConnectSqlClient(Args _args)
{
str connectionString;
str query;
TableName tableName;

System.Exception exception;
System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlDataReader dataReader;

connectionString = “Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Database_Name;Data Source=Server_Name;”;

try
{
connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();

query = “SELECT NAME FROM SYS.TABLES ORDER BY NAME”;
command = new System.Data.SqlClient.SqlCommand(query, connection);

dataReader = command.ExecuteReader();

while (dataReader.Read())
{
tableName = dataReader.get_Item(“NAME”);
info(tableName);
}

dataReader.Close();
connection.Close();
}
catch (Exception::Error)
{
connection.Close();
}
catch (Exception::CLRError)
{
connection.Close();

exception = CLRInterop::getLastException();

if (exception != null)
{
info(exception.ToString());
}
}
}