_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › CURLInSQLServerUsingCLR

Contents

1 1
2 2 --> .. 蠍 蟾讌.


  • rest api call
  • web service call

1 1 #


SqlWebRequest.cs
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;

public partial class Functions
{
   // 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);
    }

    // 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);
    }
}

貉危(覈 襦, 蟯襴 蟠朱 ろ
cd C:\Windows\Microsoft.NET\Framework64\v3.5
csc.exe /t:library  "D:\Microsoft SQL Server\clr\SqlWebRequest.cs"
copy SqlWebRequest.dll "D:\Microsoft SQL Server\clr"

create function
CREATE ASSEMBLY SqlWebRequest
FROM 'D:\Microsoft SQL Server\clr\SqlWebRequest.dll'
WITH PERMISSION_SET=UNSAFE;
GO


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

2 2 --> .. 蠍 蟾讌. #

蟶朱 蠍 蟾讌.
https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/sql-clr/Curl
sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO

EXEC sp_add_trusted_assembly 0xF9251BA2BBA78E1462F07ABBF45216B6FB3C8EE702940066607C5BAE76147DBDA9A325A0989B5AA45B5BF26EFEF7A2229C8B8DB17D2518BDFC7E18EFC78806D7
GO

CREATE ASSEMBLY [SqlClrCurl]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004D0BD75A0000000000000000E00022200B013000001000000008000000000000AA2E00000020000000400000000000100020000000020000040000000000000006000000000000000080000000020000719A0000030060850000100000100000000010000010000000000000100000000000000000000000582E00004F000000004000002C04000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B00E0000002000000010000000020000000000000000000000000000200000602E727372630000002C040000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C2E000000000000480000000200050044220000940B000009000000000000000000000000000000D82D0000800000000000000000000000000000000000000000000000000000000000000000000000133002003100000001000011731200000A0A0206280400000606036F1300000A0B1201281400000A281500000A6F1600000A6F1700000A731800000A2A000000133003006300000002000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A06046F1300000A0C1202281400000A281500000A036F1300000A0C1202281400000A6F1B00000A0B281C00000A727700007007281D00000A6F1E00000A2A001B300500D500000003000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A7E010000040B72A10000700C0006046F1300000A0D1203281400000A281500000A036F1300000A0D1203281400000A6F1B00000A0C150BDE5D1304281C00000A1B8D10000001251672A3000070A2251711046F1F00000AA2251872B3000070A225197E020000048C1D000001A2251A72C9000070A2282000000A6F1E00000A0717590B7E02000004282100000ADE0007163D76FFFFFF07153315281C00000A727700007008281D00000A6F1E00000A2A00000001100000000032002C5E005617000001133002002C00000004000011026F1900000A2D23026F1300000A0B1201281400000A0A06282200000A2D0C036F2300000A066F2400000A2A1E02282500000A2A3A1980010000041F3280020000042A0042534A4201000100000000000C00000076342E302E33303331390000000005006C0000006C030000237E0000D80300001404000023537472696E677300000000EC070000D400000023555300C0080000100000002347554944000000D0080000C402000023426C6F620000000000000002000001571502000900000000FA013300160000010000001F0000000200000002000000060000000B0000002500000012000000040000000100000003000000000095020100000000000600870163030600060263030600900031030F00830300000600B800C10206006A01C10206003601C1020600ED01C1020600A701C1020600D201C1020600E500C1020600A400440306008200440306001901C102060000013F020600C303BA020A00550109030A00C00109030A00A70392030E00D903CE030A00870292030A00CF0009030600EF02BA020E009102BA0206008A02BA020600E702BA020A00EF0309030A007A00090306000100BA02060042002E020E00D302CE03000000000700000000000100010001001000B50200004100010001003100210033013100120033015020000000009600CA03360101009020000000009600EA033F010400002100000000960006043F010700F421000000009100FF0249010A002C22000000008618240306000C0034220000000091182A0351010C0000000000000000000100100000000200B60200000100100000000200500000000300B60200000100100000000200500000000300B60200000100100000000200E303090024030100110024030600190024030A00290024031000310024031000390024031000410024031000490024031000510024031000590024031000610024031500690024031000710024031000790024031000890024030600910024030600B10024030600A10024030600990085022100A90024022600C10075022A00A10059022F00C900FA0334009900240339009900A4024700D10024034B00A10068025100D90071005700C900BC035C00E1004D001000B90065002600C900BC036D00F100F9027300C90052007E00A100B0038300F9004900100081002403060020007B00A90224008300AE022E000B0055012E0013005E012E001B007D012E00230086012E002B0096012E003300D8012E003B00DE012E004300ED012E004B000C022E005300D8012E005B00D8012E00630024022E006B004E022E0073005B0240008B00A90260008B00A9021A003F0062007800048000000100000000000000010000009100AF020000040000000000000000000000880039000000000004000000000000000000000088002D00000000000400000000000000000000008800BA0200000000000000496E743332003C4D6F64756C653E00480044454C41595F4F4E5F4552524F520052455452595F434F554E540053797374656D2E44617461006D73636F726C696200546872656164004164640053656E640049734E756C6C4F7257686974655370616365006765745F4D657373616765006765745F506970650053716C5069706500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C654174747269627574650053716C50726F63656475726541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053797374656D2E546872656164696E670053797374656D2E52756E74696D652E56657273696F6E696E6700446F776E6C6F6164537472696E670055706C6F6164537472696E6700457363617065557269537472696E6700546F53716C537472696E67005572690053716C436C724375726C2E646C6C006765745F49734E756C6C0053716C436C724375726C0053797374656D0053797374656D2E5265666C656374696F6E00576562486561646572436F6C6C656374696F6E00417267756D656E74457863657074696F6E00536C65657000416464486561646572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053716C4368617273006765745F4865616465727300436F6E636174004F626A656374004765740053797374656D2E4E657400576562436C69656E7400636C69656E7400506F73740053716C436F6E7465787400546F43686172417272617900506F737457697468526574727900006F59006F00750020006D007500730074002000730070006500630069006600790020006400610074006100200074006800610074002000770069006C006C002000620065002000730065006E007400200074006F002000740068006500200065006E00640070006F0069006E007400000540006400002952006500710075006500730074002000690073002000650078006500630075007400650064002E000001000F4500720072006F0072003A00090000152E002000570061006900740069006E006700200000076D0073002E0000000000210EB3B0DCE94F419AB644C8C813459F00042001010803200001052001011111042001010E04200101020607021251115504200011550320000E0400010E0E0420010E0E0420001D03052001011D0307070312510E115503200002052002010E0E0520020E0E0E04000012710500020E0E0E0A07051251080E1155125D0500010E1D1C04000101080507020E1155040001020E042000127D08B77A5C561934E08980A00024000004800000940000000602000000240000525341310004000001000100594568514168B07173B4258E3B5DCAC316DAE3F3C0C01DDFDBC628534C3F8D1A4F378F74CDD858CDB90804D5A862842BC650F764CBD8EC01C5C69A9EC1BFA90012AC45AC19B8198569A6E06671977DE65FC6939CFC20366252416CFFEA1DEDBB035134658E4C113C92CBA6CB1D32233F033102809E95F0D16B0C1E25E0352CB6020608080002124D124D124D09000301124D124D124D07000201124D1251030000010801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000F01000A53716C436C724375726C00004101003C496D706C656D656E746174696F6E206F66204355524C20636F6D6D616E6420696E2053514C2053657276657220446174616261736520456E67696E6500000501000000000E0100094D6963726F736F667400001E01001953716C20536572766572204769744875622053616D706C6573000017010012436F7079726967687420C2A920203230313800002901002432363565306263332D616435662D343466332D626231372D63363166373762393834376600000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E362E310100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E362E31040100000012010001005408074D617853697A65FFFFFFFF0000009B5D68E6D8B6CFDE25C606C2D49B6121BF8910B0A157824E3480F21D4099D140407F550D1F59CEB3A7F9F37EF533CD3B4AE28FF95D91461162B6B93719086F3FE7500E27DC21295E10C95B5B95A68F4EF3A9D61C05D877ACEBD3BA038CFF6A1633DB4B9D64A1ACC2BACBD480901472A5D0DA9859F8BD5E298BD8143B8A29A25F802E000000000000000000009A2E00000020000000000000000000000000000000000000000000008C2E0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00300000000000000000000D00334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430030000010053007400720069006E006700460069006C00650049006E0066006F0000000C030000010030003000300030003000340062003000000092003D00010043006F006D006D0065006E0074007300000049006D0070006C0065006D0065006E0074006100740069006F006E0020006F00660020004300550052004C00200063006F006D006D0061006E006400200069006E002000530051004C002000530065007200760065007200200044006100740061006200610073006500200045006E00670069006E0065000000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003E000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C0072004300750072006C0000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003E000F00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000046000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C000000000054001A000100500072006F0064007500630074004E0061006D00650000000000530071006C00200053006500720076006500720020004700690074004800750062002000530061006D0070006C00650073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000AC3E00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE SCHEMA CURL;
GO

--Create the function/procedure
CREATE FUNCTION CURL.XGET (@H NVARCHAR(MAX), @url NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrCurl.Curl.Get;
GO
CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000))
AS EXTERNAL NAME SqlClrCurl.Curl.Post;
GO


select curl.xget(null, 'https://api.iextrading.com/1.0/stock/msft/earnings')
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-08-28 20:16:47

覯碁 伎朱襦 蟾讌 讌 狩 覦覯企.