Scriptico

Paging and filtered search. (WDMF + Parameterized Queries).

This article demonstrates an approach recommended by Mark Piller for solving an issue that was posted in Midnight Coders Developers Forum: http://www.themidnightcoders.com/forum/Default.aspx?g=posts&t=4050.

The developer that posted this issue wanted to implement a paging feature and make a filtered search on the client side. This is a sample I developed to solve this issue, that I hope helps others. All source codes you will find at the end of this article.

Environment. The server side includes .NET framework v2.0, WebORB for .NET (v.4.3.0.2), and MSSQL 2008 R2. The client side is a flex application. The sample project is written in Visual Studio 2010, and Flex Builder 4.

Overview. WebORB Data Management for Flex (WDMF) is one of WebORB’s features that provides an extremely powerful framework enabling developers to create data-driven Flex applications. Although this feature is documented well and developers can find a bunch of information in the Midnight Coder Developers Forum, the issue described in the post above had to do with handling large amounts of records, and filtering implementation on the server side.

The first part of this issue that needed to be solved was how to return large datasets from the database without locking WebORB. Lets say there are more than 100K entries. In the WebORB documentation we will find a description of the find options client-side feature of WDMF. The developer trying to solve this issue implemented the PageSize option that the generated test-drive code already contained, and the following code snippet shows it:


//CustomerView.mxml

[Bindable]
public function set pageSize(value:int):void
{
      _pageSize = value;
      var findOptions:Object = new Object();
      findOptions.PageSize = _pageSize;
      _searchResult = ActiveRecords.Customer.findAll(findOptions);
}

This is how this approach works. The PageSize argument is an untyped object on the ActionScript side or a System.Collections.Hashtable in .NET, and it enables paging for the returned data and specifies the size of the pages returned by WebORB. The initial request returns ‘PageSize’ number of records starting from the first row. The returned collection automatically supports paging. It can be used as a data provider for data-bound UI components. For example, if the UI component is a DataGrid, it will scale the scrolling bar as if the data model contains all the returned rows. However, after the initial request, the data model contains only the first page of data. As the user scrolls through the UI component, additional pages of data are automatically retrieved from the server. The data is loaded intelligently – specifically for the pages currently visible in the ‘view port’ of the component.

Now lets look on the second part of the issue. I will use the following database schema:

...
CREATE TABLE [dbo].[customer](
	[id] [int] NOT NULL,
	[first_name] [varchar](50) NOT NULL,
	[last_name] [varchar](50) NOT NULL,
	[email] [varchar](50) NOT NULL,
 CONSTRAINT [PK_customer] 
...

Lets say that a user of my example needs to find a customer by the last name, and (or) email. Despite the fact that the first part of this issue seemed to be solved simple enough, it is absolutely not applicable from the architectural point of view. Let’s find out why. WDMF contains the findBySQL function. We must add a filter checkbox and an input to the user interface, and rewrite the pageSize function in the CustomerView.mxml as it is shown below:

[Bindable]
public function set pageSize(value:int):void
	{
		_pageSize = value;
		var findOptions:Object = new Object();
		findOptions.PageSize = _pageSize;
			
		var sqlString:String = "SELECT * FROM customer WHERE email LIKE "+ lastNameFilterInput.text+" AND last_name LIKE "+ lastNameFilterInput.text;
				
		_searchResult = ActiveRecords.Customer.findBySql(sqlString, findOptions);			
	}

Although this code will work and solve the issue with filtered search (you may try it), the code above is absolutely unacceptable for security reasons. Passing any data from the client directly is unwise. The recommended way to do this is:

On the client and server side create a class with a name QueryParameter to wrap passed parameters from the client. Actually, use an instance of AS Object class like we do with find options in WebORB. The QueryParameter class shown below makes this sample more transparent.

//QueryParameter.as
package com.test
{
	[RemoteClass(alias="com.test.QueryParameter")]
	
	public class QueryParameter
	{
		private var _parameterName : String;
		private var _value : String;
		
		public function QueryParameter(parameterName:String, value : String )
		{
			this._parameterName = parameterName;
			this._value = value;
		}
//set
//get
//QueryParameter.cs
namespace com.test
{
    public class QueryParameter
    {
        public String ParameterName { set; get; }
        public String Value { set; get; }
    }
}

Also, on the client side in the CustomerDataMapper.cs I created the following function:

public function findByParameterizedQuery(sqlString:String, queryParameters:Array, ... args):ActiveCollection
			  {
				  var remoteObject:RemoteObject = createRemoteObject();
				  var responder:Responder = extractResponder(args);
				  var options:Object = extractOptions(args);
				  return prepareCollection(new DatabaseAsyncToken(remoteObject.findByParameterizedQuery(sqlString, queryParameters, options ),responder),null,options);
			  }

This method returns an instance of the ActiveCollection class with all Customer records belonging to a specified client range. Note that I didn’t touch _CustomerDataMapper class. If you make changes in the _CustomerDataMapper class, and then regenerate the code through WebORB console, you will loose all changes.

On the server side CustomerDataMapper class I added the following:

...
using System.Data.SqlClient;
...

public QueryResult findByParameterizedQuery(String sqlQuery, QueryParameter[] parameters, Hashtable options)
        {
            String queryId = Guid.NewGuid().ToString();

            QueryOptions queryOptions = new QueryOptions(options, this);

            SqlCommand command = new SqlCommand(sqlQuery);

            if (queryOptions.IsPaged || queryOptions.IsMonitored)
                registerCollection(sqlQuery, queryId, queryOptions);


            foreach (QueryParameter currentParameter in parameters)
            {
                command.Parameters.Add(currentParameter.ParameterName, System.Data.SqlDbType.VarChar).Value = currentParameter.Value;
            }
            List domainObjectList = null;

            using (DatabaseConnectionMonitor connectionMonitor = new DatabaseConnectionMonitor(Database))
            {
                command.Connection = Database.Connection;   
                domainObjectList = fill(command, queryOptions.Offset, queryOptions.Limit);
                
                loadRelations(domainObjectList, queryOptions);

                return new QueryResult(queryId, queryOptions.IsMonitored, domainObjectList);
            }
        }

Now, lets look close in the method above. I read all WebORB find options; initialized a new instance of the SqlCommand class with the text of the query; checked and processed find options; and added all passed parameters from the client to the instance of the SqlCommand class as parameterized queries. Then I set up a database connection to the command instance; ran it; loaded relations if necessary, and returned to the client result.

With this approach you avoid the possibility of encountering an SQL injection, while still using all of WebORB’s advantages such as find options. That is it, problem solved!

I really appreciate your comments!

Resources

0. Source code

1. WebORB for .NET
2. WebORB Developer Den (NET)
3. Using Parameterized Queries in ASP.Net
4. Creating a Parameterized Query in ADO.NET

Category: Examples, WebORB (.NET)

Tagged: ,

Leave a Reply

ERROR: si-captcha.php plugin: GD image support not detected in PHP!

Contact your web host and ask them to enable GD image support for PHP.

ERROR: si-captcha.php plugin: imagepng function not detected in PHP!

Contact your web host and ask them to enable imagepng for PHP.