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 seam 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.aspx?id=3456
New: http://northwind.com/CoolLightsaberWithRealAction.aspx
You can do it in product.aspx
Response.Status = "301 Moved Permanently"
Response.StatusCode = 301;
Response.AddHeader("Location","/CoolLightsaberWithRealAction.aspx");
Response.End();
Figure: 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.
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: 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: ASP.NET 4.0 solution in the global.asax file for redirects, less code.
global.asax is quite a good solution, but it does have 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.
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 an 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! 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.
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 lease have it open and pretend to read it while you tinker.
#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 also add the UrlRewritingNet.UrlRewriter section of the config to a seperate file as this makes it more maintainable.
<?xml version="1.0"?>
<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
<providers>
<!-- providers go here -->
</providers>
<rewrites>
<!-- rules go here -->
</rewrites>
</urlrewritingnet>
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.
<configSections>
...
<section name="urlrewritingnet"
restartOnExternalChanges="true"
requirePermission="false"
type="UrlRewritingNet.Configuration.UrlRewriteSection, UrlRewritingNet.UrlRewriter" />
</configSections>
In your "web.config" add this section.
<urlrewritingnet configSource="UrlRewrite.config" />
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>
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>
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.
#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 boiler plate 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 ProductKeyUrlRewritingProvider
Inherits UrlRewritingProvider
Public Overrides Function CreateRewriteRule() As UrlRewritingNet.Web.RewriteRule
Return New ProductKeyRewriteRule
End Function
End Class
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 ProductKeyRewriteRule
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
This is a skeleton of a new rule. It does nothing at the moment, 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.
#3 Capture the URL you want to rewrite
Lets 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 ProductKeyRewriteRule
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
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
' Methods
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
We now have all of the information we need to create a regular expression and call "IsMatch" in the "IsRewrite" method. So 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.
#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 were 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 stored procedure
- some input parameters for our SQL
- some output parameters
- 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.
' Test for connectionString and throw exception if not available
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
' Check to see if this is a named connection string
Dim NamedConnectionString As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(m_ConnectionString)
If Not NamedConnectionString Is Nothing Then
m_ConnectionString = NamedConnectionString.ConnectionString
End If
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.
^.*/Product/ProductInfo\.aspx\?id=(?'ProductId'\d+)
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.
' 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 to the saved replacements
UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
' Add the name and value as input prameters to the stored procedure
cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
Next
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.
While that takes care of the input parameters, I was at a loss for the Input parameters.
' Test for outputParams and throw exception if not available
If rewriteSettings.GetAttribute("outputParams", String.Empty) = String.Empty Then
Throw New NotSupportedException(String.Format("You must specify a outputParams attribute for the DataRewriteRule {0}. Enter one or more params that match your stored procedure seperated by {',' or '|' or ';'}", rewriteSettings.Name))
End If
m_outputParams = rewriteSettings.GetAttribute("outputParams", String.Empty).Split(CChar(","), CChar(";"), CChar("|")).ToList
If m_outputParams Is Nothing OrElse m_outputParams.Count = 0 Then
Throw New NotSupportedException(String.Format("You must specify a outputParams attribute for the DataRewriteRule {0}. Enter one or more params that match your stored procedure seperated by {',' or '|' or ';'}", rewriteSettings.Name))
End If
I settled on a simple comma delimited list of values, again I would have preferred some sort of meta data stored in the web.config.
' Add a sql output parameter for each outputParam (note: Must be NVarChar(255))
For Each param As String In m_outputParams
Dim p As SqlParameter = cmd.CreateParameter
p.Direction = ParameterDirection.Output
p.ParameterName = "@" & param
p.SqlDbType = SqlDbType.NVarChar
p.Size = 255
p.Value = DBNull.Value
cmd.Parameters.Add(p)
Next
All this allows you to call a stored procedure and get a piece of 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)
' Call database
Dim conn As New SqlConnection(m_ConnectionString)
conn.Open()
Dim cmd As New SqlCommand(m_storedProcedure, conn)
cmd.CommandType = CommandType.StoredProcedure
' 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 to the saved replacements
UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
' Add the name and value as input prameters to the stored procedure
cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
Next
' Add a sql output parameter for each outputParam (note: Must be NVarChar(255))
For Each param As String In m_outputParams
Dim p As SqlParameter = cmd.CreateParameter
p.Direction = ParameterDirection.Output
p.ParameterName = "@" & param
p.SqlDbType = SqlDbType.NVarChar
p.Size = 255
p.Value = DBNull.Value
cmd.Parameters.Add(p)
Next
' Execute the stored procedure
Try
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
My.Application.Log.WriteException(ex, TraceEventType.Critical, String.Format("Unable to execute '{0}' using the connection '{1}'", m_storedProcedure, m_ConnectionString), 19782)
UrlReplacements.Clear()
Return UrlReplacements
End Try
' Get all of the output paramiters values
For Each param As SqlParameter In cmd.Parameters
If (param.Direction = ParameterDirection.Output Or param.Direction = ParameterDirection.InputOutput) And Not param.Value.ToString = String.Empty Then
UrlReplacements.Add(param.ParameterName.Replace("@", ""), param.Value.ToString)
End If
Next
' Close the connection
conn.Close()
Return UrlReplacements
End Function
To improve performance we execute the stored procedure as a NonQuery and grab the data as an output parameter.
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 Tempory 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 m_RedirectToOnFail
End If
' Sucess, so do a perminant 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
As you can see all we do once we have the replacement values is replace the keys from the “DestenationUrl” 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.
<?xml version="1.0"?>
<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
<providers>
<add name="ProductKeyUrlRewritingProvider" type="SSW.UrlRewriting.ProductKeyUrlRewritingProvider, SSW.UrlRewriting"/>
</providers>
<rewrites>
<add name="Rule2"
provider="ProductKeyUrlRewritingProvider"
connectionString="MyConnectionString"
virtualUrl="^.*/Product/ProductInfo\.aspx\?id=(?'ProductId'\d+)"
storedProcedure="proc_ProductIdToProductKey"
outputParams="ProductKey"
DestinationUrl="^~/Product/{ProductKey}"
rewriteUrlParameter="IncludeQueryStringForRewrite"
redirectToOnFail="~/productNotFound.aspx"
redirectMode="Permanent"
redirect="Application"
rewrite="Application"
ignoreCase="true" />
</rewrites>
</urlrewritingnet>
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.
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 outtakes.
Imports UrlRewritingNet.Web
Imports UrlRewritingNet.Configuration
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Configuration
Public Class ProductKeyRewriteRule
Inherits RewriteRule
Private m_ConnectionString As String
Private m_storedProcedure As String
Private m_outputParams As List(Of 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
' Methods
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()
' Test for connectionString and throw exception if not available
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
' Check to see if this is a named connection string
Dim NamedConnectionString As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(m_ConnectionString)
If Not NamedConnectionString Is Nothing Then
m_ConnectionString = NamedConnectionString.ConnectionString
End If
' Test for storedProcedure and throw exception if not available
m_storedProcedure = rewriteSettings.GetAttribute("storedProcedure", String.Empty)
If m_storedProcedure = String.Empty Then
Throw New NotSupportedException(String.Format("You must specify a storedProcedure attribute for the DataRewriteRule {0}", rewriteSettings.Name))
End If
' Test for outputParams and throw exception if not available
If rewriteSettings.GetAttribute("outputParams", String.Empty) = String.Empty Then
Throw New NotSupportedException(String.Format("You must specify a outputParams attribute for the DataRewriteRule {0}. Enter one or more params that match your stored procedure seperated by {',' or '|' or ';'}", rewriteSettings.Name))
End If
m_outputParams = rewriteSettings.GetAttribute("outputParams", String.Empty).Split(CChar(","), CChar(";"), CChar("|")).ToList
If m_outputParams Is Nothing OrElse m_outputParams.Count = 0 Then
Throw New NotSupportedException(String.Format("You must specify a outputParams attribute for the DataRewriteRule {0}. Enter one or more params that match your stored procedure seperated by {',' or '|' or ';'}", rewriteSettings.Name))
End If
' Test for redirectToOnFail and throw exception if not available
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
' 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 Tempory 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 m_RedirectToOnFail
End If
' Sucess, so do a perminant 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
Private Function GetUrlReplacements(ByVal match As Match) As Dictionary(Of String, String)
Dim UrlReplacements As New Dictionary(Of String, String)
' Call database
Dim conn As New SqlConnection(m_ConnectionString)
conn.Open()
Dim cmd As New SqlCommand(m_storedProcedure, conn)
cmd.CommandType = CommandType.StoredProcedure
' 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 to the saved replacements
UrlReplacements.Add(groupName, match.Groups(groupName).ToString)
' Add the name and value as input prameters to the stored procedure
cmd.Parameters.AddWithValue("@" & groupName, match.Groups(groupName).ToString)
Next
' Add a sql output parameter for each outputParam (note: Must be NVarChar(255))
For Each param As String In m_outputParams
Dim p As SqlParameter = cmd.CreateParameter
p.Direction = ParameterDirection.Output
p.ParameterName = "@" & param
p.SqlDbType = SqlDbType.NVarChar
p.Size = 255
p.Value = DBNull.Value
cmd.Parameters.Add(p)
Next
' Execute the stored procedure
Try
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
My.Application.Log.WriteException(ex, TraceEventType.Critical, String.Format("Unable to execute '{0}' using the connection '{1}'", m_storedProcedure, m_ConnectionString), 19782)
UrlReplacements.Clear()
Return UrlReplacements
End Try
' Get all of the output paramiters values
For Each param As SqlParameter In cmd.Parameters
If (param.Direction = ParameterDirection.Output Or param.Direction = ParameterDirection.InputOutput) And Not param.Value.ToString = String.Empty Then
UrlReplacements.Add(param.ParameterName.Replace("@", ""), param.Value.ToString)
End If
Next
' Close the connection
conn.Close()
Return UrlReplacements
End Function
End Class
----------
Outtakes (Enhancements and future additions)
What would I change and why…
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.
<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>
You should be able to configure any set of input and output parameters.
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.
Add caching
Caching is a difficult thing as it depends on the amount of data returned, but it can improve the speed.
<iframe src="http://ads.geekswithblogs.net/a.aspx?ZoneID=5&Task=Get&PageID=31016&SiteID=1" width=1 height=1 Marginwidth=0 Marginheight=0 Hspace=0 Vspace=0 Frameborder=0 Scrolling=No> <script language='javascript1.1' src="http://ads.geekswithblogs.net/a.aspx?ZoneID=5&Task=Get&Browser=NETSCAPE4&NoCache=True&PageID=31016&SiteID=1"></script> <noscript>
</noscript> </iframe>
Submit an article or tip
