Connecting to an External Database from AX 2012 using SqlClient classes

Here is how you can connect to an external database using X++ SqlClient class. This is among the few others approaches you can connect to an external database.

public System.Data.SqlClient.SqlConnection getConnection()
{
str connectionString;

System.Exception exception;
System.Data.SqlClient.SqlConnection connection;

connectionString = “Persist Security Info=False;User ID=UserID;Pwd=Password;Initial Catalog=DatabaseName;Data Source=localhost;”;

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

return connection;
}
catch (Exception::Error)
{
error(“An exception has occurred.”);
}
catch (Exception::CLRError)
{
error(“A CLR exception has occurred.”);

exception = CLRInterop::getLastException();

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

return null;
}

To run a query after a successful connection, use this code:

System.Data.SqlClient.SqlConnection conn;
System.Data.SqlClient.SqlCommand cmd;
System.Data.SqlClient.SqlDataAdapter da;
System.Data.SqlClient.SqlDataReader dr;
System.Exception netExcepn;

str sql, attributeName, attributeValue;
str 30 materialCode;

mzkConnectToDB = new MzkConnectToDB();
conn = mzkConnectToMSDgen.getConnection();

sql = strFmt(“select * from tablename;”);
cmd = new System.Data.SqlClient.SqlCommand(sql,conn);

dr = cmd.ExecuteReader();

while(dr.Read())
{
try
{
materialCode = dr.get_Item(‘material_id’);

}
}
catch(Exception::Error)
{
error(“@SYS85688”);
}
}

//Close the connection.
dr.Close();
conn.Close();
}

Advertisement