My take on implementing searches with AJAX (Part 2)

by Ishai Hachlili 7. February 2008 12:36

This series of posts will show you my way of implementing search pages using .Net and AJAX.

In Part 1, I created client side functions that collect the form values regardless of the actual contents of the form (allowing adding and changing the form without having to update any code)
by the end of that post we got the search parameters to the server side and now we need to implement the search itself.

We already have a web service with a DoSearch method, now we need to create the business logic and data access layers.
I prefer creating separate projects for each layer but you can place these classes in the same project if you like.

Let's start with the BL layer.
I'm going to create a DoSearch method that accepts the same SearchParameters object we received from the client side in the web service.

Because I'm going to use the same methods to execute all searches in the application I need to know which procedure to use. The QueryName property of SearchParameters can be used for that.
You can simply send the name of the stored procedure if you're using one database and you're not worried about the sp name being known.
For these two reasons I decided to send a name that I will use to get the actual query details.

I'm keeping the query details for searches in my application in a table called DataQueries

Table DataQueries
QueryId
QueryName
DBName - the name of the database to use for this query
SPName - the name of the stored procedure to execute

In the actual application I also had some other properties used for permissions testing and application management, so this solution gives you a lot more flexibility

Because the same procedure will be executed many times (different search parameters, paging, or different users) I want to get the query parameters only once and cache them.

The business layer DoSearch method:

public DataSet DoSearch (IshaiHachlili.RapidBackOffice.Entities.SearchParameters searchParams)
{
//Get the query details from the cached queries
QueriesBL.QueryDetails qd = QueriesBL.GetQueryByName(searchParams.QueryName);
SearchesDL dl = new SearchesDL();
DataSet ds = dl.DoSearch(qd.SPName, qd.DBName, searchParams);
return ds;
}


QueryDetails represents a single row of the DataQueries table. The first time a search is run I get a dataset with all the queries, convert it to a Dictionary with the QueryName and QueryDetails class and save the dictionary into the cache. (The code for this will be included in the download with the last post, but it's pretty basic)


Not much happening in the BL layer right now, but we'll get back to it later, for now, let's skip to the DAL.

Executing the search
I'm using the Enterprise Library's Data Application block for data access, so all I have to do is create a Database object using the passed DBName and execute the stored procedure using the SPName and searchParameters.

public DataSet DoSearch(string SPName, string DBName, IshaiHachlili.RapidBackOffice.Entities.SearchParameters searchParams)
{
Database db = DatabaseFactory.CreateDatabase(DBName);
DbCommand cmd = db.GetStoredProcCommand(SPName);

//Add form input search parameters for this search query

foreach (QueryParameter fld in searchParams.Parameters)
{
db.AddInParameter(cmd, fld.FieldName, fld.FieldType, fld.FieldValue);
}

//Add common parameters (all search stored procedures should have this parameters and support paging and sorting functionality)
db.AddInParameter(cmd, "PageIndex", DbType.Int32, searchParams.PageIndex);
db.AddInParameter(cmd, "PageSize", DbType.Int32, searchParams.PageSize);
db.AddInParameter(cmd, "SortColumn", DbType.String, searchParams.SortColumn);
db.AddInParameter(cmd, "SortOrder", DbType.String, searchParams.SortOrder);

DataSet ds = db.ExecuteDataSet(cmd);

return ds;
}



Passing the DBName to CreateDatabase allows using different connections for each search, so you can use different databases and different servers.
Currently the Data Application block supports Sql Server and Oracle, but I've only tested this code with Sql Server.
Also, the connection name refers to the connections saved in Web.Config.

You can also see that I'm using the FieldType property for each QueryParameter when I'm adding parameters to the command.
There are ways to "discover" the expected data type but it was much easier to simply add a property to the HTML form elements.

Stored Procedures
All search stored procedures should accept the specific search parameters, and the paging and sorting parameters and return a table with the results and another table with the total row count (one row and one column)
We need the number of rows so we can display the pager in the client side.

The best way to do this is with dynamic sql inside the stored procedure.
I don't really like using dynamic sql anywhere, I think it complicates maintenance and could cause problems that you will only find out when running the actual code and not when compiling, but after trying other solutions with temporary tables and other methods, this was by far the most efficient.


Business Logic Layer - Proccessing the results
In order to support formatting and links in the search results grid, I had to save some properties for each column.
These properties include some styling properties (width, position, alignement), formatting properties, action properties and permissions.
Some of these should be processed on the server side. for example, if a user doesn't have the permission to see a column, I want to remove it from the results before sending it back to the client side instead of just hiding it in the browser.
I also want to send the column definitions back to the client side where I can do the rest of the processing when the grid is created.

The ColumnCollectionName property is used just for that, it's a reference to a collection of column properties saved in the database.
I could've saved this property in the DataQueries table, but separating the column collection and the query used for a search page allows using the same data query in different pages that require different result columns or formatting.

The business layer DoSearch method - updated to support column processing:

public DataSet DoSearch (IshaiHachlili.RapidBackOffice.Entities.SearchParameters searchParams)
{
//Get the query details from the cached queries
QueriesBL.QueryDetails qd = QueriesBL.GetQueryByName(searchParams.QueryName);

SearchesDL
dl = new SearchesDL();
DataSet ds = dl.DoSearch(qd.SPName, qd.DBName, searchParams);

DataSet
processedDS = UtilitiesBL.ProcessDataTable(ds.Tables[0], searchParams.ColumnCollectionName);
processedDS.Tables.Add("TotalRows");
processedDS.Tables["TotalRows"].Merge(ds.Tables[1], true, MissingSchemaAction.AddWithKey);

return
processedDS;
}



When ProcessDataTable is done with the server side processing, the DataTable holding all the column definitions is merged into the returned dataset.
A third table is added called TotalRows. this table will hold the total row count.


The next posts will explain the server side processing in more details, returning the resutls to the client side and displaying the grid.

Files:
AjaxSearchSamplePart2.zip (849.73 kb)

DataBasesPart2.zip (31.4 MB) (The AjaxSearchSQL and AdventureWorks databases)



Tags: , , , ,

AJAX | Asp.Net | Enterprise Library | Javascript | Sql Server

My take on implementing searches with AJAX (Part 1)

by Ishai Hachlili 4. February 2008 12:44

I can’t remember a project I worked on that didn’t have at least one search page but recently I built an application where half the pages were search pages and I wanted to make it easy to add new pages and maintain the existing pages. There were also some performance requirements and UI features that we needed to support.

The basic idea is to collect the form input values, send to the server using AJAX and create a grid with the results.
 
Requirements:
-Support all the input controls a search form could have (Text Box, Drop Down, Check Box, Radio Buttons, Hidden Fields)
-Display the results in a grid that supports sorting and column dragging
-Support paging the results
-Support caching result pages and pre-fetching of the next page into the cache 
-Make it simple to add new pages and maintain existing pages.
-Support formatting of the returned data as well as links and other actions on each row

The following code samples were written using VS2008, but should work on previous versions as well, the main difference is the properties that use shorthand here
Also, in this project i used Microsoft Asp.Net AJAX exentsions, the prototype library, a type validation script from SmartWebby and the WebFX column list. In the last post I'll add a file with all the related code.


Collecting the form values

I created a simple search page with a few input controls on it and a search button and added a click event handler for the button.
When the search button is clicked I need to go through all of the input and select elements and collect the values into some object that I can send to the server.

It’s very easy to loop through the elements and get the values, the real question was what’s the best way to send the values to the server.

Asp.Net AJAX extensions has a nice feature that allows generating a javascript class from a server side class. The nice thing about it is that after using this class in the client side you can send it back to the server side and it will be converted to the server side object automatically.
This is done with the span style="font-size: 10pt; color: #2b91af; font-family: 'Courier New'">GenerateScriptType declaration.

I wanted the class on the server side to be generic so that it can be used for all search forms and here’s what I came up with:

namespace IshaiHachlili.MyTakeOnDotNet.Entities
{
public class SearchParameters : QueryParameters
{
public int PageIndex { get; set; }
public int PageSize { get; set; }
public string SortColumn { get; set; }
public string SortOrder { get; set; }
}

public class QueryParameters
{
public string QueryName { get; set; }
public string ColumnCollectionName { get; set; }
public List<QueryParameter> Parameters { get; set; }
}

public class QueryParameter{
public string FieldName { get; set; }
public string FieldValue { get; set; }
public DbType FieldType { get; set; }
}
}


SearchParameters is the class I’ll use for queries that need support for paging and sorting
QueryParameters can be used for other queries I might need in the future that don't require paging and sorting
Each QueryParameter holds the name and value of an input element and also the field type (I'll discuss the field type when I get to the server side code in a future post).
The QueryName and ColumnCollectionName properties are used to let the server side know which stored procedure it needs to run with these query parameters and what to do with the returned columns.

I created a separate project for this class because I’m going to use it in all tiers of my application, I called these project Entities.

The next thing I need to do is create a web service and a web method to call when the search button is clicked.

namespace IshaiHachlili.MyTakeOnDotNet.WebServices
{
[WebService(Namespace = http://tempuri.org/)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]

[GenerateScriptType(typeof(IshaiHachlili.MyTakeOnDotNet.Entities.SearchParameters))]
[GenerateScriptType(typeof(IshaiHachlili.MyTakeOnDotNet.Entities.QueryParameters))]
[GenerateScriptType(typeof(IshaiHachlili.MyTakeOnDotNet.Entities.QueryParameter))]

[ScriptService]
public class ContentsWS : System.Web.Services.WebService
{
[WebMethod]
public string DoSearch(IshaiHachlili.MyTakeOnDotNet.Entities.SearchParameters searchParams)
{
//Call BLL method and pass the search parameters
SearchesBL bl = new SearchesBL();
DataSet ds = bl.DoSearch(searchParams);
//Convert the results of the BLL method to a JSON array using XSLT
//Return the serialized JSON array
string result = GetResultString(ds, searchParams.PageSize);
return result;
}
}
}


You can see I use GenerateScriptType for all three query parameter types, this will allow me to instantiate them in javascript.
My search method, DoSearch, accepts the SearchParameters type and I simply pass it to my business logic layer as is.
The business layer will return a dataset that I want to convert to a string and send back to the client side.

But first, let’s see how we collect the form values and send them to the server. 

function FormController() {}

FormController.prototype.CollectFormValues=function(formContainer, parameters) {
///<summary>Collects the form values using the formcontainer name and returns the entity</summary>
var formContainerEl=$G(formContainer);
if (parameters == null) parameters = new IshaiHachlili.MyTakeOnDotNet.Entities.SearchParameters();
for (var index=0; index<formContainerEl.getElementsByTagName("INPUT").length; index++) {
var el=formContainerEl.getElementsByTagName("INPUT")[index];
this.GetInputControlValue(el, parameters);
}
for (var index=0; index<formContainerEl.getElementsByTagName("TEXTAREA").length; index++) {
var el=formContainerEl.getElementsByTagName("TEXTAREA")[index];
this.GetInputControlValue(el, parameters);
}
for (var index=0; index<formContainerEl.getElementsByTagName("SELECT").length; index++) {
var el=formContainerEl.getElementsByTagName("SELECT")[index];
this.GetInputControlValue(el, parameters);
}
return parameters;
}

FormController.prototype.GetInputControlValue = function(el, formParameters) {
if (el.tagName == 'INPUT') {
var fieldName = this.GetFieldName(el.name);
var fieldType = el.getAttribute('fieldType');
var fieldValue = '';
switch (el.type) {
case "text":
fieldValue=this.ConvertValueToType(el.value,true);
this.addFormField(formParameters, fieldName, fieldType, fieldValue);
break;
case "hidden":
fieldValue=this.ConvertValueToType(el.value,false);
this.addFormField(formParameters, fieldName, fieldType, fieldValue);
break;
case "radio":
if (el.checked) {
fieldValue=el.value;
this.addFormField(formParameters, fieldName, fieldType, fieldValue);
}
break;
case "checkbox":
fieldValue=el.checked;
this.addFormField(formParameters, fieldName, fieldType, fieldValue);
break;
}
} else if (el.tagName == 'TEXTAREA') {
var fieldName = this.GetFieldName(el.name);
var fieldType = el.getAttribute('fieldType');
var fieldValue = el.value;
this.addFormField(formParameters, fieldName, fieldType, fieldValue);
} else if (el.tagName == 'SELECT') {
var fieldName = this.GetFieldName(el.name);
var fieldType = el.getAttribute('fieldType');
var fieldValue = this.getSelectedValues(el);
this.addFormField(formParameters, fieldName, fieldType, fieldValue);
}
}

FormController.prototype.addFormField = function(formParameters, fieldName, fieldType, fieldValue) {
var formField = new IshaiHachlili.MyTakeOnDotNet.Entities.QueryParameter();
formField.FieldName=fieldName;
formField.FieldValue=fieldValue;
formField.FieldType=fieldType;

if (!formParameters.Parameters) formParameters.Parameters=[];
formParameters.Parameters.push(formField);
}


FormController.prototype.GetFieldName = function(controlID) {
var firstPos=controlID.lastIndexOf('$')+1;
var lastPos=controlID.length-firstPos;
return controlID.substr(firstPos, lastPos);
}

FormController.prototype.ConvertValueToType = function(value, isText) {
if (isInteger(value)) {
if (isText) return value;
return this.ConvertToNum(value);
} else if(isDate(value)) {
return this.ConvertToDate(value);
} else {
return value;
}
}

FormController.prototype.ConvertToNum = function(value) {
return eval(value + '+0');
}

FormController.prototype.ConvertToDate = function(value) {
aDateParts=value.split('/');
var oDate = new Date(aDateParts[2],aDateParts[1]-1,aDateParts[0]);
oDate.setMinutes(oDate.getMinutes() - oDate.getTimezoneOffset());
return oDate;
}

FormController.prototype.getSelectedValues = function(select) {
var r = new Array();
for (var i = 0; i < select.options.length; i++)
if (select.options[i].selected)
r[r.length] = select.options[i].value;
return r.join(",");
}



Calling FormController.CollectFormValues with any HTML element that contains input controls will return the parameters object with the form values for these controls.
I use the container to enable multiple forms on the same page, without having to deal with the form tag.

Here’s the code for the search button on click event

function Search() {
var fc = new FormController();
var searchParams = fc.CollectFormValues();
searchParams.PageSize=20;
searchParams.SortColumn=”FirstName”;
searchParams.SortOrder=”ASC”;
searchParams.QueryName=”SearchClients”;
searchParams.ColumnCollectionName=”ClientsSearchResults”;
IshaiHachlili.MyTakeOnDotNet.WebServices.ContentsWS.DoSearch(searchParams, onComplete, onError, this);
}



If you put a break point in the web method, you should get the SearchParameters object with all the form's input control values as well as the paging, sorting and QueryName.

In the next posts I’ll show you what I did on the server side and how I displayed the grid itself as well as adding support for paging and some other cool features that can be built on top of this implementation.

AjaxSearchSamplePart1.zip (63.20 kb)

Tags: , ,

AJAX | Asp.Net | Javascript

About Me

Ishai Hachlili is a web and mobile application developer.

Currently working on Play The Hunt and The Next Line


Recent Tweets

Twitter October 23, 05:22
@BenThePCGuy a standard where that doesn't matter is better. One more reason to get the #Lumia920, wireless charging, no need for microUSB

Twitter October 23, 05:21
@ManMadeMoon where they dance around the issues and don't really talk about them

Twitter October 23, 05:20
@BenThePCGuy are you a @wpdev ?

Twitter October 23, 04:17
@JonahLupton But if it's black it's usually better

Twitter October 23, 02:58
@jongalloway next time ask your 5 year old how to spell

@EShy