My co-worker and I are debating Flexibility vs. Reliability.
For example this method is highly flexible:
Public Shared Function GetList(ByVal whereClause As String) As String
strSQL = "SELECT field1,field2,field3,field4,field5 FROM table1 "
If whereClause <> String.Empty Then
strSQL &= "WHERE " & whereClause
End If
Return strSQL
End Function
My co-worker agress that it is flexible but would prefer to use more
reliable techniques like Overloading and Strong Typing of parameters.
Public Shared Function GetList(ByVal field1Value As String, ByVal
field2Value As Integer) As String
strSQL = "SELECT field1,field2,field3,field4,field5 FROM table1 "
strSQL &= "WHERE field1='" & field1Value & "' AND field2=" &
field2Value
Return strSQL
End Function
My argument is that you can't predict every possible combination of field
values and arguments so the number of where clauses and overloaded methods
would grow to ridiculous proportions.
Implement a GenericSearchObject that has properties of all the fields
that could possibly be used. Inside the Set of each of those
properties, set a flag to indicate the developer has set it. When it
builds the SQL, you can Append just the fields and values that were
set.
Have other people faced this issue? If so what did you think about it?
Appreciate any insights and experiences.
Thanks!
I hold the opinion that there should be absolutely no SQL in the a UI
module. In fact, there should be no SQL outside of a DAL. It is the
job of the DAL to disguise the implementation of the objects (i.e.
tables in a database, or the type of the database itself). For this
reason, I abhor data-bound controls (but agree they are useful as a
short-cut) when used directly with generic constructs such as DataSet
or DataTable.
Here's an example of how I model a database application, and you'll
see how I fit in the generic search object:
We are building a system to hold people their bank accounts. We first
list the interfaces we will require to be exposed by a DAL - these are
located in one library.
-begin namespace/library Banking-
Interface IBankDatabase
Interface IPerson
Interface IPersonCollection
Interface IPersonSearch
Interface IBankAccount
Interface IBankAccountCollection
Interface IBankAccountSearch
-end namespace/library Banking-
If we have a SQL Server back-end, we would implement the following
objects in a library:
-begin namespace/library Banking.DataAccess.SqlServer-
Public Class SqlServerBankDatabase Implements IBankDatabase
Public Class SqlServerPerson Implements IPerson
Public Class SqlServerPersonCollection Implements IPersonCollection
Public Class SqlServerPersonSearch Implements IPersonSearch
Public Class SqlServerBankAccount Implements IBankAccount
Public Class SqlServerBankAccountCollection Implements
IBankAccountColection
Public Class SqlServerBankAccountSearch Implements IBankAccountSearch
-end namespace/library Banking.DataAccess.SqlServer-
We also want to allow objects to be stored in a local MDB. Therefore,
we implement the following objects in another library:
-begin namespace/library Banking.DataAccess.Access-
Public Class AccessBankDatabase Implements IBankDatabase
Public Class AccessPerson Implements IPerson
Public Class AccessPersonCollection Implements IPersonCollection
Public Class AccessPersonSearch Implements IPersonSearch
Public Class AccessBankAccount Implements IBankAccount
Public Class AccessBankAccountCollection Implements
IBankAccountColection
Public Class AccessBankAccountSearch Implements IBankAccountSearch
-end namespace/library Banking.DataAccess.Access-
In our "Banking" library, we also need an object to create the
relevant database objects:
-begin namespace/library Banking-
Public Class BankingDatabaseFactory
Public Shared Function CreateSqlServer( _
hostName,userID,password) As _
IBankDatabase
Return New SqlServerBankDatabase(hostName,userID, _
password)
End Function
Public Shared Function CreateAccess( _
filename) As
IBankDatabase
Return New AccessBankDatabase(filename)
End Function
End Class
-end namespace/library Banking-
Now we can reference just the interfaces library ("Banking") in our UI
(or BL or whatever) without needing to know how to write SQL. In the
UI code, we could have a routine something like this:
-begin example.vb-
IBankDatabase oBank = BankingDatabaseFactory.CreateSqlServer( _
"localhost","fred","password")
Public Sub ShowEverything()
Dim oPerson As IPerson
For Each oPerson In oBank.People
Debug.WriteLine(oPerson)
Dim oAccount As IBankAccount
For Each oAccount In oPerson.Accounts
Debug.WriteLine(oAccount)
Next
Next
End Sub
Public Sub SearchForPersonByName(oName As String)
Dim oResults As IPersonCollection
oResults = oBank.PersonSearch.FindByName(oName)
Dim oPerson As IPerson
For Each oPerson In oResults
Debug.WriteLine(oPerson)
Next
End Sub
-end example.vb-
As you can see from the example, there is no need for SQL in the UI.
You may, however, wish to expose the ability for WHERE and ORDER BY
clauses on a *Search object. Although it is bad practice, it can be
used to assist RAD. The implementation of the *Search should record
such requests to a log file during development, and you should work to
remove them later on. Why?
Firstly, consider the fact that you might wish to move from one
database to another (SQL to Oracle, for example). The functions in the
SQL are different, therefore you would have to go though every
application and change the code.
Secondly, consider if you want to change the datastructure. i.e. table
names or field names. With the model outlined above, you only need
change the DAL and all applications will continue to work.
Thirdly, security. Consider a naughty programmer who sends a WHERE
clause like: 'tblPerson.Name="Fred" delete tblPerson'
If you want a generic search, implement just that! On the PersonSearch
object, for example, you would provide the following functionality:
Dim oSearch As IGenericPersonSearch
oSearch = oBank.CreateGenericPersonSearch()
oSearch.Name = "Fred"
oSearch.Age = 45
Dim oPerson As IPerson
For Each oPerson In oSearch.Results
...
Next
The SqlServerPersonSearch object would handle the assembly of the
WHERE clause by detecting which properties the programmer has set. You
can add specifics for "Age less than" etc. if needs be. But if the SQL
required is any more complicated than that, you should implement your
own function.
A couple of other points:
1) Although it is sometimes possible to make use of overloading, the
absense of TypeDef means that a "Surname" and a "Forename" are the
same type ("String"). Therefore, a FindBySurname or FindByForename
approach tends to be better. One of the more imporant points here is
the fact that the interface can demonstrate the business logic. e.g.
FindPeopleWithNoAccount() might be exposed on the IPersonSearch.
2) Trusting a UI programmer (or anyone else) to use your library as
you expect is very, very bad practice. Not only will it introduce
security holes, but you'll also get the blame for his bugs.
Finally, there are other ways of implementing the separated interface
pattern (SEP), but that defined above is the way I prefer - I don't
like an object knowing how it was created (by having a IPersonFactory
as a member of a Person object). Also, the usage of Interfaces in the
DAL could be replaced with inheritance (where each object in the
Interfaces library would be declared as MustInherit).
I hope that somewhere there you get some ideas.
Rgds,
