asp.netNOW Q&A
LANGUAGES: C#
TECHNOLOGIES: QueryStrings | Stored Procedures
Update Data Using a URL
Create an unsubscribe page that uses a URL to remove an entry in the database.
By Josef Finsel
Q: I'd like to create an unsubscribe page that uses a URL to remove an entry in the database. How can I do this?
- SF, via Hotmail
A. This actually isn't very hard to do but it does incorporate several elements that I've discussed in past installments of this newsletter, along with a couple of new ones.
The first thing you shouldn't do is open up your database to a web page. Microsoft has provided a neat tool in SQL Server 2000 that integrates XML with SQL Server in IIS and allows you to interface with a database by using a URL like this:
http://localserver/RDABA?SQL=SELECT+*+FROM+Owners+WHERE+OwnerID<3+FOR+XML+AUTO&ROOT=ROOT
However, since a user could just as easily put DELETE+FROM+Owners, this is not the best way to interface with your database, convenient though it may be. And, while a Web Service is another version of interfacing with a database through a web page, it's a bit more that what we need here.
The best way to handle this is through web pages, using QueryString and a little obfuscation (for more information on QueryStrings, see QueryString, Params, and Forms - Oh My!). Let's start with a simple database. The CreateASPNetPro020103.sql script (available for download) will do several things . First, it will create a database called ASPNETPRO020103. Then it will create a table in the database and fill it with some data. It will create two stored procedures and a login that has permission to access those procedures. The table is simple. It contains an email address, a name, an identifier and a flag that determines whether to send mail to the user or not.
Let's take a quick moment to look at the stored procedure in Figure 1. Everything in this stored procedure could be done in Transact SQL calls directly from the web form - but there are good reasons not to. The first is scalability. Calling the stored procedure requires a lot less network overhead and SQL Server takes steps to make procedures run faster than straight SQL calls. The second, and perhaps more important reason, is security. One thing that the SQL Script does is to create a login, MailingList, that has permission to run the stored procedures. By using a login with limited access we further limit the ability of a hacker attempting to do things with our data that we haven't planned on. General database access should never be done with the sa account and that account should have a password on it.
CREATE PROCEDURE OptOutStep1 @EmailAddress varchar(50), @Identifier char(36) OUTPUT
AS
IF EXISTS (SELECT * FROM MailingList WHERE EmailAddress=@EmailAddress)
BEGIN
SET @Identifier = NewID()
UPDATE MailingList SET Identifier = @Identifier WHERE EmailAddress=@EmailAddress
END
ELSE
SET @Identifier = ''
Figure 1. This simple stored procedure takes an email address, verifies that it exists and then stores a unique identifier, which it returns in the output parameter.
Now that we have a general idea of the database we'll be using and how it works, let's look at what we need to do. If someone wants to unsubscribe from a mailing list, we'll need some way for them to enter their email address. This is handled in the MailingListOptOut.aspx page. The main code we want to look at can be found in the button click event in Figure 2. Here we connect to the SQL Server and execute the stored procedure outlined in Figure 1. This particular stored procedure has two parameters: @EmailAddress and @Identifier, with the latter being the more important one for the purpose of this article because the unique identifier is reutrned in an output parameter. Returning the data as an output parameter takes a little more work than returning a single row of data but is far more scalable since SQL Server doesn't have to create the records to return them and you don't have to unpack them on the .NET side.
As you can see, we create the connection and then the command. The next step is the most confusing, and that's creating the parameters. You'll notice that the stored procedure has two parameters but we are creating three. That's because SQL Server always has a return code, which makes stored procedures something like a function and the parameters can function ByVal or ByRef. In this case, @EmailAddress is ByVal and doesn't change but @Identifier is ByRef and contains the GUID we need to use to remove the person from the mailing list.
string strReturnCode;
string strNewURL;
SqlConnection cnMailingList = new SqlConnection("data source=(local);initial catalog=aspnetpro020103;password=optional;persist security info=True;user id=mailinglist;packet size=4096");
// Create the command object and define it as a stored procedure
SqlCommand cmdOptOut1 = new SqlCommand("OptOutStep1",cnMailingList);
cmdOptOut1.CommandType=CommandType.StoredProcedure ;
//Create the return parameter
SqlParameter pRetVal = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
pRetVal.Direction = ParameterDirection.ReturnValue ;
//Create the email parameter and give it a value
SqlParameter pEmail = new SqlParameter("@EmailAddress",SqlDbType.VarChar,50);
pEmail.Value = txtEmail.Text;
// Create the output parameter
SqlParameter pIdentifier = new SqlParameter("@Identifier",SqlDbType.Char,36);
pIdentifier.Direction= ParameterDirection.Output;
// Add the parameters to the stored procedure
cmdOptOut1.Parameters.Add(pRetVal);
cmdOptOut1.Parameters.Add(pEmail );
cmdOptOut1.Parameters.Add(pIdentifier);
// Open the connection, execute the command and close the connection
cnMailingList.Open();
cmdOptOut1.ExecuteNonQuery();
cnMailingList.Close();
//Create the URL for the next step
strReturnCode = pIdentifier.Value.ToString();
strNewURL = "OptOutVerification.aspx?ID=" + strReturnCode;
Figure 2. The code to call a stored procedure with an output parameter.
Once we have the parameters set, we open the connection to the database, execute the command, close the connection and get the value in the parameter. This is all pretty straightforward. If this were a real application, we would take the email address and GUID and generate a polite email saying that someone was attempting to unsubscribe the email account and if they wanted to do that, they should click on the provided link. Since I've covered emailing from a web page in a previous installment of this column (http://www.aspnetpro.com/features/2002/08/asp200208jf_f/asp200208jf_f.asp), I'm going to generate a link and display it in the browser directly. That link will look something like this:
http://localserver/20030201CS/OptOutVerification.aspx?ID=73442FCC-5178-4981-9507-01EA46EEF413
Now that we've gotten everything set up, we can finally address the question that we set out to answer. The link contains a named value pair of ID with the unique identifier. We can use that in the second web form (OptOutVerifcation.aspx) to flag the row in the database so that mail won't be sent any more. The main difference between this page and the last is the use of a QueryString so we need to get the value from that:
string strID;
NameValueCollection colQS=Request.QueryString;
strID = colQS.GetValues(0).GetValue(0).ToString();
From there, we can go on to access the second stored procedure that updates the database. Now, even though the stored procedure tells us whether we were successful or not through an output parameter, we aren't going to do anything with that information. That's because we don't want to give a hacker any unnecessary information. By simply putting a message up saying they've been unsubscribed (without giving away the email address) we accomplish our goal.
Keep your ASP.NET questions coming to me at [email protected].
The code referenced in this article is available for download.
Josef Finsel is a software consultant for a global consulting company and specializes in .NET and SQL Server. He has published a number of VB, .NET, and SQL Server articles and, when he isn't hanging around the aspenetpro forums you can find him working on the syntax for FizzBin.NET, a programming language that works the way programmers have always suspected. He's also author of The Handbook for Reluctant Database Administrators (Apress).
