Hi JOYSON,
I would refer you that not to delete the record if user is unsubscribed just change the status to false so that in future if you require any details about the user who were on the site then you can get it from subscription table.
First i have created a sample based on single page so go through below sample carefully and you need to add sendmail code from below article with link and modify it according to your need.
I have taken reference from below articles.
SQL
Create Table
CREATE TABLE Subscribe
( SubscribeId INT IDENTITY(1,1)
,Email VARCHAR(255)
,Subscribed BIT
)
INSERT INTO Subscribe VALUES('test@gmail.com',0)
INSERT INTO Subscribe VALUES('test123@gmail.com',1)
Stored Procedure
CREATE PROCEDURE Subscribe_NewsLetter
@email VARCHAR(255)
,@id int output
AS
BEGIN
IF EXISTS (SELECT Email FROM Subscribe WHERE Email = @email AND Subscribed = 1)
BEGIN
SET @id = 0
END
ELSE IF EXISTS(SELECT Email FROM Subscribe WHERE Email = @email AND Subscribed = 0)
BEGIN
UPDATE Subscribe
SET Subscribed = 1
WHERE Email = @email
SET @id = (SELECT SubscribeId
FROM Subscribe
WHERE Email = @email)
END
ELSE
BEGIN
INSERT INTO Subscribe VALUES(@email,1)
SET @id = (SELECT SCOPE_IDENTITY())
END
RETURN @id
END
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
table th
{
background-color: #F7F7F7;
color: #333;
font-weight: bold;
}
table th, table td
{
padding: 5px;
border-color: #ccc;
}
</style>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<script type="text/javascript">
$(function () {
$('[id*=btnSubscribe]').click(function () {
var emailId = $('[id*=txtEmail]').val();
if (emailId != '') {
$.ajax({
type: "POST",
url: "Default.aspx/SendMail",
data: '{emailId : "' + emailId + '"}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
alert(r.d);
}
});
}
else {
alert('Please Enter Mail Id!');
}
return false;
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table id="tblSubscribe" runat="server">
<tr>
<td>
<asp:TextBox ID="txtEmail" CssClass="form-control" runat="server" ValidationGroup="valid" />
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnSubscribe" class="btn btn-primary" ValidationGroup="valid" Text="Subscribe"
runat="server"></asp:Button>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["unsubscribe"] != null)
{
string subscribeId = Request.QueryString["unsubscribe"];
subscribeId = Decrypt(subscribeId);
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "SELECT SubscribeId FROM Subscribe WHERE SubscribeId = @Id AND Subscribed = 1 ";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@Id", subscribeId);
con.Open();
subscribeId = cmd.ExecuteScalar() != null ? cmd.ExecuteScalar().ToString() : string.Empty;
if (!string.IsNullOrEmpty(subscribeId))
{
string updateQuery = "UPDATE Subscribe SET Subscribed = 0 WHERE SubscribeId = @Id";
SqlCommand cmdUpdate = new SqlCommand(updateQuery, con);
cmdUpdate.Parameters.AddWithValue("@Id", subscribeId);
cmdUpdate.ExecuteNonQuery();
Response.Write("You have Unsubscribed Successfully!");
}
else
{
Response.Write("You have already Unsubscribed!");
}
con.Close();
tblSubscribe.Visible = false;
}
}
}
}
[System.Web.Services.WebMethod]
public static string SendMail(string emailId)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "Subscribe_NewsLetter";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@email", emailId);
cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
string subscribeId = cmd.Parameters["@id"].Value.ToString();
con.Close();
if (!string.IsNullOrEmpty(subscribeId))
{
if (subscribeId == "0")
{
return "You have already Subscribed";
}
}
subscribeId = Encrypt(subscribeId);
// Your send mail code comes here and pass text and html file.
// and unsubscribing link with querystring encrypted so that no one can tamper with link.
string urlWithUnsubscribe = HttpContext.Current.Request.Url.ToString().Substring(0, HttpContext.Current.Request.Url.ToString().LastIndexOf('/'));
urlWithUnsubscribe += "?unsubscribe=" + subscribeId;
return urlWithUnsubscribe;
}
}
}
private static string Decrypt(string cipherText)
{
string EncryptionKey = "MAKV2SPBNI99212";
cipherText = cipherText.Replace(" ", "+");
byte[] cipherBytes = Convert.FromBase64String(cipherText);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
{
cs.Write(cipherBytes, 0, cipherBytes.Length);
cs.Close();
}
cipherText = Encoding.Unicode.GetString(ms.ToArray());
}
}
return cipherText;
}
private static string Encrypt(string clearText)
{
string EncryptionKey = "MAKV2SPBNI99212";
byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
cs.Close();
}
clearText = Convert.ToBase64String(ms.ToArray());
}
}
return clearText;
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Request.QueryString("unsubscribe") IsNot Nothing Then
Dim subscribeId As String = Request.QueryString("unsubscribe")
subscribeId = Decrypt(subscribeId)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Dim query As String = "SELECT SubscribeId FROM Subscribe WHERE SubscribeId = @Id AND Subscribed = 1 "
Using cmd As New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@Id", subscribeId)
con.Open()
subscribeId = If(cmd.ExecuteScalar() IsNot Nothing, cmd.ExecuteScalar().ToString(), String.Empty)
If Not String.IsNullOrEmpty(subscribeId) Then
Dim updateQuery As String = "UPDATE Subscribe SET Subscribed = 0 WHERE SubscribeId = @Id"
Dim cmdUpdate As New SqlCommand(updateQuery, con)
cmdUpdate.Parameters.AddWithValue("@Id", subscribeId)
cmdUpdate.ExecuteNonQuery()
Response.Write("You have Unsubscribed Successfully!")
Else
Response.Write("You have already Unsubscribed!")
End If
con.Close()
tblSubscribe.Visible = False
End Using
End Using
End If
End Sub
<System.Web.Services.WebMethod()> _
Public Shared Function SendMail(emailId As String) As String
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Dim query As String = "Subscribe_NewsLetter"
Using cmd As New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@email", emailId)
cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output
cmd.CommandType = System.Data.CommandType.StoredProcedure
con.Open()
cmd.ExecuteNonQuery()
Dim subscribeId As String = cmd.Parameters("@id").Value.ToString()
con.Close()
If Not String.IsNullOrEmpty(subscribeId) Then
If subscribeId = "0" Then
Return "You have already Subscribed"
End If
End If
subscribeId = Encrypt(subscribeId)
' Your send mail code comes here and pass text and html file.
' and unsubscribing link with querystring encrypted so that no one can tamper with link.
Dim urlWithUnsubscribe As String = HttpContext.Current.Request.Url.ToString().Substring(0, HttpContext.Current.Request.Url.ToString().LastIndexOf("/"c))
urlWithUnsubscribe += Convert.ToString("?unsubscribe=") & subscribeId
Return urlWithUnsubscribe
End Using
End Using
End Function
Private Shared Function Decrypt(cipherText As String) As String
Dim EncryptionKey As String = "MAKV2SPBNI99212"
cipherText = cipherText.Replace(" ", "+")
Dim cipherBytes As Byte() = Convert.FromBase64String(cipherText)
Using encryptor As Aes = Aes.Create()
Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, _
&H65, &H64, &H76, &H65, &H64, &H65, _
&H76})
encryptor.Key = pdb.GetBytes(32)
encryptor.IV = pdb.GetBytes(16)
Using ms As New MemoryStream()
Using cs As New CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write)
cs.Write(cipherBytes, 0, cipherBytes.Length)
cs.Close()
End Using
cipherText = Encoding.Unicode.GetString(ms.ToArray())
End Using
End Using
Return cipherText
End Function
Private Shared Function Encrypt(clearText As String) As String
Dim EncryptionKey As String = "MAKV2SPBNI99212"
Dim clearBytes As Byte() = Encoding.Unicode.GetBytes(clearText)
Using encryptor As Aes = Aes.Create()
Dim pdb As New Rfc2898DeriveBytes(EncryptionKey, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, _
&H65, &H64, &H76, &H65, &H64, &H65, _
&H76})
encryptor.Key = pdb.GetBytes(32)
encryptor.IV = pdb.GetBytes(16)
Using ms As New MemoryStream()
Using cs As New CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write)
cs.Write(clearBytes, 0, clearBytes.Length)
cs.Close()
End Using
clearText = Convert.ToBase64String(ms.ToArray())
End Using
End Using
Return clearText
End Function
ScreenShot
