473,695 Members | 2,622 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

app.config + MANY SQL string

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

Nov 20 '05 #1
5 2240

"Mart" <gu***@guest.co m> wrote in message
news:UI******** *************@n ews20.bellgloba l.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
Nov 20 '05 #2
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.co m> wrote in message
news:UI******** *************@n ews20.bellgloba l.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


Nov 20 '05 #3
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" <gu***@guest.co m> wrote in message
news:T%******** *************@n ews20.bellgloba l.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.co m> wrote in message
news:UI******** *************@n ews20.bellgloba l.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

Nov 20 '05 #4
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:
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


Nov 20 '05 #5
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" <gu***@guest.co m> wrote in message
news:ok******** *********@news2 0.bellglobal.co m...
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:
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

Nov 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
3016
by: Maxim Khesin | last post by:
I want to have a config file with my python proggie, satisfying the following requirements: 1) support key->(value, default) 2) simple and intuitive to read and edit 3) easyly readable into a python datastructure (a dictionary?) 4) not requiring any heavy libraries needed (I am distributing my proggie as a py2exe executable and do not want to bloat the size) can you guys suggest some format for this? thanks, max
0
3842
by: Ant Corrie | last post by:
I am at a loss as to why this is not working. I am trying to setup a TextWriterTraceListener in my application config file and then execute the app from the network. I get a security exception (listed below) even though I have StrongNamed my application and used the .NET Configuration Tool to create a Code Access Group in the Machine Policy that gives FullTrust to assemblies with the StrongName that I created. If you uncomment the code...
1
5353
by: vkrasner | last post by:
It works with VS2003 and does not in VS2005: in VS2003 : string sMyvalue = ConfigurationSettings.AppSettings; in VS2005 (does not work!!) string sMyvalue = ConfigurationManager.AppSettings; Anybody able to give me idea how-to read by C# element which I add to the machine.config into the new single section?
3
602
by: Tim Gallivan | last post by:
Hi all, I think read somewhere (but I can't find it ... note to self: must get new filing system ...) that there is a workaround so that an app.config can have multiple keys with the same name or something of the like. What I require is the ability to point at a development or live database without changing any existing code in my multiple DLL / single form project. <add key="dtacollect.ConnectionString"...
5
2289
by: Sridhar | last post by:
Hi, I have created a project which contains classes to read the data from the database. This project has an App.Config file which contains the SqlConnection String. when this code is called from a web application and if I need to read the connection string it is reading the connection string from the web.config of web application. I am not knowing how to change the Application domain so that it reads the App.config file instead of...
8
1967
by: theWizard1 | last post by:
Using Asp.NET 1.1, and C#. I have a directory for the website, and a directory under it named Secure. I have a web.config in each of the above directories. The web.config in the Secure directory contains the connection string information for the database. How can I use ConfigurationSettings.AppSettings to show the path of the Secure directory when getting the connection string?
5
5731
by: Keith | last post by:
Hello all, I have a C# Windows Forms app. It is in namespace App.GUI. It builds to Nav.exe. I have entered an application level setting using the designer. Its type is string, name is "FOO" and value is "monkey". I've tried the following ways to retrieve the value and only one works: //Returns null Configuration config = ConfigurationManager.OpenExeConfiguration(Assembly.GetEntryAssembly().Location); string monkey =...
10
2052
by: eagle | last post by:
I have a web.config in my application that contains the connection strings to all my datasources. I want to move these connection strings to another web config up the folder hierarchy so that all my apps can use the same connection strings. That is supposed to be how it's done, no? Instead of the web.config being in c:\inetpub\wwwroot\myApp\web.config, I have it in c:\inetpub\wwwroot\web.config. However, I get an "Object reference not...
2
18139
by: bz | last post by:
Hi, I have a library project that implements a Business Layer for a web and a desktop application All my business classes are in this lib, so I have here the connection string to database as app setting (in app.config The section in app.config looks like this <applicationSettings>
2
5391
by: Johnson | last post by:
I'm trying to fix a "sub optimal" situation with respect to connection string management. Your thoughtful responses will be appreciated. I just started with a new client who has a bunch of legacy ASP.NET applications that all manage connection strings in Web.config the same way, like this: This client has one Web.config file per application, and that one Web.config file is duplicated across all environments (i.e., dev machines, test,...
0
8583
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9126
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8861
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7673
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5841
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4349
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4588
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2283
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1984
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.