SQL Server CLR Stored Procedures for External Access

One of the really useful features of CLR stored procs is the ability to access features of the .NET Framework that can do things that are virtually impossible from within the database itself,and to return the results as useful items that can be used in other, "regular" stored procs and SQL statements. In this example, I'm going to use some "very old" code from at least 5 years ago where I wrote a method that would take a street address, either with or without zip code, and send it to the USPS.

We start out with our .NET Assembly:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security;

[assembly: AllowPartiallyTrustedCallers]
namespace SqlClrProcs
{
    public static class HttpUtils
    {
         [Microsoft.SqlServer.Server.SqlProcedure]
         public static void  SpValidateAddress(string street, string city, string state, string zip)
        {
            string res = String.Empty;
            string url1 = "http://zip4.usps.com/zip4/zcl_0_results.jsp?visited=1&pagenumber=0&firmname=&address2="; // 2 Broadway
            string url2 = "&address1=&city="; //NEW YORK
            string url3 = "&state=";//NY
            string url4 = "&urbanization=&zip5=" + zip;
             if (street == "" || city == "" || state == "")
            {
                res = "missing or invalid parameter. street:city:state";
            }
            string fullurl = url1 + street + url2 + city + url3 + state + url4;
            
            WebClient cln = new WebClient();
             try
            {
                res = cln.DownloadString(fullurl);
                 // strip off everything before where the result starts
                int startpos = res.IndexOf("<td headers=\"full\"") + 124;
                res = res.Substring(startpos);
                 if (res.ToUpper().IndexOf("<HTML") > 0)
                {
                    res= "Invalid Address";
                 }
                 // strip off everything after the result
                int endpos = res.IndexOf("</td>") - 10;
                res = res.Substring(0, endpos);
                 //clean up line breaks
                res = res.Replace("<br />", "");
                res = res.Replace("<br/>", "");
                res = res.ToUpper();
                 // clean off HTML Spaces
                res = res.Replace("&NBSP;", " ");
                res = res.Replace("&NBSP;", " ");
                res = res.Replace("&NBSP;", " ");
                res = res.Replace("&NBSP;", " ");
             res = res.Replace("\r", "").Replace("\n", "").Replace("\t", " ");
                res = res.Trim();
            }
            catch (Exception ex)
            {
                res = ex.Message + ex.StackTrace + ex.InnerException.ToString();
             }
             finally
             {
                  cln.Dispose();
             }
             SqlContext.Pipe.Send(res);
        }
    }
}

Note that there is a using directive for Microsoft.SqlServer.Server, which is required.  Note also that the method itself is static, has a return type of void, and is attributed with  [Microsoft.SqlServer.Server.SqlProcedure], and that the assembly has the [assembly: AllowPartiallyTrustedCallers] attribute.

The rest of the method simply accepts a street address, city, state and optional zipcode, and creates the requisite web-scraping url that you would normally be sending as a GET request if you used the Postal Service's own web page for this.

Then I use the "brute force" string manipulation method to strip out what we want from the downloaded web page. Interestingly, this code has not needed to be changed at all in over five years!

Of course, one could get more sophisticated and use a tool like HtmlAgilityPack to create an Address object with separate properties for street, number, direction, type (Parkway, Road, etc.), State, City and zipcode. But in this case all i need is a validated address as a single string since I already have a UDF, fnParseAddr, to handle that from the returned string.

Finally, note that with this type of CLR stored proc we output the Message using the SqlContext.Pipe object. If you don't want to use the Message object and instead want to return a row type result, you can do this instead:

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 4000));      
      // Populate the record.
      record.SetSqlString(0,  res); // output string from the ValidateAddress method      
      // Send the record to the client.
      SqlContext.Pipe.Send(record);

The above would generate a single row output with a column name of "stringcol".

At this point you can do a release build of the class and move into the SQL Server Management Studio to do all the registration. There are a number of Gotchas involved, and they all revolve around permissions needed in SQL Server for something that does external access.

Here are the necessary statements, in the order they are used. The database is "TEST":

--1
EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
--2
ALTER DATABASE TEST SET TRUSTWORTHY ON
GO
--3
ALTER AUTHORIZATION  ON DATABASE::TEST  TO sa
GO

--4
--DROP PROCEDURE SpValidateAddress
--DROP ASSEMBLY SqlClrProcs

--5
-- specify the exact path to YOUR assembly after the "FROM"
create ASSEMBLY SqlClrProcs FROM  'C:\CSHARPBIN2010\GetWebPage\GetWebPage\bin\Release\SqlClrProcs.dll'
WITH PERMISSION_SET =  EXTERNAL_ACCESS
GO

create PROCEDURE SpValidateAddress
@street Nvarchar(250),
@city NVarchar(40),
@State NVARCHAR(20),
@Zip NVARCHAR(12)  
AS
EXTERNAL NAME  [SqlClrProcs].[SqlClrProcs.HttpUtils].SpValidateAddress
GO

--6
EXEC SpValidateAddress '1002 CRESCENT PKWY','DELAND', 'FL', '32724'

An Explanation:

--1. We need to configure the database for CLR. You won't get very far without this.
--2. We need to set the database's Trustworthy property ON. Again, have to do this.
--3. Grant authorization to the user that will be executing your CLR sproc.
--4. These are just utility commands while you're debugging everything. Highlight only the statement part and hit F5 to drop.
--5. The CREATE ASSEMBLY statement needs your assembly name, exact path on the machine,
     and PERMISSION_SET set to EXTERNAL ACCESS if you are doing something like making a WebClient call.
--6. This just exercises your work. The zipcode is optional.

And that's it! SQL Server CLR Stored Procedure for external access. Finally, I should mention that I started this out compiling to .NET Framework 4.0, and SQL Server complained. So I dropped it down to 3.5 and she was happy.

You can download the Visual Studio 2010 Solution here.

By Peter Bromberg   Popularity  (3862 Views)