I created a job to get data from external database, i use following code:
static void DatabaseConnection(Args _args)
{
str serverName;
str catalogName;
str ConnectionString;
str sqlQuery;
//ADO.Net via CLR objects. Requires referenced System.Data
System.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder;
System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlParameterCollection parameterCollection;
System.Data.SqlClient.SqlDataReader dataReader;
;
new InteropPermission( InteropKind::ClrInterop ).assert();
//Defining any SQL-Server 200x query…
//use parameter instead of variables, so that the database can precompile it
//and estimate an optimal execution plan
serverName = strfmt(‘WINDYNSQL’);
catalogName = strfmt(‘MicrosoftDynamicsAX’);
sqlQuery = strfmt(‘Select * from CustTable’);
//ceating the ConnectionString dynamically, based on the current connection
serverName = SysSQLSystemInfo::construct().getLoginServer();
catalogName = SysSQLSystemInfo::construct().getloginDatabase();
connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
connectionStringBuilder.set_DataSource(serverName);
//here it becomes interesting. The current execution context will be used to
//establish a conection. If this is executed by a batch, this is the user
//configured for the batch
connectionStringBuilder.set_IntegratedSecurity(true);
connectionStringBuilder.set_InitialCatalog(catalogName);
//all this to prevent working with a fixed string…
ConnectionString = connectionStringBuilder.get_ConnectionString();
//initializing connection and command
connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
command = new System.Data.SqlClient.SqlCommand(sqlQuery);
command.set_Connection(connection);
parameterCollection = command.get_Parameters();
parameterCollection.AddWithValue(“US-001”, “AccountNum”);
//executing SQL-query
try
{
//open within catch, so that the object can correcly be disposed
//all these try-catch are quite ennoying in X++, but this because
//X++ does not know finally…
connection.Open();
try
{
//All code after the open must be in a seperate catch, so that the
//open connection-object can correcly be disposed.
dataReader = command.ExecuteReader();
while(dataReader.Read())
{
//use the named columns instead of index.
info(dataReader.get_Item(“AccountNum”));
dataReader.NextResult();
}
//Dispose ADO.Net objects ASAP
dataReader.Dispose();
}
catch //should be more precise in a real-world application
{
//if exception occures while reading, DataReader need to be
dataReader.Dispose();
}
}
catch(Exception::CLRError) //CLR exception need to be handled explicitely
//otherwise they might be ‘lost’. Happy copy&pasteing
{
//if exception occures while reading, DataReader need to be
dataReader.Dispose();
}
catch //should be more precise in a real-world application
{
connection.Dispose(); //disposing connection if it fails before opening it
}
catch(Exception::CLRError)
{
connection.Dispose();
}
connection.Dispose();
CodeAccessPermission::revertAssert();
}
It shows error while running anyone help to fix this problem
Thanks.