Send Email in ASP.NET MVC

In order to send emails using the SMTP server in ASP.NET MVC application, you can use the "WebMail" helper class with a method called "Send" using the namespace "using System.Web.Helpers". For details, please follow the steps below.

First of all, we have to create a new Asp.Net Application; let us see the description of how to create it.

• Open Visual Studio
• File>New>Project
• Visual C#>Web>ASP.NET Web Application(.NET Framework)>Enter the Project Name and Browse the file path and click OK, then select MVC click OK.
• Open the "Web.config" file and add the following code for SMTP settings.

Note: 

• Set username is your Gmail(email) and password is your email password.
• You can use any SMTP server to send emails using respective port number(Here I am using Gmail).

• Create a "RegisterModel.cs" class in the Model folder and add the following code.

using System.ComponentModel.DataAnnotations;

namespace EmailNotification.Models
{
    public class RegisterModel
    {
        [Required]
        public string Name { get; set; }

        [Required]
        public string Email { get; set; }

        [Required]
        public string Subject { get; set; }

        [Required]
        public string Message { get; set; }
    }
}

• Go to Views > Home  Open an "Index.cshtml" file and add the following code.

@model EmailNotification.Models.RegisterModel

@{
    ViewBag.Title = "Home Page";
}


@if (TempData["Success"] != null)
{
    

@TempData["Success"]
    
}

@if (TempData["Error"] != null)
{
    

@TempData["Error"]
    
}

@using (Html.BeginForm("SendEmail", "Home", FormMethod.Post))
{
    @Html.AntiForgeryToken()

    


Send Email Using SMTP Server

@Html.ValidationSummary(true, "", new { @class = "text-danger" })
        


@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            


@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            


@Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
            


@Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control", @type = "email" } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
            


@Html.LabelFor(model => model.Subject, htmlAttributes: new { @class = "control-label col-md-2" })
            


@Html.EditorFor(model => model.Subject, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Subject, "", new { @class = "text-danger" })
            


@Html.LabelFor(model => model.Message, htmlAttributes: new { @class = "control-label col-md-2" })
            


@Html.TextAreaFor(model => model.Message, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Message, "", new { @class = "text-danger" })
            




            


}

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

• Go to the Controller folder and open "HomeController.cs" file and add the following code.

using EmailNotification.Models;
using System.Web.Helpers;
using System.Web.Mvc;

namespace EmailNotification.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult SendEmail(RegisterModel model)
        {
            if (ModelState.IsValid)
            {
                string body = "Hi " + model.Name + "

";
                body += "
" + model.Message + "
";
                WebMail.Send(model.Email, model.Subject, body, null, null, null, true, null, null, null, null, null, null);
                TempData["Success"] = "Email sent successfully.";
                return RedirectToAction("Index");
            }
            TempData["Error"] = "Error occured while sending Email.";
            return RedirectToAction("Index");
        }

    }
}

• click the following link  https://myaccount.google.com/security  (you must sign in your Gmail account) then find "Allow less secure apps" option and set to ON state.

• Run the application and enter valide records then click send button.

• Email sent successfully, see the result below.

• To download the source code, please click the following GitHub link and clone the repository https://github.com/shivarajad27/EmailNotification.git

ASP.NET MVC - Export Multiple Data tables to Excel With Multiple Sheets using C#

In order to export multiple data tables to excel with multiple sheets, you can use the NuGet package ClosedXML. With this package, you can create the desired structure using the class "XLWorkbook". This class is going to provide a byte array which you can later bind to Memory Stream and return as "FileStreamResult" with the header "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" along with file name having the extension ".xlsx". For details, please follow the steps below.

Step 1:

First of all, we have to create a new Asp.Net Application; let us see the description with images of how to create it.

• Open Visual Studio
• File>New>Project
• Visual C#>Web>ASP.NET Web Application(.NET Framework)>Enter the Project Name and Browse the file path

• Select MVC > OK

Step 2:

• Open SQL-Management Studio, Create a database "College.Database"

• Creating an "Employee" table, execute the following script.

USE [College.Database]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 7/24/2018 6:04:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
 [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](100) NOT NULL,
 [DepartmentName] [varchar](100) NOT NULL,
 [Designation] [varchar](100) NOT NULL,
 [Address] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
 [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (1, N'Shivaraj', N'CSE', N'HOD', N'Xyz')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (2, N'Naveen', N'CSE', N'Lecturer', N'aaaaa')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (3, N'Seetha ', N'CSE', N'Lecturer', N'bbbb')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (4, N'Bhargavi', N'ISE', N'HOD', N'zccczx')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (5, N'Akshatha', N'ISE', N'Lecturer', N'fghjk')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (6, N'Shashidhar', N'EC ', N'HOD', N'qwert')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (7, N'Prashanth', N'EC', N'Lecturer', N'dfgh')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (8, N'Clinton', N'ME', N'HOD', N'qwert')
GO
INSERT [dbo].[Employees] ([EmployeeId], [Name], [DepartmentName], [Designation], [Address]) VALUES (9, N'Deeksha', N'ME', N'Lecturer', N'qwert')
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO

• Creating an "UspGetExcelData" stored procedure execute the following script

USE [College.Database]
GO
CREATE PROCEDURE [dbo].[UspGetExcelData] 
 @DepartmentName VARCHAR(100)
AS
BEGIN
 
 SELECT *
 FROM Employees
 WHERE DepartmentName = @DepartmentName
END

Step 3:

• We should install the "ClosedXML" NuGet package in your application
• Right click on application > Manage NuGet Packages > Browse > Search ClosedXML > Click install

• Open the "Web.config" file and add the following code for database connection string

• Create a "Repository" folder
• Create "BaseRepository" class under Repository  folder and add the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace ExportExcelExample.Repository
{
    public abstract class BaseRepository
    {
        protected string connectionString = WebConfigurationManager.ConnectionStrings["collegeDB"].ConnectionString;
    }
}

• Create "StoredProcedureParameters" class under Models folder and add the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ExportExcelExample.Models
{
    public class StoredProcedureParameters
    {
        public string Name { get; set; }
        public string Value { get; set; }
    }
}

• Create "EmployeeRepository" class under Repository folder and add the following code

using System;
using ExportExcelExample.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using System.Web;

namespace ExportExcelExample.Repository
{
    public class EmployeeRepository: BaseRepository
    {
        public DataSet GetDataSetExportToExcel()
        {
            DataSet ds = new DataSet();
            var departments = new List() { "CSE", "ISE", "EC", "ME" };

            foreach (var department in departments)
            {
                DataTable dt = new DataTable();
                dt = GetDataTableExportToExcel(department);
                ds.Tables.Add(dt);
            }

            return ds;
        }

        private DataTable GetDataTableExportToExcel(string departmentName)
        {
            DataTable dt = new DataTable();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("UspGetExcelData", con))
                {
                    var parameters = new List()
                    {
                        new StoredProcedureParameters(){Name = "@DepartmentName",Value= departmentName }
                    };

                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));
                    }

                    using (var sda = new SqlDataAdapter(cmd))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }

    }
}

• Create an "Employee" controller class and add the following code

using ClosedXML.Excel;
using ExportExcelExample.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;

namespace ExportExcelExample.Controllers
{
    public class EmployeeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ExportToExcel()
        {
            var sheetNames = new List() { "CSE", "ISE", "EC", "ME" };
            string fileName = "Department.xlsx";

            EmployeeRepository employeeRepository = new EmployeeRepository();
            DataSet ds = employeeRepository.GetDataSetExportToExcel();

            XLWorkbook wbook = new XLWorkbook();

            for (int k = 0; k < ds.Tables.Count; k++)
            {
                DataTable dt = ds.Tables[k];
                IXLWorksheet Sheet = wbook.Worksheets.Add(sheetNames[k]);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Sheet.Cell(1, (i + 1)).Value = dt.Columns[i].ColumnName;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Sheet.Cell((i + 2), (j + 1)).Value = dt.Rows[i][j].ToString();
                    }
                }
            }

            Stream spreadsheetStream = new MemoryStream();
            wbook.SaveAs(spreadsheetStream);
            spreadsheetStream.Position = 0;

            return new FileStreamResult(spreadsheetStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = fileName };
        }

    }
}

• Create "Index" view in following path, Views>Employee>Index.cshtml

@{
    ViewBag.Title = "Index";
}


Export Data Tables to Excel With Multiple Sheets





            

Results:

• Run the application and click on the Export button to download the excel file.

• Downloaded excel file will look like this

To download the source code, please click the following GitHub link and clone the repository  https://github.com/shivarajad27/ExportExcelDemo.git.