http://floditt.blogspot.in/2011/08/using-adonet-with-x.html
public void adoConnection()
{
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;
System.Exception netExcepn;
;
new InteropPermission( InteropKind::ClrInterop ).assert();
sqlQuery = "SELECT * from INVENTTABLE";
//ceating the ConnectionString dynamically, based on the current connection
serverName = SysSQLSystemInfo::construct().getLoginServer();
serverName = "GBSQLDEV2"; //change to different server
catalogName = SysSQLSystemInfo::construct().getloginDatabase();
catalogName="MicrosoftDynamicsAx"; //change to different database
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);
//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("ItemId"));
}
//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 (Exception::Error) //should be more precise in a real-world application
{
connection.Dispose(); //disposing connection if it fails before opening it
}
catch(Exception::CLRError)
{
netExcepn = CLRInterop::getLastException();
info(netExcepn.ToString());
connection.Dispose();
}
command.Dispose();
CodeAccessPermission::revertAssert();
}
public void adoConnection()
{
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;
System.Exception netExcepn;
;
new InteropPermission( InteropKind::ClrInterop ).assert();
sqlQuery = "SELECT * from INVENTTABLE";
//ceating the ConnectionString dynamically, based on the current connection
serverName = SysSQLSystemInfo::construct().getLoginServer();
serverName = "GBSQLDEV2"; //change to different server
catalogName = SysSQLSystemInfo::construct().getloginDatabase();
catalogName="MicrosoftDynamicsAx"; //change to different database
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);
//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("ItemId"));
}
//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 (Exception::Error) //should be more precise in a real-world application
{
connection.Dispose(); //disposing connection if it fails before opening it
}
catch(Exception::CLRError)
{
netExcepn = CLRInterop::getLastException();
info(netExcepn.ToString());
connection.Dispose();
}
command.Dispose();
CodeAccessPermission::revertAssert();
}
