Is there any method In .net MVC to export database date in file not in local machine and send as file attachment with email.?

Question

Here is My Export code.

        public void ExportCSV_Employee(HttpPostedFileBase file )
          {
        var sb = new System.Text.StringBuilder();
        var list = _context.ProductItems.ToList();
        
        foreach (var item in list)
        {
            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}\t{10}\t{11}\t{12}\t{13}\t{14}\t{15}\t{16}\t{17}\t{18}\t{19}\t{20}\t{21}\t{22}\t{23}\t{24}\t{25}\r", item.ScanCode, item.Name, item.UnitRetail, item.UnitCost, item.DeptName, item.PriceGroup, item.ProductCode, item.Pcode, item.StateTax, item.MaxQty, item.Modified, item.AllowFoodStamps, item.LocalTax, item.Crv, item.MinAge, item.AllowDirectDept, item.IsNegative, item.DeptType, item.ItemCategoryId, item.ProductSubCategory, item.AllowFractionDept, item.BuyDown, item.UpcType, item.OverRide, item.PkgSize, item.Discount);
        }


      //mail method to send mail

        MailMessage mail = new MailMessage();
        mail.From = new System.Net.Mail.MailAddress("mymail@gmail.com");

        SmtpClient smtp = new SmtpClient();
        smtp.Port = 587;   
        smtp.EnableSsl = true;
        smtp.DeliveryMethod = SmtpDeliveryMethod.Network; 
        smtp.UseDefaultCredentials = false; 
        smtp.Credentials = new NetworkCredential("mymail@gmail.com" , "password");  
        smtp.Host = "smtp.gmail.com";

        //recipient address
        mail.To.Add(new MailAddress("receiver@gmail.com"));

}

in this way, my mail sending work but I want to attach the date from my database as a start retrieving through StringBuilder. How can I attach my data as a file attachment with email?

Does this answer your question? [Sending email with attachments from C#, attachments arrive as Part 1.2 in Thunderbird](https://stackoverflow.com/questions/2825950/sending-email-with-attachments-from-c-attachments-arrive-as-part-1-2-in-thunde) — Rahatur, Dec 08 '20 at 15:49
No. Your link receives the file from the local PC but I want to attach the file from the direct database and send it by email. — NumanCS, Dec 08 '20 at 17:14
I do not see much difference there. All you need is to get the bytes from the database. I am posting an answer with sample codes. — Rahatur, Dec 08 '20 at 17:21

Rahatur · Accepted Answer · 2020-12-09T12:01:01.340

var sb = new System.Text.StringBuilder();
var list = _context.ProductItems.ToList();
            
foreach (var item in list)
{
    sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}\t{10}\t{11}\t{12}\t{13}\t{14}\t{15}\t{16}\t{17}\t{18}\t{19}\t{20}\t{21}\t{22}\t{23}\t{24}\t{25}\r", item.ScanCode, item.Name, item.UnitRetail, item.UnitCost, item.DeptName, item.PriceGroup, item.ProductCode, item.Pcode, item.StateTax, item.MaxQty, item.Modified, item.AllowFoodStamps, item.LocalTax, item.Crv, item.MinAge, item.AllowDirectDept, item.IsNegative, item.DeptType, item.ItemCategoryId, item.ProductSubCategory, item.AllowFractionDept, item.BuyDown, item.UpcType, item.OverRide, item.PkgSize, item.Discount);
}

//Create a virtual file in memory

byte[] bytes = null;
using (var ms = new MemoryStream())
{
  TextWriter tw = new StreamWriter(ms);
  tw.Write(sb.Tostring());
  tw.Flush();
  ms.Position = 0;
  bytes = ms.ToArray();
}

//Create Attachment

Attachment att = new Attachment(new MemoryStream(bytes), name);

//mail method to send mail

    MailMessage mail = new MailMessage();
    mail.From = new System.Net.Mail.MailAddress("mymail@gmail.com");

    SmtpClient smtp = new SmtpClient();
    smtp.Port = 587;   
    smtp.EnableSsl = true;
    smtp.DeliveryMethod = SmtpDeliveryMethod.Network; 
    smtp.UseDefaultCredentials = false; 
    smtp.Credentials = new NetworkCredential("mymail@gmail.com" , "password");  
    smtp.Host = "smtp.gmail.com";

    //recipient address
    mail.To.Add(new MailAddress("receiver@gmail.com"));

//Add the attachment to the mail message

mail.Attachments.Add(data);

Thanks for your answer but how can we send this as the File attachment with an email in the above email sending code. — NumanCS, Dec 09 '20 at 09:18
@NumanCS I have updated the codes to show how to attach the file. Hope that helps. Font forget to up vote the answer if it worked for you :) — Rahatur, Dec 09 '20 at 12:12

NumanCS · Answer 2 · 2021-02-25T10:32:18.040

After spending some time I figure out the solution for this problem first save data in a Memorystream and then pass it to the email attachment method.

     {
      public void ExportCSV_Employee(HttpPostedFileBase file )
         {
         var sb = new System.Text.StringBuilder();
          var list = _context.ProductItems.ToList();
        
        foreach (var item in list)
        {
            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}\t{10}\t{11}\t{12}\t{13}\t{14}\t{15}\t{16}\t{17}\t{18}\t{19}\t{20}\t{21}\t{22}\t{23}\t{24}\t{25}\r", item.ScanCode, item.Name, item.UnitRetail, item.UnitCost, item.DeptName, item.PriceGroup, item.ProductCode, item.Pcode, item.StateTax, item.MaxQty, item.Modified, item.AllowFoodStamps, item.LocalTax, item.Crv, item.MinAge, item.AllowDirectDept, item.IsNegative, item.DeptType, item.ItemCategoryId, item.ProductSubCategory, item.AllowFractionDept, item.BuyDown, item.UpcType, item.OverRide, item.PkgSize, item.Discount);
        }
        //Storing data in virtual memory and then pass to email attachment  

        var myString = sb.ToString();
        var myByteArray = System.Text.Encoding.UTF8.GetBytes(myString);
        var ms = new MemoryStream(myByteArray);


    MailMessage mail = new MailMessage();
    mail.From = new System.Net.Mail.MailAddress("mymail@gmail.com");

    SmtpClient smtp = new SmtpClient();
    smtp.Port = 587;   
    smtp.EnableSsl = true;
    smtp.DeliveryMethod = SmtpDeliveryMethod.Network; 
    smtp.UseDefaultCredentials = false; 
    smtp.Credentials = new NetworkCredential("mymail@gmail.com" , "password");  
    smtp.Host = "smtp.gmail.com";

    //recipient address
    mail.To.Add(new MailAddress("receiver@gmail.com"));
    string Fname = "PriceBook.dat";
        
        if ( ms!= null)
        {
            string fileName = Fname;
            
            mail.Attachments.Add(new Attachment(ms, "fileName.dat", "text/dat"));
        };
       smtp.Send(mail);
}

}

Is there any method In .net MVC to export database date in file not in local machine and send as file attachment with email.?

2 Answers2