Since not much after I launched Kartones.Net I added an ASP.NET user control to make the typical visits counter for all bloggers that wanted it (including me :P). Apart from having no code-behind, it had the SQL access inside (using a stored procedure and getting the configuration of the DB from ConnectionStrings.config, but still not layered).
So one step that was much needed was converting this to a proper Community Server 2007 control, both to take advantage of CS configuration & data providers, and to make the process of adding or moving a visits counter as easy as one line of ASP.NET markup code.
Last night I did it, among with using CSS sprites to do only one http request to obtain the images, and here it is the how-to:
First of all, I wanted a new table, to make it as small as possible, although we could have used the existing cs_weblog_Weblogs table by only adding a new long/numeric field. This is my SQL to create it:
USE [yourdbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cs_VisitCounters] (
[CounterID] [int] NOT NULL,
[HitsCounter] [int] NOT NULL,
CONSTRAINT [PK_CounterID] PRIMARY KEY NONCLUSTERED
(
[CounterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Then, we need a stored procedure to obtain the current hits. As I'm using a new table, what I do is if it doesn't exists creates it (else updates with +1):
USE [yourdbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[cs_RegisterVisitHit] (
@CounterID INT
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @CounterValue INT
SELECT @CounterValue = 0
SELECT
@CounterValue = [HitsCounter]
FROM
[yourdbname].[dbo].[cs_VisitCounters]
WHERE
[CounterID] = @CounterID
IF @CounterValue = 0
BEGIN
INSERT INTO
[yourdbname].[dbo].[cs_VisitCounters]
([CounterID], [HitsCounter])
VALUES
(@CounterID, 1)
END
ELSE
BEGIN
UPDATE
[yourdbname].[dbo].[cs_VisitCounters]
SET [HitsCounter] = (@CounterValue + 1)
WHERE [CounterID] = @CounterID
END
SELECT (@CounterValue+1)
END
GO
The SQL Part is done, now we need to add support for this stored procedure in the CS Data Providers.
First, we have to add an abstract method to CommunityServer.Components.CommonDataProvider:
public abstract long RegisterVisitHit(int CounterID);
And then, the actual code on the SqlDataProvider class:
public override long RegisterVisitHit(int CounterID)
{
long hits = 0;
using (SqlConnection connection = GetSqlConnection())
{
SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_RegisterVisitHit", connection);
SqlDataReader reader;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CounterID", SqlDbType.Int).Value = CounterID;
connection.Open();
reader = command.ExecuteReader();
if (reader.Read())
hits = Convert.ToInt64(reader[0]);
connection.Close();
}
return hits;
}
As we can see on the previous code, the great thing of using CS Data Providers is that we forget about creating connections to the DB, just get the instance and execute whatever we want.
Now we need to implement the control. As we don't want anything special, rendering as a simple LiteralControl would be enough, and we can directly inherit from CS WrappedContentBase class (one of the basic control classes). Here is the code of the full component:
using System;
using System.Text;
using System.Web.UI;
using CommunityServer.Controls;
using CommunityServer.Components;
namespace KartonesNet.Components
{
public class VisitsCounter : WrappedContentBase
{
private int counterID = -1;
private int padding = 0;
public string CounterID
{
get
{
return counterID.ToString();
}
set
{
int val;
if (int.TryParse(value, out val))
{
if (val > -1)
{
counterID = val;
}
}
}
}
public string Padding
{
get
{
return padding.ToString();
}
set
{
int val;
if (int.TryParse(value, out val))
{
if (val > 0)
{
padding = val;
}
}
}
}
protected override void BindDefaultContent(Control ParentControl, IDataItemContainer DataItemContainer)
{
StringBuilder result = new StringBuilder(350);
long hits = 0;
string hitsString = string.Empty;
if (counterID > -1)
{
hits = CommonDataProvider.Instance().RegisterVisitHit(counterID);
hitsString = hits.ToString();
if (padding > 0)
{
while (hitsString.Length < padding)
{
hitsString = hitsString.Insert(0, "0");
}
}
for (int digit = 0; digit < hitsString.Length; digit++)
{
result.Append("<span class='counter' id='counter");
result.Append(hitsString[digit]);
result.Append("'></span>");
}
ParentControl.Controls.Add(new LiteralControl(result.ToString()));
}
}
}
}
Some explanations and comments about the code:
The next step is to define the CSS stuff needed to display the counter digits (thanks here to my colleage Carlos Tallón for the cross-browser help):
span.counter
{
background:url('../images/counter_bulleted.gif') no-repeat;
height: 20px;
overflow: hidden;
padding: 4px 0 6px 15px;
width: 15px
}
#counter0
{
background-position:0px 0px
}
#counter1
{
background-position:-15px 0px
}
#counter2
{
background-position:-30px 0px
}
#counter3
{
background-position:-45px 0px
}
#counter4
{
background-position:-60px 0px
}
#counter5
{
background-position:-75px 0px
}
#counter6
{
background-position:-90px 0px
}
#counter7
{
background-position:-105px 0px
}
#counter8
{
background-position:-120px 0px
}
#counter9
{
background-position:-135px 0px
}
The counter class for spans defines the CSS sprite file, width and height (plus stuff for IE), and each counterX id defines the position of each digit inside the sprite file. I could (and probably should) have used classes for them too, but I wanted as few HTML code as possible (and I won't be doing any JS DOM stuff with them).
We're almost at the end, so now that we have everything, we need to plug our custom control, which means adding a new line under the <controls> section of Web.Config:
<add tagPrefix="KartonesNet" namespace="KartonesNet.Components" assembly="KartonesNet"/>
Finally, we can use the control on every page we want with a simple line of code:
<KartonesNet:VisitsCounter ID="VisitsCounter" CounterID="0" runat="server" />
And that's all. You can see a live sample in the left column of this blog, or in other blogs of the community. By using CSS sprites we only make one http request call to get the counter digits so it's a small cost, and being plugged in into Community Server we can do anything (like logging Event entries).
I have in mind creating at least two more controls which don't need SQL code, so when I'm finished I'll add them to my CS2007 Addon Pack.