To read the article online, visit http://www.4GuysFromRolla.com/articles/033104-1.2.aspx

Creating a Banner Ad System in ASP.NET, Part 2

By Paul Apostolos


  • Read Part 1

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

       Set @RandNum = (Round(((@RecordCount - 1) * Rand() + 1), 0))

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

       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:

    1. Display a list of all banner ads
    2. Allow users to add new banner ads
    3. Allow users to view/edit existing banner ads and click totals
    4. 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!

    Happy Programming!

  • By Paul Apostolos


    Attachments:


  • Complete Code and SQL Scripts (in ZIP format)

  • Article Information
    Article Title: ASP.NET.Creating a Banner Ad System in ASP.NET, Part 2
    Article Author: Paul Apostolos
    Published Date: March 31, 2004
    Article URL: http://www.4GuysFromRolla.com/articles/033104-1.2.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers