Network Programming in .NET

SQL server doesn’t have a native way to retrieve content from the internet, and you may wish to do a join on data that you have in your database, and data that might be available via an API.

Here is code in C# that defines this CLR UDF (Common Language Runtime User Defined Function);

using System.Data.SqlTypes;
using System.Net;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString http(SqlString url)
{
var wc = new WebClient();
var html = wc.DownloadString(url.Value);
return new SqlString (html);
}
}

Which you can then compile to a DLL and deploy to your SQL server as follows

-- Installation Instructions

EXEC sp_changedbowner 'sa'
ALTER DATABASE [<your database here>] SET trustworthy ON

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

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

CREATE ASSEMBLY [HttpDb] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300690B815D0000000000000000E00022200B013000000600000006000000000000CE2500000020000000400000000000100020000000020000040000000000000006000000000000000080000000020000000000000300608500001000001000000000100000100000000000001000000000000000000000007C2500004F00000000400000A002000000000000000000000000000000000000006000000C000000442400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D4050000002000000006000000020000000000000000000000000000200000602E72737263000000A0020000004000000004000000080000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000C00000000000000000000000000004000004200000000000000000000000000000000B025000000000000480000000200050088200000BC030000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200200000000100001100730500000A0A060F00280600000A6F0700000A0B07730800000A0C2B00082A2202280900000A002A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C0000005C010000237E0000C80100007401000023537472696E6773000000003C030000040000002355530040030000100000002347554944000000500300006C00000023426C6F620000000000000002000001471502000900000000FA013300160000010000000800000002000000020000000100000009000000040000000100000001000000030000000000B00001000000000006004F00FF0006006F00FF0006002600EC000F001F01000006005801BF000A003A00CB000A00A6002E010E006A015F01000000000100000000000100010001001000430100001500010001005020000000009600C6002F0001007C20000000008618E6000600020000000100BB000900E60001001100E60006001900E6000A003100E60006004100E600060039008D001800410097001C003900E60021002900E60006002000230067002E000B0036002E0013003F002E001B005E0010000480000000000000000000000000000000001600000004000000000000000000000026001D000000000004000000000000000000000026000A00000000000400000000000000000000002600BF00000000000000003C4D6F64756C653E0053797374656D2E4461746100487474704462006D73636F726C69620044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C756500446F776E6C6F6164537472696E670053716C537472696E67004874747044622E646C6C0075726C0053797374656D0068747470004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730055736572446566696E656446756E6374696F6E73004F626A6563740053797374656D2E4E657400576562436C69656E7400000000001CDD5E8F295E9C409C5184BA38C18A700004200101080320000105200101111107070312210E111D0320000E0420010E0E042001010E08B77A5C561934E089060001111D111D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100070100000000040100000000000000690B815D00000000020000001C010000602400006006000052534453D1084ED4A1FABB4481590D5EFF27E75E01000000433A5C55736572735C466961636820526569645C446F63756D656E74735C56697375616C2053747564696F20323031375C50726F6A656374735C4874747044625C4874747044625C6F626A5C44656275675C4874747044622E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A42500000000000000000000BE250000002000000000000000000000000000000000000000000000B0250000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000036000B00010049006E007400650072006E0061006C004E0061006D00650000004800740074007000440062002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000003E000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004800740074007000440062002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D03500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE

CREATE FUNCTION [dbo].[http] (@url [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [HttpDb].[UserDefinedFunctions].[http];

The Github repo for this code is available here; https://github.com/infiniteloopltd/SQLHttp

Update 2020:  This code has been included in Github’s 2020 Archive program, to be buried under ice in Svalbard!