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; charset=utf-8";

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

   [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;
        String document;

        // Set up the request, including authentication
        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 = "GET";
        req.ContentType = "application/json; charset=utf-8";        

        // 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, Encoding.UTF8);
        document = (String)rdr.ReadToEnd();

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

        // .. and return the output to the caller.
        return (document+" версия 1");
    }

DROP FUNCTION dbo.fn_get_webrequest
GO
DROP FUNCTION dbo.fn_post_webrequest
GO
DROP ASSEMBLY [WebServiceRequestPOST]
GO

CREATE ASSEMBLY WebServiceRequestPOST
FROM 'd:\CLR\WebService\POST\WebServicePOST.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 WebServiceRequestPOST.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 WebServiceRequestPOST.Functions.POST;
GO