Can SQL Server Send a Web Request

How can I make HTTP request from SQL server?

I got answer by powershell. What I did is open powershell in sql server then I did execute following code in powershell.

$http_Request= New-Object system.Net.WebClient;
$Result = $http_Request.downloadString("url")

sending http requests from SQL server

I would say you have identified a great scenario for use of CLR. Microsoft publishes a good overview of CLR vs TSQL and when to use one and not the other - I've included link below.

Since you are accessing resources outside of SQL Server through .Net technologies I think your approach gets a pretty clear vote for CLR.

http://msdn.microsoft.com/en-us/library/ms131045(v=sql.105).aspx

POST HTTP request that sends SQL data to a site

Well, the question is not really different is can you pass some values to some sub you have in code? Well, gee? The answer is does that simple sub in code support or have parameters that allows you to do this?

I mean, do you think you can pass SQL to Amazon.com when ordering a book, and order a book for me?

So the answer is sure, you can do this. But on the web site, you have to write code (usually called a web method) that accepts that SQL and then does something with the sql (like say hit or update or pull data from say the sql server that drives that web site).

So you can certainly create web methods on that web site. But you certainly can't just out of the blue decide to send some SQL to your web site anymore then can you decide to send some SQL updates to your on-line banking application and give yourself a really nice Christmas bonus!

So the simple answer?
Yes, you can send SQL commands to a site, but that site will have to be setup with some web service methods that can accept what you want to send.

So, say I have a web forms site. And I want to get allow me to send a query to that web site?

Ok, so we can drop in this public function into that web page as a code stub.

It could look like this:

<WebMethod()>
Public Shared Function GetSQLresults(strSQL As String, MyColumn As String) As String

    Dim strResult As String = ""

    Using cmdSQL As New SqlCommand(strSQL,
            New SqlConnection(My.Settings.Test3))

        Dim MyTable As New DataTable
        cmdSQL.Connection.Open()
        Try
            MyTable.Load(cmdSQL.ExecuteReader)
            If MyTable.Rows.Count > 0 Then
                strResult = MyTable.Rows(0).Item(MyColumn)
            Else
                strResult = "no data"
            End If

        Catch ex As Exception
            strResult = Err.Description
        End Try

    End Using

    Return strResult

End Function

So, now you can call that method. Say as jQuery, you can do this:

     <script>
            function mywebcall() {
                alert('start');

            var strSQL = "SELECT ID, HotelName, City from tblHotels";
            var MyColumn = "HotelName";

            $.ajax({
                type: "POST",
                url: 'ListViewTest.aspx/GetSQLresults',
                data: JSON.stringify({ strSQL : strSQL, MyColumn : MyColumn}),
                contentType: "application/json; charset=utf-8",
                datatype: "json",
                success: function (em) {
                    alert('SQL result = ' + em.d);
                }
            });
            }

        </script>

Now, as a general rule, I would not suggest that you setup some web service calls to just accept SQL and do anything you want. So, you can certainly setup and write public functions on that web site, and those public functions (or subs if you not returning values) can accept parameters.

In fact, you could with the above method placed in a web service asmx page?

you could type in this in the URL:

https://localhost:44316/WebService1.asmx/
GetSQLresults?strSQL=SELECT ID, HotelName from tblHotels WHERE ID = 20&MyColumn=HotelName

And the web site would spit out this:

<string xmlns="http://mywebdata.org/">My Cool</string>

Or if you ran that JavaScript (jQuery), then the output would be this:

Sample Image

Of course you use some kind of web service call - that way you get a "object" back with parameters that you can easily use in code.

As noted, be it amazon.com or YOUR web site? Some security concerns would exist here to allow any old SQL to be run here. But the general idea and concept is that you can and will and should create web service calls.

The nice thing about asp.net web methods like above?
Without any extra work they accept:

SOAP 1.1 calls
SOAP 1.2 calls
REST with post headers
REST with URL parameters like above last example.
(so the REST calls can use a simple post for above - with parameters in url)

And the web service can (and will) return JSON format data, or XML format. As per above, the jQuery ajax called passed and returned JSON data. And the REST URL with ?, & parameters sent returned xml. And all of the 4 formats (json, xml) and SOAP or rest all work without any more additional code then what I posted here.

Can I call a Web API from SQL Server?

Sure it's as simple as this;

     exec  [dbo].[APICaller_POST]
     @URL = 'http://localhost:5000/api/auth/login'
     ,@BodyJson = '{"Username":"gdiaz","Password":"password"}'

After you deploy this CLR Stored procedure:
https://github.com/geral2/SQL-APIConsumer

It has multiple procedures that allows you calling API that required a parameters or even passing multiples headers and tokens authentications.

Sample Image

Sending HTTP POST request from SQL Server 2012 or SQL CLR C#

Like Joe suggested using HttpWebRequest instead of HttpClient works without having to use unsupported assemblies:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendRequest (string query, string table)
{
    string address = "http://localhost:9000/api/search";
    HttpWebRequest request = (HttpWebRequest) WebRequest.Create(address);
    request.ContentType = "application/json; charset=utf-8";
    request.Method = "POST";

    using (var streamWriter = new StreamWriter(request.GetRequestStream()))
    {
        string json = "{\"Query\":\""+query+"\",\"Table\":\""+table+"\"}";

        streamWriter.Write(json);
        streamWriter.Flush();
    }

    var httpResponse = (HttpWebResponse)request.GetResponse();
    using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
    {
        var result = streamReader.ReadToEnd();
    }
}

HTTP Get request SQL server web API

Entity Framework's FindAsync method returns a row based on a primary key. When you change the input from the ID to Tool_Name you are no longer passing the method the primary key. Thus you need to use another EF method such as FirstOrDefault to return the details based on the Tool_Name:

var info = await _context.Tool_Registration.FirstOrDefaultAsync( tr => tr.Tool_Name == Tool_Name);

You should also probably implement two endpoints: one to return a single User record based on the ID, and one to return multiple based on the Tool_Name filter:

[HttpGet]
[Route("{UserId}")]
public async Task<ActionResult<Users>> GetUserById(int UserId)
{
    var user = await _context.Tool_Registration.FindAsync(UserId);
    return Ok(user);
}

[HttpGet]
[Route("")]
public async Task<ActionResult<IEnumerable<Users>>> GetUsers(string Tool_Name)
{
    IEnumerable<Users> users;
    if (string.IsNullOrEmpty(Tool_Name))
        users = await _context.Tool_Registration.ToListAsync();
    else
        users = await _context.Tool_Registration.Where(tr => tr.Tool_Name == Tool_Name).ToListAsync();
    return Ok(users);
}

In C# how can I send data from SQL Server to a HTTP request using the POST method?

Something like this - I did not build & run it so apologies if it isn't perfect but hopefully it will point you in the right direction

using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

private class DataToSendModel
{
    public string FullName { get; set; }
    public string OtherProperty1 { get; set; }
    public string OtherProperty2 { get; set; }
}

// async as I have used .NET Core and it is best practice, but you can remove async from everywhere if you want (I would suggest not)
public async Task SaveUser()
{
    // Always wrap in a `using` statement to endsure cleanup of connections and resources
    using (var conn = new SqlConnection(config.GetConnectionString("SomeConnectionStringConfigName")))
    using (var cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        // Consider a stored procedure for this
        cmd.CommandText = "SELECT FullName, OtherColumn1, OtherColumn2 FROM Students";

        conn.Open();

        using (var reader = await cmd.ExecuteReaderAsync())
            while (reader.Read())
            {
                var dataToSend = new DataToSendModel
                {
                    FullName = reader["FullName"].ToString(),
                    OtherProperty1 = reader["OtherColumn1"].ToString(),
                    OtherProperty2 = reader["OtherColumn2"].ToString()
                };

                // Don't use this, use HttpClient https://blog.jayway.com/2012/03/13/httpclient-makes-get-and-post-very-simple/
                //using (var wb = new WebClient())
                using (var httpClient = new HttpClient())
                {
                    var response = await httpClient.PostAsync("https://urbanwaste.000webhostapp.com/user/pickup.ph", new StringContent(JsonConvert.SerializeObject(dataToSend)));

                    response.EnsureSuccessStatusCode();

                    string content = await response.Content.ReadAsStringAsync();
                }
            }
    }
}

Calling an API from SQL Server stored procedure

Please see a link for more details.

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object