What is best practice for storing SQL code for an app

[Guest]

I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this string
so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

Thanks
Mike

 

[David Browne]

I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this string
so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

I think you have the best practice.

It used to be considered best practice to break queries over multiple lines
with string concatenation and line continuation. It was thought that this
made queries more "readable". This was crap. And the only excuse for it
was that there was a limit to the length of a line of VB code, so you
couldn't store the SQL on one line anyway. This also encouraged the bad
habit of binding parameters into your query through string concatenation.

In VB.NET lines can be as long as you want, and using parameter markers in
your queries is much easier. So SQL queries should be CONST's and written
all on one line. Like this

CONST sqlGetCustomer as string = "select * from customer where id = ?"

This seperates the SQL from the VB, and makes it easy to cut and paste the
SQL out and work on it in a database tool, then paste it back in.

It's also best practice to minimize the complexity of SQL stored in your
application.
Complicated joins and procedural logic (for databases that support that),
should be pushed down into the database as much as possible. For instance
if you have a query that joins 8 tables, create an Access QueryDef in the
database, and then do a simple select from that in your VB code. This
limits the amount of mixing of different languages in your source code.

Interleaving different languages is bad form, and makes both of the
languages hard to read and maintain. This is one big reason "old" ASP
sucked: one file would contain a tangled mess of VBScript, HTML, JavaScript,
and possibly SQL. And this is also why you shouldn't break your SQL queries
over multiple lines with string concatenation and line continuation.


David

 

[Charles Law]

Hi David

It has always been my practice to split SQL by keyword. For example,

SELECT Column1, Column2, ColumnN
FROM MyTable
WHERE ColumnX = SomeValue
ORDER BY Column2

Personally, I find it easier to read and maintain this way, and it has
nothing to do with limits on line length. Only if the string exceeds the
screen width would I continue on a new line (not with a continuation line
though), just to avoid having to scroll left and right in order to view the
whole string.

Charles

 

[Fergus Cooney]

Hi David,

I second Charle's method - split for readability.

Regards,
Fergus

 

[David Browne]

Hi David

It has always been my practice to split SQL by keyword. For example,

SELECT Column1, Column2, ColumnN
FROM MyTable
WHERE ColumnX = SomeValue
ORDER BY Column2

I do this in SQLServer but in VB this looks like

sqlQ = "SELECT Column1, Column2, ColumnN " & _
"FROM MyTable " & _
"WHERE ColumnX = SomeValue " & _
"ORDER BY Column2 "

Which I think is a real hastle to maintain.

Also I think everyone can agree that keeping embedded SQL as short and
simple as possible is the most important practice. And the shorter and
simpler it is, the less it matters how it is stored.

David

 

[Cor]

Hi Fergus,
The first time I saw something of SQL I started hating it, but I see in this
newsgroup you are very good in it.
A lot of the things you write about this and Ado.net {not the ones I
recognise now as something else } I put apart for reavaluating.
Cor

 

[Fergus Cooney]

Hi David,

Mine would be slightly different:

sqlQ = " SELECT Column1, Column2, ColumnN" _
& " FROM MyTable" _
& " WHERE ColumnX = SomeValue" _
& " ORDER BY Column2"

It's just a little bit neater on the right-hand side and ensures that I
never omit the spaces.

Regards,
Fergus

 

[Mike Stephens]

Slightly off topic. My question is, where should I store the SQL string?

What is best practice for storing the string? Should I store it as a Const,
hardcoded in my app. Should I store it in an INI / Text file? There is
potential the SQL string could change over time, and if it is hardcoded in
the EXE then then I need to replace the EXE everytime. Likewise if it is
stored in a DLL.

 

[Richard Brown]

I believe that the jist of it would be everyone is implying that the Const
version is ok.

I would take it just one minor step further, and people can scream at me for
this if they want to...
Place your sql code in a string table in a seperate resource DLL. This has
a couple of possible benefits in my mind...
If the data tables change for some reason, you can edit and redistribute the
resource DLL without having to recompile the entire EXE or other assemblies.
Also, if your program eventually supports many databases (not taking into
account the differences in actually database access programming), you can
drop new SQL syntax in via the DLL and not have to create seperate program
versions. (for example, Oracle and SQL Server have some slight differences,
Access and SQL functions are slightly different, etc).

Go ahead everyone, sock it to me.

 

[David Browne]

Slightly off topic. My question is, where should I store the SQL string?

What is best practice for storing the string? Should I store it as a Const,
hardcoded in my app. Should I store it in an INI / Text file? There is
potential the SQL string could change over time, and if it is hardcoded in
the EXE then then I need to replace the EXE everytime. Likewise if it is
stored in a DLL.

Not an ini file. That just makes things more complicated.

Either in your application (a const is fine), or in the database using a
view (access queryDef) or stored procedure.

The SQL statements in your application are the interface into your database
layer. They are source code and it is proper for changes in the interface
to require you to modify and recompile your component.

This is another reason complicated SQL should be pushed down into your
database. If your application is bound directly to the physical tables then
any schema change will require you to change and recompile your application.
In such a case, your application is "tightly bound" to your schema.

If your application is bound to views and stored procedures, however, many
changes can occur at the database level without requiring a change to the
application code. Then your app is "loosely bound" to your schema.

David

 

[Herfried K. Wagner [MVP]]

I have an app that retrieves data from an Access
database. At the moment I have the SQL string as
a Const in my app. I understand this is not best
practice. I don't want the user to have access to
read or modify this string so I don't want to store
it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

People in the ADO.NET newsgroup will tell you what the best practice is:

<
Web interface:

<http://msdn.microsoft.com/newsgroups/default.asp?url=/newsgroups/loadframes
..asp?icp=msdn&slcid=us&newsgroup=microsoft.public.dotnet.framework.adonet>

 

[Fergus Cooney]

Hi Richard,

Sock my approval to you ? Lol.

I was going to mention in my earlier post, that a separate resource is an
option for truly separating the SQL from the code. Ypu gone and listed all the
good reasons for doing so.

Now - here's a really wacky solution. :-D

Put the SQL on your web server as a Web Service. Then, not only can you
change it as-and-when without even visiting your clients, you can also monitor
their use of the program.

Regards,
Fergus

 

[David Browne]

People in the ADO.NET newsgroup will tell you what the best practice is:

No.

This is a question of VB best practice. It belongs here.
For instance in C# you can embed SQL in source code like this

string sqlQ = @"
SELECT
SOME_FIELD,
SOME_OTHER_FIELD
FROMSOME_TABLE
WHERESOME_FIELD=6
"

If you could do that in VB, it would change the best practices.

David

 

[Cor]

Fergus,
First I thought he is flaming (a new word I have learned yesterday)
This is brilliant, I did never thought about that?
Cor

 

[Richard Brown]

Oh Cor, I never 'flame', I use these newsgroups too much and have too much
respect for the people who offer their advice to do anything like that.
(Um, it may not seem like it, but I am being sincere about it).

 

[Richard Brown]

David,

Your arguments about 'pushing down' the sql into the database are 100%
correct, however, eventually, you still have the question come up of 'where
to put the original SQL statements'.

For instance, in MS Access, under certain data corruption conditions,
Queries are completely lost and must be rebuilt in the database. Where are
those query definition-definitions stored? Another program, an INI file,
where?

Also, what about SQL stored procedures? They must be run from somewhere,
normally during product installation, but still they are run. This one does
not have as much of a point as the Access problems.

In my response before, by placing the SQL code in a DLL, you are somewhat
simulating the 'querydef' idea in Access and/or views in SQL Server,
because, in some cases, without changing your application, you can change
the database structure and the sql resource library and still have the
program run properly.

For performance reasons, you can take these SQL statements and create views,
procedures, and querydefs.

 

[Cor]

Richard,
Maybe I didn't use the wrong word, but I have seen it so often in a thread
from a long discussion last night.
You should have seen that awful discussion to understand what I wrote to
Fergus.
Fergus is very active in this newsgroup and when I see his mails he
somethimes think he likes to prickle to get it sharp.
When you look in this newsgroup there are a lot of people who are very fond
with the help from Fergus.
So I thought this was a prickle one.
And it was absolutly not ment for you or either negative.
I hope you will aspect this if you thought it was in anyway some negative.
I am like Fergus, not to breaking but building.
Cor

 

[Cor]

Richard,
Now I see it, the he is not you in my post, that is Fergus.
Cor