Create a direct SQL query interface… proof of concept.

Hi, for many times I’ve investigate the possibility to create a simple interface for the users to have a select directly in the tables of AX. I know that this it’s not very good thing to do and the best way it’s creating different tools, form or other to investigate about data but I think anyway could be useful for having a workaround to avoid the creations of many simple interrogation about the data in AX.

This could be only a POC (otherwise said proof of concept) and must be re-engeeneering in best way.

First thing I’ve follow this article for creating a basic form of visualization

https://community.dynamics.com/crm/b/dynamics101trainingcentercrm/archive/2015/03/04/using-managed-host-control-in-microsoft-dynamics-ax-2012

as you could be follow istruction at this point you have a simple .NET grid.

In the form I’ve add the following methods related to a button:

Code in button

void clicked()
{
super();
element.runQuery();
}

methods in form

public class FormRun extends ObjectRun
{
System.Windows.Forms.DataGridView dataGridView;
}

public void createColumn(ResultSet rs)
{
//create columns starting from a resultset
System.Windows.Forms.DataGridViewColumnCollection columnCollection;
int colcount;
str 255 colname;

columnCollection = dataGridView.get_Columns();

for(colcount=1; colcount<=rs.getMetaData().getColumnCount(); colcount++)
{
colname = rs.getMetaData().getColumnName(colCount);
columnCollection.Add(colname,colname);
}

}

public void createRows(ResultSet rs)
{
int colcount;
System.Windows.Forms.DataGridViewRowCollection rowCollection;
System.String[] strValues;
boolean columnCreated = false;

//fill the rows of the grid
rowCollection = dataGridView.get_Rows();

while(rs.next())
{
if(!columnCreated)
{
element.createColumn(rs);
columnCreated = true;
}
strValues = new System.String[rs.getMetaData().getColumnCount()]();
for(colcount=1; colcount<=rs.getMetaData().getColumnCount(); colcount++)
{
switch(rs.getMetaData().getColumnType(colCount))
{
case Types::String : strValues.set_Item(colcount-1, rs.getstring(colCount));
break;
case Types::Integer : strValues.set_Item(colcount-1, int2str(rs.getint(colCount)));
break;
case Types::Real : strValues.set_Item(colcount-1, num2str(rs.getReal(colCount),11,5,-1,-1));
break;
case Types::Date : strValues.set_Item(colcount-1, date2str(rs.getDate(colCount),123,2,-1,2,-1,4));
break;
}
}
rowCollection.Add(strValues);
}
}

public void init()
{
super();
//get the reference of the managed host control
dataGridView = DataGridViewManagedHost.control();
//set row header visible property to false
dataGridView.set_RowHeadersVisible(false);
//set columns to auto fill
dataGridView.set_AutoSizeColumnsMode(System.Windows.Forms.DataGridViewAutoSizeColumnsMode::Fill);
}

public void run()
{
System.Windows.Forms.DataGridViewColumnCollection columnCollection;
System.Windows.Forms.DataGridViewRowCollection rowCollection;
CustTable custTable;
System.String[] strValues;

super();

//define the columns of the grid
/*
columnCollection = dataGridView.get_Columns();
columnCollection.Add(“CustomerAcount”,”Customer account”);
columnCollection.Add(“CustomerName”,”Customer name”);

//fill the rows of the grid
rowCollection = dataGridView.get_Rows();

while select firstOnly10 * from custTable
{
strValues = new System.String[2]();

strValues.set_Item(0, custTable.AccountNum);
strValues.set_Item(1, custTable.name());

rowCollection.Add(strValues);
}
*/
}

public void runQuery()
{
Connection Con;
Statement st;
ResultSet rs;
LoginProperty lp;
SqlStatementExecutePermission perm;
ExecutePermission codeperm;

str SQL = “select AccountNum, custgroup from custtable where dataareaid in (‘dat’,’001′)”;

perm = new SqlStatementExecutePermission(sql);

// Check for permission to use the statement.
perm.assert();

Con = new Connection();
St = Con.createStatement();

rs = NICE_Sql::returnResultSet(St, SQL);

element.createRows(rs);
}

This example could be improve better and better again. Anyway I leave it here for suggestion …

Bye!!!