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.