First off you'll need a database table for the url data. In this example I'm using SQL Server 2008.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Urls](
[PK] [int] IDENTITY(1,1) NOT NULL,
[Key] [varchar](10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL CONSTRAINT [DF_Urls_Key] DEFAULT (''),
[Url] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Urls_Url] DEFAULT (''),
[CreatedBy] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Urls_CreatedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Urls] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Urls] UNIQUE NONCLUSTERED
(
[Key] 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
GO
- PK column to act as the primary key
- Key column is a unique nonclustered column, which is the unique key for the shortened url
- Url column which is the fully qualified url that that the shortened url should redirect to
- CreatedBy is a simple varchar field to provide optional auditing
- CreatedDate is a simple smalldatetime field to provide optional auditing
It's worth noting that I'm using SQL_Latin1_General_CP1_CS_AS for the collation on the Key column. This isn't absolutely necessary; however, it means that the Key column is case-sensitive and makes the universe much larger for the number of unique values that be be stored in that column.
The next step is to create the view and scalar-valued functions.
GetUniqueIdentifierView
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[GetUniqueIdentifierView] AS SELECT 'ID' = NEWID() GO
GetUniqueIdentifier
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetUniqueIdentifier]()
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
RETURN (SELECT ID FROM GetUniqueIdentifierView)
END
GO
GetRandomString
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetRandomString]
(
@Length INT = 5
)
RETURNS VARCHAR(MAX)
AS
BEGIN
-- variables
DECLARE @Characters VARCHAR(62)
DECLARE @Output VARCHAR(MAX)
DECLARE @i INT
-- values
SET @Characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
SET @Output = ''
SET @i = 0
-- loop
WHILE @i < @Length
BEGIN
-- variables
DECLARE @Position INT
-- random position in character map
SET @Position = ABS(CAST(CAST(dbo.GetUniqueIdentifier() AS VARBINARY) AS INT)) % LEN(@Characters)
-- concatenate the random character map value to our string
SET @Output = @Output + SUBSTRING(@Characters, @Position + 1, 1)
-- increment loop counter
SET @i = @i + 1
END
-- return value
RETURN ISNULL(@Output, '')
END
GO
At this point we have a simple function we can call to get a random string of a specified length, for example:
SELECT dbo.GetRandomString(5)
Now we'll create the stored procedures to finish off the SQL side of things.
The first stored procedure is Urls_List. This stored procedure has optional parameters to either return all urls, return a single url for the specified PK, or return a single url for the specified Key.
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Urls_List]
(
@PK INT = 0,
@Key VARCHAR(10) = ''
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- get data
SELECT PK,
[Key],
Url,
'UrlShort' = '{BaseUrl}url/?' + [Key],
CreatedBy,
CreatedDate
FROM Urls
WHERE PK =
CASE
WHEN @PK > 0 THEN @PK
ELSE PK
END
AND [Key] =
CASE
WHEN LEN(@Key) > 0 THEN @Key
ELSE [Key]
END
ORDER BY PK
END
GO
Now you have a simple way to get the data from the Urls table, for example:
-- get all urls EXEC dbo.Urls_List -- get url for specified PK EXEC dbo.Urls_List @PK = 1 -- get url for specified Key EXEC dbo.Urls_List @Key = 'gyzAq'
You'll want to pay special attention to the {BaseUrl} reference in the Urls_List stored procedure. You could do one of two things here. You could plug your fully qualified base url directly in there (e.g. http://pavey.me/) OR you can keep that placeholder in there, and replace it generically in either the data layer, model object, or the web project, depending on your requirements.
The next stored procedure is Urls_Save. This stored procedure creates the record in the Urls table with a unique key and returns the record. If a record with the specified Url already exists it will return that record instead of creating a new one.
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Urls_Save]
(
@Url VARCHAR(2000),
@CreatedBy VARCHAR(100)
)
AS
BEGIN
-- declare variables
DECLARE @PK INT
-- see if url already exists
SELECT @PK = PK
FROM Urls
WHERE Url = @Url
-- handle nulls
SET @PK = ISNULL(@PK, 0)
-- create url if necessary
IF @PK < 1
BEGIN
-- variables
DECLARE @Key VARCHAR(5) = ''
DECLARE @KeyLength INT = 5
DECLARE @KeyIsUnique BIT = 0
-- get random key
SET @Key = dbo.GetRandomString(@KeyLength)
-- check to see if key already exists
IF NOT EXISTS(SELECT PK FROM Urls WHERE [Key] = @Key)
BEGIN
SET @KeyIsUnique = 1
END
-- if key is not unique keep looking
IF @KeyIsUnique = 0
BEGIN
WHILE @KeyIsUnique = 0
BEGIN
-- get random key
SET @Key = dbo.GetRandomString(@KeyLength)
-- check to see if key already exists
IF NOT EXISTS(SELECT PK FROM Urls WHERE [Key] = @Key)
BEGIN
SET @KeyIsUnique = 1
END
END
END
-- insert record
INSERT INTO Urls
([Key], Url, CreatedBy)
VALUES
(@Key, @Url, @CreatedBy)
-- get identity
SET @PK = SCOPE_IDENTITY()
END
-- return url
EXEC Urls_List @PK = @PK
END
GO
You'll notice in the Urls_Save stored procedure I'm using @KeyLength = 5. The GetRandomString function we created can handle creating a random string of any size, so you'll just want to set this based on your needs. In my case, a unique key length of 5 was sufficient.
This takes care of the SQL side of things. Now we are going to create a model class to represent our url data. I keep my model classes in a Model project, and I use a BaseModel class, which for purposes of this example is empty, but gives you a place to provide base support for your model classes.
BaseModel.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Sandbox.Model
{
public class BaseModel : IDisposable
{
#region "IDisposable Support"
// To detect redundant calls
private bool disposedValue;
// IDisposable
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
}
// TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
// TODO: set large fields to null.
}
this.disposedValue = true;
}
// TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
//Protected Overrides Sub Finalize()
// ' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
// Dispose(False)
// MyBase.Finalize()
//End Sub
// This code added by Visual Basic to correctly implement the disposable pattern.
public void Dispose()
{
// Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
}
}
UrlShort.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Sandbox.Model
{
public class UrlShortner : BaseModel
{
// constructor
public UrlShortner()
{
PK = 0;
Key = string.Empty;
Url = string.Empty;
UrlShort = string.Empty;
CreatedBy = string.Empty;
CreatedDate = DateTime.MinValue;
}
// public properties
public int PK { get; set; }
public string Key { get; set; }
public string Url { get; set; }
public string UrlShort { get; set; }
public string CreatedBy { get; set; }
public DateTime CreatedDate { get; set; }
}
}
Now we'll create the data layer class. I keep the data layer classes in a Data project, and I use a BaseDB class, which for purposes of this example is empty, but gives you a place to provide base support for your data layer classes. In this example we're using Microsoft Enterprise Library 5.0 – May 2011 to provide the data access.
BaseDB.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Sandbox.Data
{
public class BaseDB : IDisposable
{
#region "IDisposable Support"
// To detect redundant calls
private bool disposedValue;
// IDisposable
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
}
// TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
// TODO: set large fields to null.
}
this.disposedValue = true;
}
// TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
//Protected Overrides Sub Finalize()
// ' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
// Dispose(False)
// MyBase.Finalize()
//End Sub
// This code added by Visual Basic to correctly implement the disposable pattern.
public void Dispose()
{
// Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
}
}
Urls.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Sandbox.Utilities;
namespace Sandbox.Data
{
public class Urls : BaseDB
{
public Model.UrlShortner Get(int PK)
{
// validate
if (PK < 1)
{
return new Model.UrlShortner();
}
// lookup
List<Model.UrlShortner> x = List(PK: PK);
// return
if (x.Count > 0)
{
return x.First();
}
else
{
return new Model.UrlShortner();
}
}
public Model.UrlShortner Get(string Key)
{
// validate
if (Key.IsBlank())
{
return new Model.UrlShortner();
}
// lookup
List<Model.UrlShortner> x = List(Key: Key);
// return
if (x.Count > 0)
{
return x.First();
}
else
{
return new Model.UrlShortner();
}
}
public List<Model.UrlShortner> List(Int32 PK = 0, string Key = "")
{
// variables
Database DB = DatabaseFactory.CreateDatabase();
List<Model.UrlShortner> Urls = new List<Model.UrlShortner>();
// command
using (DbCommand cmd = DB.GetStoredProcCommand("dbo.Urls_List"))
{
// parameters
DB.AddInParameter(cmd, "@PK", DbType.Int32, PK);
DB.AddInParameter(cmd, "@Key", DbType.String, Key);
// execute query and get results
using (DataTable DT = new DataTable())
{
using (IDataReader IDR = DB.ExecuteReader(cmd))
{
if (IDR != null)
{
DT.Load(IDR);
}
}
// convert to business object
Urls = DT.ToList<Model.UrlShortner>().ToList();
}
}
// return list
return Urls;
}
public Model.UrlShortner Save(string Url, string CreatedBy = "")
{
// variables
Database DB = DatabaseFactory.CreateDatabase();
Model.UrlShortner UrlShort = new Model.UrlShortner();
// command
using (DbCommand cmd = DB.GetStoredProcCommand("dbo.Urls_Save"))
{
// parameters
DB.AddInParameter(cmd, "@Url", DbType.String, Url);
DB.AddInParameter(cmd, "@CreatedBy", DbType.String, CreatedBy);
// execute query and get results
using (DataTable DT = new DataTable())
{
using (IDataReader IDR = DB.ExecuteReader(cmd))
{
if (IDR != null)
{
DT.Load(IDR);
}
}
// convert to business object
if (DT.Rows.Count > 0)
{
UrlShort = DT.Rows[0].ToObject<Model.UrlShortner>();
}
}
}
// return value
return UrlShort;
}
}
}
It might seem like overkill for this example, but I'm using some extension methods to make the data layer much more generic. For example, instead of having to do manual column mapping to map the data from the DataTable to the model object I am able to use ToObject and ToList to do that generically. You wouldn't believe how much time generic extension methods like this will save you over the course of your project.
Extensions.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Data;
using System.ComponentModel;
namespace Sandbox.Utilities
{
public static class Extensions
{
private static Dictionary<Type, IList<PropertyInfo>> typeDictionary = new Dictionary<Type, IList<PropertyInfo>>();
public static IList<PropertyInfo> GetPropertiesForType<T>()
{
// variables
var type = typeof(T);
// get types
if (!typeDictionary.ContainsKey(typeof(T)))
{
typeDictionary.Add(type, type.GetProperties().ToList());
}
// return
return typeDictionary[type];
}
public static T ToObject<T>(this DataRow row) where T : new()
{
// variables
IList<PropertyInfo> properties = GetPropertiesForType<T>();
// return
return CreateItemFromRow<T>(row, properties);
}
public static IList<T> ToList<T>(this DataTable table) where T : new()
{
// variables
IList<T> result = new List<T>();
// foreach
foreach (DataRow row in table.Rows)
{
result.Add(row.ToObject<T>());
}
// return
return result;
}
private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
{
// variables
T item = new T();
// foreach
foreach (var property in properties)
{
// make sure a column exists in the table with this property name
if (row.Table.Columns.Contains(property.Name))
{
// get the value from the current data row
object value = row[property.Name];
// set property accordingly
if (value != null & value != DBNull.Value)
{
SetProperty<T>(item, property.Name, value);
}
}
}
// return
return item;
}
public static string GetProperty<T>(this T obj, string Property)
{
// reflection
PropertyInfo propertyInfo = obj.GetType().GetProperty(Property, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
object property = null;
// make sure property is valid
if (propertyInfo != null)
{
property = propertyInfo.GetValue(obj, null);
}
// return value
if (property != null)
{
return property.ToString();
}
else
{
return string.Empty;
}
}
public static T SetProperty<T>(this T obj, string Property, object Value)
{
// reflection
PropertyInfo prop = obj.GetType().GetProperty(Property, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
// trim strings
if (Value.GetType() == typeof(string))
{
Value = Value.ToString().Trim();
}
// make sure property is valid
if (prop != null && prop.CanWrite)
{
prop.SetValue(obj, Value, null);
}
// return
return obj;
}
public static bool HasValue(this string Value)
{
return !Value.IsBlank();
}
public static bool IsBlank(this string Value)
{
bool ReturnValue = true;
if (Value != null)
{
ReturnValue = Value.Trim().Length == 0;
}
return ReturnValue;
}
}
}
Now to round this all out we need to create a shortened url and use it. To create a shortened url you simply call the Save method from the data layer:
// create shorterned url
using (Data.Urls DB = new Data.Urls())
{
using (Model.UrlShortner MyUrl = DB.Save(Url: "http://www.google.com", CreatedBy: "Testing"))
{
// MyUrl now has a reference to your shortened url
}
}
I typically would use a shortened url like this if I was going to include some kind of custom url for a user, for example resetting their password. The url may include an encrypted token and could look pretty ugly, so having a way to provide a nice clean url makes that much more appealing for the email.
Example Url:
http://dev.sandbox.com/forgot-password.aspx?q=urXsbqy9knIpqqOddkFICXTAspidUXCmPS2vJsLqkVanePU%2bGgxLahBGGl1EA%2f%2fXyXxGT4EX6kcqYSvy8BTpib6eEB61Q5WxNcNfEjk1OYx4HGyvO5oKs34JZ%2f1p9jMuAvVpkbkKpBBjYD2UiotIiYob%2baSzHmxRUuUJYkRepd6kSosnXOXssKVQJ%2bWbQQkYfNZWt2OUe9nGw1UOBd1aeO3O9JyEqSiMkYoIF1blW3f9MUx461LSnB9FL2Q4Vbn%2bDGyK0kEHPQVaz5fhpSXIPrfN3Q%2flRtjyqWDXrRnx2%2bE%3e
Example Shortened Url:
http://dev.sandbox.com/url/?Z4cgw
So now we can create the shortened url and know what it looks like, all that's left to do is allow your web application to actually recognize the shortened url and redirect to the actual url.
Start off by creating a new folder in your web project. I called mine url, which is why in the example shortened url above I used /url. This could literally be any folder name you wanted, but something short makes sense.
After you've got the folder created, create a Default.aspx page in that folder, with the following:
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Sandbox.Web.url.Default" %> <!-- see code behind -->
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Sandbox.Utilities;
namespace Sandbox.Web.url
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ProcessUrl();
}
}
private void ProcessUrl()
{
// variables
string Key = Request.QueryString.ToString();
string Url = string.Empty;
// check database for url
if (Key.HasValue())
{
using (Data.Urls DB = new Data.Urls())
{
using (Model.UrlShortner x = DB.Get(Key: Key))
{
Url = x.Url;
}
}
}
// redirect
if (Url.HasValue())
{
Response.Redirect(Url);
Response.End();
}
else
{
Response.Redirect("~/home.aspx");
Response.End();
}
}
}
}
This page simply takes the querystring value and checks to see if there is a matching record in the Urls table with the specified Key. If found, it redirects to that url. If not, it falls back to redirecting the user to some default/home page.
This is just one way to build a URL shortner. You can strip out the requirement for the extension methods or change it to use your own style of model/data classes, but the overall concept doesn't change:
- Database table for the url data
- Views/Functions/Procs for creating/accessing the url data
- Model object for representing the url data
- Data layer class for calling the stored procedures and returning the model object
- Folder for processing the shortened url and redirecting appropriately
Matt Pavey is a Microsoft Certified software developer who specializes in ASP.Net, VB.Net, C#, AJAX, LINQ, XML, XSL, Web Services, SQL, jQuery, and more. Follow on Twitter @matthewpavey
0 comments: