From time to time, your website structure may change. When this happens, you do not want to have to start from scratch with your Google rankings, so you need to map all of your Old URLs to new ones.
This may seem like a trivial thing, but it is essential to keep your current rankings, that you worked hard for, intact.
In our scenario, the old site used a query string with the product ID in it, and the new site uses nice friendly names.
Old: http://northwind.com/Product/ProductInfo.aspx?id=3456
New: http://northwind.com/CoolLightsaberWithRealAction.aspx
Updated #1 January 5th, 2010 - As suggested by Adam Cogan, I changed:
- Change Figures to SSW standard with “Good example, ….”, “OK example, ….” And “Bad example, ….“
- Prefix main headings with “Option #x” to make them stand out
- Prefix process steps with “Step #x” to differentiate them.
- Remove tiny URLs so the reader knows where they are going
- Spell check (i.e., run through Word)
- Link to rules for better regex
- Change “outtakes” to “TODO:”’s
- End on an impact line (“In conclusion I …..”)
- Change Title to “Solution - SEO permanent redirects for old URLs?”
Updated #2 January 6th, 2010 - As suggested by John Liu, I changed the SQL call to be completely wrapped in a “try catch” statement and to close the connection in the “Finally” area. Dam, I thought no one at SSW could read VB.
Updated #3 January 7th, 2010 - As suggested by Peter Gfader, I changed the source to use a parameterised SQL statement instead of a Stored Procedure. He pointed out that “Stored procedures are bad, m'kay?”
Updated #4 January 8th, 2010 - Updated to reflect latest code changes to increase flexibility of the rule.
Option #1 - You Can Do It in product.aspx
Response.Status = "301 Moved Permanently"
Response.StatusCode = 301;
Response.AddHeader("Location","/CoolLightsaberWithRealAction.aspx");
Response.End();
Figure: Bad example, write it right into the old page.
Why is this not a good approach?
- The old page may not exist, you may be building a whole new version of the site.
- It is slow. You have to wait for the page to load, which probably means your master page, and all the code which goes with that.
- It leaves old pages dotted about your site that you do not really want.
Option #2 - You Can Do It in the global.asax
protected void Application_BeginRequest(object sender, EventArgs e)
{
if (Request.FilePath.Contains("/product.aspx?id=")
{
Response.Status="301 Moved Permanently"
Response.StatusCode=301;
Response.Redirect ("/CoolLightsaberWithRealAction.aspx", true);
}
}
Figure: Bad example, ASP.NET 2.0 solution in the global.asax file for redirects
protected void Application_BeginRequest(object sender, EventArgs e)
{
if (Request.FilePath.Contains("/product.aspx?id=")
{
Response.RedirectPermanent("/CoolLightsaberWithRealAction.aspx", true);
}
}
Figure: Bad example, ASP.NET 4.0 solution in the global.asax file for redirects, less code.
Using the global.asax has its draw backs.
- To change it, you must make a code change to your site and re-deploy
- If you have multiple redirects, it is going to get ugly fast.
Option #3 - You Can Do It with the IIS7 URL Rewrite Module
Using the IIS7 URL Rewrite Module which can be installed using the Microsoft Web Platform Installer is the best option, but unfortunately, it does not currently support looking up a database.
If you have identifiable patterns in the rewrites that you want to perform, then this is fantastic. So if you have all of the information that you need in the URL to do the rewrite, then you can stop reading and go and install it.
With the IIS7 URL Rewrite Module, you can:
- Rewrite and redirect URLs
- Handles requests before ASP.NET is aware of (good performance)
- Solves both problems: redirecting broken pages and creating nice URLs
- Various rule actions. Instead of rewriting a URL, a rule may perform other actions, such as issue an HTTP redirect, abort the request, or send a custom status code to HTTP client.
- Nice graphical rule editor
- Regex pattern matching for requests and rewrites
- URL rewrite module v2 adds support for outbound response rewriting
- Fix up the content of any HTTP response by using regular expression pattern matching (e.g. modify links in outgoing response)
As it turns out, we found out yesterday that the next version of the IIS7 URL Rewriting Module IS going to support loading from a database! Whether that is just loading the rules, or you can load some of the data you need has yet to be seen. But as we can’t get even a beta for a couple of weeks, and our release date is in that region, we could not wait.
Option #4 - You Can Do It with UrlRewritingNet.UrlRewriter
Using the UrlRewritingNet.UrlRewriter component, you can do pretty much everything that the IIS7 Rewrite Module does, but it does not have a nice UI to interact with. The best part of UrlRewritingNet.UrlRewriter is that its rules engine is extensible, so we can add a new rule type to load from a database.
The first thing you do with any new toolkit is read the documentation, or at least have it open and pretend to read it while you tinker.
Step #1 - Add UrlRewritingNet.UrlRewriter to Our Site
To add UrlRewritingNet.UrlRewriter to our site, you need to add UrlRewritingNet.UrlRewriter.dll (you can download this from their site) to the Bin folder and make a couple of modifications to the web.config. I have opted to add the UrlRewritingNet.UrlRewriter section of the config to a separate file as this makes it more maintainable.
="1.0"
<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
<providers>
</providers>
<rewrites>
</rewrites>
</urlrewritingnet>
Figure: Boilerplate URLRewriting config
Create a new blank file called "urlrewriting.config" and insert the code above. As you can see, you can add numerous providers and rules. Lookup the documentation for the built in rules model that uses the same method we will be using to capture URLs, but has a regular expression based replace implementation that lets you reform any URL into any other URL, provided all the values you need are either static, or included in the incoming URL.
<configSections>
...
<section name="urlrewritingnet"
restartOnExternalChanges="true"
requirePermission="false"
type="UrlRewritingNet.Configuration.UrlRewriteSection, UrlRewritingNet.UrlRewriter" />
</configSections>
Figure: ASP.NET Section definition for URLRewriting.
In your "web.config", add this section:
<urlrewritingnet configSource="UrlRewrite.config" />
Figure: You can use an external file or inline.
After the sections definition, but NOT inside any other section, add the section implementation, but use the "configSource" tag to map it to the "urlrewriting.config" file you created previously. You could also just add the contents of "urlrewriting.config" under "urlrewritingnet" element and remove the need for the additional file, but I think this is neater.
<system.web>
<httpModules>
<add name="UrlRewriteModule"
type="UrlRewritingNet.Web.UrlRewriteModule, UrlRewritingNet.UrlRewriter" />
</httpModules>
</system.web>
Figure: HttpModules make it all work in IIS6.
We need IIS to know that it needs to do some processing, but there are some key differences between IIS6 and IIS7, to make sure that both load your rewrite correctly, especially if you still have developers on Windows XP, you will need to add both of them. Add this one to the "HttpModules" element, before any other rewriting modules, it tells IIS6 that it needs to load the module.
<system.webServer>
<modules>
<add name="UrlRewriteModule"
type="UrlRewritingNet.Web.UrlRewriteModule, UrlRewritingNet.UrlRewriter" />
</modules>
</system.webServer>
Figure: Modules make it all work in IIS7.
II7 does things a little differently, so add the above to the "modules" element of "system.webServer". This does exactly the same thing, but slots it into the IIS7 pipeline.
You should now be able to add rules as specified in the documentation and have them run successfully, provided your regular expression is correct :), but for this process, we need to write our custom rule.
Step #2 - Creating a Blank Custom Rule
For some reason, I have not yet fathomed, you need to create a “Provider” as well. It just has boilerplate code, but I would assume that there are circumstances when it would be useful to have some code in there.
Imports UrlRewritingNet.Configuration.Provider
Public Class SqlUrlRewritingProvider
Inherits UrlRewritingProvider
Public Overrides Function CreateRewriteRule() As UrlRewritingNet.Web.RewriteRule
Return New SqlRewriteRule
End Function
End Class
Figure: Simple code for the provider.
All you need to do in the Provider is override the “CreateRewriteRule” and pass back an instance of your custom rule.
Imports UrlRewritingNet.Web
Imports UrlRewritingNet.Configuration
Imports System.Configuration
Public Class SqlRewriteRule
Inherits RewriteRule
Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
MyBase.Initialize(rewriteSettings)
End Sub
Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
Return false
End Function
Public Overrides Function RewriteUrl(ByVal url As String) As String
Return url
End Function
End Class
Figure: Boilerplate Rule.
This is a skeleton of a new rule. It does nothing now, and in fact will not run as long as the “IsRewrite” function returns false.
The “Initialize” method passes any setting that is set on the rule entry in the config file. As we want to create a dynamic and reusable rule, we will be using a lot of settings. The settings are written as Attributes in the XML, but are in effect name value pairs.
The “IsRewrite” will determine whether we want to run the logic behind the rule. I would not advice any performance intensive calls here (like calling the database), so you should find a quick and easy way of determining if we want to proceed to rewrite the URL. The best way of doing this will be via a regular expression.
“RewiteUrl” provides the actual logic to do the rewrite. We will be calling the database here, so this is more intensive work.
Step #3 - Capture the URL You Want to Rewrite
Let’s first consider the capturing of the URL so we can do the IsRewrite. To provide our regular expression, we will need to options, the first being our pattern, the second being the Regular expression options. We add the options so we can have both Case sensitive and insensitive settings. The standard field name for regular expressions that match is “VirtualUrl” we will just call the other “RegexOptions”.
Imports UrlRewritingNet.Web
Imports UrlRewritingNet.Configuration
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Configuration
Public Class SqlRewriteRule
Inherits RewriteRule
Private m_regexOptions As Text.RegularExpressions.RegexOptions
Private m_virtualUrl As String = String.Empty
Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
Me.m_regexOptions = rewriteSettings.GetEnumAttribute(Of RegexOptions)
("regexOptions", RegexOptions.None)
Me.m_virtualUrl = rewriteSettings.GetAttribute("virtualUrl", "")
MyBase.Initialize(rewriteSettings)
End Sub
Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
Return true
End Function
Public Overrides Function RewriteUrl(ByVal url As String) As String
Return url
End Function
End Class
Figure: Retrieving values from the config is easy.
In order to capture these values, we just add two fields to our class, and parse out the data from “rewriteSettings” for these two fields in the Initialize method.
Imports UrlRewritingNet.Web
Imports UrlRewritingNet.Configuration
Imports System.Text.RegularExpressions
Imports System.Configuration
Public Class ProductKeyRewriteRule
Inherits RewriteRule
Private m_regex As Text.RegularExpressions.Regex
Private m_regexOptions As Text.RegularExpressions.RegexOptions
Private m_virtualUrl As String = String.Empty
Private Sub CreateRegEx()
Dim helper As New UrlHelper
If MyBase.IgnoreCase Then
Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), _
((RegexOptions.Compiled Or RegexOptions.IgnoreCase) Or Me.m_regexOptions))
Else
Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), _
(RegexOptions.Compiled Or Me.m_regexOptions))
End If
End Sub
Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
Me.m_regexOptions = rewriteSettings.GetEnumAttribute(Of RegexOptions)_
("regexOptions", RegexOptions.None)
Me.m_virtualUrl = rewriteSettings.GetAttribute("virtualUrl", "")
CreateRegEx
MyBase.Initialize(rewriteSettings)
End Sub
Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
Return Me.m_regex.IsMatch(requestUrl)
End Function
Public Overrides Function RewriteUrl(ByVal url As String) As String
Return url
End Function
End Class
Figure: Creating an instance of a regular expression and using that is always faster than creating one each time.
We now have all of the information we need to create a regular expression and call "IsMatch" in the "IsRewrite" method. Therefore, we add another field for the regular expression and add a “CreateRegEx” method to create our regular expression using the built in “Ignorecase” option as well as our “RegexOptions” value. This creates a single compiled copy of our regular expression so it will operate as quickly as possible. Remember that this code will now be called for EVERY incoming URL request.
Step #4 - Rewrite the URL with Data from the Database
Now that we have captured the URL, we need to rewrite it. In order to do this, we will need some extra fields, and this is where things get a little complicated because we want to be generic. We will need:
- a connection string so we know where to load the data from
- a SQL Statement
- some input parameters for our SQL
- some output data
- a destination URL to inject our output into
- a place to redirect users to if all else fails
The connection string is easy, or is it.
m_ConnectionString = rewriteSettings.GetAttribute("connectionString", String.Empty)
If m_ConnectionString = String.Empty Then
Throw New NotSupportedException(String.Format_
("You must specify a connectionString attribute for the DataRewriteRule {0}", rewriteSettings.Name))
End If
Dim NamedConnectionString As ConnectionStringSettings = _
ConfigurationManager.ConnectionStrings(m_ConnectionString)
If Not NamedConnectionString Is Nothing Then
m_ConnectionString = NamedConnectionString.ConnectionString
End If
Figure: Make sure that you check whether values are correct.
There are two ways for a connection string to be stored in ASP.NET, inline and shared. We don’t want to be fixed to a specific type, so we need to assume shared and if we can’t find a shared string, assume the string provided in the connection string and not a key for the shared string.
The stored procedure is just a string, but the input parameters, now that is a quandary. Where can we get them from and now can we configure them. Although it would probably be best if we could have sub elements to the rule definition in the “web.config” we can’t, so all we have is a set of name value pairs.
^.*/Product/ProductInfo\.aspx\?id=(?'ProductId'\d+)
Figure: Follow the rule: Do you test your regular expressions?
The solution I went for was to use Named groups in the regular expression. The only input parameter with this expression would be “@ProductId” and should be populated by the data in the capture group for the regular expression.
Dim groupNames = From groupName In m_regex.GetGroupNames _
Where Not groupName = String.Empty And Not IsNumeric(groupName)
For Each groupName As String In groupNames
UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
Next
Figure: Retrieving the named groups is easier than you think, but remember that it also contains the unnamed groups as a number.
So for each of the group names found in the regular expression, I will be adding a SqlParameter to the SqlCommand object with the value that is returned. Again, a better solution would be to have meta data along with this that would identify the input parameters as well as data types and where to get them from, but alas it is not possible in this context.
All this allows you to call a parameterised SQL statement and get some data back that you can use in the “RewriteUrl” method. I created a “GetUrlReplacements” method to encapsulate this logic.
Private Function GetUrlReplacements(ByVal match As Match) As Dictionary(Of String, String)
Dim UrlReplacements As New Dictionary(Of String, String)
Dim paramString As String = String.Empty
' Call database
Using conn As New SqlConnection(m_ConnectionString)
Try
conn.Open()
Dim cmd As New SqlCommand(m_parameterisedSql, conn)
cmd.CommandType = CommandType.Text
' Get all the named groups from the regular expression and use them
' as the stored procedure parameters.
Dim groupNames = From groupName In m_regex.GetGroupNames Where Not groupName = _
String.Empty And Not IsNumeric(groupName)
' Iterate through the named groups
For Each groupName As String In groupNames
' Add the name and value as input parameters to the stored procedure
cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
paramString = paramString & "[@" & groupName & "=" _
& match.Groups(groupName).ToString & "]"
If UrlReplacements.ContainsKey(groupName) Then
UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
Else
UrlReplacements(groupName) = match.Groups(groupName).ToString
End If
Next
' Define the data capture method
Dim sqlReader As SqlClient.SqlDataReader
' Execute the SQL
sqlReader = cmd.ExecuteReader()
If sqlReader.HasRows Then
Dim isDone As Boolean = False
Do While sqlReader.Read()
If isDone Then
' If more than one record is returned, exit and record
My.Application.Log.WriteEntry(String.Format_
("Too many results from execution of '{0}' using parameters '{1}' _
on the connection '{2}'. Make sure your query only returns _
a single record.", m_parameterisedSql, paramString, _
m_ConnectionString), TraceEventType.Error, 19786)
Exit Do
End If
' Add a sql output parameter for each outputParam (note: Must be NVarChar(255))
For i As Integer = 0 To sqlReader.FieldCount - 1
If UrlReplacements.ContainsKey(sqlReader.GetName(i)) Then
UrlReplacements.Add(sqlReader.GetName(i), sqlReader.GetValue(i).ToString)
Else
UrlReplacements(sqlReader.GetName(i)) = sqlReader.GetValue(i).ToString
End If
Next
isDone = True
Loop
sqlReader.Close()
Else
My.Application.Log.WriteEntry(String.Format("No results from execution of _
'{0}' using parameters '{1}' on the connection '{2}'", m_parameterisedSql, _
paramString, m_ConnectionString), TraceEventType.Error, 19784)
UrlReplacements.Clear()
UrlReplacements.Add("results", "None")
End If
Catch ex As System.Data.SqlClient.SqlException
My.Application.Log.WriteException(ex, TraceEventType.Error, String.Format_
("Unable to execute '{0}' using parameters '{1}' on the connection '{2}'", _
m_parameterisedSql, paramString, m_ConnectionString), 19783)
UrlReplacements.Clear()
UrlReplacements.Add("ex", "SqlException")
Catch ex As Exception
My.Application.Log.WriteException(ex, TraceEventType.Error, String.Format_
("Unable to connect using the connection '{0}'", m_ConnectionString), 19782)
UrlReplacements.Clear()
UrlReplacements.Add("ex", ex.GetType.ToString)
End Try
End Using
Return UrlReplacements
End Function
Figure: Always encapsulate your more complicated logic, especially database calls.
The SQL is called and the first, and only the first returned record is parsed into a name value collection allowing for multiple values to be returned.
Now that we have the relevant data, we can rewrite the URL.
Public Overrides Function RewriteUrl(ByVal url As String) As String
' Get the url replacement values
Dim UrlReplacements As Dictionary(Of String, String) = GetUrlReplacements(Me.m_regex.Match(url))
' Take a copy of the target url
Dim newUrl As String = m_destinationUrl
' Replace any valid values with the new value
For Each key As String In UrlReplacements.Keys
newUrl = newUrl.Replace("{" & key & "}", UrlReplacements(key))
Next
' Test to see is any failed by looking for any left over '{'
If newUrl.Contains("{") Then
' If there are left over bits, then only do a temporary redirect to the failed URL
Me.RedirectMode = RedirectModeOption.Temporary
My.Application.Log.WriteEntry(String.Format_
("Unable to locate a product url replacement for {0}", url), TraceEventType.Error, 19781)
Return (String.Format(m_RedirectToOnFail, Me.Name, "NotFound", _
UrlReplacementsToQueryString(UrlReplacements)))
End If
' Success, so do a permanent redirect to the new url.
My.Application.Log.WriteEntry(String.Format("Redirecting {0} to {1}", url, newUrl), _
TraceEventType.Information, 19780)
Me.RedirectMode = RedirectModeOption.Permanent
Return newUrl.Replace("^", "")
End Function
Figure: Make sure that there is a backup plan for your rewrites.
As you can see, all we do once we have the replacement values is replace the keys from the “DestinationUrl” value with the new values. One additional test is done to check that we have not mis-configured and left some values out, so check to see if there are any “{“ left and redirect to the “redirectOnFailed” location if we did. This will be caught if either we did not get any data back, or we just messed up the configuration.
Let's setup the rule in the config.
="1.0"
<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
<providers>
<add name="SqlUrlRewritingProvider" type="SSW.UrlRewriting.SqlUrlRewritingProvider,
SSW.UrlRewriting"/>
</providers>
<rewrites>
<add name="Rule2"
provider="SqlUrlRewritingProvider"
connectionString="MyConnectionString"
virtualUrl="^.*/Product/ProductInfo\.aspx\?id=(?'ProductId'\d+)"
parameterisedSql="SELECT dbo.CatalogEntry.Code as ProductId,
dbo.CatalogItemSeo.Uri as ProductKey FROM dbo.CatalogEntry
INNER JOIN dbo.CatalogItemSeo ON dbo.CatalogEntry.CatalogEntryId =
dbo.CatalogItemSeo.CatalogEntryId WHERE Code = @ProductId"
DestinationUrl="^~/{ProductKey}"
rewriteUrlParameter="IncludeQueryStringForRewrite"
redirectToOnFail="~/default.aspx?rewrite=productNotFound"
redirectMode="Permanent"
redirect="Application"
rewrite="Application"
ignoreCase="true" />
</rewrites>
</urlrewritingnet>
Figure: You can configure as many rules as you like.
The final config entry for the rule looks complicated, but it should all make sense to you now that all the logic has been explained. There are some additional properties here that are part of the Rewriting engine, but you will find all of them in the documentation.
In conclusion, hopefully the IIS7 module will support a more elegant solution in its next iteration, and you can always just hard code an HttpModule. This however is the beginning of a more dynamic solution that can be used over and over again, even in the one site.
For those of you that can’t be bothered to piece this all together, here is the full rule source, but don’t forget to skip to the bottom for the TODO.
Imports UrlRewritingNet.Web
Imports UrlRewritingNet.Configuration
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Configuration
Public Class SqlRewriteRule
Inherits RewriteRule
Private m_ConnectionString As String
Private m_parameterisedSql As String
Private m_destinationUrl As String = String.Empty
Private m_regex As Text.RegularExpressions.Regex
Private m_regexOptions As Text.RegularExpressions.RegexOptions
Private m_virtualUrl As String = String.Empty
Private m_RedirectToOnFail As String
Private Sub CreateRegEx()
Dim helper As New UrlHelper
If MyBase.IgnoreCase Then
Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), _
((RegexOptions.Compiled Or RegexOptions.IgnoreCase) Or Me.m_regexOptions))
Else
Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), _
(RegexOptions.Compiled Or Me.m_regexOptions))
End If
End Sub
Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
Me.m_regexOptions = rewriteSettings.GetEnumAttribute(Of RegexOptions)_
("regexOptions", RegexOptions.None)
Me.m_virtualUrl = rewriteSettings.GetAttribute("virtualUrl", "")
Me.m_destinationUrl = rewriteSettings.GetAttribute("destinationUrl", "")
Me.CreateRegEx()
m_ConnectionString = rewriteSettings.GetAttribute("connectionString", String.Empty)
If m_ConnectionString = String.Empty Then
Throw New NotSupportedException(String.Format("You must specify a _
connectionString attribute for the DataRewriteRule {0}", rewriteSettings.Name))
End If
Dim NamedConnectionString As ConnectionStringSettings = _
ConfigurationManager.ConnectionStrings(m_ConnectionString)
If Not NamedConnectionString Is Nothing Then
m_ConnectionString = NamedConnectionString.ConnectionString
End If
m_parameterisedSql = rewriteSettings.GetAttribute("parameterisedSql", String.Empty)
If m_parameterisedSql = String.Empty Then
Throw New NotSupportedException(String.Format_
("You must specify a parameterisedSql attribute for the DataRewriteRule _
{0}", rewriteSettings.Name))
End If
m_RedirectToOnFail = rewriteSettings.GetAttribute("redirectToOnFail", String.Empty)
If m_RedirectToOnFail = String.Empty Then
Throw New NotSupportedException(String.Format("You must specify a _
redirectToOnFail attribute for the DataRewriteRule {0}", rewriteSettings.Name))
End If
MyBase.Initialize(rewriteSettings)
End Sub
Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
Return Me.m_regex.IsMatch(requestUrl)
End Function
Public Overrides Function RewriteUrl(ByVal url As String) As String
Dim UrlReplacements As Dictionary(Of String, String) = GetUrlReplacements(Me.m_regex.Match(url))
Dim newUrl As String = m_destinationUrl
For Each key As String In UrlReplacements.Keys
newUrl = newUrl.Replace("{" & key & "}", UrlReplacements(key))
Next
If newUrl.Contains("{") Then
Me.RedirectMode = RedirectModeOption.Temporary
My.Application.Log.WriteEntry(String.Format("Unable to locate _
a product url replacement for {0}", url), TraceEventType.Error, 19781)
Return (String.Format(m_RedirectToOnFail, Me.Name, "NotFound", _
UrlReplacementsToQueryString(UrlReplacements)))
End If
My.Application.Log.WriteEntry(String.Format_
("Redirecting {0} to {1}", url, newUrl), TraceEventType.Information, 19780)
Me.RedirectMode = RedirectModeOption.Permanent
Return newUrl.Replace("^", "")
End Function
Private Function GetUrlReplacements(ByVal match As Match) As Dictionary(Of String, String)
Dim UrlReplacements As New Dictionary(Of String, String)
Dim paramString As String = String.Empty
Using conn As New SqlConnection(m_ConnectionString)
Try
conn.Open()
Dim cmd As New SqlCommand(m_parameterisedSql, conn)
cmd.CommandType = CommandType.Text
Dim groupNames = From groupName In m_regex.GetGroupNames _
Where Not groupName = String.Empty And Not IsNumeric(groupName)
For Each groupName As String In groupNames
cmd.Parameters.AddWithValue("@" & _
groupName, match.Groups(groupName).ToString)
paramString = paramString & "[@" & groupName & _
"=" & match.Groups(groupName).ToString & "]"
If UrlReplacements.ContainsKey(groupName) Then
UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
Else
UrlReplacements(groupName) = match.Groups(groupName).ToString
End If
Next
Dim sqlReader As SqlClient.SqlDataReader
sqlReader = cmd.ExecuteReader()
If sqlReader.HasRows Then
Dim isDone As Boolean = False
Do While sqlReader.Read()
If isDone Then
My.Application.Log.WriteEntry(String.Format("Too many results _
from execution of '{0}' using parameters '{1}' _
on the connection '{2}'. Make sure your query only returns _
a single record.", m_parameterisedSql, paramString, m_ConnectionString), _
TraceEventType.Error, 19786)
Exit Do
End If
For i As Integer = 0 To sqlReader.FieldCount - 1
If UrlReplacements.ContainsKey(sqlReader.GetName(i)) Then
UrlReplacements.Add(sqlReader.GetName(i), sqlReader.GetValue(i).ToString)
Else
UrlReplacements(sqlReader.GetName(i)) = sqlReader.GetValue(i).ToString
End If
Next
isDone = True
Loop
sqlReader.Close()
Else
My.Application.Log.WriteEntry(String.Format("No results from execution _
of '{0}' using parameters '{1}' on the connection '{2}'", _
m_parameterisedSql, paramString, m_ConnectionString), TraceEventType.Error, 19784)
UrlReplacements.Clear()
UrlReplacements.Add("results", "None")
End If
Catch ex As System.Data.SqlClient.SqlException
My.Application.Log.WriteException(ex, TraceEventType.Error, String.Format_
("Unable to execute '{0}' using parameters '{1}' _
on the connection '{2}'", m_parameterisedSql, _
paramString, m_ConnectionString), 19783)
UrlReplacements.Clear()
UrlReplacements.Add("ex", "SqlException")
Catch ex As Exception
My.Application.Log.WriteException(ex, TraceEventType.Error, _
String.Format("Unable to connect using the _
connection '{0}'", m_ConnectionString), 19782)
UrlReplacements.Clear()
UrlReplacements.Add("ex", ex.GetType.ToString)
End Try
End Using
Return UrlReplacements
End Function
Private Function UrlReplacementsToQueryString(ByVal dic As Dictionary(Of String, String)) As String
Dim quer As String = String.Empty
For Each dicEntry In dic
quer = String.Format("{0}&{1}={2}", quer, dicEntry.Key, dicEntry.Value)
Next
Return quer
End Function
End Class
Figure: Full source listing for the rule.
----------
TODO
What would I change and why… or things that I just did not have time to do.
TODO: Add More Configurable Parameters
The lack of meta data will lead to limitations in the future and ultimately the duplication of code. The ideal solution would be something like the ASP.NET SqlDataSource configuration, with a nice UI.
<del><asp:SqlDataSource ID="SqlDataSource1" runat="server"
CacheExpirationPolicy="Sliding"
ConnectionString="MyConnectionString"
EnableCaching="True"
SelectCommand="ssw_proc_SeoProductIdToProductKey"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:RegexParameter DbType="StringFixedLength" DefaultValue="0"
Name="ProductId" RegexGroupName="ProductId" _
Size="100" Type="String" />
<asp:Parameter DbType="StringFixedLength" _
Direction="Output" Name="ProductKey"
Size="255" Type="String" />
</SelectParameters>
</asp:SqlDataSource></del>
Figure: Good Example, code from the ASP.NET 2.0 SqlDataSource.
You should be able to configure any set of input and output parameters.
TODO: Retrieve a Record and Replace Based on the Columns
It may make more sense to return a single record and perform the replaces based on the columns that are returned. This may help to reduce complexity while increasing functionality.
TODO: Add Caching to Improve Performance
Caching is a difficult thing as it depends on the amount of data returned, but it can improve the speed.
