Processing

by
No comments yet

Monday, November 28, 2011

Import From Excel in Batch Job – Dynamics AX

First i will quickly go through all the different approaches that i tried unsuccessfully and then i will discuss my final solution that worked.  i had three unsuccesfull attempts: 1. Standard procedure to import data from using AX’s system classes for Excel(SysExcelApplication, SysExcelWorkbooks .. etc) . All these base classes are configured to run only on client (verify the RunOn property). So my program was running fine on client but failed to run on Batch Job on Server. FYI:  i changed the RunOn property to “Called from” but that didn’t help. My next approach was to do all the excel stuff in C#.net and then consume the .net assembly in AX.
2. Using .Net’s System.Data.Oledb :  i chose this namespace instead of Microsoft.Office.InterOp.Excel because this doesn’t need the Office Excel installed on the Server. I faced the same issue as in approach 1,  my AX program was able to create an instance for .Net class in AX client, but was failing to create the instance in Batch Job. 3. Using .Net’s Microsoft.Office.InterOp.Excel: i was left with this choice and i thought this would work without any issue but the same result. AX client is able to consume my dll but Batch Job was failing. Final solution: Thanks to my friend Dusan Chalic for recommending me to use Excel Reader from codeplex, it worked perfectly, here is the C# solution: a) add reference to Excel.dll (download it from above link in codeplex) in your Visual Studio Project b) create a method to read the Excel contents into a DataSet instance c) create a method that will take a row number and return the corresponding row (AX will call this method) d) Here is C# class that will read data from Excel file :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel;
using System.IO;
using System.Data;
namespace ExcelReader
{
    public class ReadDataFromXL
    {
        DataTable xlDataTable;
        public string RetrieveFromXL(string fileName)
        {
            //pass the filename with path
            IExcelDataReader xlReader = null;
            FileStream xlStream = null;
            DataSet xlDataSet = null;
            string empId, fullName, accPriority, routNum, accNum, accType;
            xlDataTable = new DataTable();
            xlDataTable.Columns.Add(“EmpId”, typeof(string));
            xlDataTable.Columns.Add(“FullName”, typeof(string));
            xlDataTable.Columns.Add(“AccPriority”, typeof(string));
            xlDataTable.Columns.Add(“RoutNumber”, typeof(string));
            xlDataTable.Columns.Add(“AccNum”, typeof(string));
            xlDataTable.Columns.Add(“AccType”, typeof(string));
            try
            {
                xlStream = File.Open(fileName, FileMode.Open, FileAccess.Read);
                //1. Reading from a binary Excel file (’97-2003 format; *.xls)
                xlReader = ExcelReaderFactory.CreateBinaryReader(xlStream);
                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                //xlReader = ExcelReaderFactory.CreateOpenXmlReader(xlStream);
                xlReader.IsFirstRowAsColumnNames = false;
                xlDataSet = xlReader.AsDataSet();
                int rowNumber = 0;
                while (xlReader.Read())
                {
                    rowNumber++;
                    if (rowNumber < 5)
                        continue;
                    empId = SanTryParse(xlReader.GetString(0));
                    fullName = SanTryParse(xlReader.GetString(1));
                    accPriority = SanTryParse(xlReader.GetString(2));
                    routNum = SanTryParse(xlReader.GetString(3));
                    accNum = SanTryParse(xlReader.GetString(4));
                    accType = SanTryParse(xlReader.GetString(5));
                    if (empId == “” && fullName == “” && accPriority == “”)
                        break;
                    //Console.WriteLine(string.Format(“{0} {1} {2} {3} {4} {5}”, empId, fullName, accPriority, routNum, accNum, accType));
                    // fill the datatable
                    xlDataTable.Rows.Add(empId, fullName, accPriority, routNum, accNum, accType);
                }
                //Console.WriteLine(“Row Count: ” + xlDataTable.Rows.Count);
                xlReader.Close();
            }
            catch (Exception ex)
            {
                if(xlReader != null)
                     xlReader.Close();
            }
            return “Done”;
        }
        public int GetRowCount()
        {
            return xlDataTable.Rows.Count;
        }
        public string GetRow(int index)
        {
            string empId, fullName, accPriority, routNum, accNum, accType;
            DataRow currRow = xlDataTable.Rows[index];
            int endCol = 5;
            string result;
            empId = SanTryParse(currRow[0]);
            fullName = SanTryParse(currRow[1]);
            accPriority = SanTryParse(currRow[2]);
            routNum = SanTryParse(currRow[3]);
            accNum = SanTryParse(currRow[4]);
            accType = SanTryParse(currRow[5]);
            result = empId + “!” + fullName + “!” + accPriority + “!” + routNum + “!” + accNum + “!” + accType;
            return result;
        }
        private string SanTryParse(object input)
        {
            if (input == null)
                return “”;
            return Convert.ToString(input);
        }
    }
}
e) Sign the above VS project, compile and deploy the assembly to GAC.
f) Now the AX part – open AOT -> References node -> right click and Add reference to the above assembly in GAC
g) create a a new batch job Class in AX (Extending RunBaseBatch)
h) create a method , here is the code to call the methods in C# class and read the data from Excel:

void importDataFromXlReader() {     Set   permissionSet;     System.Exception e;     str result,  currRowStr;     int totalRows, i, j;     List values;     ListIterator iter;     str valuesArr[6];     SanTempTable buffer;  // Temporary table to hold the data from Excel   ;     try     {         permissionSet =  new Set(Types::Class);         permissionSet.add(new InteropPermission(InteropKind::ClrInterop));         permissionSet.add(new InteropPermission(InteropKind::ComInterop));         permissionSet.add(new InteropPermission(InteropKind::DllInterop));         CodeAccessPermission::assertMultiple(permissionSet);         xlReader = new ExcelReader.ReadDataFromXL();         result = xlReader.RetrieveFromXL(fileName);         //info(result);         if(result == “Done”)         {           totalRows = xlReader.GetRowCount();           if(totalRows <= 0)              {               errMessage = “Zero Rows read from XL, there is an issue”;               throw error(errMessage);             }           lastRow = totalRows; //lastRow is class vraiable used for ProgressBar           info(strFmt(“Total Rows:  %1″, totalRows));           for(i=0; i<totalRows ; i++)           {              currRowStr = xlReader.GetRow(i);              //info(strFmt(“Current Row: %1″, currRowStr));              values = Global::strSplit(currRowStr, ‘!’);              iter = new ListIterator(values);              j = 0;             while(iter.more())              {               j++;                //info(iter.value());               if(j<=6)                  valuesArr[j] = iter.value();                iter.next();             }              //info(strFmt(“Individual Values: %1 %2 %3 %4 %5 %6 “, valuesArr[1], valuesArr[2], valuesArr[3], valuesArr[4], valuesArr[5], valuesArr[6] ));             // fill the buffer              buffer.EmplId = valuesArr[1];              buffer.EmpName = valuesArr[2];                          buffer.AccountPriority = str2Int(valuesArr[3]);                         buffer.RoutingNumber = valuesArr[4];             buffer.AccountNumber = valuesArr[5];             buffer.AccountType = valuesArr[6];             buffer.insert();            }// end for         }// end if         CodeAccessPermission::revertAssert();     }     catch(Exception::CLRError)     {         info(CLRInterop::getLastException().ToString());         e = CLRInterop::getLastException();         errMessage = e.get_Message() + “\n”;         while( e )         {           info( e.get_Message() );            e = e.get_InnerException();           errMessage = errMessage +  “\n” + e.get_Message();         }         throw error(errMessage);     } } with these 2 code snippets we should be able to import data from Excel in AX 2009 Batch Jobs.
Happy Daxing and Cyber Monday shopping San.

Cache in Dynamics Ax 2009

by
No comments yet

Cache in Dynamics Ax 2009

Many a time we have issue regarding data, on debugging found that old record is overriding new record… Sometimes it could be because the cache is not getting refreshed. Cache are used both on client and server. Microsoft Dynamics AX runtime manages the cache by replacing  old record with new record are added to the cache.
If one want to delete(or) clean up  the local cache file, below are the following step:
·         Stop the AOS
·         Delete the ‘.auc’ file from location ‘ user\AppData\Local\ *.auc’
·         Restart AOS.
Scenario – Suppose there are 2 AOS that required to flush the cache but then we cannot stop or start the AOS. How can one refresh the Cache?
There are three menu action items that can be used to flush system data, the AOD, and dictionary.
The thing about these though is that they are set to run at “called from” which when ran clear the client cache.
The trick is to copy these three menu items, and then change the run from property to server. Now you can launch these menu items, and they will run on the server, therefore clearing the AOD, sys data, and dictionary, causing a refresh.
So the three menu items that you copy  are:[Under Menu items > Action] 1. SysFlushAOD 2. SysFlushData 3. SysFlushDictionary

Progress bar

by
No comments yet

How to: Create Progress Indicators

            Dynamics AX 2009       

Use a progress indicator during operations that take more than 2 seconds.

  • Use an hourglass mouse pointer if the process takes 2-7 seconds.
  • Use a progress bar if the process takes 8 seconds or more.

  1. Call the startLengthyOperation method when the operation starts.
  2. Call the endLengthyOperation method when the operation ends.

Both of these methods are on the Global application class.

The hourglass pointer is displayed until you call the endLengthyOperation method.

NoteNote
You do not have to declare a variable when you use the Global class.

Example: Display an Hourglass Pointer

The following example overrides the clicked method on a form control to refresh the database log.

void clicked()
{
    super();
    startLengthyOperation();
    sysDatabaseLog_ds.research();
    endLengthyOperation();
}

  1. Initialize a SysOperationProgress variable.
  2. Set a caption for the form by using the SysOperationProgress.setCaption method.
  3. Set an animation to run while the operation is in progress by using the SysOperationProgress.setAnimation method.A number of animations are provided with Microsoft Dynamics AX. To view them, run the Tutorial_ShowAVIFiles class. If you use one of these animation files, you need to declare the AviFiles macro at the top of your code.
  4. Specify the total number of operation steps.This is needed for the time-remaining calculation. If you do not set the total number of operation steps, the progress indicator is not shown. The total is often a count of the number of records, and may be time-consuming to calculate. Don’t specify the total if the time is taken to calculate the records is comparable to the total time taken for the operation.
  5. Perform the operation. For each step, specify a description and a step number. For example: 
    for (i = 1; i <= 100; i++)
{
    progress.setText(strfmt("Step %1", i));
    progress.incCount();
}

    The description must be short and informative because it might change quickly during the execution.

    As an alternative to incCount() you can use setCount(int i).

During the execution, the progress indicator is updated accordingly. The estimated time remaining is calculated and displayed.

The default update interval is 3 seconds. If the task of updating the display takes more than 10% of the update interval due to latency on the network connection, the update interval is increased by 1 second.

TipTip
Separate an operation into as many steps as possible. This gives the user the best information and the best remaining-time estimate. The time spent informing the user has no impact because the form is updated only once a second—even less frequently on low bandwidth connections.

Example: Use a Single Progress Indicator

The following example creates a simple progress indicator.

static void operationProgress(Args _args)
{
    #AviFiles
    SysOperationProgress progress = new SysOperationProgress();
    int i;

    ;

    progress.setCaption("My Task");
    progress.setAnimation(#AviUpdate);
    progress.setTotal(30000);
    for (i = 1; i <= 30000; i++)
    {
        progress.setText(strfmt("Step %1", i));
        progress.setCount(i, 1);
    }
}
If you have a more complex operation, you can have more than one progress indicator. All of the previously described methods have a default parameter that indicates which progress indicator (or bar) is being referred to.

NoteNote
The time-remaining calculation is done solely on the first bar. The first bar must always show the overall progress.

Example: Use Three Progress Indicators

static void SysOperationProgress(Args _args)
{
    #define.count1(10)
    #define.count2(5)
    #define.count3(200)
    #AviFiles

    // 3 bars.
    SysOperationProgress progress = new SysOperationProgress(3);
    int i, j, k;
    ;

    progress.setCaption("My Task");
    progress.setAnimation(#AviUpdate);

    // Bar 1.
    progress.setTotal(#count1, 1);
    // Bar 2.
    progress.setTotal(#count2, 2);
    // Bar 3.
    progress.setTotal(#count3, 3);

    for (i=0; i<#count1; i++)
    {
        // Bar 1.
        progress.setText(strfmt("Bar 1 - Step %1 of %2", i, #count1), 1);
        progress.setCount(i, 1);
        for (j=0; j<#count2; j++)
        {
            // Bar 2.
            progress.setText(strfmt("Bar 2 - Step %1 of %2", j, #count2), 2);
            progress.setCount(j, 2);
            for (k=0; k<#count3; k++)
            {
                // Bar 3.
                progress.setText(
                    strfmt("Bar 3 - Step %1 of %2", k, #count3), 3);
                progress.setCount(k, 3);
                sleep(20);      // Time-consuming task.
            }
        }
    }
}
Following are progress indicator options for user input during an operation:

  • SysOperationProgress.hide – hides the progress form and pauses the time-remaining calculation. The progress indicator reappears when setCount, incCount, or setText is subsequently called.
  • SysOperationProgress.kill – terminates the progress form and starts the progress again. You don’t have to reset the caption, animation, and total.
Runbase is the standard framework for job execution, and must have a progress indicator. In the standard application, most progress indicators are used from the RunBase framework.Use the RunBase.progressInit method to initialize the progress indicator:

public void progressInit(
    str       caption,
    int       total,
    Filename  animation,
    int       updateInterval = 1,
    int       numOfBars      = 1)

Indicating progress during the actual operation is similar to the standard Operation Progress framework. Use the member variable progress:

progress.incCount();

If you have more than one progress bar, use the progress variable defined in RunBase. It points to the Progress class initialized in the progressInit method. For example:

{
    this.progressInit("Caption",10,#AviFileCopy,1,2);
    progress.setCount(bar1count);
    progress.setTotal(maxBar2Count,bar2count);
    ...
    ...
    progress.setCount(bar2count,2);
}