|
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
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.
|
