SQL ile Web Servis Kullanmak

http://secure.smartbearsoftware.com/samples/testcomplete12/webservices/Service.asmx adresi üzerindeki demo web servisten HelloWorld metodunu çağıracağız. Örnek kod aşağıdadır.

DECLARE @Object INT;
DECLARE @Status INT;
DECLARE @Response NVARCHAR(1000);
 
 
DECLARE @requestBody NVARCHAR(MAX) = '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <HelloWorld xmlns="http://smartbear.com" />
  </soap:Body>
</soap:Envelope>'
 
 
EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST',  'http://secure.smartbearsoftware.com/samples/testcomplete12/webservices/Service.asmx', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Host', 'secure.smartbearsoftware.com'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'text/xml'
DECLARE @len INT = len(@requestBody) 
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'SOAPAction', "http://smartbear.com/HelloWorld"
EXEC sp_OAMethod @Object, 'send', null, @requestBody
 
EXEC sp_OAGetProperty @Object, 'Status', @Status OUT
EXEC sp_OAGetProperty @Object, 'ResponseText', @Response OUTPUT
EXEC sp_OADestroy @Object
 
PRINT @Status
PRINT @Response

Asp.Net Jquery Ajax ile Post

Client Side Kodları

<script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js'></script>
<script type="text/javascript">
    function Calistir() {
        var conString = $('#txtSqlConString').val();
        var queryString = $('#txtQueryString').val();
        //alert('@Url.Action("SqlCalistir", "Home")')
        $.ajax({
            type: "POST",
            url: '@Url.Action("SqlCalistir", "Home")',
            contentType: "application/json; charset=utf-8",
            data: "{'sqlCommand':'" + queryString + "','sqlConnection':'" + conString + "'}",
            dataType: "json",
            success: function (msg) {
                alert(msg);
            },
            error: function () {
                alert('Error!');
            }
        });
    }
</script>

Server Side Kodları

[HttpPost]
       public JsonResult SqlCalistir(string sqlCommand,string sqlConnection)
       {
           string result = string.Empty;
           DataTable dt = new DataTable();
           using (OleDbConnection con = new OleDbConnection(sqlConnection))
           {
               using (OleDbCommand cmd = new OleDbCommand(sqlCommand, con))
               {
                   con.Open();
                   OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                   da.Fill(dt);
                   result =DataTableToJSONWithJSONNet(dt);
                   con.Close();
               }
           }
           return Json(result, JsonRequestBehavior.AllowGet); 
       }

private string DataTableToJSONWithJSONNet(DataTable table)
       {
           string JSONString = string.Empty;
           JSONString = Newtonsoft.Json.JsonConvert.SerializeObject(table);
           return JSONString;
       }