Creating a Banner Ad System in ASP.NET, Part 2By Paul Apostolos
In Part 1 of this article we examined the requirements for the banner ad system, along with the data model for storing banners and a User Control for displaying a banner. In this part we'll turn our attention to the stored procedure that does all the heavy lifting in actually retreiving a random banner.
Step 5: Creating the
NRCA_sp_Get_Random_Banner Stored Procedure
The real meat of this ad system is the stored procedure
NRCA_sp_Get_Random_Banner. An examination of that code follows. I begin the procedure by declaring an input parameter named
@Categorythat will be used to narrow the result set to the appropriate category. For example, the category might be "Homepage Ads", or "ASP Section Ads". I also declare four variables and create a temporary table that will be used in the body of the procedure.
The temporary table has two attributes: an identity field
Temp_Id and a field that will hold an
Ad_Id of a banner ad. The variables will be explained as they are used. The stored procedure, then,
begins like so:
In the next segment I initialize the values of two of the variables.
@Count will be used as a counter
while loop later so I set its initial value to zero. I set the value of
to the maximum weight of all of the banner ads in the same category that are currently active.
In order to allow for weighting, I create a
while loop that will exit when the value in the variable
@Count is equal to
@Count is initialized to zero this will
be an accurate representation of the output (I also could have initialized
@Count to one and set the
condition of the while loop to
@MaxWeight >= @Count). For each iteration of the loop I insert an
Ad_Id into the temporary table from the banner ad table for each banner ad whose
attribute is greater than
@Count variable is incremented.
A sample of the temporary table's contents is shown below. As you can see, for a banner with weight x, there will be x rows in the temporary table. Therefore, a banner table with, say, a weight eight times that of another, will have eight times as many rows in this temporary table.
At this point I have a table populated with a weighted representation of the appropriate banner ads for that page.
Now I want to select a random record from that table. To do this, I first set the value of
to the number of records in the temporary table.
Then, I use the SQL Server function
Rand() to select a random number that will correspond to a
Temp_Id from the temporary table.
(For more information on using a stored procedure to select a random record check out Getting a Random Record Using a Stored Procedure.)
Finally, I select the random ad from the banner ad table.
Step 6: Displaying a Random Banner on an ASP.NET Web Page
To display a random ad banner on an ASP.NET Web page you need to register the
Banner.ascxUser Control, set its
Categoryproperty, and place the User Control on the page wherever you want the banner to appear.
That's it for displaying the banner ad, now all that's left is to build the click tracking page and the administration page to allow the advertising sales department staff to administer the site's banner ads themselves!
Maintain Click Tracking
When designing an advertising system, you will be repeatedly asked to provide statistics. The advertisers themselves are usually very interested on how many times their ads have been shown and how many click-throughs they've generated, and the advertising department is also interested in such statistics to help gauge what ad styles and placements are performing the best.
My banner system provides click-through tracking. That is, each time a particular banner is clicked by a user, it
increments a "hit" count for that banner. This is accomplished by having the banner ad image enclosed in a hyperlink
href attribute set to an ASP.NET page. This page accepts a querystring variable named
which is the unique ID for the banner. The page then uses a stored procedure to insert the
Ad_Id and the user's IP address into the banner ad tracking table. The stored procedure returns the
value of the
Redirect_URL from the banner ad table corresponding to the
Ad_Id and the page
redirects the user to the appropriate link.
The following two code snippets show: (1) the stored procedure used to record the click-through and return the URL to redirect the user to, and (2) the ASP.NET Web page that utilizes the stored procedure and redirects the user to the appropriate URL after recording the click through.
The only new item in the above code is the output parameter in the stored procedure. To use output parameters with
stored procedures, change the parameter's
Direction property to
and don't set the parameter's value. After the command is executed, the parameter's value will be available.
The Administration Page
At this point we have created the necessary User Controls, ASP.NET Web pages, and stored procedures to have a fully functional banner advertising system. The only problem is that, currently, the only means to add new banners to the rotation is to manually add a row into the corresponding SQL Server table. Since one of the requirements when designing this system was to make it easy for the advertising department to be able to manage the advertising rotation themselves, I needed to create a series of administration pages.
The banner administration page, at the very least, needs to provide four functions:
- Display a list of all banner ads
- Allow users to add new banner ads
- Allow users to view/edit existing banner ads and click totals
- Allow users to delete banner ads
In the code download there is an administration page (
administration.aspx). This page lists all of the banners
currently in the system in a DropDownList. The user can select an existing banner and edit its properties. A user
can also create a new banner ad or delete an existing banner ad. Check out the code in the download for more
This article demonstrated how to use ASP.NET and SQL Server to build a banner ad system complete with click tracking, weighting, and backend administration functions. My only regret is that the advertising sales staff no longer offers me homemade baked goods for speedy report delivery or new advertisement placement!