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.
No comments:
Post a Comment