Eric Wise

Here's an example of what I love about the Domain Manager pattern I've developed in Easy Assets .NET as far as making things easily extendable for you developers out there.

Let's say that we have a table of Employees.  The table has the following fields: EmployeeID, FirstName, LastName, DepartmentID, and JobDescriptionID.  My domain manager object exposes two functions for getting lists of data from the database: GetList() and GetSummaryList().

GetList() returns a strongly typed collection of Employee Objects

GetSummaryList() returns a datatable

Each of these functions is exposed in the DAO class and takes a parameter of type EmployeeQuery.  EmployeeQuery exposes an employee object to optionally search on.  What this allows me to expose to you developers is the concept of searching by properties of an Employee object.  That is to say if you want to see a list of employees whose last name starts with 'W' you would create an EmployeeQuery with an Employee object inside it whose lastname property is set to 'W'.

    Public Shadows Function GetSummaryList(ByVal query As EmployeeQuery) As DataTable
        Dim mySQL As New System.Text.StringBuilder
        Dim params As New ArrayList
 
        mySQL.Append("SELECT * FROM Employees WHERE 1=1 ")
 
        If Not IsNothing(query.Employee) Then
 
            Dim p As SqlClient.SqlParameter
            If Not IsNothing(query.Employee.LastName) AndAlso query.Employee.LastName.Length > 0 Then
                mySQL.Append("AND LastName LIKE @LastName + '%' ")
                p = New SqlClient.SqlParameter("@LastName", query.MessageTopic.LastName)
                params.Add(p)
            End If
  
            If Not IsNothing(query.Employee.FirstName) AndAlso query.Employee.FirstName.Length > 0 Then
                mySQL.Append("AND FirstName LIKE @FirstName + '%' ")
                p = New SqlClient.SqlParameter("@FirstName", query.Employee.FirstName)
                params.Add(p)
            End If
 
            If Not IsNothing(query.Employee.DepartmentID) AndAlso query.Employee.DepartmentID > 0 Then
                mySQL.Append("AND DepartmentID = @DepartmentID ")
                p = New SqlClient.SqlParameter("@DepartmentID", query.Employee.DepartmentID)
                params.Add(p)
            End If
 
            If Not IsNothing(query.Employee.JobDescriptionID) AndAlso query.Employee.JobDescriptionID > 0 Then
                mySQL.Append("AND JobDescriptionID = @JobDescriptionID ")
                p = New SqlClient.SqlParameter("@JobDescriptionID", query.Employee.JobDescriptionID)
                params.Add(p)
            End If 
        End If
 
        Return ExecuteDataset(mySQL.ToString(), params, CommandType.Text).Tables(0)
    End Function

Notice how the method checks to see if you did populate the employee object and if so it begins checking the properties and building your where clause to filter accordingly.  This means that on the front end you can bind a datagrid to a datatable with only employee last name of 'W' as follows:

        'Create employee query to get only last names with 'W'
        Dim query As New EasyAssets.DAC.EmployeeQuery
        query.Employee = New EasyAssets.DAC.Employee
 
        query.Employee.LastName = "W"
  
        DataGrid1.DataSource = DomainManager.ListSummary(query)
        DataGrid1.DataBind()

As a developer using my product you don't need to know anything about what is going on in the datalayer.  All you need to know is to ask the DomainManager to give you the results of an employee query with an employee that looks like the one you passed in.

Pretty simple eh?  Anytime you want a parameterized query from a table this is all the code you need to write.  Someday soon I'll blog on how to modify the DAC class to do more specialized queries for custom reporting.