This series of posts will show you my way of implementing search pages using .Net and AJAX.
In Parts
1 and
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)
Subscribe