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 @Category that 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:
CREATE PROCEDURE NRCA_sp_Get_Random_Banner
(
@Category varchar(20)
)
AS
Declare @Count smallint
Declare @MaxWeight smallint
Declare @RecordCount int
Declare @RandNum int
CREATE TABLE #BannerAdTempTable
(
Temp_Id int identity(1,1),
Ad_Id int
)
In the next segment I initialize the values of two of the variables. @Count will be used as a counter
for a while loop later so I set its initial value to zero. I set the value of @MaxWeight
to the maximum weight of all of the banner ads in the same category that are currently active.
Set @Count = 0
Set @MaxWeight =
(
SELECT Max(Weight)
FROM tbl_NRCA_Banner_Ads
WHERE Category = @Category AND
StartDate < getdate() AND
EndDate > getdate() AND
Active = 1
)
In order to allow for weighting, I create a while loop that will exit when the value in the variable
@Count is equal to @MaxWeight. Because @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 Weight
attribute is greater than @Count.
Finally, the @Count variable is incremented.
WHILE (@MaxWeight) > @Count
BEGIN
INSERT INTO #BannerAdTempTable
Select Ad_Id From tbl_NRCA_Banner_Ads Where
Page = @Page AND
StartDate < getdate() AND
EndDate > getdate() AND
Active = 1 AND
Weight > @Count
Set @Count = @Count + 1
END
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 @RecordCount
to the number of records in the temporary table.
Set @RecordCount = (Select Count(*) From #BannerAdTempTable)
Then, I use the SQL Server function Rand() to select a random number that will correspond to a
Temp_Id from the temporary table.
Finally, I select the random ad from the banner ad table.
Select Ad_Id, Image_Src_Path, Height, Width, Alt_Text
From tbl_NRCA_Banner_Ads
WHERE Ad_Id = (Select Ad_Id From #BannerAdTempTable Where Temp_Id = @RandNum)
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.ascx User Control, set its
Category property, and place the User Control on the page wherever you want the banner to appear.
<%@ Page Trace="False" Language="VB" %>
<%@ Register TagPrefix="MyControls" TagName="banner" Src="banner.ascx" %>
<script runat="server">
Public Sub Page_Load()
banner_ad.page = "home_page"
End Sub
</script>
<html>
<body>
Sample page with banner ad<br><br>
<MyControls:banner id="banner_ad" runat="server" />
</body>
</html>
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
with its href attribute set to an ASP.NET page. This page accepts a querystring variable named Ad_Id,
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.
CREATE PROCEDURE NRCA_sp_Banner_Ad_Tracking_Insert
(
@Ad_Id int,
@IP_Address varchar(20),
@URL varchar(255) output
)
AS
INSERT INTO tbl_NRCA_Banner_AdTracking
(
Ad_Id,
IP_Address
)
VALUES
(
@Ad_Id,
@IP_Address
)
Set @URL = (SELECT Redirect_URL
FROM tbl_NRCA_Banner_Ads WHERE Ad_Id = @Ad_Id)
GO
<%@ Page Trace="False" Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Sub Page_Load()
'//Make sure there is an incoming Ad_Id
If Len(Request.QueryString("Ad_Id")) > 0 Then
try
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
myConnection = New _
SqlConnection( ConfigurationSettings.AppSettings("MySQLDSN") )
myCommand = New _
SqlCommand("NRCA_sp_Banner_Ad_Tracking_Insert", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterAd_Id As New SqlParameter("@Ad_Id", SqlDbType.Int, 4)
parameterAd_Id.Value = Request.Querystring("Ad_Id")
myCommand.Parameters.Add(parameterAd_Id)
Dim parameterIP_Address As New SqlParameter("@IP_Address", _
SqlDbType.VarChar, 50)
parameterIP_Address.Value = Request.ServerVariables("REMOTE_HOST")
myCommand.Parameters.Add(parameterIP_Address)
Dim parameterURL As New SqlParameter("@URL", _
SqlDbType.VarChar, 255)
parameterURL.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterURL)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close
Response.Redirect(parameterURL.Value)
catch exp As Exception
Trace.Write(exp.ToString())
end try
End If
End Sub
</script>
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 ParameterDirection.Output
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
details.
Conclusion
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!
