So here's some fun stuff we can do with inheritance, custom collections, and domain managers. The purpose of a domain manager is to be a central object to handle the I/O of your classes in a multi-tiered application. To meet these needs we will want a class that will take any object in our program and allow us to perform basic CRUD (Create Retrieve Update Delete) on the object (or collections of the object).
For the purposes of this sample I am going to create a domain manager that contains logic for a contact record. A contact can be any person in the whole world. Adding more object types to the domain manager is as simple as creating the DAO, Query, and object classes then adding the DAO to the _SupportedDAOs collection in the domain manager.
Every object is broken into three parts as follows:
- The class object contains all the properties and collection code.
- The Query object contains information needed to filter data
- The DAO object contains all the sql logic.
Domain Object Base Class
So for our domain manager to handle the loading of all objects in the program, we are going to need to create a base class for all our objects to inherit. If you don't know why we need the base class, I suggest you read up on your inheritance concepts! We'll call our base class DomainObject (DomainObjects for collections):
Imports System.Collections
Public MustInherit Class DomainObjects
Inherits CollectionBase
Public Sub New()
End Sub
Public Overridable Sub Sort(ByVal mySorter As IComparer)
innerlist.Sort(mySorter)
End Sub
Public Overridable Sub Add(ByVal myDomainObject As DomainObject)
List.Add(myDomainObject)
End Sub
Public Overridable Sub Remove(ByVal myDomainObject As DomainObject)
List.Remove(myDomainObject)
End Sub
Public Function Contains(ByVal myDomainObject As DomainObject) As Boolean
Return List.Contains(myDomainObject)
End Function
Default Public Overridable Property Item(ByVal index As Integer) As DomainObject
Get
Return DirectCast(MyBase.List.Item(index), DomainObject)
End Get
Set(ByVal Value As DomainObject)
list.Item(index) = Value
End Set
End Property
End Class
Public Class DomainObject
Private _domainMgr As DomainMGR
Public Property ObjectDomainMGR() As DomainMGR
Get
If IsNothing(_domainMgr) Then
If IsNothing(System.Web.HttpContext.Current) Then
_domainMgr = New DomainMGR
ElseIf IsNothing(System.Web.HttpContext.Current.Application("DOMAIN_MANAGER")) Then
_domainMgr = New DomainMGR
Else
_domainMgr = System.Web.HttpContext.Current.Application("DOMAIN_MANAGER")
End If
End If
Return _domainMgr
End Get
Set(ByVal Value As DomainMGR)
_domainMgr = Value
End Set
End Property
End Class
Notice a few things here:
- We are inheriting from CollectionBase. The consequences of this inheritance is that our collection is based on an arraylist. If you wanted to use dictionarybase instead (hashtable) you're more than welcome to. In my experience dictionarybase is a pain in the ass to sort though.
- Don't worry about ObjectDomainMGR, it's just a reference to a cached version of the domain manager (code below)
Query Object Base Class
Query objects simply contain objects that may or may not be used as search criteria and a settable property of sort expression. The base class looks something like this:
Public Interface IDomainQuery
Property SortExpression() As String
ReadOnly Property QueryType() As String
End Interface
DAO Object Base Class
Here's where all our I/O lives. I like using the microsoft data application block so do note that it imports it. You'll notice that it exposes some properties like “SupportsQueryType“, “SupportsLoadType“, etc. You'll see later that these properties are set in the DAO to be the name of the class that is allowed to call the code in the DAO. That is to say that if an object calls CustomerDAO.Save() and the SupportsSaveType property doesn't list Customer as a valid caller, the domain manager will not execute the code.
Moving along, here is the code for the DAO Base Class:
Imports Microsoft.Practices.EnterpriseLibrary.Data
Public MustInherit Class BaseDAO
Private _SupportsQueryType As String = ""
Private _SupportsLoadType As String = ""
Private _SupportsSaveType As String = ""
Private _SupportsDeleteType As String = ""
Private _SupportsValueType As String = ""
Private _InstanceName As String = ""
Private _DataReader As IDataReader
Public Property InstanceName() As String
Get
Return _InstanceName
End Get
Set(ByVal Value As String)
_InstanceName = Value
End Set
End Property
Public Property DataReader() As SqlClient.SqlDataReader
Get
Return _DataReader
End Get
Set(ByVal Value As SqlClient.SqlDataReader)
_DataReader = Value
End Set
End Property
Public Property SupportsQueryType() As String
Get
Return _SupportsQueryType
End Get
Set(ByVal Value As String)
_SupportsQueryType = Value
End Set
End Property
Public Property SupportsLoadType() As String
Get
Return _SupportsLoadType
End Get
Set(ByVal Value As String)
_SupportsLoadType = Value
End Set
End Property
Public Property SupportsSaveType() As String
Get
Return _SupportsSaveType
End Get
Set(ByVal Value As String)
_SupportsSaveType = Value
End Set
End Property
Public Property SupportsDeleteType() As String
Get
Return _SupportsDeleteType
End Get
Set(ByVal Value As String)
_SupportsDeleteType = Value
End Set
End Property
Public Property SupportsValueType() As String
Get
Return Me._SupportsValueType
End Get
Set(ByVal Value As String)
Me._SupportsValueType = Value
End Set
End Property
Public Function Load(ByVal obj As DomainObject) As DomainObject
End Function
Public Function GetList(ByVal dq As IDomainQuery) As DomainObjects
End Function
Public Function GetSummaryList(ByVal dq As IDomainQuery) As DataTable
End Function
Public Function Save(ByVal obj As DomainObject) As DomainObject
End Function
Public Function Delete(ByVal obj As DomainObject) As Boolean
End Function
Public Function GetValue(ByVal dq As IDomainQuery) As Object
End Function
Public Function ExecuteNonQuery(ByVal SQL As String) As Integer
Return DatabaseFactory.CreateDatabase(_InstanceName).ExecuteNonQuery(CommandType.Text, SQL)
End Function
Public Function ExecuteNonQuery(ByVal ProcedureName As String, ByVal Params As ArrayList) As DBCommandWrapper
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper = db.GetStoredProcCommandWrapper(ProcedureName)
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
db.ExecuteNonQuery(cw)
Return cw
End Function
Public Function ExecuteNonQuery(ByVal SQL As String, ByVal Params As ArrayList, ByVal type As CommandType) As DBCommandWrapper
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper
Select Case type
Case CommandType.StoredProcedure
cw = db.GetStoredProcCommandWrapper(SQL)
Case CommandType.Text
cw = db.GetSqlStringCommandWrapper(SQL)
End Select
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
db.ExecuteNonQuery(cw)
Return cw
End Function
Public Function ExecuteReader(ByVal SQL As String) As SqlClient.SqlDataReader
_DataReader = DatabaseFactory.CreateDatabase(_InstanceName).ExecuteReader(CommandType.Text, SQL)
Return _DataReader
End Function
Public Function ExecuteReader(ByVal ProcedureName As String, ByVal Params As ArrayList) As SqlClient.SqlDataReader
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper = db.GetStoredProcCommandWrapper(ProcedureName)
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
Return db.ExecuteReader(cw)
End Function
Public Function ExecuteReader(ByVal SQL As String, ByVal Params As ArrayList, ByVal type As CommandType)
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper
Select Case type
Case CommandType.StoredProcedure
cw = db.GetStoredProcCommandWrapper(SQL)
Case CommandType.Text
cw = db.GetSqlStringCommandWrapper(SQL)
End Select
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
Return db.ExecuteReader(cw)
End Function
Public Function ExecuteScalar(ByVal SQL As String) As Integer
Return DatabaseFactory.CreateDatabase(_InstanceName).ExecuteScalar(CommandType.Text, SQL)
End Function
Public Function ExecuteScalar(ByVal ProcedureName As String, ByVal Params As ArrayList) As Integer
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper = db.GetStoredProcCommandWrapper(ProcedureName)
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
Return db.ExecuteScalar(cw)
End Function
Public Function ExecuteScalar(ByVal SQL As String, ByVal Params As ArrayList, ByVal type As CommandType) As Integer
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper
Select Case type
Case CommandType.StoredProcedure
cw = db.GetStoredProcCommandWrapper(SQL)
Case CommandType.Text
cw = db.GetSqlStringCommandWrapper(SQL)
End Select
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
Return db.ExecuteScalar(cw)
End Function
Public Function ExecuteDataset(ByVal SQL As String) As DataSet
Return DatabaseFactory.CreateDatabase(_InstanceName).ExecuteDataSet(CommandType.Text, SQL)
End Function
Public Function ExecuteDataset(ByVal ProcedureName As String, ByVal Params As ArrayList) As DataSet
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper = db.GetStoredProcCommandWrapper(ProcedureName)
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
Return db.ExecuteDataSet(cw)
End Function
Public Function ExecuteDataset(ByVal SQL As String, ByVal Params As ArrayList, ByVal type As CommandType) As DataSet
Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
Dim cw As DBCommandWrapper
Select Case type
Case CommandType.StoredProcedure
cw = db.GetStoredProcCommandWrapper(SQL)
Case CommandType.Text
cw = db.GetSqlStringCommandWrapper(SQL)
End Select
For Each p As SqlClient.SqlParameter In Params
If p.Direction = ParameterDirection.Input Then
cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
ElseIf p.Direction = ParameterDirection.Output Then
cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
End If
Next
Return db.ExecuteDataSet(cw)
End Function
End Class
Domain Manager
Now we'll examine the domain manager. Notice that it contains an arraylist called “SupportedDAOs“. This allows you the freedom of not allowing it to execute code from DAOs that isn't ready. So in a case where multiple coders are working on the project one can code a DAO and have it ready for use but not actually link it into the live program until it is added to the _SupportedDAOs arraylist. Notice also the following things:
- .Load takes a domain object (with its key populated) and returns the object loaded from the database
- .List takes a domain query, parses it, and returns a collection
- .Store saves the given domain object
- .Remove deletes the given domain object
The beauty of the SupportedTypes in the DAO and the domain manager is that unless something is listed as supported, it will not allow the action to occur. This allows you to have the stub function there in case you want to add or remove functionality with turning it on or off as simple as setting a property. You can see in the domain manager code how it checks to see if the domain object passed in is supported by the dao collection.
Code as follows:
Public Class DomainManager
Private _SupportedDAOs As New ArrayList
Private _InstanceName As String
Public Sub New(ByVal Instance As String)
Me._InstanceName = Instance
_SupportedDAOs.Add(New ContactDAO)
End Sub
Public Property InstanceName() As String
Get
Return _InstanceName
End Get
Set(ByVal Value As String)
_InstanceName = Value
End Set
End Property
Public Function Load(ByVal d As DomainObject) As DomainObject
Dim dao As Object
For Each dao In _SupportedDAOs
If dao.SupportsLoadType = d.GetType.ToString() Then
Dim hydratedObject As DomainObject
Try
dao.InstanceName = _InstanceName
hydratedObject = dao.Load(d)
If (Not IsNothing(hydratedObject)) Then
hydratedObject.ObjectDomainMGR = Me
End If
Catch ex As Exception
Throw ex
Finally
Try
If (Not dao.DataReader Is Nothing) Then
dao.DataReader.Close()
End If
Catch ex As Exception
Trace.Write(ex.Message)
End Try
End Try
Return hydratedObject
End If
Next
Throw New Exception("Load not supported for this type[" + d.GetType.ToString() + "]")
End Function
Public Function List(ByVal d As IDomainQuery) As DomainObjects
Dim dao As Object
For Each dao In _SupportedDAOs
If dao.SupportsQueryType = d.QueryType.ToString() Then
Dim hydratedObjects As DomainObjects
Try
dao.InstanceName = _InstanceName
hydratedObjects = dao.GetList(d)
'Set a reference to domain manager on each object in list
For Each hydratedObjectEntry As DomainObject In hydratedObjects
hydratedObjectEntry.ObjectDomainMGR = Me
Next
Catch ex As Exception
Throw ex
Finally
Try
If (Not dao.DataReader Is Nothing) Then
dao.DataReader.Close()
End If
Catch ex As Exception
Trace.Write(ex.Message)
End Try
End Try
Return hydratedObjects
End If
Next
Throw New Exception("Query not supported for this type[" + d.QueryType.ToString() + "]")
End Function
Public Function ListSummary(ByVal d As IDomainQuery) As DataTable
Dim dao As Object
For Each dao In _SupportedDAOs
If dao.SupportsQueryType = d.QueryType Then
Dim summary As DataTable
Try
dao.InstanceName = _InstanceName
summary = dao.GetSummaryList(d)
Catch ex As Exception
Throw ex
Finally
Try
dao.DataReader.Close()
Catch
End Try
End Try
Return summary
End If
Next
Throw New Exception("Query not supported for this type[" + d.QueryType.ToString() + "]")
End Function
Public Function Save(ByVal d As DomainObject) As DomainObject
Dim dao As Object
For Each dao In _SupportedDAOs
If dao.SupportsSaveType = d.GetType.ToString() Then
Dim savedObject As DomainObject
Try
dao.InstanceName = _InstanceName
savedObject = dao.Save(d)
Catch ex As Exception
Throw ex
Finally
Try
dao.DataReader.Close()
Catch
End Try
End Try
Return savedObject
End If
Next
Throw New Exception("Save not supported for this type[" + d.GetType.ToString() + "]")
End Function
Public Function Delete(ByVal d As DomainObject) As Boolean
Dim dao As Object
For Each dao In _SupportedDAOs
If dao.SupportsDeleteType = d.GetType.ToString() Then
Dim result As Boolean = False
Try
dao.InstanceName = _InstanceName
result = dao.Delete(d)
Catch ex As Exception
Throw ex
Finally
Try
dao.DataReader.Close()
Catch
End Try
End Try
Return result
End If
Next
Throw New Exception("Delete not supported for this type[" + d.GetType.ToString() + "]")
End Function
Public Function GetValue(ByVal d As IDomainQuery) As Object
Dim dao As Object
For Each dao In _SupportedDAOs
If dao.SupportsValueType = d.QueryType Then
Dim retval As Object
Try
dao.InstanceName = _InstanceName
retval = dao.GetValue(d)
Catch ex As Exception
Throw ex
Finally
Try
dao.DataReader.Close()
Catch
End Try
End Try
Return retval
End If
Next
Throw New Exception("GetValue not supported for this type[" + d.QueryType.ToString() + "]")
End Function
End Class
Sample: ContactInfo
Now here is the code to wire up a contact's information record:
Public Class ContactInfoDAO
Inherits BaseDAO
Public Sub New()
SupportsDeleteType = "YourNamespace.ContactInfo"
SupportsLoadType = "YourNamespace.ContactInfo"
SupportsSaveType = "YourNamespace.ContactInfo"
SupportsQueryType = "YourNamespace.ContactInfoQuery"
End Sub
Public Shadows Function Load(ByVal myContactInfo As ContactInfo) As ContactInfo
Dim mysql As New System.Text.StringBuilder
Dim params As New ArrayList
Dim p As SqlClient.SqlParameter
mysql.Append("SELECT * FROM TheTable WHERE UserID = @ID")
p = New SqlClient.SqlParameter("ID", myContactInfo.UserID)
params.Add(p)
DataReader = ExecuteReader(mysql.ToString(), params, CommandType.Text)
If DataReader.Read Then
myContactInfo = New ContactInfo
If Not IsDBNull(DataReader("ID")) Then myContactInfo.ID= Convert.ToString(DataReader("ID"))
If Not IsDBNull(DataReader("Address1")) Then myContactInfo.Address1 = DataReader("Address1").ToString()
If Not IsDBNull(DataReader("Address2")) Then myContactInfo.Address2 = DataReader("Address2").ToString()
If Not IsDBNull(DataReader("City")) Then myContactInfo.City = DataReader("City").ToString()
If Not IsDBNull(DataReader("State")) Then myContactInfo.State = DataReader("State").ToString()
If Not IsDBNull(DataReader("PostalCode")) Then myContactInfo.PostalCode = DataReader("PostalCode").ToString()
If Not IsDBNull(DataReader("Phone")) Then myContactInfo.Phone = DataReader("Phone").ToString()
If Not IsDBNull(DataReader("Fax")) Then myContactInfo.Fax = DataReader("Fax").ToString()
If Not IsDBNull(DataReader("Cellphone")) Then myContactInfo.CellPhone = DataReader("Cellphone").ToString()
If Not IsDBNull(DataReader("externalEmail")) Then myContactInfo.ExternalEmail = DataReader("externalEmail").ToString()
End If
Return myContactInfo
End Function
Public Shadows Function GetList(ByVal query As ContactInfoQuery) As ContactInfos
Dim myContactInfos As New ContactInfos
Dim mySQL As New System.Text.StringBuilder
Dim params As New ArrayList
mySQL.Append("SELECT * FROM TheTable Where 1=1 ")
Dim p As SqlClient.SqlParameter
If Not IsNothing(query.ContactInfo) Then
If Not IsNothing(query.ContactInfo.Address1) AndAlso query.ContactInfo.Address1.Length > 0 Then
mySQL.Append("AND Address1 like @Address1 + '%' ")
p = New SqlClient.SqlParameter("@Address1", query.ContactInfo.Address1)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.Address2) AndAlso query.ContactInfo.Address2.Length > 0 Then
mySQL.Append("AND Address2 like @Address2 + '%' ")
p = New SqlClient.SqlParameter("@Address2", query.ContactInfo.Address2)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.City) AndAlso query.ContactInfo.City.Length > 0 Then
mySQL.Append("AND city like @city + '%' ")
p = New SqlClient.SqlParameter("@city", query.ContactInfo.City)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.State) AndAlso query.ContactInfo.State.Length > 0 Then
mySQL.Append("AND State like @State + '%' ")
p = New SqlClient.SqlParameter("@State", query.ContactInfo.State)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.PostalCode) AndAlso query.ContactInfo.PostalCode.Length > 0 Then
mySQL.Append("AND PostalCode = @PostalCode + '%' ")
p = New SqlClient.SqlParameter("@PostalCode", query.ContactInfo.PostalCode)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.Phone) AndAlso query.ContactInfo.Phone.ToString.Length > 0 Then
mySQL.Append("AND ContactInfo like @Phone + '%' ")
p = New SqlClient.SqlParameter("@Phone", query.ContactInfo.Phone)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.Fax) AndAlso query.ContactInfo.Fax.ToString.Length > 0 Then
mySQL.Append("AND Fax like @Fax + '%' ")
p = New SqlClient.SqlParameter("@Fax", query.ContactInfo.Fax)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.CellPhone) AndAlso query.ContactInfo.CellPhone.Length > 0 Then
mySQL.Append("AND CellPhone like @CellPhone + '%' ")
p = New SqlClient.SqlParameter("@CellPhone", query.ContactInfo.CellPhone)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.ExternalEmail) AndAlso query.ContactInfo.ExternalEmail.Length > 0 Then
mySQL.Append("AND ExternalEmail like @ExternalEmail + '%' ")
p = New SqlClient.SqlParameter("@ExternalEmail", query.ContactInfo.ExternalEmail)
params.Add(p)
End If
End If
If query.SortExpression.Length > 0 Then
mySQL.AppendFormat("ORDER BY {0}", query.SortExpression)
End If
DataReader = ExecuteReader(mySQL.ToString)
While DataReader.Read
Dim myContactInfo As ContactInfo = New ContactInfo
If Not IsDBNull(DataReader("ID")) Then myContactInfo.Id= Convert.Int32(DataReader("ID"))
If Not IsDBNull(DataReader("Address1")) Then myContactInfo.Address1 = DataReader("Address1").ToString()
If Not IsDBNull(DataReader("Address2")) Then myContactInfo.Address2 = DataReader("Address2").ToString()
If Not IsDBNull(DataReader("City")) Then myContactInfo.City = DataReader("City").ToString()
If Not IsDBNull(DataReader("State")) Then myContactInfo.State = DataReader("State").ToString()
If Not IsDBNull(DataReader("PostalCode")) Then myContactInfo.PostalCode = DataReader("PostalCode").ToString()
If Not IsDBNull(DataReader("Phone")) Then myContactInfo.Phone = DataReader("Phone").ToString()
If Not IsDBNull(DataReader("Fax")) Then myContactInfo.Fax = DataReader("Fax").ToString()
If Not IsDBNull(DataReader("Cellphone")) Then myContactInfo.CellPhone = DataReader("Cellphone").ToString()
If Not IsDBNull(DataReader("externalEmail")) Then myContactInfo.ExternalEmail = DataReader("externalEmail").ToString()
myContactInfos.Add(myContactInfo)
End While
Return myContactInfos
End Function
Public Shadows Function GetSummaryList(ByVal query As ContactInfoQuery) As DataTable
Dim mySQL As New System.Text.StringBuilder
Dim params As New ArrayList
mySQL.Append("SELECT * FROM TheTable WHERE 1=1 ")
If Not IsNothing(query.ContactInfo) Then
Dim p As SqlClient.SqlParameter
If Not IsNothing(query.ContactInfo.Address1) AndAlso query.ContactInfo.Address1.Length > 0 Then
mySQL.Append("AND Address1 LIKE @Address1 + '%' ")
p = New SqlClient.SqlParameter("@Address1", query.ContactInfo.Address1)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.Address2) AndAlso query.ContactInfo.Address2.Length > 0 Then
mySQL.Append("AND Address2 LIKE @Address2 + '%' ")
p = New SqlClient.SqlParameter("@Address2", query.ContactInfo.Address2)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.City) AndAlso query.ContactInfo.City.Length > 0 Then
mySQL.Append("AND City LIKE @City + '%' ")
p = New SqlClient.SqlParameter("@City", query.ContactInfo.City)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.State) AndAlso query.ContactInfo.State.Length > 0 Then
mySQL.Append("AND state LIKE @state + '%' ")
p = New SqlClient.SqlParameter("@state", query.ContactInfo.State)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.PostalCode) AndAlso query.ContactInfo.PostalCode.Length > 0 Then
mySQL.Append("AND PostalCode = @PostalCode + '%' ")
p = New SqlClient.SqlParameter("@PostalCode", query.ContactInfo.PostalCode)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.Phone) AndAlso query.ContactInfo.Phone.Length > 0 Then
mySQL.Append("AND Phone LIKE @Phone + '%' ")
p = New SqlClient.SqlParameter("@Phone", query.ContactInfo.Phone)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.Fax) AndAlso query.ContactInfo.Fax.Length > 0 Then
mySQL.Append("AND Fax LIKE @Fax + '%' ")
p = New SqlClient.SqlParameter("@Fax", query.ContactInfo.Fax)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.CellPhone) AndAlso query.ContactInfo.CellPhone.Length > 0 Then
mySQL.Append("AND CellPhone LIKE @CellPhone + '%' ")
p = New SqlClient.SqlParameter("@Fax", query.ContactInfo.CellPhone)
params.Add(p)
End If
If Not IsNothing(query.ContactInfo.ExternalEmail) AndAlso query.ContactInfo.ExternalEmail.Length > 0 Then
mySQL.Append("AND ExternalEmail LIKE @ExternalEmail + '%' ")
p = New SqlClient.SqlParameter("@ExternalEmail", query.ContactInfo.ExternalEmail)
params.Add(p)
End If
End If
Return ExecuteDataset(mySQL.ToString(), params, CommandType.Text).Tables(0)
End Function
Public Shadows Function Save(ByVal myContactInfo As ContactInfo) As ContactInfo
Dim params As New ArrayList
param = New SqlClient.SqlParameter("@ContactTypes", SqlDbType.Int)
If myContactInfo.ContactTypeID < 1 Then
param.Value = 2
Else
param.Value = myContactInfo.ContactTypeID
End If
params.Add(param)
param = New SqlClient.SqlParameter("@address1", SqlDbType.VarChar, 50)
If Not IsNothing(myContactInfo.Address1) AndAlso myContactInfo.Address1.Length > 0 Then
param.Value = myContactInfo.Address1
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@Address2", SqlDbType.VarChar, 50)
If Not IsNothing(myContactInfo.Address2) AndAlso myContactInfo.Address2.Length > 0 Then
param.Value = myContactInfo.Address2
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@City", SqlDbType.VarChar, 30)
If Not IsNothing(myContactInfo.City) AndAlso myContactInfo.City.Length > 0 Then
param.Value = myContactInfo.City
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@State", SqlDbType.Char, 2)
If Not IsNothing(myContactInfo.State) AndAlso myContactInfo.State.Length > 0 Then
param.Value = myContactInfo.State
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@postalCode", SqlDbType.VarChar, 10)
If Not IsNothing(myContactInfo.PostalCode) AndAlso myContactInfo.PostalCode.Length > 0 Then
param.Value = myContactInfo.PostalCode
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@phone", SqlDbType.VarChar, 20)
If Not IsNothing(myContactInfo.Phone) AndAlso myContactInfo.Phone.Length > 0 Then
param.Value = myContactInfo.Phone
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@fax", SqlDbType.VarChar, 20)
If Not IsNothing(myContactInfo.Fax) AndAlso myContactInfo.Fax.Length > 0 Then
param.Value = myContactInfo.Fax
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@cellPhone", SqlDbType.VarChar, 20)
If Not IsNothing(myContactInfo.CellPhone) AndAlso myContactInfo.CellPhone.Length > 0 Then
param.Value = myContactInfo.CellPhone
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@externalEmail", SqlDbType.VarChar, 50)
If Not IsNothing(myContactInfo.ExternalEmail) AndAlso myContactInfo.ExternalEmail.Length > 0 Then
param.Value = myContactInfo.ExternalEmail
Else
param.Value = DBNull.Value
End If
params.Add(param)
param = New SqlClient.SqlParameter("@LastEditAppUser", SqlDbType.VarChar, 15)
If Not IsNothing(Web.HttpContext.Current) Then
param.Value = Web.HttpContext.Current.User.Identity.Name
Else
param.Value = "SYSTEM"
End If
params.Add(param)
param = New SqlClient.SqlParameter("@isDeleted", SqlDbType.Bit)
param.Value = myContactInfo.IsDeleted
params.Add(param)
ExecuteNonQuery("SaveSproc", params)
Return myContactInfo
End Function
Public Shadows Function Delete(ByVal myContactInfo As ContactInfo) As Boolean
param = New SqlClient.SqlParameter("@Id", SqlDbType.Int)
param.Value = myContactInfo.ID
params.Add(param)
ExecuteNonQuery("DeleteSproc", params)
End Function
End Class
Now let's check out the Contact Query object. It's simple, containing only an instance of a contact class. In the case where you had Addresses or Phone Number objects you could add those as properties of the query and build your where clause accordingly based on the presence of those objects.
Public Class ContactInfoQuery
Implements IDomainQuery
Private _ContactInfo As ContactInfo
Private _sortExpression As String = ""
#Region "Constructors"
Public Sub New()
End Sub
Public Sub New(ByVal sortExpression As String)
Me.SortExpression = sortExpression
End Sub
#End Region
Public Property ContactInfo() As ContactInfo
Get
Return _ContactInfo
End Get
Set(ByVal Value As ContactInfo)
_ContactInfo = Value
End Set
End Property
Public ReadOnly Property QueryType() As String Implements IDomainQuery.QueryType
Get
Return "SchoolOne.IHomeSchool.BLL.ContactInfoQuery"
End Get
End Property
Public Property SortExpression() As String Implements IDomainQuery.SortExpression
Get
Return _sortExpression
End Get
Set(ByVal Value As String)
_sortExpression = Value
End Set
End Property
End Class
And now the contact object itself.
Public Class ContactInfos
Inherits DomainObjects
Public Overloads Sub</