X++ is, as you know, a powerful language which allows you to do almost all reasonable things with your data. But, sometimes you need to use the full power of your SQL-Server, or access an
external database. This can be done with
ODBC or
ADO. But ODBC is not enabling you the full capacities of your database and ADO is not the most
performant way to access databases. Instead of these two possibilities it is in most situation handier to use the
ADO.Net instead of ADO (I
posted an example in 2008 on msdn). The only thing to do in Dynamics Ax 2009 is to reference the System.Data assembly, which inclued the SQL-Server client implementation. The .Net framework includes a client for
Oracle and
ODBC. Using the Oracle-client requires the assembly System.Data.OracleClient, the ODBC is part of the System.Data assembly, like the SqlServer-client. Other providers can be found
here.
This sample gives you a pretty good idea of how to implement this on your own:
public static server void ExecuteADONETQuery()
{
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
sqlQuery = "SELECT DISTINCT TOP 3 PDT.ACCOUNTRELATION, PDT.ITEMRELATION, PDT.DATAAREAID FROM PRICEDISCTABLE PDT" +
" LEFT OUTER JOIN INVENTTABLE ON (PDT.ITEMRELATION = INVENTTABLE.ITEMID " +
" AND PDT.ITEMCODE = 0 " +
" AND PDT.DATAAREAID = INVENTTABLE.DATAAREAID) " +
" WHERE PDT.DATAAREAID = @DATAAREAID ";
//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...
//on my computer, this would be equal to
//"Data Source=DYNAMICSVM;Initial Catalog=DynamicsAx1;Integrated Security=True"
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);
//initializing the parameter @DATAAREAID with AddWithValue
//http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx
//To prevent using the System.Data.DBTypes. These might not work on the server.
//This will automatically set the correct DBType during the runtime and prevent running
//into this pitfall...
parameterCollection = command.get_Parameters();
parameterCollection.AddWithValue("@DATAAREAID", "CEE");
//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("ITEMRELATION"));
}
//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();
}
connection.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();
}
command.Dispose();
CodeAccessPermission::revertAssert();
}
