One idea is to put all your queries in a Class.
Use a separate class for each table in the database.
Then you could use a code generator like CodeSmith - point it at your
database and generate all the classes in a few seconds. (CodeSmith is a
fantastic, free tool!!!)
You could have a Base class that each generated class inherits so they all
have some common functionality
(like reading the connection string from the config file, getting the
database type from config, etc.)
Then create a 3rd level which inherits the generated level.
This level could be empty or it could override methods in the 2nd level or
it could have additional hand written methods in it that are specifc to the
table in question.
Any code that needs a SQL string *always* calls the 3rd level class.
The main reason for having a 3rd level is because you may wish to
re-generate the 2nd level if the DB changes and you do not want to lose your
hand coded SQL commands!
e.g. this could be (part of) a generated class that inherits from BaseClass:
Public MustInherit Class CostcenterGeneratedClass
Inherits BaseClass
Public Shared Function Delete() As String
If mDBType = "SQL Server" Then
strSQL = "DELETE FROM costcenter WHERE costcode=@costcode"
ElseIf mDBType = "Oracle" Then
strSQL = "DELETE FROM costcenter WHERE costcode=:costcode"
End If
Return strSQL
End Function
End Class
This is a a 3rd level class that "overrides" the behavior of the 2nd level
Delete function by hiding it using the Shadows keyword. It also shows how to
add a user defined method named Select (which probably should be generated
but this is just an example. Also Select is a key word so by enclosing it in
brackets you tell the compiler to ignore that problem.)
Public Class CostcenterSQLStrings
Inherits CostcenterGeneratedClass
#Region "User Defined Methods"
Public Shared Shadows Function Delete() As String
If mDBType = "SQL Server" Then
strSQL = "DELETE FROM costcenter "
ElseIf mDBType = "Oracle" Then
strSQL = "DELETE FROM costcenter "
End If
Return strSQL
End Function
Public Shared Function [Select]() As String
If mDBType = "SQL Server" Then
strSQL = "SELECT costcode,ccname FROM costcenter WHERE
costcode=@costcode"
ElseIf mDBType = "Oracle" Then
strSQL = "SELECT costcode,ccname FROM costcenter WHERE
costcode=:costcode"
End If
Return strSQL
End Function
#End Region
End Class
Whenever your code needs a SQL string you simply use the format:
TableSQLStrings.Method(Params)
e.g.
strSQLDelete = CostcenterSQLStrings.Delete
--
Joe Fallon
"Mart" <gu***@guest.com> wrote in message
news:T%*********************@news20.bellglobal.com ...
Hi David,
Thanks, I see the point. I was thinking to the architecture before
going further and I was trying to make it easier for maintenance.
I understand that queries are part of the code. Should I go ahead and
make every VB forms contain the specific SQL queries? Maybe I should
read more on the subject...Can you refer me a book or resource for good
practice about architecture involving VB.NET and DB ?
Thank you
Mart
David Browne wrote:
"Mart" <gu***@guest.com> wrote in message
news:UI*********************@news20.bellglobal.com ...
Hi everybody,
I plan to have a LOT of SQL string in my app. So I need your advice for
this.
SQL strings should be stored in your database.
Your application should only contain simple queries like
"Select * from someTable where someKey = @val"
or
"exec MyProc @val1, @val2"
Is it a good idea to store all my SQL string in the app.config file?
For a maintenance point of vue it would be easy to access and change any
SQL squery without having to open/edit any forms.
No. Queries are source code, not configuration data.
If you change a query in your application, you should have to recompile.
David