app.config + MANY SQL string

[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

[David Browne]

Re: app.config + MANY SQL string


"Mart" <guest@guest.co m> wrote in message
news:UIqvb.1643 6$iT4.2054656@n ews20.bellgloba l.com...[color=blue]
> Hi everybody,
>
> I plan to have a LOT of SQL string in my app. So I need your advice for
> this.[/color]

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"
[color=blue]
> 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.[/color]

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

David

[Mart]

Re: app.config + MANY SQL string

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:
[color=blue]
> "Mart" <guest@guest.co m> wrote in message
> news:UIqvb.1643 6$iT4.2054656@n ews20.bellgloba l.com...
>[color=green]
>>Hi everybody,
>>
>>I plan to have a LOT of SQL string in my app. So I need your advice for
>>this.[/color]
>
>
> 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"
>
>[color=green]
>>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.[/color]
>
>
> No. Queries are source code, not configuration data.
> If you change a query in your application, you should have to recompile.
>
> David
>
>[/color]

[Joe Fallon]

Re: app.config + MANY SQL string

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 CostcenterGener atedClass
Inherits BaseClass

Public Shared Function Delete() As String
If mDBType = "SQL Server" Then
strSQL = "DELETE FROM costcenter WHERE costcode=@costc ode"
ElseIf mDBType = "Oracle" Then
strSQL = "DELETE FROM costcenter WHERE costcode=:costc ode"
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 CostcenterSQLSt rings
Inherits CostcenterGener atedClass

#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=@costc ode"
ElseIf mDBType = "Oracle" Then
strSQL = "SELECT costcode,ccname FROM costcenter WHERE
costcode=:costc ode"
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 = CostcenterSQLSt rings.Delete

--
Joe Fallon



"Mart" <guest@guest.co m> wrote in message
news:T%tvb.1786 3$iT4.2098500@n ews20.bellgloba l.com...[color=blue]
> 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:
>[color=green]
> > "Mart" <guest@guest.co m> wrote in message
> > news:UIqvb.1643 6$iT4.2054656@n ews20.bellgloba l.com...
> >[color=darkred]
> >>Hi everybody,
> >>
> >>I plan to have a LOT of SQL string in my app. So I need your advice for
> >>this.[/color]
> >
> >
> > 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"
> >
> >[color=darkred]
> >>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.[/color]
> >
> >
> > No. Queries are source code, not configuration data.
> > If you change a query in your application, you should have to recompile.
> >
> > David
> >
> >[/color]
>[/color]

[Mart]

Re: app.config + MANY SQL string

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 Fallon wrote:[color=blue]
> 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 CostcenterGener atedClass
> Inherits BaseClass
>
> Public Shared Function Delete() As String
> If mDBType = "SQL Server" Then
> strSQL = "DELETE FROM costcenter WHERE costcode=@costc ode"
> ElseIf mDBType = "Oracle" Then
> strSQL = "DELETE FROM costcenter WHERE costcode=:costc ode"
> 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 CostcenterSQLSt rings
> Inherits CostcenterGener atedClass
>
> #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=@costc ode"
> ElseIf mDBType = "Oracle" Then
> strSQL = "SELECT costcode,ccname FROM costcenter WHERE
> costcode=:costc ode"
> 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 = CostcenterSQLSt rings.Delete
>[/color]

[Joe Fallon]

Re: app.config + MANY SQL string

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("costco de='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" <guest@guest.co m> wrote in message
news:ok4xb.993$ j94.14937@news2 0.bellglobal.co m...[color=blue]
> 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 Fallon wrote:[color=green]
> > 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[/color][/color]
all[color=blue][color=green]
> > 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[/color][/color]
or[color=blue][color=green]
> > it could have additional hand written methods in it that are specifc to[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
your[color=blue][color=green]
> > hand coded SQL commands!
> >
> > e.g. this could be (part of) a generated class that inherits from[/color][/color]
BaseClass:[color=blue][color=green]
> >
> > Public MustInherit Class CostcenterGener atedClass
> > Inherits BaseClass
> >
> > Public Shared Function Delete() As String
> > If mDBType = "SQL Server" Then
> > strSQL = "DELETE FROM costcenter WHERE costcode=@costc ode"
> > ElseIf mDBType = "Oracle" Then
> > strSQL = "DELETE FROM costcenter WHERE costcode=:costc ode"
> > End If
> > Return strSQL
> > End Function
> >
> > End Class
> >
> >
> > This is a a 3rd level class that "overrides" the behavior of the 2nd[/color][/color]
level[color=blue][color=green]
> > Delete function by hiding it using the Shadows keyword. It also shows[/color][/color]
how to[color=blue][color=green]
> > add a user defined method named Select (which probably should be[/color][/color]
generated[color=blue][color=green]
> > but this is just an example. Also Select is a key word so by enclosing[/color][/color]
it in[color=blue][color=green]
> > brackets you tell the compiler to ignore that problem.)
> >
> > Public Class CostcenterSQLSt rings
> > Inherits CostcenterGener atedClass
> >
> > #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=@costc ode"
> > ElseIf mDBType = "Oracle" Then
> > strSQL = "SELECT costcode,ccname FROM costcenter WHERE
> > costcode=:costc ode"
> > 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 = CostcenterSQLSt rings.Delete
> >[/color]
>[/color]