How to generate SQL tables in excel and attached to email using ASP.net

Step 1: Ensure that the database SQL queries are working

Step 2: Creating Console Application

  • From start page select File -> New -> Project
  • In new dialogs pop up, select Templates -> Visual C# -> Window -> Console Application
  • Name your project and click on ok button

Step 3: Create a connection with database using App.config

  • Click on the project -> Add -> New Item
  • In new dialog pop up, Search installed template search App.config -> select Application Configuration file and click on Add button
  • In App.config, add connection string and app setting
<?xml version=”1.0" encoding=”utf-8" ?>
<configuration>
<connectionStrings>
<add name=”Local”
connectionString=”Data Source=Sai-PC;
Initial Catalog=AdventureWorks2008R2;
Persist Security Info=True;
User ID=Sa;Password=########;
Min Pool Size=0;
Max Pool Size=10000;
Pooling=true;”
providerName=”System.Data.SqlClient” />
</connectionStrings>
<appSettings>
<add key=”MailFrom” value=”sai#######@gmail.com” />
<add key=”Host” value=”smtp.gmail.com” />
<add key=”Port” value=”587" />
</appSettings>
</configuration>
  • Adding classes; right click on project -> select add -> select class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
namespace MailScheduler
{
   public class GetExcelFile
   {
     public void getData()
   {
      DataSet ds = null;
      using(SqlConnection con = new          SqlConnection(ConfigurationManager.ConnectionStrings[“Local”].ConnectionString))
   {
    try
    {
      SqlCommand cmd = new SqlCommand(“GetSalesDetails”, con);
      cmd.CommandType = CommandType.StoredProcedure;
      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = cmd;
      ds = new DataSet();
      da.Fill(ds);
     } catch (Exception)
    {
      throw;
    } finally
    {
      ds.Dispose();
     }  
   }
  }
 }
}

Step 4: Add NuGet Package (ClosedXML)

Step 5: Add a class to create Excel using Dataset

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using ClosedXML.Excel;
namespace MailScheduler
{
  public class GetExcelFile
  {
   public void getData()
   {
    DataSet ds = null;
    using(SqlConnection con = new   SqlConnection(ConfigurationManager.ConnectionStrings[“Local”].ConnectionString))
   {
   try
   {
    SqlCommand cmd = new SqlCommand(“GetSalesDetails”, con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    ds = new DataSet();
    da.Fill(ds);
    ExportDataSetToExcel(ds);
   } catch (Exception)
   {
    throw;
   } finally
   {
    ds.Dispose();
   }
  }
}
  private void ExportDataSetToExcel(DataSet ds)
  {
   string AppLocation = “”;
   AppLocation =    System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
   AppLocation = AppLocation.Replace(“file:\\”, “”);
   string file = AppLocation + “\\ExcelFiles\\DataFile.xlsx”;
   using(XLWorkbook wb = new XLWorkbook())
   {
    wb.Worksheets.Add(ds.Tables[0]);
    wb.Style.Alignment.Horizontal =   XLAlignmentHorizontalValues.Center;
   wb.Style.Font.Bold = true;
   wb.SaveAs(file);
  }
 }
}
}

Step 6: Add a class to send email

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;
namespace MailScheduler
{
 public class SendMail
 {
  public void SendEmail(string MailTo, string MailSubject)
  {
  try
  {
   string AppLocation = “”;
   AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
   AppLocation = AppLocation.Replace(“file:\\”, “”);
   string file = AppLocation + “\\ExcelFiles\\DataFile.xlsx”;
   MailMessage mail = new MailMessage();
   SmtpClient SmtpServer = new      SmtpClient(ConfigurationManager.AppSettings[“Host”].ToString());
   mail.From = new    MailAddress(ConfigurationManager.AppSettings[“MailFrom”].ToString());
   mail.To.Add(MailTo); // Sending MailTo
   List < string > li = new List < string > ();
   li.Add(“saihacksoft@gmail.com”);
   //li.Add(“saihacksoft@gmail.com”);
   //li.Add(“saihacksoft@gmail.com”);
   //li.Add(“saihacksoft@gmail.com”);
   //li.Add(“saihacksoft@gmail.com”);
   mail.CC.Add(string.Join < string > (“,”, li)); // Sending CC
   mail.Bcc.Add(string.Join < string > (“,”, li)); // Sending Bcc
   mail.Subject = MailSubject; // Mail Subject
   mail.Body = “Sales Report *This is an automatically generated    email, please do not reply*”;
   System.Net.Mail.Attachment attachment;
   attachment = new System.Net.Mail.Attachment(file); //Attaching    File to Mail
   mail.Attachments.Add(attachment);
   SmtpServer.Port =    Convert.ToInt32(ConfigurationManager.AppSettings[“Port”]); //PORT
   SmtpServer.EnableSsl = true;
   SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
   SmtpServer.UseDefaultCredentials = false;
   SmtpServer.Credentials = new NetworkCredential(“Email id of    Gmail”, “Password of Gmail”);
   SmtpServer.Send(mail);
  } catch (Exception ex)
  {
   throw ex;
  }
}
}
}