Introduction

URIs (Uniform Resource Indicators) are strings that uniquely identify a resource on a network or the Internet. The most common type of URI people are familiar with is the URL (Uniform Resource Locator), which uniquely identifies web sites on the World Wide Web. URIs can be divided into locators (URLs), names (URNs), or both. A URL acts like an address for something by specifying its location, whereas a URN specifies its unique name on the network. In the case of a web site, the two are usually the same thing.

Goals

My goal for this project was to use .NET's native System.URI parsing to provide a library of functions that returns the properties or parsed parts of a passed URI string. I expose all of the available .NET 2.0 System.Uri parsed elements and Boolean properties with a matching SQL Server CLR function.

The Code

The brains of the logic reside in two private methods: one that returns parsed URI component, and another that returns the Boolean properties. ParseURI is the private method that returns a parsed URI component. Its parameters are:

URI
The URL or URN to be parsed.

Component
A valueof type System.Uri.UriComponents that specifies what component of the URI we want to return. See http://msdn.microsoft.com/en-us/library/7767559y%28v=VS.80%29.aspx for a list of all possible values in .NET 2.0.

EscapeMode
Specifies whether the URI is escaped or not, one of:
0 = Safe Unescaped. SafeUnescaped leaves the following characters escaped if the character has a reserved meaning in the requested Component value: "%", "#", "?", "/", "\", and "@". Other characters remain unescaped.
1 = Unescaped. no escaping is performed.
2 = Escaped. Escaping is performed according to the rules in RFC 2396.

ReturnNullOnParseError
If TRUE (1), returns a NULL value if the URI cannot be parsed; otherwise, throws a .NET exception specifying the exact parsing error that occurred.

ReturnNullIfURINull
If TRUE (1), returns a NULL value if the URI passed is itself NULL; otherwise, throws a .NET exception specifying "ERROR, a null URI was passed to the URI parser."

ReturnNullIfURIEmpty
If TRUE (1), returns a NULL value if the URI passed is the empty string (""); otherwise, throws a .NET exception specifying "ERROR, an empty URI was passed to the URI parser."

Here is the method itself:

public static SqlString ParseURI(
   SqlString URI, 
   UriComponents Component, 
   SqlInt16 EscapeMode, 
   SqlBoolean ReturnNullOnParseError, 
   SqlBoolean ReturnNullIfURINull, 
   SqlBoolean ReturnNullIfURIEmpty
)
{
   SqlString rtn = new SqlString(null);
   UriFormat Format;
   if (URI.IsNull == true)
   {
     if (ReturnNullIfURINull == true)
     {
        return rtn;
     }
     else
     {
        throw new Exception("ERROR, a null URI wa passed to the URI parser.");
     }
   }
   if (URI.ToString().TrimStart().TrimEnd().Length < 1)
   {
     if (ReturnNullIfURIEmpty == true)
     {
        return rtn;
     }
     else
     {
        throw new Exception("ERROR, an empty URI was passed to the URI parser.");
     }
 }
 switch ((Int16)EscapeMode)
 {
    case 0:
       Format = UriFormat.SafeUnescaped;
       break;
    case 1:
       Format = UriFormat.Unescaped;
       break;
    case 2:
       Format = UriFormat.UriEscaped;
       break;
    default:
       Format = UriFormat.SafeUnescaped;
       break;
 }
 try
 {
    System.Uri uri = new System.Uri(URI.ToString());
    rtn = (SqlString)uri.GetComponents(Component, Format);
 }
 catch (Exception ex)
 {
    if (ReturnNullOnParseError.IsTrue)
    {
       rtn = SqlString.Null;
    }
    else
    {
       throw ex;
    }
 }
 return rtn;
}

The method that returns the Boolean properties in System.Uri to the caller looks similar, but replaces EscapeMode with a Property parameter that specifies which Boolean we want, one of:

1 = System.Uri.IsAbsoluteUri
2 = System.Uri.IsDefaultPort
3 = System.Uri.IsFile
4 = System.Uri.IsLoopback
5 = System.Uri.IsUnc
6 = System.Uri.IsWellFormedOriginalString

See http://msdn.microsoft.com/en-us/library/system.uri%28v=VS.80%29.aspx for info on these.

public static SqlBoolean GetURIProperty(
   SqlString URI,                     
   Int16 Property,                     
   SqlBoolean ReturnNullOnParseError, 
   SqlBoolean ReturnNullIfURINull, 
   SqlBoolean ReturnNullIfURIEmpty
)
{
    SqlBoolean rtn = new SqlBoolean();
    rtn = SqlBoolean.Null;

    if (URI.IsNull == true)
    {
       if (ReturnNullIfURINull == true)
       {
          return rtn;
       }
       else
       {
          throw new Exception("ERROR, null URI passed to URI parser.");
       }
    }

    if (URI.ToString().TrimStart().TrimEnd().Length < 1)
    {
    if (ReturnNullIfURIEmpty == true)
    {
       return rtn;
    }
    else
    {
       throw new Exception("ERROR, empty URI passed to URI parser.");
    }
  }
  
  try
  {
    System.Uri uri = new System.Uri(URI.ToString());
    switch (Property)
    {
       case 1:
          rtn = uri.IsAbsoluteUri;
          break;
       case 2:
          rtn = uri.IsDefaultPort;
          break;
       case 3:
          rtn = uri.IsFile;
          break;
       case 4:
          rtn = uri.IsLoopback;
          break;
       case 5:
          rtn = uri.IsUnc;
          break;
       case 6:
          rtn = uri.IsWellFormedOriginalString();
          break;
     }
   }
   catch (Exception ex)
   {
     if (ReturnNullOnParseError.IsTrue)
     {
        rtn = SqlBoolean.Null;
     }
     else
     {
        throw ex;
     }
   }
   return rtn;
 }

Finally, I create twenty CLR functions that wrap all the publically scoped URI components and properties:

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetAbsoluteURI(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
    return ParseURI(URI, UriComponents.AbsoluteUri, EscapeMode,   ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetAuthority(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.SchemeAndServer, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetHost(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.Host, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetHostAndPort(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.HostAndPort, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetHttpRequestURL(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.HttpRequestUrl, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetPath(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.Path, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetPathAndQuery(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.PathAndQuery, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetPort(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.Port, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetQuery(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.Query, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetScheme(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.Scheme, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetSchemeAndServer(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.SchemeAndServer, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetStrongAuthority(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.StrongAuthority, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetStrongPort(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.StrongPort, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString xf_URIParser_GetUserInfo(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI,
   [SqlFacet(IsNullable = false)] SqlInt16 EscapeMode, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull,
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return ParseURI(URI, UriComponents.UserInfo, EscapeMode, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean xf_URIParser_IsAbsoluteURI(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return GetURIElement(URI, 1, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean xf_URIParser_IsDefaultPort(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return GetURIElement(URI, 2, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean xf_URIParser_IsFile(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return GetURIElement(URI, 3, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean xf_URIParser_IsLoopback(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return GetURIElement(URI, 4, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean xf_URIParser_IsUnc(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return GetURIElement(URI, 5, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean xf_URIParser_IsWellFormedOriginalString(
   [SqlFacet(MaxSize = -1, IsNullable = false)] SqlString URI, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullOnParseError, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURINull, 
   [SqlFacet(IsNullable = true)] SqlBoolean ReturnNullIfURIEmpty)
 {
 return GetURIElement(URI, 6, ReturnNullOnParseError, ReturnNullIfURINull, ReturnNullIfURIEmpty);
 }

Example Output

Download

Please read the disclaimer below, and understand that I do not provide any support for this software, source or binaries, including but not limited to installation, configuration, maintenance, and tuning. If you're good with that, then you can download the source code for the URIParser.cs class here.

Disclaimer

The source code is provided to you as is, without warranty. There is no warranty for the program, expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose and non infringement of third party rights. The entire risk as to the quality and performance of the program is with you. Should the program prove defective, you assume the cost of all necessary servicing, repair and correction.

URI Parser: A set of 20 CLR functions for URI parsing

Introduction

Goals

The Code

Example Output

Download

Disclaimer

Rate

Share

Categories

Share

Rate

URI Parser: A set of 20 CLR functions for URI parsing

Introduction

Goals

The Code

Example Output

Download

Disclaimer

Rate

Share

Categories

Share

Rate

Related content

An Implementation of the FNV1a Hash Algorithm for SQL Server

JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server

XLSExport: A CLR procedure to export proc results to Excel

SQLCLR function to return free space for all drives on a server

CLR Table-Valued Function Example with Full Streaming (STVF / TVF)