For most things that you can’t do straigh-off in T-SQL, you have the option to write a Common Language Runtime procedure. This gives you access to a huge array of APIs, so you can create really cool stuff, way beyond what T-SQL would normally allow you to do. Here, we’ll explore how to write a web request procedure in C#, so you can download a web document (for instance, a file on a REST service) directly into a table or a string variable using just a regular function call.

This article assumes that you are familiar with HTTP concepts such as GET and POST. Also, you should be comfortable with .NET programming and working in Visual Studio. However, you don’t need any deeper networking knowledge, as the .NET Framework makes a pretty good abstraction for you in its object model. This demo is based on the .NET 3.0/3.5 framework, and all the code is written in C#. The example code will work just fine in the Express Edition of Visual C#.

Let me also start off by say that I’m no C# hero. Any obvious subjects to improvement that you spot, please let me know in the comments section.

Starting a new C# project

In Visual C#, create a new project and select “Class library”, so your code will compile to a DLL file. This may differ between versions of Visual Studio and SQL Server, but you’ll figure it out.

The C# code

Here’s the example code of a C# procedure that we’re going to use. Starting off, we’re including Microsoft.SqlServer.Server because we’re going to need this to communicate with the server – retrieving arguments and returning values or recordsets.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Globalization;

// For the SQL Server integration
using Microsoft.SqlServer.Server;

// Other things we need for WebRequest
using System.Net;
using System.Text;
using System.IO;

We’re setting up two basic functions, GET() and POST(), corresponding to their respective HTTP equivalents. We’re going to create a class, simply called Functions, but you could call it what you like.

public partial class Functions
{

Partial, in this case means that you can split the code over several different files if you want. You will notice that both functions are prefixed with an attribute (the stuff within brackets). The attribute is there for Visual Studio, so it knows that we are building an SQL Server function. Here’s the GET() function in its entirety, with comments:

    // Function to return a web URL as a string value.
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
    public static SqlString GET(SqlString uri, SqlString username, SqlString passwd)
    {
        // The SqlPipe is how we send data back to the caller
        SqlPipe pipe = SqlContext.Pipe;
        SqlString document;

        // Set up the request, including authentication
        WebRequest req = WebRequest.Create(Convert.ToString(uri));
        if (Convert.ToString(username) != null & Convert.ToString(username) != "")
        {
            req.Credentials = new NetworkCredential(
                Convert.ToString(username),
                Convert.ToString(passwd));
        }
        ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";

        // Fire off the request and retrieve the response.
        // We'll put the response in the string variable "document".
        WebResponse resp = req.GetResponse();
        Stream dataStream = resp.GetResponseStream();
        StreamReader rdr = new StreamReader(dataStream);
        document = (SqlString)rdr.ReadToEnd();

        // Close up everything...
        rdr.Close();
        dataStream.Close();
        resp.Close();

        // .. and return the output to the caller.
        return (document);
    }

The POST() function looks fairly similar:

    // Function to submit a HTTP POST and return the resulting output.
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString POST(SqlString uri, SqlString postData, SqlString username, SqlString passwd)
    {
        SqlPipe pipe = SqlContext.Pipe;
        SqlString document;
        byte[] postByteArray = Encoding.UTF8.GetBytes(Convert.ToString(postData));

        // Set up the request, including authentication, 
        // method=POST and encoding:
        WebRequest req = WebRequest.Create(Convert.ToString(uri));
        ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";
        if (Convert.ToString(username) != null & Convert.ToString(username) != "")
        {
            req.Credentials = new NetworkCredential(
                Convert.ToString(username),
                Convert.ToString(passwd));
        }
        req.Method = "POST";
        req.ContentType = "application/x-www-form-urlencoded";

        // Submit the POST data
        Stream dataStream = req.GetRequestStream();
        dataStream.Write(postByteArray, 0, postByteArray.Length);
        dataStream.Close();

        // Collect the response, put it in the string variable "document"
        WebResponse resp = req.GetResponse();
        dataStream = resp.GetResponseStream();
        StreamReader rdr = new StreamReader(dataStream);
        document = (SqlString)rdr.ReadToEnd();

        // Close up and return
        rdr.Close();
        dataStream.Close();
        resp.Close();

        return (document);
    }

You’ll notice that I haven’t bothered trapping errors in this tutorial code, but I suppose this could be a good idea in production code, or you could write a TRY-CATCH block in T-SQL when calling the function.

Finally, we just need to close the curly brackets on the class, then it’s compile time! 🙂

}

How to enable the server to run CLR code

If you haven’t enabled CLR integration on the server (it’s disabled by default), you need to do this using sp_configure:

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Before you enable CLR execution, you should be aware of what it means to server security, and you should probably check with the server’s owner.

If you haven’t enabled CLR execution, you’ll get the following error message when you try to execute your code:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable
        "clr enabled" configuration option.

Also, in order to run “unsafe” managed code (i.e. code that has access to stuff outside the SQL Server context), you need to mark the database as trustworthy. This is done using the ALTER DATABASE statement:

ALTER DATABASE myDatabase SET TRUSTWORTHY ON;

If you’ve forgotten this last step, you’ll get the following error:

Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'SqlWebRequest' failed because assembly
      'SqlWebRequest' is not authorized for PERMISSION_SET = UNSAFE.
      The assembly is authorized when either of the following is true:
      the database owner (DBO) has UNSAFE ASSEMBLY permission and the
      database has the TRUSTWORTHY database property on; or the assembly
      is signed with a certificate or an asymmetric key that has a
      corresponding login with UNSAFE ASSEMBLY permission.

Setting up your assembly and function in the database

You have to load the DLL (the assembly) into the database. This is done in the database you’re going to work in. The assembly is actually stored in the database, so you don’t need to keep the DLL file once you’ve registered the assembly.

USE myDatabase
GO

CREATE ASSEMBLY SqlWebRequest
FROM 'D:\Stuff\SqlWebRequest.dll'
WITH PERMISSION_SET=UNSAFE;
GO

The UNSAFE clause means that the assembly has more privileges, which is needed in this case to get “outside access”, in this case to the networking functionality. If you’re simply doing arithmetics, string parsing or something like that, PERMISSION_SET=SAFE is probably a better idea.

Finally, all you need to do is create the CLR functions that reference the functions in the assembly. This follows the basics of the CREATE FUNCTION statement, except we’re using the EXTERNAL NAME clause to point to the assembly, class and C# function name:

CREATE FUNCTION dbo.fn_get_webrequest(
     @uri        nvarchar(max),
     @user       nvarchar(255)=NULL,
     @passwd     nvarchar(255)=NULL
)
RETURNS nvarchar(max)
AS
EXTERNAL NAME SqlWebRequest.Functions.GET;

GO

CREATE FUNCTION dbo.fn_post_webrequest(
     @uri         nvarchar(max),
     @postdata    nvarchar(max),
     @user        nvarchar(255)=NULL,
     @passwd      nvarchar(255)=NULL
)
RETURNS nvarchar(max)
AS

EXTERNAL NAME SqlWebRequest.Functions.POST;

GO

How to call the function in T-SQL

Now your CLR function is ready to use! Try using it to get stock prices from Yahoo, for instance:

PRINT dbo.fn_get_webrequest('http://quote.yahoo.com/d/quotes.csv?'+
       's=AAPL+YHOO+GOOG+GE+MSFT&f=snl1t1ghc1', DEFAULT, DEFAULT);

This is what the output should look like:

"AAPL","Apple Inc.",449.07,"3:40pm",437.66,451.54,+6.27
"YHOO","Yahoo! Inc.",20.78,"3:40pm",20.575,20.85,+0.05
"GOOG","Google Inc.",789.77,"3:40pm",784.40,795.9499,-1.00
"GE","General Electric ",23.09,"3:40pm",22.91,23.15,+0.28
"MSFT","Microsoft Corpora",27.38,"3:40pm",27.34,27.60,+0.0

Hope you found this tutorial useful. Again, let me know in the comment section if there’s anything I’ve missed.