How to Implement Landing Page Hit Tracking in ASP.NET (Part 3)
- by Scott Whigham on January 23, 2009 10:30 AMIn 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!
Netter Beitrag. Ich lese jetzt weiter hier im Blog.
I am quite new to wordpress. but what you write in this blog is really good and very informative. I think it will help me in the future. Thanks for the great work. I put a link to your site at my blog,hope you dont mind?
Well I arrived here on another post but ended up staying for 20 minutes reading your stuff! Enjoyed it :-D
great i tend to agree with what you said. http://www.rapidsloth.com/Black-Rebel-Motorcycle-Club---Love-Burns.html has users that raise this topic and give reviews. i find it interesting and its wroth a read.
are you a fan of blackjack, roulette, slots ? check out the latest free online casino games at the all new www.realcazinoz.com, the finest online casino guide on the web.
You're right on the money with this article, keep up the good work!
loved vegas and a fan of blackjack, roulette, slots ? check out the latest free online casinos games at the all new www.realcazinoz.com, the finest online casino guide on the web.
To enjoy a game of poker, there is no need to travel to famous casino destinations. You can live the excitement by playing video poker at www.realcazinoz.com . You can even enjoy the all-time and now favorite Texas Hold’em. Best of all, you don’t have to carry a lot of cash with you, when at your home casino; a selection of deposit options make it easy for players to get started. Play video poker and begin to win. Video poker has become a popular game and our casino welcomes players, by matching their deposit up to $250.00. This means you have more cash to begin playing. Try Texas Hold’em; the objective in this game is to make positive decisions as to when and how much to wager, when to use certain tactics like raise, call or fold. Each player begins with two cards, and the remaining cards are shared among players.
You should definitely check out the free $50 bonus being offered at our new Bet Phoenix Casino. Limited time offer at: Bet Phoenix. Good luck!
You should really sign-up for the free $10 bonus at SlotoCash. Only available now at: SlotoCash. Good luck!
Hi, i am mike.Thank's for sharing this news.This is very useful and informative material.Good post and keep it up.website are always helpful one way in the other .That"s cool stuff like my new iphone(ssttt Dont tell anyone if i bought it yesterday).Anyways mobile phone like Iphone is a good way to get started to renoute your dreams into the world of realty. LOL iphone again. ^^thank u
This is a nice blog which helps me to get more information to the topic. I will bookmark it!
You should definitely take a look at the free $25 bonus at Saturn Bingo. I won $200 playing there!
Excellent post to read, I just passed this onto a colleague who was doing a little search on that. And he actually bought me lunch because I found this for him smile. So let me rephrase that: Thanks for lunch!
I think you should get out there and see the new releases as soon as possible. Some of the big blockbusters are quite mind blowing and are setting new standards. I think there is also going to be a growing trend towards more specialist small budget movies from independant studios that are going to be worth a look too.
Hi, i am ivan.Thank's for sharing this post.This is very useful and informative material.Good post and keep it up.Blogs are always helpful one way like Gadgetrix.com . because in this blog,Olympus is more better than Nikon for digital slr camera . bye bye http://gadgetrix.com/about
My pals and I very much love facebook's new "like" feature. Want to be able to "like" anything you want? Check out this site, it does exactly that: www.fbliker.net
Good blog.
I thought it was going to be some boring old site, but I'm glad I visited. I will post a link to this page on my blog. I believe my visitors will find that very useful.
This is my first time I have visited your site. I found a lot of interesting stuff in your blog. From the volume of comments on your posts, I guess I am not the only one! keep up the good work.
I’ve gotten some good information on this site. Definitely worth checking out for future reference.
Nice article it's very, i have just created my own website and it looks like this will help me a lot! Do you have any tips for me? ~ www.webmastershelpguide.com/
The web-site above states you can end up with a free iPad on the internet..... Has anybody tried some of these free iPads offers?????
In fact, some of the Swiss luxury watch, in appearance and identity numbers are there, and done a lot of anti-counterfeiting
measures,. For example, Rolex has 5 numbers, they are: (1) case models, (2) Watch production sequence number
movement and on the number, (4) the movement and on the production sequence number, (5) the band number. In addition to the
band number in the band discount out surface, the rest are hidden, not to remove the watch strap or open the rear door in order to
see it.
post? Is actually this in turn placed under creative commons?
Sharyl Mccandrew
A credit card allows the simple Shopping via the internet and is despite secuity problems the important payment method. It will be nice if there an alternative soon.
Great post!
I am wondering whats the best canvas carrying case cover for iPad available in the market? So far I have found a good stock at bing and the local walmart
Is anyone using the Apple wireless keyboard with their iPad? If so, have you come across any problems?
Is anyone using the Apple wireless keyboard on their iPad? If so, have you come across any issues?
Fascinating…undoubtedly food for thought. I hope you do not mind if I send this on to a few other folks I know.
I am mainly here to help, you'll need to check comments here. Definitely.
Hi buddy, I'm just browsing the networks seek some info and saw your page. I am striked by the content that you own on this blog. It displays how good you grasp this subject. Bookmarked this website, will come back for further. You, buddy, ROll!!!
Watch Harry Potter And The Half Blood Prince Online Free
Hi!. Thanks for the article. I¡¯ve been digging around for information, but I think I¡¯m getting lost!. Yahoo lead me here ¨C good for you I suppose! Keep up the good work. I will be returning over here in a few days to see if there is any additional info.
Very sufficient to pay for it, all that we need are most excellent.
Very Interesting Blog! Thank You For Thi Blog!
Which golf clubs will be the best for beginner ?
Being a rookie blogger myself personally, it is important to see a decent blogger on a regular basis. I plan to master a lot from looking at other individuals posts and Personally i think that yours has a good deal to offer the audience, appreciate it for finding the time and making the effort. Regards
amazing! you must have done a lot of perform on this. thanks for sharing. this weblog goes in my bookmarks.
As being a newcomer blogger personally, absolutely awesome you just read a quality blogger for something different. I plan to find out a lot from looking at some others personal blogs and Personally i think that your own has quite a lot to offer the subscriber, many thanks for finding the time and making the effort. Daisy
Hey...from my experience I didn't had a good read from a long time.Really happy i noticed it on bing.I was speaking with my colleagues three days ago about this topic and I have to say you make things very clear for me.Thanks and good luck
amazing! you should have carried out a good deal of function on this. thanks for sharing. this weblog goes in my bookmarks.
I can't decide between getting a small digital camera like a Canon Powershot or spending the big bucks on a DSLR. Are DSLR cameras worth the extra money?
Which golf clubs will be the best for beginner ?
Very Interesting Blog! Thank You For Thi Information!
Your blog theme looks cool. What template did you use ?
Heyy dude, can i post articles to your website ? Let me know if you are interested
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Very informative post. Thanks for taking the time to share your view with us.
This really is really a useful suggestion. I have just included this on faves.com
Very informative post. Thanks for taking the time to share your view with us.
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Over de voor- en nadelen van het afsluiten van een lening zonder BKR-toetsing.
I just sent this post to a bunch of my friends as I agree with most of what you’re saying here and the way you’ve presented it is awesome.
Very Interesting Blog! Thank You For Thi Information!
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
Leven met migraine: hoe ga je met je migraine om en wat is de beste behandeling?
A very interesting read and a great post alltogether. Would you mind if I posted the same article on my blog (with a reference to your website)?
A very interesting read and a great post alltogether. Would you mind if I posted the same article on my blog (with a reference to your website)?
A very interesting read and a great post alltogether. Would you mind if I posted the same article on my blog (with a reference to your website)?
A very interesting read and a great post alltogether. Would you mind if I posted the same article on my blog (with a reference to your website)?
Great text and nice site.
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
Very informative post. Thanks for taking the time to share your view with us.
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Thanks For This Blog, was added to my bookmarks.
Your blog site may be appearing issues on my Firefox browser. Thanks.
Can you be fascinated to become website link partners?
You will find spam reviews in your blog.
As you mention John Deere, brings me way back to being a lad and reminds me of being on my granddad's green tractor, great great great days...anyway, great post, Just shared this on digg
I always love to pick up more blackjack tips. Someone would never quess how involved this game can be. Thanks for the wonderful info...
Great post, I bet a lot of work and research went into this article.
Great post!