0

I have a program that: 1. execution sql query to MSSQL 2. inserts rows from sql query into excel 3. sends excel document by mail
if the number of lines to be inserted into an excel document is more 100000 then an error occurs:System.OutOfMemoryException I set the value in the project properties - platform target = x64 but it did not help

  using (SqlConnection sqlConn = new SqlConnection(connectionString))
                using (SqlCommand cmd = new SqlCommand(Qry, sqlConn))
                {
                    sqlConn.Open();
                    cmd.CommandTimeout = 0;
                    {
                        string Recipients = configMan.GetAppSetting("Recipients");
                        string CcRecipients = "";
                        string Subject = configMan.GetAppSetting("Subject");
                        string Body;

                        if (string.IsNullOrWhiteSpace(Recipients) || Recipients.IndexOf('@') == -1)
                        {
                        }
                        Body = $"<h3>{Subject} {configMan.GetAppSetting("Body")}</h3>";
                        string fileExcel = configMan.GetAppSetting("FilePathNameExcel");
                        int iCount;
                        ExcelLibSql excelLib = new ExcelLibSql();
                        iCount = excelLib.SaveFromSQLToNewExcelNewSheet(cmd, fileExcel, "report", log);
                        List<string> arrAttachFiles = new List<string>();
                        if (File.Exists(fileExcel))
                        {
                            arrAttachFiles.Add(fileExcel);
                        }
                        else
                        {
                        }
                        if (CcRecipients.Length > 0)
                        {
                            CcRecipients += ";";
                        }
                        CcRecipients += configMan.GetAppSetting("RecipientsTEST");
                        if (configMan.GetAppSetting("Test") == "True")
                        {
                            Body = "<b>Тест.</b><br/> <br/>" + "Recipients: " + Recipients + "<br/>CcRecipients: " + CcRecipients + "<br/><br/>" + Body;
                            Recipients = configMan.GetAppSetting("RecipientsTEST");
                            CcRecipients = "";
                        }
                        if (ews.SendMessage(log, Recipients, CcRecipients, Subject, Body, BodyType.HTML, arrAttachFiles))
                        {
                            log.WriteToFile($"send mail: {Recipients} copy: {CcRecipients}", true);
                        }
                    }
                }
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Joh Travell
  • 71
  • 3
  • Did you check how the ExcelLibSql handles the SaveFromSQLToNewExcelNewSheet? The OutOfMemoryException might come from there – Thai Anh Duc Oct 29 '19 at 14:14
  • My answer here is possibly useful, it uses OpenXml directly - https://stackoverflow.com/questions/32690851/export-big-amount-of-data-from-xlsx-outofmemoryexception/32787219#32787219 – petelids Oct 29 '19 at 14:26

0 Answers0