Custom Commands

Behind every query there is the concept of a command similar to what the DBCommand object does for ADO.NET or a method call does for a web service. In fact, a query can almost be seen as a wrapper for a command. The Query Application Block is built to handle such commands in the most common way and in doing so caters for most data access requirements. All QAB commands implement the ICommand interface and there is a general one for each query type. However, there are always going to be situations that demand something slightly different or non-standard. For example, the QAB assumes that one query = one command but supposing you have a large BLOB to store away and want to buffer the writes? this requires multiple command executions to one query. Another example is when you have a complex XML data file where you want to search for data using search criteria from one element to find data in another element. The QAB has had to make certain assumptions about searching and finding data in XML files which in no way encompasses all possible cases. For example the QAB can only find data in an XML file based on Attributes and Elements of the configured data Element itself. So if the configured element is "Employee" say, then a list of employees can be returned based on any attribute or child Element of an <Employee> like <Employee Surname="Bloggs"/> or <Employee><Surname>Bloggs</Surname></Employee>. Supposing that all <Employee> elements are contained within <Company name="company name"> elements and you wish to perform a search along the lines of get me all the employees of company "X". This would not be possible with the standard QAB XML Query as the company "name" is not an attribute or child element of the configured data element "Employee".

For such situations we can use Custom Commands. To avoid confusion I need to explain that Custom Commands do not follow exactly the same concept as Custom Providers do in terms of the standard way that all Enterprise Library extensions are supported. You may have come across Custom Queries and Custom Parameters. These follow the Enterprise Library model for custom providers where you build a class based on an interface and provide parameters to your class through constructors and a standard CustomProviderData configuration node. Custom Commands, conversely, are handled entirely within the QAB and not by the Enterprise Library. They do not require configuring except for registering as a type to be linked to a Query. All other properties come from the Query itself.

There are three types of Custom Command depending on the Base class that you inherit from. Data Commands provide custom commands for Data Queries, File Commands provide custom commands for XML queries and Service Commands provide custom commands for WCF and Web services.

Custom Commands are built around the Plugin pattern which means that you create separate assemblies to hold your custom command types. Typically for each DAL project I would create a single and separate assembly and place all of my custom commands in it. The ratio of assemblies to custom command types is really a matter of choice but a single assembly does keep the number of assemblies loaded through reflection down to a minimum and thus improves performance at the expense of a little transparency.

It would be very unlikely that a developer would need to get at a custom command to instantiate it as this is done within the QAB for you as part of the normal function of a query. However a CustomCommandFactory exists for such a purpose:

[C#]
// create a CustomCommand with a configured name of "File Custom Command"
ICustomCommand customCommand = CustomCommandFactory.CreateCustomCommand("File Custom Command");


All Custom Commands, no matter what type, have access to a set of Parameters from configuration delivered to it via a Parameters property that yields a ParameterDictionary. All other properties are specific to the type of custom command, see below.

Creating a Data Command

A Data Command is any custom command that inherits from the DataCommand base class. This base class gives you a DAAB Database object, a SQL command name (stored procedure name, SQL String or table name) and a Command Type (StoredProcedure, Text or TableDirect). This information is provided for you by the QAB from configuration when the QAB instantiates the custom command object. What you need to do is to overide the ExecuteForRead() and ExecuteForWrite() methods. Note: it is not always the case that you need to override both. In the example of buffered writes for large BLOB objects only the ExecuteForWrite() method needs to be overridden as the default read action works fine for BLOB objects of any size.

Creating a File Command

A File Command is any custom command that inherits from the FileCommand base class. This base class gives you a full path name to a data file, currently only XML file queries are supported so this file would be an XML data file. This data file path name is provided for you by the QAB from configuration when the QAB instantiates the custom command object. What you need to do is to overide both the ExecuteForRead() and ExecuteForWrite() methods. Note: Unlike the base DataCommand class there is no default implementation for these methods.

Creating a Service Command

A Service Command is any custom command that inherits from the ServiceCommand base class. This base class gives you an Endpoint name, a Url Address, a Service name (Type) and a Method name. In addition to these supplied properties there are three utility properties; ServiceObject which supplies a reflected Service object, MethodInfo that supplied a reflected MethodInfo object and IsServiceSet which is a boolean that tells you if a service object has been instantiated or not. All these properties are provided for you by the QAB from configuration when the QAB instantiates the custom command object. What you need to do is to overide the ExecuteForRead() and ExecuteForWrite() methods. Note: The base ServiceCommand class provides a default implementation for these methods which simply invokes the service method with the supplied parameters so you may find that you do not need to override both methods.

Linking a Custom Command to a Query

This is done through configuration. Having registered your custom command through adding a custom command to configuration you can then link it to any query of the appropriate base type. That query will then use your custom command rather than the built-in default command. See the Configuration page for more details.

Sample Custom Data Command

The following DataCommand sample custom command implements buffered writing of large BLOBs to a relational database. In configuration we have added a ParameterSet with two parameters: Id to find the record in the Photos table and BitmapFile a byte[] array. Note how we create a class called BufferedBitmapCommand that inherits from DataCommand. Also we only need to override the ExecuteForWrite() method and all our parameters, command and database information is all there ready for us. Incidentally the Command property will have been configured with the text @"UPDATE Dbo.Photos SET PhotoImage.Write(@Bytes, null, 0) WHERE Id = @Id" and CommandType set to CommandType.Text also we are using a new feature of SQL Server 2005 and up, the UPDATE with .WRITE feature:

[C#]
public class BufferedBitmapCommand : DataCommand
{
	private const int CHUNK_SIZE = 8040; 

	/// <summary>
	/// Initializes a new instance of the <see cref="BufferedBitmapCommand "/> class.
	/// </summary>
	public BufferedBitmapCommand ()
	{ }

	/// <summary>
	/// Executes a write action.
	/// </summary>
	public override void ExecuteForWrite()
	{
		// Upload in multiples of 8040 for sql perf reasons. (i.e. 8040, 16080, 24120, 32160, 40200, 48240, 56280...)
		// Upload BLOB in max blocks of CHUNK_SIZE to DB using Id parameter as the key.
		DbCommand dbCommand = Database.GetSqlStringCommand(Command);
		Database.AddInParameter(dbCommand, "@Id", DbType.Int32, Parameters["Id"].Value);
		Database.AddInParameter(dbCommand, "@Bytes", DbType.Binary, null);

		using (MemoryStream stream = new MemoryStream((byte[])Parameters["BitmapFile"].Value))
		{
			using (BinaryReader reader = new BinaryReader(stream))
			{
				byte[] buffer = reader.ReadBytes(CHUNK_SIZE);
				while ( buffer.Length > 0 )
				{
					Database.SetParameterValue(dbCommand, "@Bytes", buffer);
					Database.ExecuteNonQuery(dbCommand);
					buffer = reader.ReadBytes(CHUNK_SIZE);
				}
			}
		}
	}
}

Note: the PhotoImage column in this example cannot start with a NULL value when using the above command so some mechanism needs to be in place to ensure that the first block of bytes is added to the column in the normal fashion.

Last edited May 25, 2010 at 9:04 PM by ewdev, version 17

Comments

No comments yet.