My take on implementing searches with AJAX (Part 4)

by Ishai Hachlili 12. February 2008 13:40

In the previous posts in this series I collected the form values, executed the search and processed the search results columns on the server side.

Now it’s time to return the results to the client side and display the search results grid.

I’m going to use the WebFX columnlist for my grid. This is my favorite javascript grid control.
It allows column resizing and drag & drop reordering as well as client side sorting (which will be great for one page search results).
It doesn’t support any formatting or links in the grid, so I’ll have to make some upgrades to it.

If you look at the documentation for the WebFXColumnList, you can see it uses an array of the column names and an array of data rows where each row is an array of values (that should be in the same order as the column names).

JavaScript sample – showing a grid using WebFXColumnList

var aColumns = [
'Rank',
'Flavor',
'Color',
'Share'
];

var aData = [
['1','Vanilla','White','29%'],
['2','Chocolate','Brown','8.9%'],
['3','Butter pecan','Light brown','5.3%'],
['4','Strawberry','Pink','5.3%'],
['5','Neapolitan','Greenish brown','4.2%'],
['6','Chocolate chip','Brown','3.9%'],
['7','French vanilla','Yellowish white','3.8%'],
['8','Cookies and cream','Light brown','3.6%'],
['9','Vanilla fudge ripple','White', '2.6%'],
['10','Praline pecan','Brown','1.7%'],
['11','Cherry','Red','1.6%'],
['12','Chocolate almond','Brown','1.6%'],
['13','Coffee','Dark brown', '1.6%'],
['14','Rocky road','brown', '1.5%'],
['15','Chocolate marshmallow','Light brown','1.3%']
];

var el = document.getElementById('container');

var o = new WebFXColumnList();
o.create(el, aColumns);
o.addRows(aData);

Sample taken from http://webfx.eae.net/dhtml/collist/usage.html


Right now I have a DataSet, so I need to convert it to these arrays and the best way to do it is with xslt.
Because I want more than just column names returned to the client side, I’m going to use the Column Collection DataTable I created in the previous post for the aColumns array.

My XSLT will create the actual javascript vars, in the client side I'll simply evaluate the returned string and I'll have the variables ready to use

ConvertSearchResultsDS.xslt

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/" >

var aColumns = [<xsl:for-each select="*/Columns">
<xsl:sort select="Position" data-type="number" order="ascending"/>
{
<xsl:for-each select="./*">
<xsl:value-of select="name()"/>:'<xsl:value-of select="."/>'<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:for-each>
}<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:for-each>
];


var aData = [<xsl:for-each select="//Data">
[<xsl:for-each select="./*[name()=//Columns/Name]">
'<xsl:call-template name="escape-quotes">
<xsl:with-param name="text" select="normalize-space(text())"/>
</xsl:call-template>'<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:for-each>]<xsl:if test="position() &lt; last()">,</xsl:if>
</xsl:for-each>
];
</xsl:template>


<xsl:variable name="quote-char">
<xsl:text>'</xsl:text>
</xsl:variable>

<xsl:variable name="escaped-quote">
<xsl:text>\'</xsl:text>
</xsl:variable>


<xsl:template name="escape-quotes">
<xsl:param name="text"/>
<xsl:choose>
<xsl:when test="contains($text,$quote-char)">
<xsl:variable name="pre" select="substring-before($text,$quote-char)"/>
<xsl:variable name="post">
<xsl:call-template name="escape-quotes">
<xsl:with-param name="text" select="substring-after($text,concat($pre,$quote-char))"/>
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="concat($pre,$escaped-quote,$post)"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>




I'm going to do the transformation in the web service.
I called the method GetResultString and in the web method code from part 1 I already called it.

protected string GetResultString(DataSet ds, int pageSize)
{
string result = TransformXsl(ds, "~/XSLT/ConvertSearchResultsDS.xslt");
int rowCount = (int)ds.Tables["TotalRows"].Rows[0]["TotalRows"];
result += String.Format("var TotalPages = {0};", CalculateTotalPages(rowCount, pageSize));

return result;
}



TransformXsl is a simple function that takes the dataset and xslt and does the transformation using the XslCompiledTransform class.
CalculateTotalPages returns the number of total pages using the total row count and page size


Displaying the grid

The JavaScript Search function that calls the web method defined a function called onComplete for the callback.
This function will receive the results string, create the WebFXColumnList object, initialize it and add the rows.

function onComplete (result) {
eval(result);

if (typeof(aColumns)!="undefined") {
var el =$get("searchResultsContainer"); //the element that will contain the grid
var searchResultsObject = new WebFXColumnList();
searchResultsObject.create(el, aColumns);
searchResultsObject.addRows(aData);
} else {
alert('No results returned');
}
}


After evaluating the returned javascript code I should have the aData and aColumns variables and from that point I can continue with the create and addRows method calls to display the grid.

The string created by the xslt is not exactly what the WebFXColumnList expects, so I'll need to make some changes to the javascript.

The create method of the ColumnList expects a simple array with names, so I need to go through it and change all references to aColumns.
I replaced the aColumns[i] to aColumns[i].Header when creating the head table.

I also need to add support for hidden columns, I want the create method to skip these. I need to add support for the formatting in the addRows function, and make sure everything else works.

I'm not going to go into all of these changes, but an updated script will be included in the download.


Files:

AjaxSearchSamplePart4.zip (919.09 kb)

 

Tags: , ,

AJAX | Asp.Net | Javascript

My take on implementing searches with AJAX (Part 3)

by Ishai Hachlili 12. February 2008 09:44

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

In part 1 and part 2, I created client side functions for collecting form values and the server side methods for executing the search.

In this post I’ll go into more details on how ProcessDataTable works.

ProcessDataTable accepts two parameters, a data table and a column collection name.


The Column Collection

The column collection is a set of column definitions. This is where the properties for each column in the search results are saved.
There are a lot of properties for each column, from display to permissions. Instead of defining these properties in HTML (the way the ASP.Net grid is defined) I decided to save the definitions in the database.
Saving the properties in the database allows me to change them without touching the code, while the application is live.


There are two tables for saving the columns. The first simply saves the column collection name and Id

Table ColumnCollections
ColumnCollectionId
ColumnCollectionName

The Columns table includes the actual definitions for each column

Table Columns
ColumnId
ColumnCollectionId
Name – The name of the column in the search results this definition applies to.
Header – The header text for the column in the grid
Position – the position of the column in the grid
Type– the type of column (columns can be labels, links, etc…)
SortType – this property is used by WebFXColumnList for client side sorting
Width – the width of the grid column
Align – the alignment of the grid column
VirtualColumn – Virtual columns don’t have a column in the search results, the contents of these columns are created dynamically from other column values.
AllowedRoles – Column permissions, we will check if the current user is allowed to see each column
DeniedRoles
AllowedUsers
DeniedUsers
ActionExpression – This is used for links. The expression can also be a javascript call.
ActionFields – The names of fields in this column collection to be used in the expression (separated by commas)
ActionConditions – a javascript code segement that can be evaluated to true or false
ActionConditionFields the names of fields to be used in the condition
ShowWhenConditionIsFalse – if the action condition is false, you can still show the column value without the link
FormatExpression – a formatting expression
FormatLocation – where to do the formatting (client side or server side)
FormatType – the type of formatting to do (composite string formatting, regex, and other types)
FormatFields – the fields to use for formatting.


Here is an example for using these properties

Adding an edit button for each row:
Assuming we have an ItemId column and a Status column where 1=Editable and 2=Closed

Name = Edit
LinkType = ActionLink (a type we’ll use in the grid for javascript actions)
VirtualColumn = true (this column won’t have an actual column in the results)
ActionExpression = Edit({0})
ActionFields = ItemId
ActionConditions = {0}==1
ActionConditionFields = Status
ShowWhenConditionIsFalse = false (don’t show the Edit link when the value of Status is not 1)
FormatExpression = ‘Edit’
FormatLocation = ClientSide
FormatType=Composite

The action expression will get the value of item id for each row, so the actual code created for the link will be Edit(1), Edit (4), etc..
The ActionCondition will get the value of the status field, which can be 1 or 2, so the code will be 1==1 or 2==1
There is no real formatting to be done here, we just want to have the Edit text, so composite formatting with no fields will work here.


The ProcessDataTable method
The first step in this method is to get the column collection from the database.
Keep in mind that we will need to get the same column collection again every time a new search is done or another page is requested, so this information should be cached as well.


public static DataSet ProcessDataTable(DataTable Data, string columnCollectionName)
{
DataSet ds = new DataSet();//The dataset that will be returned

//Get the Columns Collection
DataSet columnProperties = ColumnsDL.GetColumns(columnCollectionName);

//Add virtual columns with empty values
foreach (DataRow dr in columnProperties.Tables[0].Rows)
{
bool isVirtual = (bool)dr["VirtualColumn"];
if (isVirtual)
{
DataColumn dc = Data.Columns.Add(dr["Name"].ToString(), typeof(String));
foreach (DataRow drData in Data.Rows)
{
drData[dc] = "NA";
}
}
}
Data.AcceptChanges();

//Remove unauthorizes columns
foreach (DataRow dr in columnProperties.Tables[0].Rows)
{
string columnName = dr["Name"].ToString();
bool userHasPermissions = UtilitiesBL.CheckUserPermissions(dr["AllowedRoles"].ToString(), dr["DeniedRoles"].ToString(), dr["AllowedUsers"].ToString(), dr["DeniedUsers"].ToString());
if (!userHasPermissions)
{
dr.Delete();
if (Data.Columns[columnName] != null)
{
Data.Columns.Remove(columnName);
}
}
}

//Server Side formatting
foreach (DataRow dr in columnProperties.Tables[0].Rows)
{
string columnName = dr["Name"].ToString();

if (!String.IsNullOrEmpty(dr["FormatExpression"].ToString()) && dr["FormatLocation"].ToString() == "1")
{
string formatExpression = dr["FormatExpression"].ToString();
string formatFields = dr["FormatFields"].ToString();
FormatTypes formatType = (FormatTypes)Convert.ToInt32(dr["FormatType"]);

foreach (DataRow dataRow in Data.Rows)
{
switch (formatType)
{
case FormatTypes.Composite:
dataRow[columnName] = CompositeFormatting(formatExpression, formatFields, dataRow, columnName);
break;

case FormatTypes.RegEx:
dataRow[columnName] = RegExFormatting(formatExpression, dataRow, columnName);
break;
}
}
}
}

//Add Data table to final dataset
ds.Tables.Add("Data");
ds.Tables["Data"].Merge(Data, true, MissingSchemaAction.AddWithKey);

//Add ColProps table to final data set
ds.Tables.Add("Columns");
ds.Tables["Columns"].Merge(columnProperties.Tables[0], true, MissingSchemaAction.AddWithKey);


return ds;
}



CheckUserPermissions is a simple method that takes the current user (using HttpContext.User.Identity.Name)and checks if that user has permissions or if that user is in a role (using HttpContext.Current.User.IsInRole) that’s allowed to see this column

The CompositeFormatting uses String.Format and the RegExFormatting method uses RegEx.Replace to perform the server side formatting.

The last step is to add the Data and Columns DataTables to the new DataSet.
We will need the Columns information in the client side when creating the grid so we can add the actions, conditions and do the client side formatting

The next post will deal in returning the dataset we just created to the client side and displaying the grid





Files:

AjaxSearchSamplePart3.zip (880.94 kb)

AjaxSearchSqlPart3.zip (140.25 kb)

Tags: ,

AJAX | Asp.Net

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

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