How to Implement Landing Page Hit Tracking in ASP.NET (Part 3)

In Part 1 we talked about the goals of the project and the two base tables. In Part 2 we talked about the LandingPageHit table

Now that we have the base structures, the next (and near-final) database-driven piece would be the stored procedures to audit the hit, verify a hit exists, and associate a User with a hit upon registration. Execute this code:

     CREATE PROC dbo.AddLandingPageHit (

        @AbsolutePath VARCHAR(512)

        , @AbsoluteUri VARCHAR(2048)

        , @QueryString VARCHAR(256)

        , @IpAddress VARCHAR(128) = NULL

        , @ReferringUrl NVARCHAR(1024) = NULL

        , @LandingPageHitId UNIQUEIDENTIFIER OUT

    )

    AS

    DECLARE @LandingPageId INT

    -- If a LandingPage with the same AbsoluteUri does not already exist, add this as a new LandingPage

    SET @LandingPageHitId = NULL -- Override any values passed in

    SELECT @LandingPageId = LandingPageId

    FROM dbo.LandingPage WITH(NOLOCK)

    WHERE AbsoluteUri = @AbsoluteUri

    IF @LandingPageId IS NOT NULL

        GOTO AddLandingPageHit

    ELSE -- Add Landing Page

        BEGIN

            INSERT dbo.LandingPage (AbsolutePath, AbsoluteUri, QueryString)

            VALUES (@AbsolutePath, @AbsoluteUri, @QueryString)

            SET @LandingPageId = SCOPE_IDENTITY()

        END

    AddLandingPageHit:

    BEGIN

        SET @LandingPageHitId = NEWID()

        INSERT dbo.LandingPageHit (LandingPageHitId, LandingPageId, IpAddress, ReferringUrl)

        VALUES (@LandingPageHitId, @LandingPageId, @IpAddress, @ReferringUrl)

    END

    GO

    CREATE PROC dbo.VerifyLandingPageHitIdExists 

    (

        @LandingPageHitId UNIQUEIDENTIFIER

        , @IpAddress VARCHAR(128)

        , @Exists BIT OUT

    )

    AS

    IF EXISTS (

        SELECT * 

        FROM dbo.LandingPageHit WITH(NOLOCK)

        WHERE LandingPageHitId = @LandingPageHitId

            AND IpAddress = @IpAddress

    )

        SET @Exists = 1

    ELSE

        SET @Exists = 0

    GO

    CREATE PROC dbo.AssociateUserWithLandingPage (

        @UserId INT

        , @LandingPageHitId UNIQUEIDENTIFIER

    )

    AS

    UPDATE dbo.LandingPageHit

        SET UserId = @UserId

    WHERE LandingPageHitId = @LandingPageHitId

    GO

This is some pretty advanced SQL code - it makes use of an output parameters (@LandingPageHitId and @Exists), SCOPE_IDENTITY(), NEWID(), NOLOCK, GOTO, and optional parameters. For more information about these, check out our Transact SQL Programming video tutorials. The three stored procedures are:

dbo.AddLandingPageHit - The end result of this is that it adds a hit to the LandingPageHit table and returns us a unique @LandingPageHitId that we can associate with this user on our website.
dbo.VerifyLandingPageHitIdExists (discussed later)
dbo.AssociateUserWithLandingPage(discussed later)

What we've done so far...

Just to make sure you're still with me, here's what we've done so far:

Step 1: Create a new SQL Server database. Execute the commands below in that database
Step 2: Create/open website project and add references
Step 3: Create a test page
Step 4: Create the LandingPageHit table and supporting objects

Time for Step 5!

Step 5: Create the LandingPage class

Now, back to Visual Studio. Add a new C# class and paste the following code into the file:

    using System;

    using System.Data;

    using System.Data.Common;

    using System.Collections.Generic;

    using System.Collections.Specialized;

    using System.Text;

    using System.Web;

    using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

    public class LandingPage

    {

        /// <param name="referringUrl">Must be a string since you cannot pass a null Uri</param>

        public Guid AuditLandingPageHit(Uri absoluteUriOfLandingPage, string ipAddress, string referringUrl, string connectionString)

        {

            HttpContext.Current.Request.Url

            SqlDatabase sqlDatabase = new SqlDatabase(connectionString);

            using (DbCommand myCommand = sqlDatabase.GetStoredProcCommand("dbo.AddLandingPageHit"))

            {

                sqlDatabase.AddInParameter(myCommand, "AbsoluteUri", DbType.String, absoluteUriOfLandingPage.ToString());

                sqlDatabase.AddInParameter(myCommand, "AbsolutePath", DbType.String, absoluteUriOfLandingPage.AbsolutePath);

                sqlDatabase.AddInParameter(myCommand, "QueryString", DbType.String, absoluteUriOfLandingPage.Query);

                if (!String.IsNullOrEmpty(ipAddress))

                    sqlDatabase.AddInParameter(myCommand, "IpAddress", DbType.String, ipAddress);

                if (!String.IsNullOrEmpty(referringUrl))

                    sqlDatabase.AddInParameter(myCommand, "ReferringUrl", DbType.String, referringUrl);

                sqlDatabase.AddOutParameter(myCommand, "LandingPageHitId", DbType.Guid, 16);

                sqlDatabase.ExecuteNonQuery(myCommand);

                return (Guid)sqlDatabase.GetParameterValue(myCommand, "LandingPageHitId");

            }

        }

        /// <summary>

        /// Scenario: a user clicks an ad for ChildSite and gets a LandingPageHitId created on ChildSite. They click Add to Cart and

        /// get forwarded to parent or click through to a separate child site. We want their account/conversion associated

        /// with the first ad. All outbound URLs on ChildSite are written with the LPID. When the user then hits another of our

        /// sites, we verify that this is a valid LPID and, if so, the new site sets a cookie so that, if the user later

        /// registers, we can track their registration to the original LPID.

        /// </summary>

        public bool VerifyLandingPageHitIdExists(Guid landingPageHitId, string ipAddress, string connectionString

            )

        {

            SqlDatabase sqlDatabase = new SqlDatabase(connectionString);

            using (DbCommand myCommand = sqlDatabase.GetStoredProcCommand("dbo.VerifyLandingPageHitIdExists"))

            {

                sqlDatabase.AddInParameter(myCommand, "LandingPageHitId", DbType.Guid, landingPageHitId);

                sqlDatabase.AddInParameter(myCommand, "IpAddress", DbType.String, ipAddress);

                sqlDatabase.AddOutParameter(myCommand, "Exists", DbType.Boolean, 1);

                sqlDatabase.ExecuteNonQuery(myCommand);

                return Convert.ToBoolean(sqlDatabase.GetParameterValue(myCommand, "Exists"));

            }

        }

        /// <summary>

        /// After a user registers, we associate a landing page with them if they had a cookie set on client

        /// </summary>

        public void AssociateUserWithLandingPage(int UserId, Guid landingPageHitId, string connectionString)

        {

            SqlDatabase sqlDatabase = new SqlDatabase(connectionString);

            using (DbCommand myCommand = sqlDatabase.GetStoredProcCommand("dbo.AssociateUserWithLandingPage"))

            {

                sqlDatabase.AddInParameter(myCommand, "UserId", DbType.Int32, UserId);

                sqlDatabase.AddInParameter(myCommand, "LandingPageHitId", DbType.Guid, landingPageHitId);

                sqlDatabase.ExecuteNonQuery(myCommand);

            }

        }

    }

You can see that we have three classes that will execute our stored procedures. The DbCommand has us execute the stored procedures. Later on, when we use these classes, we'll pass in the Uri (absoluteUriOfLandingPage) from HttpContext.Current.Request.Url. The most interesting piece of this is the output parameter, @LandingPageHitId. This is the GUID that SQL Server sends back - it will look something like CA9E941B-CC7A-41B6-9996-00035A1AED27. It's a unique value - and that's what we want.

Step 6: Create a WebsiteUtility class

Create a new class file called "WebsiteUtilities" and paste the following code:

    using System;

    using System.Web;

    public class WebsiteUtilities

    {

        public static string ConnectionString

        {

            get { return "server=localhost;Trusted_Connection=true;database=LearnItFirst"; }

        }

        public static string GetLandingPageHitId(HttpRequest request)

        {

            if (!string.IsNullOrEmpty(request.Cookies.Get("LandingPageHitId").Value))

                return request.Cookies.Get("LandingPageHitId").Value;

            else

                return null;

        }

        public static void SetLandingPageHitCookie(HttpRequest request)

        {

            // If user already has a cookie, skip setting a new one

            if (!String.IsNullOrEmpty(GetLandingPageHitId(request)))

                return;

            // First time visitor - log the hit

            try

            {

                var track = new LandingPage();

                Guid landingPageHitId = track.AuditLandingPageHit(

                    request.Url

                    , request.UserHostAddress

                    , (request.UrlReferrer != null ? request.UrlReferrer.ToString() : null)

                    , ConnectionString

                    );

                HttpCookie cookie = new HttpCookie("LandingPageHitId", landingPageHitId.ToString());

                cookie.Expires = DateTime.Now.AddDays(60);

                HttpContext.Current.Response.Cookies.Set(cookie);

            }

            catch (Exception exc)

            {

                // Do something awesome

            }

        }

    }

The logic:

ConnectionString - this might be in your web.config, I don't know...
GetLandingPageHitId - if someone already has a cookie set for the LandingPageHitId, it uses that otherwise it creates a new record in the LandingPageHit table and returns the LandingPageHitId
SetLandingPageHitCookie - does what it says...

TIME OUT...

Take a break...

Relax...

This is some heavy stuff...

Come back in part 4 and we'll wire it all up!