Storing SQL

[Guest]

Unfortunately, where I work it is forbidden to use stored procedures. I know,
I know...it's pretty stupid. But I can not change that.

Anyway, currently we have our sql stored in an embedded resx file as name
value pairs. At runtime a ResourceManager class is instantiated and a key is
passed to retrieve the sql for a given key.

A group of us were discussing this problem and the performance efficiency of
using the ResourceManger since internally a HashTable is loaded up each time
you instantiate an instance of it versus perhaps creating a class with
constants and their values to use instead. <gag>

I hate the idea of the constants, it's not object oriented at all. Therefore
my question is, does anyone have any better ideas on how to store the sql
statements with performance in mind without sacraficing object oriented
design?

Just as an aside, we also are faced with the issue of dynamic where clauses.
The stored sql will need to have where clauses added to it dynamically to
allow for filtering of data. Any suggestions here? Remember, this is an
enterprise wide issue not a mom and pop shop issue.

 

[sdbillsfan]

Just out of curiouosity, what was the reasoning for storing the sql in
a resource file? I won't comment on how ridiculous the design
is...oops...but at this point there's no reason not to use a constants
class (or if you still want initial runtime loading, a static class).

 

[Guest]

Why did I suggest using a resource file versus reading from a text file, xml
file, constants class?

1. More elegant solution as far as object oriented design goes. Contants
file is generally frowned upon in today's object oriented approaches. Gone
are the VB Global variable days.(no complaints here).
2. Sql information is embedded in binary format in the assembly therefore
the schema information about the data is not as accessable (i.e. more secure
than a text file or xml file).
3. Resources can be deployed in satallite assemblies thus if a resource
value changes then you only make the change in the resource assembly thus no
harm to your applications main assembly.

I still think storing them in the resources file is the best thing versus
File IO with text files, xml files (especially with the expense of DOM). And,
as far as constants class goes...well I just think that is VERY bad practice
that reaks with "Im a VB hold out" all over it.

But if its performance that is the issue, which it is, I wonder what the
performance gain would be if we used a god constants class versus an embedded
resource and reading it via ResourceManager?

There has to be a better way tho.

I wish we could use stored procedures, it would make life easier and is
better for performance and is just an all around better approach. However,
the powers that be aren't allowing it. <sigh>

 

[sdbillsfan]

I don't understand why you consider a constants class very bad practice
and/or not object oriented. These SQL Statements are constants,
correct? It makes no sense to me when people store constants in a
dynamic manner. If something is a constant, store it as such and then
you have no worry about the performance plus the bonus of compile time
checking. As far as "updates in satellite assemblies" is concerned, put
your constants class in this assembly and it's just as easily updated.
I know you're upset about not being able to use stored procedures (I
would be too) but there's no need to add a further performance hit by
storing constants in anything but a constant/static manner.

 

[Jeff S]

Just curious here about why you are forbidden to use stored procedures.
What is the reasoning that you were given?

 

[Guest]

I was told that there is not enough resources on the data side to maintain
and take ownership of the stored procedures. And there is a bit of politics
involved, which is not something I personally want to get into. I'm a
straight shooter, tell it like it is, I'm not a political person. <shrugs>

But whatever keeps them warm at night. I just have to work with what I've got.