Mailing List Using ASP.NET and SQL Server

Maintaining contact with your customers and providing timely information is critical to the success of your website. The simplest way to do this is using a one-way distribution list for announcements. There are plenty of paid services available such as Constant contact but why pay someone else what you can build into your own web site.

• Step 1. SQL Server table and stored procedures
• Step 2. Create ASP.NET Mailing List Admin Page
• Step 3. Create ASP.NET Mailing List Removal Page

Step 1. SQL Server table and stored procedures

First we create a table called "mailinglist". Open SQL Server and open a new query window in your database. Cut and paste the following SQL statement below to create the table we'll be using for our mailling list. This table will store our email addresses.

CREATE TABLE [dbo].[mailinglist](
	[customerid] [int] IDENTITY(1,1) NOT NULL,
	[email] [varchar](75) NULL,
 CONSTRAINT [PK_mailinglist] PRIMARY KEY CLUSTERED 
(
	[customerid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Next we create a stored procedure to loop through email addresses when we send our messages out later.

CREATE  PROCEDURE [dbo].[sp_mailinglist_select] 
AS
BEGIN
SELECT     TOP (100) PERCENT Email, customerid
FROM         dbo.mailinglist
WHERE     (Email IS NOT NULL)
ORDER BY email
END

Now we create a stored procedure to allow our customers remove themselves from the announcements. We use their email address and their customerID in the table as criteria so only they can remove themselves. The messages that we will be sending out will have a hardcoded url for the customer to remove themselves.

CREATE  PROCEDURE [dbo].[sp_mailinglist_remove] 
(
@ID integer,
@email varchar(100)
)
AS
begin
delete from customer where email=@email and customerid=@ID
end

Be sure to assign execute permissions for these stored procedure to the sql login that your web site uses to query the database.

Being good programmers we always test our code on the database before hooking it into a web page so manually execute the stored procedure we just created in a query window

Next Lesson

In Step 2. ASP.NET Mailing List Admin page we'll setup an ASP.NET page to send our announcements to the mailing list users.