• Home
  • Blog
  • Executing SQL Queries via SharePoint Web Services

Executing SQL Queries via SharePoint Web Services

Can you really execute native SQL Queries from SharePoint Web Services?

Microsoft SharePoint is great for building enterprise systems tying together various data sources.  If the information you are looking for is in a SharePoint List or Document Library, it is straightforward to call the built-in Web Services to query or manipulate that data.  Through custom Web Parts, you can run server-side code and easily retrieve data that lives outside SharePoint.

 

 
But what if you don’t have the access to run Server-Side code?  How can you get to the data that lives in SQL Server from your client-side web application?  You can’t call external XML web services because of the Same Origin Policy Restrictions.  True, you can work around this if you have access to a JSONP web service, and some browsers and servers are starting to support CORS to allow limited cross-site access.  But if you don’t have access to the server, can’t control the browser environment and no JSONP web services are available, you aren't out of luck.  I'll show you how to get SharePoint to execute the SQL Queries on your behalf and return the results to your web browser.  With a few tweaks, this same technique can also be used to access arbitrary XML Web Services.  In a later article, I'll expand this example to do just that.

A Word of Warning:

 
There is a downside to this approach.  Since you will be using the SharePoint Server as a proxy, the SQL logs will show the connection coming from the SharePoint Server.  Also, you must either pass in a username/password with the web service call or use a guest SQL account.  You will have to consider the security impact of either approach carefully.

How does it work?

 
You will be using the WebPartPages.GetDataFromDataSourceControl method.  This method is intended to be used by SharePoint Designer to render data during page design and is very sparsely documented.  According to MSDN, it takes two string parameters: dscXml and contextUrl.  That is the extent of the MSDN documentation.
 
In order to make it easier to work with, I created a helper function called SqlQuery that accepts a Server name, Database name, User name, password, Sql Query and a callback function.  Pass in the proper parameters and your callback will be executed with the results.  You can paste the source code below into the HTML source of a Content Editor Web Part on a page on your SharePoint Server to test.  Note, I am using JQuery to simplify the AJAX calls and form interaction.

Source Code:

 
  <scriptsrc="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.2.min.js"type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            //SqlQuery function - proxies a SQL Server call through sharepoint web services and executes your callback with the results.
            function SqlQuery(server, database, user, password, query, callback) {                 var soapMessage = ["<?xml version='1.0' encoding='utf-8'?>"];                 soapMessage.push("<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/'>");                 soapMessage.push("  <soap:Body>");                 soapMessage.push("      <GetDataFromDataSourceControl xmlns='http://microsoft.com/sharepoint/webpartpages'>");                 soapMessage.push("          <dscXml>&lt;asp:SqlDataSource runat=&quot;server&quot;  __designer:commandsync=&quot;true&quot; ProviderName=&quot;System.Data.SqlClient&quot;  ConnectionString=&quot;");                 soapMessage.push("Data Source=" + server + ";User ID=" + user + ";Password=" + password + ";Initial Catalog=" + database + ";&quot; ");                 soapMessage.push(" SelectCommand=&quot;" + query + " &quot;/&gt;</dscXml>");                 soapMessage.push("          <contextUrl></contextUrl>");                 soapMessage.push("      </GetDataFromDataSourceControl>");                 soapMessage.push("  </soap:Body>");                 soapMessage.push("</soap:Envelope>");                 var message = soapMessage.join("");                 var thisSite = window.location.href.split(window.location.pathname).shift();                  $.ajax({                     url: thisSite + "/_vti_bin/webpartpages.asmx",                     beforeSend: function (xhr) { xhr.setRequestHeader("SOAPAction""http://microsoft.com/sharepoint/webpartpages/GetDataFromDataSourceControl"); },                     type: "POST",                     dataType: "xml",                     contentType: "text/xml; charset=\"utf-8\"",                     data: message,                     complete: function (xData, status) {                         if (status == "success") {                             var rows = $($.parseXML($(xData.responseXML).find("GetDataFromDataSourceControlResult").prop("text"))).find("Row");                             callback(rows, status);                         } else {                             callback(xData, status);                         }                     }                 });             }; 
            //Set up some variables to interact with the form.
            var executeButton = $("#executeQuery"), serverInput = $("#server"), databaseInput = $("#database"), userNameInput = $("#userName"), passwordInput = $("#password"), queryInput = $("#query"); 
 
            //When the user clicks the button, execute the query, parse and display the results.
            executeButton.click(function () {                 window.status = "calling SQL Query";
                SqlQuery(serverInput.val(), databaseInput.val(), userNameInput.val(), passwordInput.val(), queryInput.val(), function (rows, status) {
                    window.status = status;                     if (status == "success") {                         var output = ["<ol>"];
                       //rows is now a jquery object with the response from the sql query
                        rows.each(function (i, element) {                             var row = [];                             for (var j = 0; j < element.attributes.length; j++) {                                 var attribute = element.attributes[j];                                 row.push("'" + attribute.nodeName + "'='" + attribute.value + "'");                             }                             output.push("<li>" + row.join() + "</li>");                         });                         $("#output").html(output.join("") + "</ol>");                     } else {                         $("#output").html($(rows.responseXML).text());                     }                 });             });         });          </script>
<!-- And the form to let the user interact with the SqlQuery function. -->
     <h1>         SQL Query Demo</h1>     <labelfor='userName'>         User Name:</label>     <inputtype='text'id='userName'value='SQLUser'/>     <labelfor='password'>         Password:</label>     <inputtype='password'id='password'value='********'/>     <labelfor='server'>         Server:</label>     <inputtype='text'id='server'value='SERVERNAME'/>     <labelfor='database'>         Database:</label>     <inputtype='text'id='database'value='Food'/>     <labelfor='query'>         Query:</label>     <textareaid='query'rows='5'cols='80'>SELECT TOP 10 [NDB_No] ,[Seq] ,[Amount] ,[Msre_Desc] ,[Gm_Wgt] FROM [Food].[dbo].[WEIGHT]</textarea>     <inputtype='button'id='executeQuery'value='Execute Query'/>     <h1>         Output</h1>     <divid='output'>     </div>
 

Example:

This blog has been relocated from http://mbsguru.blogspot.com/ with authorization.
Lance Russell

VP, System Design/Architecture

Looking for spaghetti code that is nearly impossible to maintain and upgrade?  Then stay away from Lance.  Far away.   Lance’s highly-logical mind can only create organized, well-written applications and integrations that are a breeze to maintain.   There is no business application coding problem that Lance cannot figure out.  One of our hi-profile, global clients tried to make him fail.  He did not and they came back with the quote “Lance has to be one of the best SharePoint consultants in the world”.   Those are definitely true words.   His expertise in all areas of software development, including Dynamics CRM and ERP, Integrations and web sites is top-notch.  And he is funny too.  You’ll appreciate his humorous approach when you see everyone gelling, working together happily, and your project humming along smoothly.

Recent Projects

In a recent project, Lance worked with a global manufacturer of gaming equipment to restructure their entire technology platform. He created Item Master, a SharePoint web-based application used to manage the approval process of changing inventory items, reducing approvals from 2+ weeks to less than 2 days. He also created a product configurator, which automated the time consuming and cumbersome pick/pack/ship process of their orders which were typically customized and highly complicated. To top it all off, he tied it all together with a much needed compliance component that checked orders against their jurisdiction’s regulations.

the most surprising part of working with them was how well they worked with our team. Their calm and professional demeanour throughout the project put people at ease and everyone clicked from day one. The relationship was truly unique, in that you just don’t see that level of trust, bonding and collaboration.”

1000 Characters left