Run SQL query and get data in excel from D365 finance and operations

Run SQL query and export data in excel from D365 finance and operations

As we know, in case 0f cloud implementation of d365 finance and operations we do not have access to SQL server on production environment. Hence, whenever we need to get data from multiple tables as and when required, we have two options:

1. Develop a new report.

2. Create a request to Microsoft

Any of the above option may take 1-2 days’ time. Sometimes we can’t afford to lose this much time. Also, whenever we need the different data, we need to follow any one of the above option.

To solve this problem, I have developed a class which takes the SQL query as input and the result is given in an excel file.

When you run the class you will get the below dialog box. Once you enter the query and click OK, it exports the result of query into excel.

This is only to get the data from database. Only select queries are run through this class, update/insert is not supported. One should not update any data through SQL, it is not the best practice.

Thank you for reading..!!!

Please find the code below: 

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

class Custom_ExportExcel_SSMS extends RunBase

{

   Dialog                          dialog;

   Filename                        FileUploadName, textFile;

   int                             fileLineNumber;

   NoYes                           generateTemplate;

   DialogField                     df_qry;     

   FileName                        filename;

   str                             sqlQry;

   /// <summary>

   ///

   /// </summary>

   /// <returns></returns>

   public ClassDescription caption()

   {

       

       return ret = "SSMS result export";

   }

   public Object dialog()

   {

       DialogGroup         dialogGroup;

       FormBuildControl    formBuildControl;

       FileUploadBuild     dialogFileUpload;

       FilenameSave        fileSave;

       Set                 enumSet = new Set(Types::Enum);

       ;

       

       dialog = super();

       

       df_qry = dialog.addField(extendedTypeStr(Notes), 'Query');                

       return dialog;

   }

   public boolean getFromDialog()

   {

       sqlQry    =   df_qry.value();

       return true;

   }

   public static Custom_ExportExcel_SSMS construct()

   {

       return new Custom_ExportExcel_SSMS ();

   }

   public static void  main(Args _args)

   {

       Custom_ExportExcel_SSMS   exportData = Custom_ExportExcel_SSMS::construct();

       if(exportData.prompt())

       {

           exportData.generateFile();

       }

   }

   private Void generateFile()

   {

       int                             column = 1;

       str                             fileContent;

       int             colCount, colNumber, rowNumber;

       Connection Con = new Connection();

       Statement Stmt = Con.createStatement();

       ResultSet R;

       ResultSetMetaData   rMetadata;

       MemoryStream memoryStream = new MemoryStream();

       fileName = 'SSMS query result.xlsx';

       R =Stmt.executeQuery(sqlQry);

           

       rowNumber   =   1;

       using (var package = new ExcelPackage(memoryStream))

       {

           var currentRow              = 1;

           var worksheets              = package.get_Workbook().get_Worksheets();

           var exportWorksheet         = worksheets.Add("Export");

           var cells                   = exportWorksheet.get_Cells();

           OfficeOpenXml.ExcelRange    cell;

           System.String               value;

                                   

           while ( R.next() )

           {

               if(currentRow == 1)

               {

                   rMetadata = R.getMetaData();

                   colCount = rMetadata.getColumnCount();

                           

                   for(colNumber = 1; colNumber <= colCount; colNumber++)

                   {

                       cell = null; cell = cells.get_Item(currentRow, column); column++; value = rMetadata.getColumnName(colNumber); cell.set_Value(value);

                   }

                   currentRow++;

               }

               column = 1;

               for(colNumber = 1; colNumber <= colCount; colNumber++)

               {

                   switch(rMetadata.getColumnType(colNumber))

                   {

                       case Types::String :

                       case Types::RString :

                       case Types::VarString :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; value = R.getString(colNumber); cell.set_Value(value);

                           break;

                       case Types::Int64 :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getInt64(colNumber));

                           break;

                       case Types::Enum :

                       case Types::Integer :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getInt(colNumber));

                           break;

                       case Types::Real :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getReal(colNumber));

                           break;

                       case Types::Date :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getDate(colNumber));

                           break;

                       case Types::UtcDateTime :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getDateTime(colNumber));

                           break;

                       case Types::Guid :

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getGuid(colNumber));

                           break;

                       default:

                           cell = null; cell = cells.get_Item(currentRow, column); column++; cell.set_Value(R.getInt64(colNumber));

                           break;

                   }

                   

               }

               currentRow++;

           }

           

           package.Save();

           file::SendFileToUser(memoryStream, fileName);

       }

   }

}