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:
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.
1// …
2// Lookup database here and find the friendly name for the product with the ID 3456
3// …
4Response.Status = "301 Moved Permanently"
5Response.StatusCode = 301;
6Response.AddHeader("Location","/CoolLightsaberWithRealAction.aspx");
7Response.End();
Figure: Bad example, Write it right into the old page.
Why is this not a good approach?
1protected void Application_BeginRequest(object sender, EventArgs e)
2{
3
4 if (Request.FilePath.Contains("/product.aspx?id=")
5 {
6 // ...
7 // Lookup the ID in the database to get the new friendly name
8 // ...
9 Response.Status="301 Moved Permanently"
10 Response.StatusCode=301;
11 Response.Redirect ("/CoolLightsaberWithRealAction.aspx", true);
12 }
13}
Figure: Bad example, ASP.NET 2.0 solution in the global.asax file for redirects
1protected void Application_BeginRequest(object sender, EventArgs e)
2{
3
4 if (Request.FilePath.Contains("/product.aspx?id=")
5 {
6 // ...
7 // Lookup the ID in the database to get the new friendly name
8 // ...
9 Response.RedirectPermanent("/CoolLightsaberWithRealAction.aspx", true);
10 }
11}
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.
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 an install it.
With the IIS7 URL Rewrite Module you can
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! Wither 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.
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.
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<?xml version="1.0"?>
2<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
3 <providers>
4 <!-- providers go here -->
5 </providers>
6 <rewrites>
7 <!-- rules go here -->
8 </rewrites>
9</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 URL’s, 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.
1<configSections>
2 ...
3 <section name="urlrewritingnet"
4 restartOnExternalChanges="true"
5 requirePermission="false"
6 type="UrlRewritingNet.Configuration.UrlRewriteSection, UrlRewritingNet.UrlRewriter" />
7</configSections>
Figure: ASP.NET Section definition for URLRewriting.
In your “web.config” add this section.
1<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.
1<system.web>
2 <httpModules>
3 <add name="UrlRewriteModule"
4 type="UrlRewritingNet.Web.UrlRewriteModule, UrlRewritingNet.UrlRewriter" />
5 </httpModules>
6</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.
1<system.webServer>
2 <modules>
3 <add name="UrlRewriteModule"
4 type="UrlRewritingNet.Web.UrlRewriteModule, UrlRewritingNet.UrlRewriter" />
5 </modules>
6</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 you have your regular expression is correct :), but for this process we need to write our 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.
1Imports UrlRewritingNet.Configuration.Provider
2
3Public Class SqlUrlRewritingProvider
4 Inherits UrlRewritingProvider
5
6 Public Overrides Function CreateRewriteRule() As UrlRewritingNet.Web.RewriteRule
7 Return New SqlRewriteRule
8 End Function
9
10End 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.
1Imports UrlRewritingNet.Web
2Imports UrlRewritingNet.Configuration
3Imports System.Configuration
4
5Public Class SqlRewriteRule
6 Inherits RewriteRule
7
8 Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
9 MyBase.Initialize(rewriteSettings)
10 End Sub
11
12 Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
13 Return false
14 End Function
15
16 Public Overrides Function RewriteUrl(ByVal url As String) As String
17 Return url
18 End Function
19
20End 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 are 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 wither 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.
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”.
1Imports UrlRewritingNet.Web
2Imports UrlRewritingNet.Configuration
3Imports System.Data.SqlClient
4Imports System.Text.RegularExpressions
5Imports System.Configuration
6
7Public Class SqlRewriteRule
8 Inherits RewriteRule
9
10 Private m_regexOptions As Text.RegularExpressions.RegexOptions
11 Private m_virtualUrl As String = String.Empty
12
13 Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
14 Me.m_regexOptions = rewriteSettings.GetEnumAttribute(Of RegexOptions)("regexOptions", RegexOptions.None)
15 Me.m_virtualUrl = rewriteSettings.GetAttribute("virtualUrl", "")
16 MyBase.Initialize(rewriteSettings)
17 End Sub
18
19 Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
20 Return true
21 End Function
22
23 Public Overrides Function RewriteUrl(ByVal url As String) As String
24 Return url
25 End Function
26
27
28End 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.
1Imports UrlRewritingNet.Web
2Imports UrlRewritingNet.Configuration
3Imports System.Text.RegularExpressions
4Imports System.Configuration
5
6Public Class ProductKeyRewriteRule
7 Inherits RewriteRule
8
9 Private m_regex As Text.RegularExpressions.Regex
10 Private m_regexOptions As Text.RegularExpressions.RegexOptions
11 Private m_virtualUrl As String = String.Empty
12
13 ' Methods
14 Private Sub CreateRegEx()
15 Dim helper As New UrlHelper
16 If MyBase.IgnoreCase Then
17 Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), ((RegexOptions.Compiled Or RegexOptions.IgnoreCase) Or Me.m_regexOptions))
18 Else
19 Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), (RegexOptions.Compiled Or Me.m_regexOptions))
20 End If
21 End Sub
22
23 Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
24 Me.m_regexOptions = rewriteSettings.GetEnumAttribute(Of RegexOptions)("regexOptions", RegexOptions.None)
25 Me.m_virtualUrl = rewriteSettings.GetAttribute("virtualUrl", "")
26 CreateRegEx
27 MyBase.Initialize(rewriteSettings)
28 End Sub
29
30 Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
31 Return Me.m_regex.IsMatch(requestUrl)
32 End Function
33
34 Public Overrides Function RewriteUrl(ByVal url As String) As String
35 Return url
36 End Function
37
38
39End 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.
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 were things get a little complicated because we want to be generic. We will need:
The connection string is easy, or is it.
1' Test for connectionString and throw exception if not available
2m_ConnectionString = rewriteSettings.GetAttribute("connectionString", String.Empty)
3If m_ConnectionString = String.Empty Then
4 Throw New NotSupportedException(String.Format("You must specify a connectionString attribute for the DataRewriteRule {0}", rewriteSettings.Name))
5End If
6' Check to see if this is a named connection string
7Dim NamedConnectionString As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(m_ConnectionString)
8If Not NamedConnectionString Is Nothing Then
9 m_ConnectionString = NamedConnectionString.ConnectionString
10End If
Figure: Make sure that you check wither 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 that 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.
1^.*/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.
1' Get all the named groups from the regular expression and use them as the stored procedure parameters.
2Dim groupNames = From groupName In m_regex.GetGroupNames Where Not groupName = String.Empty And Not IsNumeric(groupName)
3' Iterate through the named groups
4For Each groupName As String In groupNames
5 ' Add the name and value to the saved replacements
6 UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
7 ' Add the name and value as input prameters to the stored procedure
8 cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
9Next
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.
1Private Function GetUrlReplacements(ByVal match As Match) As Dictionary(Of String, String)
2 Dim UrlReplacements As New Dictionary(Of String, String)
3 Dim paramString As String = String.Empty
4 ' Call database
5 Using conn As New SqlConnection(m_ConnectionString)
6 Try
7 conn.Open()
8 Dim cmd As New SqlCommand(m_parameterisedSql, conn)
9 cmd.CommandType = CommandType.Text
10 ' Get all the named groups from the regular expression and use them as the stored procedure parameters.
11 Dim groupNames = From groupName In m_regex.GetGroupNames Where Not groupName = String.Empty And Not IsNumeric(groupName)
12 ' Iterate through the named groups
13 For Each groupName As String In groupNames
14 ' Add the name and value as input prameters to the stored procedure
15 cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
16 paramString = paramString & "[@" & groupName & "=" & match.Groups(groupName).ToString & "]"
17 If UrlReplacements.ContainsKey(groupName) Then
18 UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
19 Else
20 UrlReplacements(groupName) = match.Groups(groupName).ToString
21 End If
22 Next
23 ' Defigne the data capture method
24 Dim sqlReader As SqlClient.SqlDataReader
25 ' Execute the SQL
26 sqlReader = cmd.ExecuteReader()
27 If sqlReader.HasRows Then
28 Dim isDone As Boolean = False
29 Do While sqlReader.Read()
30 If isDone Then
31 ' If more than one record is returned, exit and record
32 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)
33 Exit Do
34 End If
35 ' Add a sql output parameter for each outputParam (note: Must be NVarChar(255))
36 For i As Integer = 0 To sqlReader.FieldCount - 1
37 If UrlReplacements.ContainsKey(sqlReader.GetName(i)) Then
38 UrlReplacements.Add(sqlReader.GetName(i), sqlReader.GetValue(i).ToString)
39 Else
40 UrlReplacements(sqlReader.GetName(i)) = sqlReader.GetValue(i).ToString
41 End If
42 Next
43 isDone = True
44 Loop
45 sqlReader.Close()
46 Else
47 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)
48 UrlReplacements.Clear()
49 UrlReplacements.Add("results", "None")
50 End If
51 Catch ex As System.Data.SqlClient.SqlException
52 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)
53 UrlReplacements.Clear()
54 UrlReplacements.Add("ex", "SqlException")
55 Catch ex As Exception
56 My.Application.Log.WriteException(ex, TraceEventType.Error, String.Format("Unable to connect using the connection '{0}'", m_ConnectionString), 19782)
57 UrlReplacements.Clear()
58 UrlReplacements.Add("ex", ex.GetType.ToString)
59 End Try
60 End Using
61 Return UrlReplacements
62End 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.
1Public Overrides Function RewriteUrl(ByVal url As String) As String
2 ' Get the url replacement values
3 Dim UrlReplacements As Dictionary(Of String, String) = GetUrlReplacements(Me.m_regex.Match(url))
4 ' Take a copy of the target url
5 Dim newUrl As String = m_destinationUrl
6 ' Replace any valid values with the new value
7 For Each key As String In UrlReplacements.Keys
8 newUrl = newUrl.Replace("{" & key & "}", UrlReplacements(key))
9 Next
10 ' Test to see is any failed by looking for any left over '{'
11 If newUrl.Contains("{") Then
12 ' If there are left over bits, then only do a Tempory redirect to the failed URL
13 Me.RedirectMode = RedirectModeOption.Temporary
14 My.Application.Log.WriteEntry(String.Format("Unable to locate a product url replacement for {0}", url), TraceEventType.Error, 19781)
15 Return (String.Format(m_RedirectToOnFail, Me.Name, "NotFound", UrlReplacementsToQueryString(UrlReplacements)))
16 End If
17 ' Sucess, so do a perminant redirect to the new url.
18 My.Application.Log.WriteEntry(String.Format("Redirecting {0} to {1}", url, newUrl), TraceEventType.Information, 19780)
19 Me.RedirectMode = RedirectModeOption.Permanent
20 Return newUrl.Replace("^", "")
21End 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 miss-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.
Lets setup the rule in the config.
1<?xml version="1.0"?>
2<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
3 <providers>
4 <add name="SqlUrlRewritingProvider" type="SSW.UrlRewriting.SqlUrlRewritingProvider, SSW.UrlRewriting"/>
5 </providers>
6 <rewrites>
7 <add name="Rule2"
8 provider="SqlUrlRewritingProvider"
9 connectionString="MyConnectionString"
10 virtualUrl="^.*/Product/ProductInfo.aspx?id=(?'ProductId'd+)"
11 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"
12 DestinationUrl="^~/{ProductKey}"
13 rewriteUrlParameter="IncludeQueryStringForRewrite"
14 redirectToOnFail="~/default.aspx?rewrite=productNotFound"
15 redirectMode="Permanent"
16 redirect="Application"
17 rewrite="Application"
18 ignoreCase="true" />
19 </rewrites>
20</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 propertied here that are part of the Rewriting engine, but you will find them all 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 beginnings 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.
1Imports UrlRewritingNet.Web
2Imports UrlRewritingNet.Configuration
3Imports System.Data.SqlClient
4Imports System.Text.RegularExpressions
5Imports System.Configuration
6
7Public Class SqlRewriteRule
8 Inherits RewriteRule
9
10 Private m_ConnectionString As String
11 Private m_parameterisedSql As String
12 Private m_destinationUrl As String = String.Empty
13 Private m_regex As Text.RegularExpressions.Regex
14 Private m_regexOptions As Text.RegularExpressions.RegexOptions
15 Private m_virtualUrl As String = String.Empty
16 Private m_RedirectToOnFail As String
17
18 ' Methods
19 Private Sub CreateRegEx()
20 Dim helper As New UrlHelper
21 If MyBase.IgnoreCase Then
22 Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), ((RegexOptions.Compiled Or RegexOptions.IgnoreCase) Or Me.m_regexOptions))
23 Else
24 Me.m_regex = New Regex(helper.HandleRootOperator(Me.m_virtualUrl), (RegexOptions.Compiled Or Me.m_regexOptions))
25 End If
26 End Sub
27
28 Public Overrides Sub Initialize(ByVal rewriteSettings As RewriteSettings)
29 Me.m_regexOptions = rewriteSettings.GetEnumAttribute(Of RegexOptions)("regexOptions", RegexOptions.None)
30 Me.m_virtualUrl = rewriteSettings.GetAttribute("virtualUrl", "")
31 Me.m_destinationUrl = rewriteSettings.GetAttribute("destinationUrl", "")
32 Me.CreateRegEx()
33 ' Test for connectionString and throw exception if not available
34 m_ConnectionString = rewriteSettings.GetAttribute("connectionString", String.Empty)
35 If m_ConnectionString = String.Empty Then
36 Throw New NotSupportedException(String.Format("You must specify a connectionString attribute for the DataRewriteRule {0}", rewriteSettings.Name))
37 End If
38 ' Check to see if this is a named connection string
39 Dim NamedConnectionString As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(m_ConnectionString)
40 If Not NamedConnectionString Is Nothing Then
41 m_ConnectionString = NamedConnectionString.ConnectionString
42 End If
43 ' Test for storedProcedure and throw exception if not available
44 m_parameterisedSql = rewriteSettings.GetAttribute("parameterisedSql", String.Empty)
45 If m_parameterisedSql = String.Empty Then
46 Throw New NotSupportedException(String.Format("You must specify a parameterisedSql attribute for the DataRewriteRule {0}", rewriteSettings.Name))
47 End If
48
49 ' Test for redirectToOnFail and throw exception if not available
50 m_RedirectToOnFail = rewriteSettings.GetAttribute("redirectToOnFail", String.Empty)
51 If m_RedirectToOnFail = String.Empty Then
52 Throw New NotSupportedException(String.Format("You must specify a redirectToOnFail attribute for the DataRewriteRule {0}", rewriteSettings.Name))
53 End If
54 MyBase.Initialize(rewriteSettings)
55 End Sub
56
57 Public Overrides Function IsRewrite(ByVal requestUrl As String) As Boolean
58 Return Me.m_regex.IsMatch(requestUrl)
59 End Function
60
61 Public Overrides Function RewriteUrl(ByVal url As String) As String
62 ' Get the url replacement values
63 Dim UrlReplacements As Dictionary(Of String, String) = GetUrlReplacements(Me.m_regex.Match(url))
64 ' Take a copy of the target url
65 Dim newUrl As String = m_destinationUrl
66 ' Replace any valid values with the new value
67 For Each key As String In UrlReplacements.Keys
68 newUrl = newUrl.Replace("{" & key & "}", UrlReplacements(key))
69 Next
70 ' Test to see is any failed by looking for any left over '{'
71 If newUrl.Contains("{") Then
72 ' If there are left over bits, then only do a Tempory redirect to the failed URL
73 Me.RedirectMode = RedirectModeOption.Temporary
74 My.Application.Log.WriteEntry(String.Format("Unable to locate a product url replacement for {0}", url), TraceEventType.Error, 19781)
75 Return (String.Format(m_RedirectToOnFail, Me.Name, "NotFound", UrlReplacementsToQueryString(UrlReplacements)))
76 End If
77 ' Sucess, so do a perminant redirect to the new url.
78 My.Application.Log.WriteEntry(String.Format("Redirecting {0} to {1}", url, newUrl), TraceEventType.Information, 19780)
79 Me.RedirectMode = RedirectModeOption.Permanent
80 Return newUrl.Replace("^", "")
81 End Function
82
83 Private Function GetUrlReplacements(ByVal match As Match) As Dictionary(Of String, String)
84 Dim UrlReplacements As New Dictionary(Of String, String)
85 Dim paramString As String = String.Empty
86 ' Call database
87 Using conn As New SqlConnection(m_ConnectionString)
88 Try
89 conn.Open()
90 Dim cmd As New SqlCommand(m_parameterisedSql, conn)
91 cmd.CommandType = CommandType.Text
92 ' Get all the named groups from the regular expression and use them as the stored procedure parameters.
93 Dim groupNames = From groupName In m_regex.GetGroupNames Where Not groupName = String.Empty And Not IsNumeric(groupName)
94 ' Iterate through the named groups
95 For Each groupName As String In groupNames
96 ' Add the name and value as input prameters to the stored procedure
97 cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
98 paramString = paramString & "[@" & groupName & "=" & match.Groups(groupName).ToString & "]"
99 If UrlReplacements.ContainsKey(groupName) Then
100 UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
101 Else
102 UrlReplacements(groupName) = match.Groups(groupName).ToString
103 End If
104 Next
105 ' Defigne the data capture method
106 Dim sqlReader As SqlClient.SqlDataReader
107 ' Execute the SQL
108 sqlReader = cmd.ExecuteReader()
109 If sqlReader.HasRows Then
110 Dim isDone As Boolean = False
111 Do While sqlReader.Read()
112 If isDone Then
113 ' If more than one record is returned, exit and record
114 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)
115 Exit Do
116 End If
117 ' Add a sql output parameter for each outputParam (note: Must be NVarChar(255))
118 For i As Integer = 0 To sqlReader.FieldCount - 1
119 If UrlReplacements.ContainsKey(sqlReader.GetName(i)) Then
120 UrlReplacements.Add(sqlReader.GetName(i), sqlReader.GetValue(i).ToString)
121 Else
122 UrlReplacements(sqlReader.GetName(i)) = sqlReader.GetValue(i).ToString
123 End If
124 Next
125 isDone = True
126 Loop
127 sqlReader.Close()
128 Else
129 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)
130 UrlReplacements.Clear()
131 UrlReplacements.Add("results", "None")
132 End If
133 Catch ex As System.Data.SqlClient.SqlException
134 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)
135 UrlReplacements.Clear()
136 UrlReplacements.Add("ex", "SqlException")
137 Catch ex As Exception
138 My.Application.Log.WriteException(ex, TraceEventType.Error, String.Format("Unable to connect using the connection '{0}'", m_ConnectionString), 19782)
139 UrlReplacements.Clear()
140 UrlReplacements.Add("ex", ex.GetType.ToString)
141 End Try
142 End Using
143 Return UrlReplacements
144 End Function
145
146 Private Function UrlReplacementsToQueryString(ByVal dic As Dictionary(Of String, String)) As String
147 Dim quer As String = String.Empty
148 For Each dicEntry In dic
149 quer = String.Format("{0}&{1}={2}", quer, dicEntry.Key, dicEntry.Value)
150 Next
151 Return quer
152 End Function
153
154End Class
Figure: Full source listing for the rule.
-———
What would I change and why…or things that I just did not have time to do.
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.
1<asp:SqlDataSource ID="SqlDataSource1" runat="server"
2 CacheExpirationPolicy="Sliding"
3 ConnectionString="MyConnectionString"
4 EnableCaching="True"
5 SelectCommand="ssw_proc_SeoProductIdToProductKey"
6 SelectCommandType="StoredProcedure">
7 <SelectParameters>
8 <asp:RegexParameter DbType="StringFixedLength" DefaultValue="0"
9 Name="ProductId" RegexGroupName="ProductId" Size="100" Type="String" />
10 <asp:Parameter DbType="StringFixedLength" Direction="Output" Name="ProductKey"
11 Size="255" Type="String" />
12 </SelectParameters>
13</asp:SqlDataSource>
Figure: Good Example, code from the ASP.NET 2.0 SqlDataSource.
You should be able to configure any set of input and output parameters.
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.
Caching is a difficult thing as it depends on the amount of data returned, but it can improve the speed.
Technorati Tags: SSW .NET Software Development CodeProject SP 2010 SharePoint
No related videos found.
If you've made it this far, it's worth connecting with our principal consultant and coach, Martin Hinshelwood, for a 30-minute 'ask me anything' call.
We partner with businesses across diverse industries, including finance, insurance, healthcare, pharmaceuticals, technology, engineering, transportation, hospitality, entertainment, legal, government, and military sectors.
SuperControl
Kongsberg Maritime
Flowmaster (a Mentor Graphics Company)
NIT A/S
YearUp.org
Big Data for Humans
Brandes Investment Partners L.P.
Ericson
Cognizant Microsoft Business Group (MBG)
Philips
Bistech
Xceptor - Process and Data Automation
Qualco
Higher Education Statistics Agency
CR2
New Signature
Akaditi
Workday
Royal Air Force
Ghana Police Service
Nottingham County Council
New Hampshire Supreme Court
Department of Work and Pensions (UK)
Washington Department of Enterprise Services
Healthgrades
Lean SA
Workday
Teleplan
Boxit Document Solutions
Deliotte