A Task to send the details of the anything using Table ( here Faculty Course details) through Email:
Output:
Database:
USE [Testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Department](
[DNo] [int] NOT NULL,
[DName] [nvarchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_Blog_Department] PRIMARY KEY CLUSTERED
(
[DNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
*********************************************************************
USE [Testdb]
GO
/****** Object: Table [dbo].[Blog_Course] Script Date: 2/4/2018 11:50:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Course](
[CID] [int] IDENTITY(1,1) NOT NULL,
[CName] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[Duration] [nvarchar](50) NULL,
[EndDate] [nvarchar](50) NULL,
[Time] [nvarchar](50) NULL,
[Dno] [int] NULL,
[BatchType] [nvarchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_Blog_Course] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
***************************************************************************
USE [Testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Faculty](
[FID] [int] IDENTITY(1001,1) NOT NULL,
[FName] [nvarchar](50) NULL,
[EmailID] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[DNo] [int] NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_Blog_Faculty] PRIMARY KEY CLUSTERED
(
[FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
********************************************************************
USE [Testdb]
GO
/****** Object: Table [dbo].[Blog_StudentDetails] Script Date: 2/4/2018 11:52:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_StudentDetails](
[SID] [int] NULL,
[SName] [nvarchar](50) NULL,
[EmailID] [nvarchar](50) NULL,
[Mobile] [int] NULL
) ON [PRIMARY]
GO
*******************************************************************
Output:
Database:
USE [Testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Department](
[DNo] [int] NOT NULL,
[DName] [nvarchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_Blog_Department] PRIMARY KEY CLUSTERED
(
[DNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
*********************************************************************
USE [Testdb]
GO
/****** Object: Table [dbo].[Blog_Course] Script Date: 2/4/2018 11:50:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Course](
[CID] [int] IDENTITY(1,1) NOT NULL,
[CName] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[Duration] [nvarchar](50) NULL,
[EndDate] [nvarchar](50) NULL,
[Time] [nvarchar](50) NULL,
[Dno] [int] NULL,
[BatchType] [nvarchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_Blog_Course] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
***************************************************************************
USE [Testdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_Faculty](
[FID] [int] IDENTITY(1001,1) NOT NULL,
[FName] [nvarchar](50) NULL,
[EmailID] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[DNo] [int] NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](50) NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedBy] [nvarchar](50) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_Blog_Faculty] PRIMARY KEY CLUSTERED
(
[FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
********************************************************************
USE [Testdb]
GO
/****** Object: Table [dbo].[Blog_StudentDetails] Script Date: 2/4/2018 11:52:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Blog_StudentDetails](
[SID] [int] NULL,
[SName] [nvarchar](50) NULL,
[EmailID] [nvarchar](50) NULL,
[Mobile] [int] NULL
) ON [PRIMARY]
GO
*******************************************************************
Added EndDate in Blog_course Table
*************************************************
Stored Procedure:
Create procedure usp_Blog_FacultyReport
as
begin
select [dbo].[Blog_Department].[DName] as Category,[Blog_Course].CName as Course,[Blog_Course].BatchType,convert(varchar,[dbo].[Blog_Course].StartDate,103) as StartDate,
[dbo].[Blog_Course].Duration,[dbo].[Blog_Course].[Time] as Timings,
(CASE
WHEN [dbo].[Blog_Course].StartDate > = DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())) then 'Upcoming Batch'
WHEN [dbo].[Blog_Course].EndDate <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) then 'Batch Completed'
WHEN convert(varchar, [dbo].[Blog_Course].StartDate, 120) <= (convert(varchar, [dbo].[Blog_Course].EndDate, 120)) then 'Going on Batch'
END) as Status from [dbo].[Blog_Department] inner join
[dbo].[Blog_Course]
on [dbo].[Blog_Department].DNo = [dbo].[Blog_Course] .Dno
where [dbo].[Blog_Course].[Status]=1
end
===================================================
Note: Based on requirement you we can do changes to the above tables and stored procedures
CoursesInfo.aspx.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class CourseInfo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
// CoursesInfo.aspx add Button
protected void Button1_Click(object sender, EventArgs e)
{
FacultyReport obj = new FacultyReport();
obj.SendMail();
}
public class FacultyReport
{
public string PrepareTable()
{
string currentDate = DateTime.Now.ToString("dd-MM-yyyy");
string mailBody = @"<html><body><p><Strong>Dear All,</strong></p><h2 style='color:blue;'>Batch Updates Of Mr. Narasimha Rao</h2><table border=2 cellpadding='5' cellspacing='0' style='padding:5px;' width='80%'> <tr style='background-color:#A9A9A9'><th>S.No</th><th>Category</th><th>Courses</th><th>Batch Type</th><th>StartDate</th><th>Duration</th><th>Timings</th><th>Status</th></tr><tr style = 'background:#6495ED;' >@courseTable@</tr></table><br/><br/><table><tr> <td style='background-color:#A9A9A9';line-height:20 ;padding:15px 0;font-size:14px;color:#444444;> Sent from Dotnet Narasimha </td> </tr> </ body></ html > ";
mailBody = mailBody.Replace("@date@", currentDate);
string SiteSqlServer = "Data source=xx;database=Testdb;Uid=xx;Password=xxx;";
using (var con = new SqlConnection(SiteSqlServer))
{
using (SqlCommand cmd = new SqlCommand("usp_Blog_FacultyReport", con))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables.Count > 0)
{
DataTable courseTable = (ds.Tables[0].DefaultView).ToTable();
string rowStr = string.Empty;
int sno = 1;
if (courseTable.Rows.Count > 0)
{
foreach (DataRow row in courseTable .Rows)
{
rowStr += "<tr style='background:#6495ED;width='80%'>";
rowStr += "<td>" + sno.ToString() +"</td>";
rowStr += "<td align=center>" + row["Category"].ToString() + "</td>";
rowStr += "<td align=center>" + row["Course"].ToString() + "</td>";
rowStr += "<td align=center>" + row["BatchType"].ToString() + "</td>";
rowStr += "<td align=center>" + row["StartDate"].ToString() + "</td>";
rowStr += "<td align=center>" + row["Duration"].ToString() + "</td>";
rowStr += "<td align=center>" + row["Timings"].ToString() + "</td>";
rowStr += "<td align=center>" + row["Status"].ToString() + "</td>";
rowStr += "</tr>";
sno++;
}
mailBody = mailBody.Replace("@courseTable@", rowStr);
}
else
{
rowStr += "<tr style='background:#fff;'><td colspan='16'>No information available</td></tr>";
mailBody = mailBody.Replace("@courseTable@", rowStr);
}
}
}
}
return mailBody;
}
public void SendMail()
{
try
{
string body = PrepareTable();
using (var message = new MailMessage("From@gmail.com", "From@gmail.com"))
{
message.Subject = "Mr. Narasimha Rao Course Schedule Details :: " + DateTime.Now.ToString("dd-MM-yyyy");
message.To.Add(new MailAddress("To@gmail.com"));
message.CC.Add(new MailAddress("To@gmail.com"));
message.Bcc.Add(new MailAddress("To@gmail.com"));
message.Body = body;
message.IsBodyHtml = true;
SmtpClient client = new SmtpClient();
client.EnableSsl = true;
client.Host = "smtp.gmail.com";
client.Port = 587;
client.Credentials = new NetworkCredential("From@gmail.com.com", "password");
client.Send(message);
}
}
catch (Exception ex)
{
}
}
}
}
OUTPUT:
Note: In Department table I took Department name as Basics and Advanced as Category.
We will explore this example more, as we have just posted to send mail updates .we will update this using Students table to send mulitple mail alerts.
Lets hope you will also Explore
