SQL Server’s SQLXML templates to feed AJAX pages (Part 2)

Note: This is the second part of the article, please read the first part if you haven’t already.

Let’s now look at the most interesting part of this article: accessing data using JavaScript and SQLXML.

In the previous part, we’ve seen how to configure SQLXML and write a very simple template that retrieves XML-formatted data and sends it through HTTP.

We will now exploit that to display a set of data in to a simple HTML page, only using asynchronous JavaScript requests.

The true interest of that technique is that you don’t have to worry about coding anything in a server-side language, as the transformation will be performed by SQL Server (even though additional transformation can be done using XSLT), and the result available as a simple web resource.

For the sake of this demo, I’ll explain how to create an AJAX page (using prototype) that allows to search an Employee from the AdventureWorks sample database, and refreshes a list of all possible matches as we type.

Note: If AdventureWorks is not installed on SQL Server, you can download it from Microsoft. Here is a step by step explanation on how to do it.

Writing a stored procedure

In the AdventureWorks database, there is a view named HumanResources.vEmployees, which contains a bunch of rows. We’ll use that view to get the data we need to populate the list.

To retrieve those data, we’ll first create a stored procedure that will take two parameters:

  • A string
  • An integer that defines how we search the names (by first name, last name, or both)

Here is the code for that Stored Procedure:

CREATE PROCEDURE [HumanResources].[SearchEmployees]
(
    @SearchString varchar(128),
    @SearchMode tinyint = 0
)
AS
SET NOCOUNT ON

IF @SearchMode = 0
    SELECT (FirstName + ' ' + LastName) AS FullName
    FROM HumanResources.vEmployee
    WHERE Upper(FirstName) LIKE Upper(@SearchString + '%')
    ORDER BY FirstName, LastName
    FOR XML RAW;
ELSE IF @SearchMode = 1
    SELECT (FirstName + ' ' + LastName) AS FullName
    FROM HumanResources.vEmployee
    WHERE Upper(LastName) LIKE Upper(@SearchString + '%')
    ORDER BY LastName, FirstName
    FOR XML RAW;
ELSE
    SELECT (FirstName + ' ' + LastName) AS FullName
    FROM HumanResources.vEmployee
    WHERE Upper(FirstName + ' ' + LastName) LIKE Upper(@SearchString + '%')
    OR Upper(LastName + ' ' + FirstName) LIKE Upper(@SearchString + '%')
    ORDER BY FirstName, LastName
    FOR XML RAW;

SET NOCOUNT OFF

Note how I use the FOR XML RAW statement to explicitly tell SQL Server to return the result as an XML stream.

Creating a SQLXML template

Next, we need to create the XML file that will be called by JavaScript.

I’ll put it into the directory I created in the first part of this article (c:\inetpub\wwwroot\sqlxml\temp) as “SearchUser.xml”.

The content of that file is the following:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:header>
    <sql:param name="SearchString"></sql:param>
    <sql:param name="SearchMode"></sql:param>
    </sql:header>
    <sql:query>
        EXEC HumanResources.SearchEmployees @SearchString, @SearchMode
    </sql:query>
</root>

As you can see, it’s pretty easy to understand. I simply inserted a call to the stored procedure and attached two parameters as XML variables.To check if it works, open your browser and type the URL to the file, plus both the parameters in the query string: http://localhost/sqlxml/temp/SearchUser.xml?SearchString=t&SearchMode=0.

You should get a list of names looking like this:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <row FullName="Tawana Nusbaum" />
    <row FullName="Taylor Maxwell" />
    <row FullName="Tengiz Kharatishvili" />
    <row FullName="Terrence Earls" />
    <row FullName="Terri Duffy" />
    <row FullName="Terry Eminhizer" />
    <row FullName="Tete Mensa-Annan" />
    <row FullName="Thierry D'Hers" />
    <row FullName="Thomas Michaels" />
    <row FullName="Tom Vande Velde" />
    <row FullName="Tsvi Reiter" />
</root>

Write the search Page

I’m going to use Prototype.js, a JavaScript library, to quickly put together a page that allows the user to enter a name, and returns a list of users from the database matching that input. Using a library such as Prototype.js helps developers actually focusing features instead of spending time on purely utilitarian mechanisms such as implementing the code to handle asynchronous HTTP requests etc. It works as an include file that you declare before any other script, between the <HEAD> tags.

<script type="text/javascript" src="prototype.js"></script>

The HTML

Nothing fancy here, a few input tags, and two empty DIVs to display the dynamic content.

<body>
    <h1>Search user by name</h1>
    <div>
        <input type="text" id="txtInput" />
        <input type="checkbox" id="chkFirstName" /><label>First Name</label>
        <input type="checkbox" id="chkLastName" /><label>Last Name</label>
        <a href="#" id="lnkRefresh">Refresh</a>
    </div>
    <div id="pnlStatus"></div>
    <div id="pnlResult"></div>
</body>

The JavaScript

Thanks to Prototype, I only need to:

  • Create variables that will reference the various objects in the page
  • Write a method that is triggered every time a user types something in the input text box and calls the Ajax.Request method, that implements everything that is required to perform an asynchronous request to the XML template.
  • Write another method that will be triggered whenever the XML is available.
<script type="text/javascript">
// UI Elements
var pnlResult = null;
var txtInput = null;
var chkFirstName = null;
var chkLastName = null;
var pnlStatus = null;
var xmlDoc = null;
var lnkRefresh = null;

function form_load()
{
    pnlResult = $("pnlResult");
    txtInput = $("txtInput");
    chkFirstName = $("chkFirstName");
    chkLastName = $("chkLastName");
    pnlStatus = $("pnlStatus");
    lnkRefresh = $("lnkRefresh");

    txtInput.onkeyup = lnkRefresh.onclick = SearchUser
}

function SearchUser()
{
    var userStr = txtInput.value;
    var myAjax;
    var url;
    var pars;
    var nMode;

    if(userStr.length >= 1)
    {
        nMode = 0;

        if(chkFirstName.checked)
        {
            nMode = 0;
        }

        if(chkLastName.checked)
        {
            nMode = 1;
        }

        if ( (chkLastName.checked) && (chkFirstName.checked) )
        {
            nMode = 2;
        }

        url = 'http://localhost/sqlxml/temp/SearchUser.xml';
        pars = 'SearchString=' + userStr + '&SearchMode=' + nMode;

        pnlStatus.innerHTML = "Processing...";

        myAjax = new Ajax.Request(
            url,
            {
            method: 'get',
            parameters: pars,
            onComplete: showResponse
            });
    }
}

function showResponse(originalRequest)
{
    var nodes;
    var nCount;
    pnlResult.innerHTML = "";
    pnlStatus.innerHTML = "";

    xmlDoc = originalRequest.responseXML;
    nodes = xmlDoc.getElementsByTagName("row");
    nCount = nodes.length;

    if(nCount == 0)
    {
        pnlResult.innerHTML = "Sorry, your search did not return any result...";
    }
    else
    {
        pnlStatus.innerHTML = nCount + " user(s) found...";
        for(var i=0; i&lt;nCount;i++)
        {
            pnlResult.innerHTML += nodes[i].getAttribute("FullName") + "<br />";
        }
    }
}

// Page loaded listener
if(window.addEventListener)
{window.addEventListener("load", form_load, false);}
else if(document.addEventListener)
{document.addEventListener("load", form_load, false);}
else if(window.attachEvent)
{window.attachEvent("onload", form_load);}
</script>

Voila. We’re pretty much done.Please note that the SQLXML virtual directory is great but not suitable for every situation, as it makes data from your database publicly available to anyone smart enough to find the URL in your JavaScript source code. So, and it’s a general rule in the web business, be very careful about what you expose to your customers, and how you do it.Source code for SQL Server’s SQLXML templates and AJAX

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: