Difference between Refresh(), Reread(), Research() and ExecuteQuery() in AX 2012

In X++ we have the below methods for fetching data after any changes are made, below is the short description of what they are and when to use each one.

Refresh() refreshes the user view with whats stored in the caches. This does not touch the DB.
Use this after any form change has been made through code.

ReRead() fetches only the current record from database and does not re read the complete datasource.
Use this when you need to update only the current record after modifying any value.

ReSearch() will execute the same query again and fetch the results from the database.
Use this if you need to get the current most data from database.

ExecuteQuery() will run the query again just like research does but it will also take any query changes into account.
Use this is you have modified the query on run-time and need the updated results according to the new query.

Connecting to an External Database from AX 2012 using ODBC

To connect to an external database with X++ you can use Open Database Connection (ODBC) protocol through the OdbcConnection class among other ways. This approaches requires you to setup us a Data Source Name(DSN) on your server machine with the required access. Below is the code sample for how you can achieve this.To read more about this visit MSDN.

 

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("databaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

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();
}