Connect external database and get data

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

7266.1.png

Thanks.

Which line is 83?

dataReader.Dispose();

I bold that Martin

I saw the bold text, but I wasn’t sure what it means, as you didn’t mention it.

The errors says that dataReader is null. When you debugged the code (I assume you know how to use the debugger), did you see that? If it’s so, you already know why it fails - you can’t call methods on empty references.

It happens when an error is thrown before dataReader can be instantiated, such as when connection.Open() fails. You can check if dataReader exists before trying to use it, restructure your code so this error handling code is called only when dataReader exists or move your logic to a C# class library and utilize the using statement.

k thanks. We can’t do from AX job ah?

What do you mean?

You suggest to go with C# class library and utilize the using statement, but in X++ when i used using statement it shows syntax error.
So i asked there is any other way to connect external database from X++

First of all, if you read that there is a using statement in C# and you go and try to use the keyword in a completely different language, you shouldn’t be surprised when it fails. If it worked in X++ in AX 2012, I wouldn’t have mentioned C# at all. (By the way, it’s supported in X++ in AX 7).

Secondly, the fact that X++ doesn’t have the using keyword doesn’t mean that can’t connect to an external database from X++. That was a logical shortcut on your side. Even if we talk about your particular solution and the bug there, I suggested two other approaches that don’t depend on using.
Utilizing C# is just one possible way, although pretty powerful.

Thanks very much Martin.