app.config + MANY SQL string

  • Thread starter Thread starter Mart
  • Start date Start date
M

Mart

Hi everybody,

I plan to have a LOT of SQL string in my app. So I need your advice for
this.

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.

Also, any forms needing its recordset to fill its fields, could access
easily the app.config and execute the correspondant SQL string.

Even more, it could be used to avoid software piracy by encrypting the
app.config file, so nothing would work if it is copied (I don't plan to
commercialise, but just personnal challenge to make it bullet proof on
every side).

Thanks you for you taught :)

Mart
 
Mart said:
Hi everybody,

I plan to have a LOT of SQL string in my app. So I need your advice for
this.
Click to expand...

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.
Click to expand...

No. Queries are source code, not configuration data.
If you change a query in your application, you should have to recompile.

David
 
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
 
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
 
Hi Joe,

Thank you very much for this great answer. If i understand, you suggest
that I implement a DB wrapper? Is it the right term?

I'm not sure I understand everything so here is some questions:

1- I understand to create a class for each table, but what happen when I
have SQL query that need several table to extract data? It is involving
many relations between tables.

2- What happen when I have a SQL query that is made dynamically. I mean
constructed within the code?

Thank you for answering my questions.

Have a nice day.

Mart



Joe said:
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
Click to expand...
 
1. It depends on which table is the one you would normally associate with
the query.
e.g. 2 tables - CostCenter and PO. When you link them together are you
loking for information about the PO or the CostCenter? If the PO then you
put the query in the PO class. (3rd level - where the hand coded stuff
goes!)

2. You should dynamically construct the input parameters not the SQL string
itself. Then call the string using the right set of input parameters.

e.g. You build up the Field=Value list into a string and pass the whole
thing to this method:

strSQL = GetList("costcode='123' AND status='A')
Public Shared Function GetList(ByVal whereClause As String) As String

strSQL = "SELECT costcode,ccname,status,userid,tstamp FROM costcenter "

If whereClause <> String.Empty Then

strSQL &= "WHERE " & whereClause

End If

Return strSQL

End Function


--
Joe Fallon


Mart said:
Hi Joe,

Thank you very much for this great answer. If i understand, you suggest
that I implement a DB wrapper? Is it the right term?

I'm not sure I understand everything so here is some questions:

1- I understand to create a class for each table, but what happen when I
have SQL query that need several table to extract data? It is involving
many relations between tables.

2- What happen when I have a SQL query that is made dynamically. I mean
constructed within the code?

Thank you for answering my questions.

Have a nice day.

Mart



Joe said:
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
Click to expand...
Click to expand...
 
Back
Top