The Database Information

The database table for this specific blog is relatively simple. It contains four columns: id, title, date, and posting. The following SQL statement creates this table, called BlogPosts:

CREATE TABLE BlogPosts (
    id int IDENTITY (1, 1) NOT NULL,
    date datetime NOT NULL,
    title text NOT NULL,
    posting text NOT NULL
)

When this query runs, it creates the table, but it is empty. The code download for this example, located at www.wrox.com, contains a SQL file that will add records to the table.

There are primarily two pieces of desired information needed for the search results: id (used in the URL to the blog post) and title. With this knowledge, you can create the search query:

SELECT TOP 10
id, title
FROM BlogPosts
WHERE posting LIKE '%SEARCHSTRING%' OR
title LIKE '%SEARCHSTRING%'
ORDER BY date DESC

This query selects the id and title columns from the BlogPosts table where the contents of posting and title contain an instance of the search string. The results returned are ordered by descending date, and only ten records are returned.

Before delving into the code, however, the returned data structure needs discussing.

The Returned Data Structure

The only two pieces of information retrieved are the blog post ID and its title, and a result of a search could contain more than one blog post. At this point, you need a result set of objects that each contains title and id properties:

[
    {
        title : "Title 1",
        id    : "1"
    },
    {
        title : "Title 2",
        id    : "2"

    },
    {
        title : "Title 3",
        id    : "3"
    }
]

This code illustrates what the data returned from the server looks like. The structure is an array containing several objects, each with title and id properties. With the result set mapped out, you can now approach the SiteSearch class.

The SiteSearch Class

This widget uses a simple class called SiteSearch to connect to the database, perform the search query, and return the results as a JSON string. This class has one private field _conString, the connection string to the MSSQL database, and accepts a string argument containing the connection string:

private string _conString;

public SiteSearch(string connectionString)
{
    _conString = connectionString;
}

As you can see, the constructor is extremely simple. The only operation performed is assigning _conString the value of the connectionString argument. As you've probably already guessed, the class's sole method, Search(), is the meat and potatoes of the class.

The Search() method accepts one argument: the search string. This search string is used in a String.Format() method to format the search query:

public string Search (string searchString)
{
    string query = String.Format("SELECT TOP 10 id, title FROM BlogPosts
        WHERE posting LIKE '{0}%' OR title LIKE '{0}%' ORDER BY date DESC",
        searchString);

The variable query now holds the completed query string used later in the method.

The JSON C# library provides the ability to build JSON strings dynamically. Two classes are used to create these strings: JSONArray and JSONObject. As you might have guessed, JSONArray creates an array, whereas JSONObject creates an object. Referring back to the returned data structure, remember the client code expects an array of objects. Therefore, the next step is to instantiate a JSONArray object:

Nii.JSON.JSONArray jsa = new Nii.JSON.JSONArray();

For the moment, this is all the JSON code used, and you won't see it again until further in the database operation.

Next, create a database connection and a SqlCommand:

using (SqlConnection conn = new SqlConnection(_conString))
{
    SqlCommand command = new SqlCommand(query, conn);
    conn.Open();

This code creates the database connection using the connection string stored in _conString. A new SqlCommand is then created using the search query and database connection. This essentially prepares the query to run against the database, which the next few lines illustrate:

using (SqlDataReader reader = command.ExecuteReader())
{


}

This code creates a SqlDataReader returned by the ExecuteReader() method. This data reader provides the ability to read the result from the executed database query. Before any data is retrieved from the data reader, you should first check if the result contains any rows. You can do this using the HasRows property:

using (SqlDataReader dataReader = command.ExecuteReader())
{
    try {
        if (reader.HasRows)
    {
            int i = 0;
            while (reader.Read())
            {

            }
        }
    catch {}
}

This code uses the HasRows property to determine if the data reader contains any rows. If so, the processing of the contained data can begin.

The while loop is used to read the result set through the Read() method of the SqlDataReader class. The Read() method returns a Boolean value; when the end of the result set is reached, Read() returns false and the loop exits. Inside the loop, you create a JSONObject object and populate it with its properties and their values with the put() method. The put() method of the JSONObject class accepts two arguments: a string containing the property name and that property's value. To access the database column's value, pass the column name as the index to dataReader:

using (SqlDataReader dataReader = command.ExecuteReader())
{
    try {
        if (reader.HasRows)
        {

            int i = 0;
            while (reader.Read())
            {
                JSONObject jso = new JSONObject();
                jso.put("title",reader["title"]);
                jso.put("id",reader["id"]);

                jsa.put(i++,jso);
            }
        }
    catch {}
}

When the JSONObject is populated, you add it to the JSONArray object created earlier. The JSONArray class exposes a put() method as well, but the arguments are a bit different. The first is the index of the existing element of the array, while the second is the JSONObject object.

When the loop exits, the JSONArray is completed and ready for returning. But what if the query returned no results? The client-side code handles this functionality. In the event that no rows are found from the query, an empty array is returned. The client-side code will check the length of the result set, and perform the necessary operation.

At this point, the JSON string construction is complete. To retrieve the string representation of the JSONArray, use the ToString() method:

return jsa.ToString();

This last line of the Search() method returns the JSON string that can be written into the page.

Building the Search Page

With the working class completed, all that remains on the server-side is the search page. This page will accept an argument in the query string called search, which contains the search term. An example query string could look like the following:

http://yoursite.com/search.aspx?search=ajax

To provide this functionality, check for the existence of this parameter by using the Response.QueryString collection:

Response.CacheControl = "no-cache";
Response.ContentType = "text/plain; charset=utf-8";

if (Request.QueryString["search"] != null)
{

}

The first two lines should be familiar to you by now. The first line sets the CacheControl header to no-cache so that the browser will not cache the data, while the second sets the MIME ContentType to text/plain with UTF-8 encoding.

After the headers are set, the existence of the search parameter in the query string is checked. Inside the if code block, a SiteSearch object is instantiated:

if (Request.QueryString["search"] != null)
{
    string searchTerm = Request.QueryString["search"];
    string conStr = "uid=sa;pwd=pass;data source=localhost;initial
        catalog=BlogPosts";

    SiteSearch siteSearch = new SiteSearch(con);
}

The first new line creates the searchTerm variable, a string storing the value of the search parameter in the query string, followed by the database connection string.

Connecting to an MSSQL database in C# is quite different from connecting to a MySQL database with PHP, demonstrated in Chapter 7. In PHP, the user name, password, and database host name were passed to the mysql_connect() function. In C# (and other .NET languages, for that matter), this information is contained in what is called a connection string. As seen in the previous example, this connection string contains name/value pairs separated by semicolons. The names in the preceding sample mean the following:

• uid: The user name for the particular database. In the previous example, sa is a built-in user meaning Super-Admin.

• pwd: The password to connect to the database.

• data source: The host name or IP address of the database server.

• initial catalog: The database name your queries are run against.

Of course, the information in the connection string should reflect the login information of your own MSSQL database; therefore, feel free to change the information wherever needed. Changing this information requires you to recompile the web application; however, a widget should be ready to use out of the box (or ZIP file), so a different approach is desirable.

The web.config file of an ASP.NET application contains configuration settings available for accessing within the application. This file's purpose is twofold:

1. It provides a central location for all application settings.

2. It provides a secure means of storing settings that contain sensitive data (such as database information), as the web server will not serve .config files.

Inside the web.config file, you'll notice that it is nothing more than an XML file with the document root being <configuration/>. To add your own custom settings, add an element called <appSettings/> as the first child of <configuration/>:

<configuration>
    <appSettings>


    </appSettings>

To add a setting, use the <add/> element, which has key and value attributes:

<appSettings>
    <add key="connectionStr" value="uid=sa;pwd=pass;data source=localhost;
            initial catalog=BlogPosts"/>
</appSettings>

With the connection string now a part of the application settings, the if block can be changed to the following:

if (Request.QueryString["search"] != null)
{
    string searchTerm = Request.QueryString["search"];
    string conStr = ConfigurationSettings.AppSettings["connectionStr"];

    SiteSearch siteSearch = new SiteSearch(conStr);
}

This code is slightly different from the code earlier. Instead of hard coding the database information, the information is pulled from the application settings with the ConfigurationSettings.AppSettings collection. Pass the value from the key attribute in the web.config file to the AppSettings collection, and the value is retrieved.

With the SiteSearch object instantiated, the final steps inside the if block is to perform the search and output the results to the page:

if (Request.QueryString["search"] != null)
{
    string searchTerm = Request.QueryString["search"];
    string conStr = ConfigurationSettings.AppSettings["connectionStr"];

    SiteSearch siteSearch = new SiteSearch(conStr);

    string json = siteSearch.Search(searchTerm);
    Response.Write(json);
}

The new lines in this code call the Search() method to perform the search. The resulting JSON string is returned and stored in the json variable, which is then written to the page via the Response.Write() method.

With the .aspx file complete, the final code looks like this:

Response.CacheControl = "no-cache";
Response.ContentType = "text/plain; charset=utf-8";

if (Request.QueryString["search"] != null)
{
    string searchTerm = Request.QueryString["search"];
    string conStr = ConfigurationSettings.AppSettings["connectionStr"];

    SiteSearch siteSearch = new SiteSearch(conStr);

    string json = siteSearch.Search(searchTerm);
    Response.Write(json);
}

The User Interface

The first step in any client-side component is to build the user interface with HTML. For this widget, you will use four elements contained within a <div/> element:

<div class=" ajaxSiteSearchContainer">
    <form class=" ajaxSiteSearchForm">
        <input class=" ajaxSiteSearchTextBox" />
        <input type=" submit" value=" Go" class=" ajaxSiteSearchButton" />
    </form>
    <div class=" ajaxSiteSearchResultPane">
        <a class=" ajaxSiteSearchLink" href=" http://yoursite.com">Result Text</a>
    </div>
</div>

Every element contains a class attribute. This ensures that the widget is easily customizable with CSS, and you can tailor it to fit in almost every web site.

Of course, you will not add this HTML directly into the HTML code of your web site; JavaScript dynamically creates the HTML and appends it to the desired HTML element.

The AjaxSiteSearch Class

The AjaxSiteSearch class encapsulates everything needed to display the user interface, make requests to the server, and display the server's response, aside from the CSS information and other dependencies. The class's constructor accepts one argument, an HTML element to append the search user interface:

function AjaxSiteSearch(oElement) {

}

The first step is to write the HTML elements that make up the user interface. This is done, naturally, with DOM methods:

var oThis = this;
this.result = null;

this.widgetContainer = document.createElement("div");
this.form = document.createElement("form");
this.textBox = document.createElement("input");
this.submitButton = document.createElement("input");
this.resultPane = document.createElement("div");

this.widgetContainer.className = "ajaxSiteSearchContainer";
this.form.className = "ajaxSiteSearchForm";
this.textBox.className = "ajaxSiteSearchTextBox";
this.submitButton.className = "ajaxSiteSearchButton";
this.resultPane.className = "ajaxSiteSearchResultPane";

this.submitButton.type = "submit";
this.submitButton.value = "Go";

The first line of this code creates the oThis variable, a pointer to the object. This comes in handy later in the constructor. The following lines create the needed HTML elements and assign their class names, and the Submit button's type and value attributes are set to submit and Go, respectively.

When the user clicks the Submit button or presses the Enter key when the form has focus, the form's onsubmit event fires. The following event handler will start the search process:

this.form.onsubmit = function () {
    oThis.clearResults();

    return false;
};

This is where the object pointer's use is required. Inside the onsubmit event handler, the scope changes; so, this references the <form/> element instead of the AjaxSiteSearch object. Because the event handler makes calls to the AjaxSiteSearch object, an external variable referencing the object is required, and that is what oThis does.

The first line of the onsubmit event handler calls the object's clearResults() method. This method, covered later, removes any links in the results list from a prior search. This ensures that only the results from the current search request are displayed. In the last line, the value of false is returned. This overrides the form's default behavior, which is to submit the form.

Also during the onsubmit event, the form is validated. If the text box does not contain any text, the user is notified that no text is entered:

this.form.onsubmit = function () {
    oThis.clearResults();

    if (oThis.textBox.value != "") {
        oThis.search();
    } else {
        alert("Please enter a search term");
    }

    return false;
};

If text is entered, however, the object's search() method is called. This method, also covered shortly, is responsible for retrieving the search term and making the XMLHttp request to the server.

With the elements created and the onsubmit event handler written, all that remains in the constructor is appending the elements to the document:

this.form.appendChild(this.textBox);
this.form.appendChild(this.submitButton);
this.widgetContainer.appendChild(this.form);
this.widgetContainer.appendChild(this.resultPane);

var oToAppend = (oElement)?oElement:document.body;
oToAppend.appendChild(this.widgetContainer);

Because this widget appends itself to the given HTML element, it is a good idea to create an AjaxSiteSearch object during the page's onload event. Otherwise, the desired destination element could not exist, thus throwing an error.

AjaxSiteSearch.prototype.clearResults = function () {
    while (this.resultPane.hasChildNodes()) {
        this.resultPane.removeChild(this.resultPane.firstChild);
    }
};

AjaxSiteSearch.prototype.search = function () {
    var oThis = this;
    var sUrl = encodeURI("search.aspx?search=" + this.textBox.value);

    var oReq = zXmlHttp.createRequest();
    oReq.onreadystatechange = function () {
        if (oReq.readyState == 4) {
            if (oReq.status == 200) {
                oThis.handleResponse(oReq.responseText);
            }
        }
    };

    oReq.open("GET", sUrl, true);
    oReq.send();
};

AjaxSiteSearch.prototype.handleResponse = function (sJson) {
    this.result = JSON.parse(sJson);
};

AjaxSiteSearch.prototype.handleResponse = function (sJson) {
    this.result = JSON.parse(sJson);

    if (this.result.length > 0) {
        //Results go here
    } else {
        alert("Your search returned no results.");
    }
};

AjaxSiteSearch.prototype.handleResponse = function (sJson) {
    this.result = JSON.parse(sJson);

    if (this.result.length > 0) {
        var oFragment = document.createDocumentFragment();
        for (var i = 0; i < this.result.length; i++) {
            var linkResult = document.createElement("a");
            linkResult.href = "http://yoursite.com/?postid=" + this.result[i].id;
            linkResult.innerHTML = this.result[i].title;
            linkResult.className = "ajaxSiteSearchLink";

            oFragment.appendChild(linkResult);
        }

        this.resultPane.appendChild(oFragment);
    } else {
        alert("Your search returned no results.");
    }
};