Flexibility vs Reliability

[Joe Fallon]

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.

Have other people faced this issue? If so what did you think about it?

Appreciate any insights and experiences.

Thanks!
--
Joe Fallon

[Fergus Cooney]

Hi Joe,

I'd want to add maintainability and utility (which includes memorability)
to the mix. Now we have a four-dimensional space. In practice in that whole
arena, I imagine that you'd find most cases clustered around the same little
area (as judged over the whole space). Zooming in you'd perhaps discover some
overall patterns, but really, each case has to be given its position based on
its own merits.

Each of these qualities is subjective. There comes a point where one more
overload tips the scale for Joe, but Richard could happily add two more.
Neither are right. Next month, when Martin joins the project, he has to learn
either the complete list of overloads or the limits of the all-encompassing
parameter.

Are you really using table1 and field1, field2, etc. That's a better area
for improvemnt I would have thought. ;-)

But if you're looking for personal opinions, I'd go for maximum
flexibility

Public Shared Function GetList (ByVal strSQL As String) As String
Return strSQL
End Function
SCNR ;-)

Regards,
Fergus

[Tom Leylan]

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote...

My co-worker and I are debating Flexibility vs. Reliability.
Hi Joe: I think it's nice when we can have these types of discussions in a
newsgroup.
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.

One of the criteria for choosing one over the other is "who has access to
it" meaning if GetList() is used by the UI developer then your first example
is fine and it is fair to trust the UI developer will pass a legal string or
suffer the consequences. If a user's input would be used to supply the
where clause however then you should consider stricter control.

Another measure is "have there been any problems?" Meaning... should we be
overly concerned about a goofy where clause being passed when it has never
happened? If your flexible implementation fails every day causing chaos and
disruption then "sure" it's time to parse the dang string and add an "are
you sure?" dialog box or reassign the person doing it.

You would want to support "LIKE" and >, >=, < and <= also so the
combinations continue to grow... not to mention OR, BETWEEN and the
parenthetical grouping of some conditions.

I also don't think I would overload the method if I wanted to control things
a bit more. I'd probably name the methods something related to what the
various versions did, i.e. GetListWhereName(), GetListWhereId(),
GetListWhereState() or whatever. They might feed a generic GetList but if
you try to overload GetList() you will run out of unique signatures long
before you are even close to done.

Tom

[Jay B. Harlow [MVP - Outlook]]

Joe,
I would go for your first example, However I would overload GetList, instead
of checking for String.Empty.

Const strSQL As String = "SELECT field1,field2,field3,field4,field5 FROM table1"
Public Shared Function GetList(ByVal whereClause As String) As String Return strSQL & " WHERE " & whereClause End Function Public Shared Function GetList() As String
Return strSQL
End Function
Notice that the first overload with a string assumes that you have a
properly formed where clause, where as the second is the 'all' case.

Rather then attempt your second function, for largely the same reasons you
state. If I wanted this flexibity I would recommend implementing a Query
Object pattern.

http://www.martinfowler.com/eaaCatalog/queryObject.html

Which will allow significantly more flexibity.

The Query Object Pattern is from Martin Fowler's "Patterns of Enterprise
Application Architecture" from Addison Wesley

http://www.martinfowler.com/books.html#eaa

A simplified form of the Query Object would be to have your second function
accept an ParamArray of Criteria objects, where each Criteria object has a
field, a comparison operator and a value.

Something like (incomplete, untested):
' You could just use a string instead of defining the Enum.
Public Enum Operator
None
Equal
NotEqual
GreaterThen
End Enum

Public Class Criteria
private m_field As String
private m_operator As Operator
private m_value As String
Public Sub New(field As String, operator As Operator, value As
Integer)
Public Sub New(field As String, operator As Operator, value As
Double)
Public Sub New(field As String, operator As Operator, value As
String)
...
Public Overrides Function ToString() As String
End Class

Public Shared Function GetList(ByVal ParamArray criterias() as Criteria)
As String
Dim sb As New StringBuilder("SELECT * FROM WHEREVER ")
if criterias.Length > 0 then
sb.Append(" WHERE ")
End If
For Each criteria As Criteria In criterias
sb.Append(criteria) ' uses the overloaded ToString function
sb.Append(" AND ")
Next
if criterias.Length > 0 Then
sb.Length -= 5 ' remove the trailing AND
End If
Return sb.ToString()
End Function

Then when you call it, you would use:

GetList(New Criteria("Field1", Operator.Equal, 1), New
Criteria("Field2", Operator.NotEqual, 2))

The advantage of this simplified Query Object is that Criteria's overloaded
constructors can handle quoting & converting the value correctly.

Although you made the GetList shared, I see more value in it being
overridable on an object. Where you have Table Data Gateway objects, where
the 'SQL' for each table in its own object, this object have overridable
methods to get & invoke those methods. The first & third methods support
polymorphism better then the second. The problem of course is you are
exposing field names, Martin's book has ideas on how to mitigate that also.

http://www.martinfowler.com/eaaCatal...taGateway.html

Hope this helps
Jay

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... 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.

Have other people faced this issue? If so what did you think about it?

Appreciate any insights and experiences.

Thanks!
--
Joe Fallon

[Joe Fallon]

Jay,
I appreciate the comments.

For the current project I will recommend the flexible option.

Good to know the more advanced techniques are available though.
--
Joe Fallon
"Jay B. Harlow [MVP - Outlook]" <Ja********@email.msn.com> wrote in message
news:ei**************@TK2MSFTNGP09.phx.gbl...

Joe,
I would go for your first example, However I would overload GetList, instead of checking for String.Empty.

Const strSQL As String = "SELECT field1,field2,field3,field4,field5

FROM table1"

Public Shared Function GetList(ByVal whereClause As String) As String Return strSQL & " WHERE " & whereClause

End Function

Public Shared Function GetList() As String
Return strSQL
End Function

Notice that the first overload with a string assumes that you have a
properly formed where clause, where as the second is the 'all' case.

Rather then attempt your second function, for largely the same reasons you
state. If I wanted this flexibity I would recommend implementing a Query
Object pattern.

http://www.martinfowler.com/eaaCatalog/queryObject.html

Which will allow significantly more flexibity.

The Query Object Pattern is from Martin Fowler's "Patterns of Enterprise
Application Architecture" from Addison Wesley

http://www.martinfowler.com/books.html#eaa

A simplified form of the Query Object would be to have your second

function accept an ParamArray of Criteria objects, where each Criteria object has a
field, a comparison operator and a value.

Something like (incomplete, untested):
' You could just use a string instead of defining the Enum.
Public Enum Operator
None
Equal
NotEqual
GreaterThen
End Enum

Public Class Criteria
private m_field As String
private m_operator As Operator
private m_value As String
Public Sub New(field As String, operator As Operator, value As
Integer)
Public Sub New(field As String, operator As Operator, value As
Double)
Public Sub New(field As String, operator As Operator, value As
String)
...
Public Overrides Function ToString() As String
End Class

Public Shared Function GetList(ByVal ParamArray criterias() as Criteria) As String
Dim sb As New StringBuilder("SELECT * FROM WHEREVER ")
if criterias.Length > 0 then
sb.Append(" WHERE ")
End If
For Each criteria As Criteria In criterias
sb.Append(criteria) ' uses the overloaded ToString function
sb.Append(" AND ")
Next
if criterias.Length > 0 Then
sb.Length -= 5 ' remove the trailing AND
End If
Return sb.ToString()
End Function

Then when you call it, you would use:

GetList(New Criteria("Field1", Operator.Equal, 1), New
Criteria("Field2", Operator.NotEqual, 2))

The advantage of this simplified Query Object is that Criteria's overloaded constructors can handle quoting & converting the value correctly.

Although you made the GetList shared, I see more value in it being
overridable on an object. Where you have Table Data Gateway objects, where
the 'SQL' for each table in its own object, this object have overridable
methods to get & invoke those methods. The first & third methods support
polymorphism better then the second. The problem of course is you are
exposing field names, Martin's book has ideas on how to mitigate that also.
http://www.martinfowler.com/eaaCatal...taGateway.html

Hope this helps
Jay

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

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.

Have other people faced this issue? If so what did you think about it?

Appreciate any insights and experiences.

Thanks!
--
Joe Fallon

[Joe Fallon]

Tom,
Points taken.
Good reasoning.

I agree.
--
Joe Fallon
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:52********************@twister.nyc.rr.com...

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote...

My co-worker and I are debating Flexibility vs. Reliability.
Hi Joe: I think it's nice when we can have these types of discussions in

a newsgroup.

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.
One of the criteria for choosing one over the other is "who has access to
it" meaning if GetList() is used by the UI developer then your first

example is fine and it is fair to trust the UI developer will pass a legal string or suffer the consequences. If a user's input would be used to supply the
where clause however then you should consider stricter control.

Another measure is "have there been any problems?" Meaning... should we be overly concerned about a goofy where clause being passed when it has never
happened? If your flexible implementation fails every day causing chaos and disruption then "sure" it's time to parse the dang string and add an "are
you sure?" dialog box or reassign the person doing it.

You would want to support "LIKE" and >, >=, < and <= also so the
combinations continue to grow... not to mention OR, BETWEEN and the
parenthetical grouping of some conditions.

I also don't think I would overload the method if I wanted to control things a bit more. I'd probably name the methods something related to what the
various versions did, i.e. GetListWhereName(), GetListWhereId(),
GetListWhereState() or whatever. They might feed a generic GetList but if
you try to overload GetList() you will run out of unique signatures long
before you are even close to done.

Tom

[Joe Fallon]

;-)
--
Joe Fallon
"Fergus Cooney" <fi****@post.com> wrote in message
news:OB**************@TK2MSFTNGP12.phx.gbl...

Hi Joe,

I'd want to add maintainability and utility (which includes memorability) to the mix. Now we have a four-dimensional space. In practice in that whole arena, I imagine that you'd find most cases clustered around the same little area (as judged over the whole space). Zooming in you'd perhaps discover some overall patterns, but really, each case has to be given its position based on its own merits.

Each of these qualities is subjective. There comes a point where one more overload tips the scale for Joe, but Richard could happily add two more.
Neither are right. Next month, when Martin joins the project, he has to learn either the complete list of overloads or the limits of the all-encompassing parameter.

Are you really using table1 and field1, field2, etc. That's a better area for improvemnt I would have thought. ;-)

But if you're looking for personal opinions, I'd go for maximum
flexibility

Public Shared Function GetList (ByVal strSQL As String) As String
Return strSQL
End Function
SCNR ;-)

Regards,
Fergus

[_Andy_]

On Sat, 1 Nov 2003 12:30:40 -0500, "Joe Fallon"
<jf******@nospamtwcny.rr.com> wrote:

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,

[Joe Fallon]

Andy,
Thanks for the comments.
Very well thought out.

A bit beyond my OOP experience right now, but I now know there are other
approaches to this problem.
As I continue to learn I will keep this in mind.

Thanks!
--
Joe Fallon

"_Andy_" <wi******@nospamthanks.gov> wrote in message
news:8e********************************@4ax.com...

On Sat, 1 Nov 2003 12:30:40 -0500, "Joe Fallon"
<jf******@nospamtwcny.rr.com> wrote:

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 methodswould 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,

[Joe Fallon]

;-)

FYI:
My config file determines the database type (Oracle or SQL Server) and based
on that I return the appropriate SQL String.

--
Joe Fallon

"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:nr********************@twister.nyc.rr.com...

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote...

A bit beyond my OOP experience right now, but I now know there are other
approaches to this problem.
Hi Joe... I couldn't grasp it all but in case I can be of some assistance
let me offer the following:

I avoid direct references to SQL within the UI also. It immediately ties
the UI to SQL and (depending upon the example) possibly to SQL-Server.

I'll suppose that Andy's key concept is to simply abstract SQL such that the UI
can form a query that "any" back-end can interpret.

That said I don't believe we actually know where "GetList" resides. That it returns a SQL string might not be an issue at all. It could of course
return a sort of query object that includes the query string but it is hard to know exactly where to stop abstracting and to get on with software
development. :-)

Bottom line "stick with flexibility."

[Tom Leylan]

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote...

FYI:
My config file determines the database type (Oracle or SQL Server) and based on that I return the appropriate SQL String.

Hi Joe: That's cool. Just to be clear the idea is to keep all those
details out of the UI layer and in a separate data access layer. By doing
it that way alternative UIs (a browser for instance) can access the same
data using the same routines. And the data itself can be stored in text
files, .DBF files, SQL Server or fetched from an online commercial database
without changing the UI.

I just noticed you're in update New York? I'm in New York City.

Tom

..

[Joe Fallon]

Tom,
I was in NYC on business this week.
Didn't you post a comment in the CodeSmith groups?
Or was it CSLA?

Nice to see you again.

I wrote a separate DAL which works for both SQL Server and Oracle.
I could always extend it for other providers but I only need these right
now.
--
Joe Fallon

"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:4Q*********************@twister.nyc.rr.com...

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote...

FYI:
My config file determines the database type (Oracle or SQL Server) and based

on that I return the appropriate SQL String.

Hi Joe: That's cool. Just to be clear the idea is to keep all those
details out of the UI layer and in a separate data access layer. By doing
it that way alternative UIs (a browser for instance) can access the same
data using the same routines. And the data itself can be stored in text
files, .DBF files, SQL Server or fetched from an online commercial

database without changing the UI.

I just noticed you're in update New York? I'm in New York City.

Tom

.

[Fergus Cooney]

Hi Joe,

I'm working on your Levels problem - it's looking like the only way to
get all the points that you want is to have objects rather than shared
classes. Can you live with that, I wonder. ;-)

Regards,
Fergus

[Tom Leylan]

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote...

Tom,
I was in NYC on business this week.
Didn't you post a comment in the CodeSmith groups?
Or was it CSLA?

It was CSLA... I feel a little dumb explaining data abstraction now :-) Hey
I didn't know...

Tom

[Joe Fallon]

Fergus,
Thanks for taking a look at it.

1. The current thinking is that we cannot live with using instantiated
objects.

2. We have assumed there is no ideal solution and are prepared to live with
it as is.
(The developers should be trained to only use the 4th level class in their
code.)

There is actually some usefulness internal to levels 1-3 that allows methods
to be called directly.
e.g. If Level 1 needs a string from Level 2 it can just call for it. (The
alternative is to hard code the string.)
My co-worker doesn't like this as he is afraid that Level 4 might have
overridden the Level 2 method and a direct call to Level 2 would then be
inaccurate. However, I argue that the strings are so straight forward that
we could just as easily hardcode them.

At any rate, it would be worth knowing if there is no way around this issue
so we can just put it to bed and move on.

================================================== ===================

One last point - we are implementing CSLA. Rocky Lhotka made a comment on
his site to the effect that the use of Shadows is "a hack" and that any code
that implemented it was not well thought out. I do not have enough knowledge
to challenge that comment plus I respect Rocky's opinion. Do you have any
insights on this? To me Shadows is the same thing as Overrides. It is just
that Shadows is used for Shared methods and Overrides is used for classes
that get instantiated as objects. Am I oversimplifying this?

Thanks for your input!
--
Joe Fallon

"Fergus Cooney" <fi****@post.com> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

Hi Joe,

I'm working on your Levels problem - it's looking like the only way to
get all the points that you want is to have objects rather than shared
classes. Can you live with that, I wonder. ;-)

Regards,
Fergus

[Joe Fallon]

1.Here is the context in which Rocky made his comment:
http://www.searchcsla.com/Details.aspx?msn_id=2708
It is the final message on the page.

2. Your comments confirm that we have to live with the existing behavior.
Which is good to know!

3. The 'cannot live with' option is simply shorthand for saying we don't
want to go down the road of instantiating objects all over the app when the
Shared methods look and feel so nice. Since my co-worker has already been
down the instantiating route in an older language he really loved the
cleanness of "just using" shared methods. So the decision to move forward
with this strategy has been made and we don't want to change to the other
style. If this strategy didn't work at all then that would be a different
story and of course we could live with another solution.

4. Level 1 is the Base class. Normally it would not know of Level 2's
existence as you stated. That is why we asked you to look into a way to hide
those methods. But they are Public so every level really does know about
every other level. This is the "problem" we have decided to live with in
order to use the Shared methods strategy.

5. "Level 4 should give everything that 3, 2 and 1 do expect that which is
shadowed. Anything shadowed that is still required could have a Level 4
access method which would allow it to stay hidden from direct access."
But Level 4 is in another .dll and use of it from Level 1-3 would require a
reference to the Level 4 .dll which would be circular since Level 4 already
references Levels 1-3.
I guess this is "cheating" on my part. Hard coding in this case would be
more honest since I abused the "problem" in #4 above right off the bat!

6. I will try to look at your solution when I get some free time. Off on
another business trip for a few days.

7. Thanks so much for taking the time to look into this. You have really put
our minds at ease on this topic. We really like the functionality and can
live with the shortcomings.

--
Joe Fallon

[Fergus Cooney]

Hi Joe,

Thanks for the Rocky link. He's talking about Shadows with reference
to Factories and Developer classes and what have you - ie, he's being
specific. So he may or may not think that in your case. But class methods
can't use Overrides, and Overrides is a less efficent version of Shadows in
your hierarchy. So no worries there, I'd say.

|| Your comments confirm that we have to live with the existing
|| behavior. Which is good to know!

I wasn't actually saying that..and am just about to refute it! ;-)

|| we don't want to go down the road of instantiating objects all
|| over the app when the Shared methods look and feel so nice.
|| Since my co-worker has already been down the instantiating
|| route in an older language he really loved the cleanness of "just
|| using" shared methods

The solution I've got instantiates just one object within itself and in use
it has exactly the same look and feel as a shared class because there is
only a single variable (called AcctDAO) available (from a tiny one-line
module). As clean as shared classes but with nothing exposed below
level 4.

|| Level 1 is the Base class. Normally it would not know of Level 2's
|| existence as you stated. That is why we asked you to look into a
|| way to hide those methods. But they are Public so every level really
|| does know about every other level

That's true in a strict sense, but whoever works on any level must
'ignore' any knowledge they have of the later levels and refrain from
using their methods. Otherwise why have levels?

With the version I have, nothing is available below Level 4 - except
to Level 4 itself. Anything Public in 1, 2, 3 is strictly look-but-can't-touch.

|| But Level 4 is in another .dll and use of it from Level 1-3 would
|| require a reference to the Level 4 .dll which would be circular
|| since Level 4 already references Levels 1-3.

Ah! Do you mean you want 4 availble to 1, 2 and 3?? Or are we
talking at cross-purposes here?

|| I guess this is "cheating" on my part. Hard coding in this case
|| would be more honest since I abused the "problem" in #4 above
|| right off the bat!

Damn right it's cheating!! This is a new wrinkle to the problem. What
are 1, 2, 3 doing with information about Level 4? What are you trying
to achieve? :-( (lol)
..
|| I will try to look at your solution when I get some free time.
|| Off on another business trip for a few days.

Have fun again! I hope you pop in before you go and answer that
last part above before you go - I might be able to incorporate it. Then
I'll post your project back.

Oh, one last question - what control do you have over Level 2? Is it
totally auto-generated or can you specify methods and fields that can
be incorporated, etc?

Regards,
Fergus

[Joe Fallon]

" That's true in a strict sense, but whoever works on any level must
'ignore' any knowledge they have of the later levels and refrain from
using their methods. Otherwise why have levels?"
I agree. The developers should not "know" about them or use them.
=============================================
"With the version I have, nothing is available below Level 4 - except
to Level 4 itself. Anything Public in 1, 2, 3 is strictly
look-but-can't-touch."
Where did this come from??? That was the original problem. Did you solve it?
=============================================
"Ah! Do you mean you want 4 availble to 1, 2 and 3?? Or are we
talking at cross-purposes here?"
Level 1-3 contain other methods. Some of these methods/properties need to
contact the database so I need a SQL string. If I am in Level 1 and need a
string I can either hard code it or "cheat" and use one that exists in Level
2 or 3.
(I can't get to 4 due to a circular reference since it is in a separate .dll
that refers to the the .dll that contains
Level 1-3.) The UI code should ONLY ever use Level 4.
=============================================
"Oh, one last question - what control do you have over Level 2? Is it
totally auto-generated or can you specify methods and fields that can
be incorporated, etc?"
Level 2 code is generated by Codesmith. (Which is a FANTASTIC free tool, by
the way.)
I have complete control over what goes in to Level 2.
It usually has the 4 CRUD methods (with some overloads) plus 4 semi-standard
methods with 3 overloads each.
As we think up more methods that apply to (almost) every table we will add
them to Codesmith and re-generate Level 2. This is why we need level 3 to be
separate. It contains methods that are unique to a particular table and they
are hand written. We do not want to lose those changes when we re-generate
the code.
Partial classes may resolve this issue in the future in terms of number of
levels but it won't change the number of separate files. Codesmith does not
insert into Regions so I can't target a section of an existing file.
=============================================

--
Joe Fallon

[Fergus Cooney]

Hi Joe,

|| That was the original problem. Did you solve it?

I did, but I have to use objects rather than shared classes. But
it looks just like shared classes, so I hope it's ok.
|| Where did this come from???

I said two posts ago:

~ The solution that I'm looking at has an object AcctDAO which is
~ at Level 4. Level 4 shadows anything below it. Levels 3, 2 and 1
~ are completely inaccessible. Levels 3, 2 and 1 live in their own dll
~ while Level 4 can also have its own dll or be with the App.

But I guess I said too much and you went straight past it!

|| Level 1-3 contain other methods. Some of these methods/properties
|| need to contact the database so I need a SQL string. If I am in Level
|| 1 and need a string I can either hard code it or "cheat" and use one
|| that exists in Level 2 or 3.

That was the first fly in the ointment. But there's no problem if
these are only dumb properties (ie. no action). Properties can be
passed down to the lowest levels from above. For example, if your
programmers try and access the Instance property of any level which
has a higher level, an Exception will be thrown which tells them that
this level has been superceded and gives the name of the topmost
level - but without knowing it until the topmost tells it.

The same mechanism can be used to pass any value down. As long
as these things are anticipated in the first place, all levels can utilise any
property that may be defined by a later level (even next year's). If it's
necessary, I think a way can be found to apply this to methods as well
as properties. We can talk about it when you come back.

|| The UI code should ONLY ever use Level 4.

Yes. All application code will <only> be able use Level 4. It it tries
with the other levels and will get the Exception mentioned above.

|| Level 2 code is generated by Codesmith. I have complete
|| control over what goes in to Level 2.

That's good. That was the second potential fly in the ointment.

|| Codesmith. (Which is a FANTASTIC free tool, by the way.)

;-) I shall check it out, thanks.

Regards,
Fergus

[Joe Fallon]

Hi Fergus,
I am finally caught up from my latest business trip!
Now I can get back to the really interesting stuff.

I got your project on Shadows vs. Overrides.
I would like to clarify a few points on that topic when you get some time.

I am also interested in the singleton solution you mentioned.
It would be good to learn that startegy in case I need a fallback position
for some unforeseen reason.

--
Joe Fallon
"Fergus Cooney" <fi****@post.com> wrote in message
news:OB**************@TK2MSFTNGP12.phx.gbl...

Hi Joe,

I'd want to add maintainability and utility (which includes memorability) to the mix. Now we have a four-dimensional space. In practice in that whole arena, I imagine that you'd find most cases clustered around the same little area (as judged over the whole space). Zooming in you'd perhaps discover some overall patterns, but really, each case has to be given its position based on its own merits.

Each of these qualities is subjective. There comes a point where one more overload tips the scale for Joe, but Richard could happily add two more.
Neither are right. Next month, when Martin joins the project, he has to learn either the complete list of overloads or the limits of the all-encompassing parameter.

Are you really using table1 and field1, field2, etc. That's a better area for improvemnt I would have thought. ;-)

But if you're looking for personal opinions, I'd go for maximum
flexibility

Public Shared Function GetList (ByVal strSQL As String) As String
Return strSQL
End Function
SCNR ;-)

Regards,
Fergus

[Joe Fallon]

Hi Fergus,
I am finally caught up from my latest business trip!
Now I can get back to the really interesting stuff.

I got your project on Shadows vs. Overrides.
I would like to clarify a few points on that topic when you get some time.

I am also interested in the singleton solution you mentioned.
It would be good to learn that startegy in case I need a fallback position
for some unforeseen reason.

--
Joe Fallon
"Fergus Cooney" <fi****@post.com> wrote in message
news:OB**************@TK2MSFTNGP12.phx.gbl...

Hi Joe,

I'd want to add maintainability and utility (which includes memorability) to the mix. Now we have a four-dimensional space. In practice in that whole arena, I imagine that you'd find most cases clustered around the same little area (as judged over the whole space). Zooming in you'd perhaps discover some overall patterns, but really, each case has to be given its position based on its own merits.

Each of these qualities is subjective. There comes a point where one more overload tips the scale for Joe, but Richard could happily add two more.
Neither are right. Next month, when Martin joins the project, he has to learn either the complete list of overloads or the limits of the all-encompassing parameter.

Are you really using table1 and field1, field2, etc. That's a better area for improvemnt I would have thought. ;-)

But if you're looking for personal opinions, I'd go for maximum
flexibility

Public Shared Function GetList (ByVal strSQL As String) As String
Return strSQL
End Function
SCNR ;-)

Regards,
Fergus