366CHAPTER 10

Type-ahead suggest

10.2The server-side framework: C#

The type-ahead suggest that we are about to tackle involves three parts: the server, the database, and the client. The database could actually be an XML file, but the same basic concept can be applied.

10.2.1The server and the database

The server and the database code can be handled at the same time since we are just going to connect to a database of information. In this example, we will use Microsoft’s Northwind database and obtain the data from the Products table, but you can make this work for any database you want.

The idea behind the XMLHttpRequest object is to be able to send a request from the client to the server and receive back a formatted data structure. In this case, we create a text document dynamically with the data that we obtained from a database query. The text document will hold JavaScript code to create an array containing the data. You can see the steps for building the JavaScript array in listing 10.1.

Listing 10.1 typeAheadData.aspx.cs

private void Page_Load(object sender,		b	Initialize code
System.EventArgs e)			on page load
{

Response.ContentType = "text/html"; c string strQuery =

Request.Form.Get("q").ToString(); string strAny = "";

if (Request.Form.Get("where").ToLower()

	== "true")		e	Declare a
{
	strAny =	"%";		string
}

string strSql = "Select top 15 " + "ProductName, " +

"ProductId FROM Products " + "WHERE ProductName like '" + strAny + strQuery + "%"

"' ORDER BY ProductName"; DataTable dtQuestions = FillDataTable(

strSql); g System.Text.StringBuilder strJSArr =

new System.Text.StringBuilder( "arrOptions = new Array(");

Set content type

d Request form element

f Build SQL

statement

Initialize database query

h Build JavaScript array

The server-side framework: C#	367

int iCount = 0;

foreach (DataRow row in dtQuestions.Rows)

{

if (iCount > 0)

{

strJSArr.Append(",");

}

strJSArr.Append("["); strJSArr.Append("\"" +

row["ProductName"].ToString()

+"\","); strJSArr.Append("\"" +

row["Productid"].ToString()

+"\"");

strJSArr.Append("]");

iCount++;

}

strJSArr.Append(");");

Response.Write(strJSArr.ToString());

}

public static DataTable 1) Execute query FillDataTable(string sqlQuery)

{

string strConn = "Initial Catalog = "+ "Northwind;Data Source=127.0.0.1; " "Integrated Security=true;";

SqlConnection conn = new SqlConnection(strConn); SqlDataAdapter cmd1 = new

SqlDataAdapter(); cmd1.SelectCommand = new

SqlCommand(sqlQuery,conn); DataSet dataSet1 = new DataSet(); cmd1.Fill(dataSet1); cmd1.Dispose();

conn.Close();

return dataSet1.Tables[0];

}

i Loopresultsthrough

j Write string to page

The code in listing 10.1 lets us receive the values from the client and process the data into a string forming a JavaScript array. This newly created array is returned to the client where it will be processed. We need to initialize this on the page load bof the document. The first step when we return the string is to make sure that the content type of the page is set to text/html c.

368CHAPTER 10

Type-ahead suggest

The client-side code will post the values to this page via the XMLHttpRequest object. Therefore, we need to request the form element q for the text we are supposed to query d. Unlike most type-ahead suggests, we’ll allow users to find results in the middle of a word, so we declare a string eto handle this situation. The client script passes the boolean string within the form element named where. If it is true, we add a % to the start of our search term to enable searching from anywhere in the string.

We can now build the SQL statement fto obtain the values from the database based on user input. To minimize the impact on the user, we limit the search by only allowing 15 records to be returned. We can then initialize gour procedure to query the database 1)and return a data table with the available search results.

Once the results have been returned from the database, we can start building the JavaScript array h. We then loop through our record set i, building the two-dimensional array containing the product name and ID. When the looping is complete, we write our string to the page j so that our JavaScript statement can use it.

If you are using a server-side language that has a code-behind page and an HTML page, you need to remove all the extra tags from the HTML page. With our C# page, the only tag that should be on the ASPX page is the following:

<%@ Page Language="c#" AutoEventWireup="false" Codebehind="TypeAheadXML.aspx.cs" Inherits="Chapter10CS.TypeAheadXML"%>

If we did not remove the extra HTML tags that are normally on the ASPX page by default, we would not have a valid text (with JavaScript) file, meaning that our JavaScript DOM methods would not be able to use the data. To ensure that the data being transferred back to our client is correct, we need to run a quick test.

10.2.2Testing the server-side code

It is important to test the server-side code when you are working with Ajax since JavaScript is known for its problems, the causes of which are hard to find. Improvements have been made with Mozilla’s JavaScript console, but it is always a good idea to make sure that the server is performing properly to eliminate the chances of error.

We have two options for testing the server-side code. Since we will be using the HTTP POST method with our XMLHttpRequest object, either we have to create a simple form with two textboxes and submit it to the server-side page, or we can comment out the lines that check for the form submission and hard-code values in its place. As you can see in the partial code listing in listing 10.2, the form request statements are commented out and replaced with hard-coded values.