Using DiscoverParameters with the Ajax Search Page

by Ishai Hachlili 15. February 2008 19:20

This is a small upgrade to the DoSearch method in the data access layer.
Instead of using the FieldType property for each parameter, I'm using db.DiscoverParameters, and instead of adding new parameters I'm setting the values of the discovered parameters.

I didn't want to use a discovery but after starting to use the Enterprise Library's Data block, I decided to give it a try.
As you can see it's very easy to use, just one line of code, and the discovery results are supposed to be cached, so the performance hit should be too hard.

Here's the updated code:

/// <summary>
/// Performs a search
/// </summary>
/// <param name="SPName">the name of the Stored Procedure to execute</param>
/// <param name="DBName">the name of the DataBase where the stored procedure is (if not the default database)</param>
/// <param name="searchParams">The search parameters including page size</param>
/// <returns>a dataset of the search results</returns>
public DataSet DoSearch(string SPName, string DBName, IshaiHachlili.MyTakeOnDotNet.Entities.SearchParameters searchParams)
{
    Database db = DatabaseFactory.CreateDatabase(DBName);
    DbCommand cmd = db.GetStoredProcCommand(SPName);
    db.DiscoverParameters(cmd);
    
    //Add form input search parameters for this search query
    foreach (QueryParameter fld in searchParams.Parameters)
    {
        //db.AddInParameter(cmd, fld.FieldName, fld.FieldType, fld.FieldValue);
        cmd.Parameters['@' + fld.FieldName].Value = fld.FieldValue;
    }

    //Add common parameters (all search stored procedures should have this parameters and support paging and sorting functionality)
    cmd.Parameters["@PageIndex"].Value = searchParams.PageIndex;
    cmd.Parameters["@PageSize"].Value = searchParams.PageSize;
    cmd.Parameters["@SortColumn"].Value = searchParams.SortColumn;
    cmd.Parameters["@SortOrder"].Value = searchParams.SortOrder;

    DataSet ds = db.ExecuteDataSet(cmd);

    return ds;
}

Tags:

Enterprise Library

How To: Use connections not saved in web.config with the Data Application Block

by Ishai Hachlili 15. February 2008 18:18
While working on the search pages solution (read about it here) I realized I need to be able to save data connections in a database.

I’m using a database to manage everything that has to do with the application itself (membership, roles, search page info, etc…) but the actual data comes from other databases.

The Data Application Block supports using different connection as long as these connections are defined in the web.config file.
Because I didn’t want to make changes to the config file too often, and I also wanted users to be able to add new data connections without my help, having these connections in a database will be great.

After looking through the data block’s code, I realized I need to create a new configuration source.
I figured someone has already done this, and sure enough, a quick google search later I found out that such a solution exists right there in the enterprise library’s Quick Starts folder.

I quickly added the SqlConfiguration project to my solution and thought my problems were solved.
Unfortunately, it just didn’t work.
I also didn’t really want to save the entire connections section as on row in a table, that will make it harder to maintain. So I decided to create an implementation just for the connections section and inherit the rest for the SqlConfiguration.

The DataConnections table:
ConnectionId
ConnectionName
ConnectionString
ProviderName

you can see the columns are the same as the connection attributes in web config.


The Configuration Source:

namespace IshaiHachlili.RapidBackOffice.Configuration.ConnectionsSqlConfigurationSource
{
    public class ConnectionsSqlConfigurationSource : IConfigurationSource
    {
        private string defaultConnectionString = String.Empty;
        private const string GetConfig = "EntLib_GetConfig";
        private const string SetConfig = "EntLib_SetConfig";
        private const string RefreshSection = "EntLib_UpdateSectionDate";
        private const string RemoveSection = "EntLib_RemoveSection";

        public ConnectionsSqlConfigurationSource()
        {
            if (ConfigurationManager.ConnectionStrings.Count > 0)
            {
                defaultConnectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
            }
        }

        public System.Configuration.ConfigurationSection GetSection(string sectionName)
        {
            if (sectionName != "connectionStrings")
            {
                IConfigurationSource source = new SqlConfigurationSource(defaultConnectionString, GetConfig, SetConfig, RefreshSection, RemoveSection);
                return source.GetSection(sectionName);
            } 
            ConnectionStringsSection section = new ConnectionStringsSection();

            //System.Configuration.ConnectionStringsSection
            //Get the connections from the DL
            ConnectionsDL dl = new ConnectionsDL();
            IDataReader dr = dl.GetDataConnections();
            while (dr.Read())
            {
                string connectionName = dr["ConnectionName"].ToString();
                string connectionString = dr["ConnectionString"].ToString();
                string providerName = dr["ProviderName"].ToString();
                section.ConnectionStrings.Add(new ConnectionStringSettings(connectionName, connectionString, providerName));
            }

            return section;
        }
    }

    public class ConnectionsDL
    {
        public IDataReader GetDataConnections()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("GetDataConnections");
            IDataReader dr = db.ExecuteReader(cmd);
            return dr;
        }
    }
}


The GetDataConnections stored procedure simply returns all the connections in the DataConnections table.
Because I'm using the CreateDatabase() method with no DB name, the default database will be used. This is the management database for my application and the only connection that will be saved in the web.config.


Using the new configuration source in the data layer

protected Database getDatabase(string DBName)
{
    if (String.IsNullOrEmpty(DBName))
        return DatabaseFactory.CreateDatabase();

    IConfigurationSource source = new ConnectionsSqlConfigurationSource();
    DatabaseProviderFactory factory = new DatabaseProviderFactory(source);
    Database db = factory.Create(DBName);
    return db;
}


I added this method to a base class that all the classes in my data layer inherit from. now instead of using the DatabaseFactory.CreateDatabase() I use getDatabase(DBName).
As you can see, when there's no DBName, I still use the DatabaeFactory method, there's no reason to get the connections from the database when using the default connection that's saved in the web.config.
You can also add caching to avoid calling the database everytime you need to get the connections list.

Tags:

Asp.Net | Enterprise Library | Sql Server

My take on implementing searches with AJAX (Part 5)

by Ishai Hachlili 15. February 2008 16:39

So far in this series of posts I created a search page that uses AJAX to fetch the results and display them using a modified version of the WebFX ColumnList control.
The only thing left to do to meet the requirements is add support for paging and sorting.

We already have the paging and sorting parameters in the SearchParameters object, so we're sending these parameteres and returning onlt the first page of the results.
Now we need to add paging buttons and enable users to view the other pages.
We also want to cache pages and support prefetching of the next page.


The Pager Control
I wanted to make a reusable paging control that I can use whenever I need paging support.

The main method of the pager control is ShowPage, this method is called with the page index and if the page is not in the cache it will call the server side method to get that page.
Before calling the ShowPage method we need to set some parameters to let the pager know what we want to do.
-ServerSideMethod - the name of the method to call
-ServerMethodType - 1 for a web service method, 2 for PageMethods
-UsePredictiveFetching - when true, the next page will be fetched and cached
-ShowPageCallback - a reference to a method for showing the page (needed because fetching a page is done asynchronously and the pager can be used by different classes)
-SearchParameters - a SearchParameters object that will be sent to the server side method, the pager control will set the paging and sorting parameters every time the ShowPage method is called, but the search specific parameters won't change between pages.

The Pager JavaScript Class:
Pager.js.txt (4.93 kb)

In the updated Search function the Pager class is created the first time the function is called, the SearchParameters are set and the ShowFirstPage method is called (this method simply calls ShowPage with the value 1).
The pager variable is declared outside of the function because we need it later for showing other pages (and if we created it every time, there will be no point for the page caching).

The updated Search function:

var pager = null;

function Search() {
var fc = new FormController();
var searchParams = fc.CollectFormValues("searchFormContainer");
searchParams.PageSize=20;
searchParams.SortColumn="FirstName";
searchParams.SortOrder="ASC";
searchParams.QueryName="SearchClients";
searchParams.ColumnCollectionName="ClientsSearchResults";

if (pager==null) {
pager = new Pager();
pager.ServerMethodName = 'IshaiHachlili.MyTakeOnDotNet.WebServices.ContentsWS.DoSearch';
pager.ServerMethodType = 1;
pager.ShowPageCallBack = showGrid;
pager.UsePredictiveFetching = true;
}

pager.SearchParams = searchParams;

pager.ClearCachedPages();
pager.ShowFirstPage();
}

var searchResultsObject;
function showGrid(result) {
eval(result);

if (typeof(aColumns)!="undefined") {
if (TotalPages>0) {
pager.TotalPages=TotalPages;
}

var el =$get("searchResultsContainer"); //the element that will contain the grid
if (searchResultsObject == null) {
var searchResultsObject = new WebFXColumnList();
searchResultsObject.SortingCallback = pager.Sort.bind(pager);
searchResultsObject.create(el, aColumns);
} else {
searchResultsObject.clear();
}

searchResultsObject.addRows(aData);
} else {
alert('No results returned');
}
}


I renamed the onComplete method to reflect that it is now used to show the grid and not as the onComplete call back to the server side call.
You can also see that I moved the searchResultsObject declaration outside of the function. The reason for doing that is to keep the same positions and widths for columns (If the ColumnList is created everytime a page is shown any changes the user made to the widths and positions of columns will be reset).


The Pager class has two methods that can be used for next/previous paging, the ShowNextPage and ShowPreviousPage. It also has two methods for showing the first and last pages.
Adding buttons to control the paging is easy, you can also use the TotalPages value to create direct page links.


Sorting when there's more than one results page.
If there's only one results page the WebFX control can be sorted on the client side, but when there's more than one page we need to change the sorting paramters in SearchParameters and execute the search again.
If you take a look at the Pager class, you can see the Sort method and all it does is change the sorting parameters, clear the page cache and call the ShowFirstPage method.

The Sort method should be called from the grid, when a column header is clicked. Since the WebFX control already support sorting, all I had to do is add a call from the WebFXColumnList.sort function to the Pager's Sort method.
I also wanted to cancel the client side sorting if there's more than one page, no reason to sort on the client side and then replace the whole grid with the new sorted data from the server side.
I added a property to the ColumnList called SortingCallback, and in the showGrid function I set the value of this property to the pager's sort method.



Let's see what we have so far
1. A search page with support for paging, pre-fetcing the next page and caching previous pages.
2. A grid that supports column dragging, resizing, and formatting and links in the cell contents.
3. Easy maintainance. Adding a new search parameter requries only a simple update to the HTML form and the stored procedure, no need to touch the server side code at all.
4. Better performance. Because we only transfer the page data instead of refreshing the entire page (or loading the whole grid's HTML with an update panel), the size of the download is much smaller. Also, caching saves more requests for the same data and pre-fetching gives the user a better experience by having the next page ready faster.

and, best of all, with this implementation I just saved myself a lot of work. From now on, the DBA and designer can create search pages without me being involved. I actually made it even easier by moving the search function inside another class, and the only code that needs to be written is a call to a function with the values for some properties.


What's next? Some advanced features...
This framework will allow you to create pages easily without having to recompile you code but there are some other features that can be added to make it even cooler.

Saved Searches
Allowing users to saves their favorite and most used searches is a great feature that will be easy to implement.
By using the FormController's CollectFormValues to get a JSON of the search parameters and saving these parameters with some name, it should be easy to load the values back to the form later.
Most of the work will be the function that sets the values for the form elements. Apart from that we need to add some HTML for saving and loading searches, a text box with a button for saving and a drop down with a button for loading. Should'nt be too hard.

New search results alerts
Once we have the JSON saved on the server side we can execute this searches at any time, we just need to deserialize the JSON back to the SearchParameters object and execute a search.
It should be very easy to create a service that executes searches periodically and sends a notification when new results are found.

Grid Personalization
Different users might use the same search pages for different things and it might be easier for each user to get the results grid he way they want it. Since we already have a grid that supports setting the widths and positions of columns, we can save this properties for each user and load them back.
In my implementation of this feature I simply saved the column ids, positions and widths for each user and after the default grid is created I just ran over the columns and re-set this properties.

Dynamically created forms
By adding support for dynamically created forms, I can move the whole process of adding a new search page to the database layer. Add a stored procedure, add the data query, columns and form input controls in the database, and open a generic search page with the name of the form to load. Now you don't even need to upload an HTML file.
Of course, getting to a place where these forms look good enough and support all the layout requirements might be too hard to make it worth it.


Files:  
AjaxSearchSamplePart5.zip (935.24 kb)

This zip file contains all the code shown in this series and some additional code that was mentioned.

 

Tags: ,

AJAX | 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