Eric Wise

I received a lot of feedback from yesterday's post and some people were a bit concerned by my sample code since the querystring being built only accepted matches on = or LIKE.  "How do we do a NOT?  What about aggregates".  That, my friends, is where today's lesson picks up.

Easy Assets .NET in its default source install exposes queries on = and LIKE because they are the most common and as far as the functionality of the program out of the box is concerned, the only type of query used.  Now, when it comes to reporting and extensions I know that developers will want to write their own queries particular to their business needs so here is how to go about it.  In the following example, we are going to build a query to return a count of employees grouped by last name.  Please reference yesterday's post for information on the DAO class and the default query residing there.

First, it is important to note that the domain manager object, the central I/O handler for the program, expects a query object of type EmployeeQuery.  Yet when we get to the DAO class we don't want to execute the ListSummary(ByVal query As EmployeeQuery)  method, we instead want to execute a count by last name query.  This is where the magic of overloading and inheritence come into play.

First, let's create an EmployeeCountByLastNameQuery.  Because the domain manager expects a type employee query we will inherit the employee query class to satisfy it.  Here's the resulting code:

    1 Public Class EmployeeQuery
    2     Inherits DomainQuery
    3  
    4     Private _Employee As Employee 
    5  
    6     Public Property Employee() As Employee 
    7         Get
    8             Return _Employee 
    9         End Get
   10         Set(ByVal Value As Employee)
   11             _Employee = Value
   12         End Set
   13     End Property
   14  
   15     Public Overrides ReadOnly Property QueryType() As String
   16         Get
   17             Return "EasyAssets.DAC.EmployeeQuery"
   18         End Get
   19     End Property
   20 End Class
   21  
   22 Public Class EmployeeCountByLastNameQuery
   23     Inherits EmployeeQuery
   24 End Class

Inside the domain manager, the QueryType property is checked to find the proper DAO Class.  Inside each DAO is a property called "SupportsQueryType" which is populated in the constructor as follows:

    1     Public Sub New()
    2         SupportsDeleteType = "EasyAssets.DAC.Employee"
    3         SupportsLoadType = "EasyAssets.DAC.Employee"
    4         SupportsSaveType = "EasyAssets.DAC.Employee"
    5         SupportsQueryType = "EasyAssets.DAC.EmployeeQuery"
    6     End Sub

The domain manager checks to make sure these types match up with the types passed into its Load, Delete, Save, Getlist, and ListSummary functions.  This also has the effect of if you commented out the SupportsDeleteType in the DAO then the domain manager wouldn't allow calls to the delete method of the object effectively allowing you to "lock down" that functionality from domain manager callers.

Now that we have created our count query class and the query type is set to EasyAssets.DAC.EmployeeQuery all we need to do is overload our ListSummary method as follows:

    Public Shadows Function GetSummaryList(ByVal query As EmployeeCountByLastNameQuery) As DataTable
        Dim mySQL As New System.Text.StringBuilder
        mySQL.Append("SELECT COUNT(*) AS EmployeeCount, LastName FROM Employees ")
        mySQL.Append("Group By LastName ")
 
        If query.SortExpression.Length > 0 Then
            mySQL.AppendFormat("ORDER BY {0}", query.SortExpression)
        End If
 
        Return ExecuteDataset(mySQL.ToString()).Tables(0)
    End Function

Now all you have to do is call the following code and automagically you'll get the proper query results back:

        DataGrid1.DataSource = DomainManager.ListSummary(New EmployeeCountByLastNameQuery)
        DataGrid1.DataBind()

Being that there already is an EmployeeQuery and the SupportTypes are already set up out of the box for you to extend and create your own queries involves just creating the new query class and overloading the method.  Two step process!