473,581 Members | 6,733 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Storing SQL

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.

--
-Demetri
Feb 15 '06 #1
5 2548
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).

Feb 16 '06 #2
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>
--
-Demetri
"sd********@gma il.com" wrote:
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).

Feb 16 '06 #3
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.

Feb 16 '06 #4
Just curious here about why you are forbidden to use stored procedures.
What is the reasoning that you were given?

"Demetri" <De*****@discus sions.microsoft .com> wrote in message
news:66******** *************** ***********@mic rosoft.com...
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.

--
-Demetri

Feb 16 '06 #5
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.

--
-Demetri
"Jeff S" wrote:
Just curious here about why you are forbidden to use stored procedures.
What is the reasoning that you were given?

"Demetri" <De*****@discus sions.microsoft .com> wrote in message
news:66******** *************** ***********@mic rosoft.com...
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.

--
-Demetri


Feb 16 '06 #6

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

Similar topics

3
3038
by: Mark | last post by:
I have a website with an increasing amount of articles and news reports and so I am thinking of moving away from storing each article as a seperate page to having a single page and storing articles in a databasewhich are retrieved using a GET parameter. I see the advantage to me in using this approach as being making use of MySQL's fulltext...
4
3864
by: Brian Burgess | last post by:
Hi all, Anyone know of any special issues with storing cookies with ASP? I'm trying this with two browsers: One is IE 6.0 with cookies set to 'prompt'. This has been working properly as any new site I goto seems to prompt me to store their cookie. The other is Pocket IE on Pocket PC 2002, with the cookies set to 'enabled'. My...
6
2561
by: Alfonso Morra | last post by:
I have written the following code, to test the concept of storing objects in a vector. I encounter two run time errors: 1). myClass gets destructed when pushed onto the vector 2). Prog throws a "SEGV" when run (presumably - attempt to delete deleted memory. Please take a look and see if you can notice any mistakes I'm making. Basically,...
5
2134
by: Don Vaillancourt | last post by:
I'm building a system when one can upload a document to the website. I will be storing the document on the hard-drive for quick/easy access, but I was also thinking of storing it in an existing database since most of the sites information is all stored there. As well there would be only one place to worry about backing up. And if the...
6
3039
by: bissatch | last post by:
Hi, I am currently writing a news admin system. I would like to add the ability to add images to each article. What I have always done in the past is uploaded (using a form) the image to a folder on the server and then in the database table that I INSERT the news article, I'll store the path of the uploaded image. To me this seems a...
4
2719
by: Rednelle | last post by:
Greetings all, As a newbie, using Access 2000, I would appreciate advice on the best way to include pictures. I have developed a 'Home Inventory' database which can include jpeg thumbnails of my items and I can review things at anytime - on the desktop where I created all this - no problem so far. I networked the mdb file (only) across...
2
1944
by: Robert Hanson | last post by:
I am new to the asp.net application building and I have read the information regarding the storing of information using session vs cookies vs viewstate. I am asking for suggestions/guidance as to when each is appropriate. I noticed that in the case of cookies, there is a liability because of web browser settings migh disallow the storing of...
2
5026
by: jakk | last post by:
Below is the exception that Iam getting. It says that the DataView that Iam storing in the session is not Serializable. BUt works fine if I store in the inproc session and fails if I switch to storing the session in Sql Server. Please let me know if Iam doing anything wrong. Unable to serialize the session state. Please note that...
6
3188
by: (PeteCresswell) | last post by:
User wants to go this route instead of storing pointers in the DB and the documents outside. Only time I tried it was with only MS Word docs - and that was a loooong time ago - and it seemed to me like there were performance issues at the time. How about the different types? The MS docs I would expect Access to differentiate and handle...
2
1998
by: Mythran | last post by:
We followed an example found on MSDN to create an encrypted FormsAuthenticationTicket and storing the ticket in a cookie. Is this the "correct" way to store the authentication ticket? We are attempting to create a web service from the web application and our goal is to have the user login to the web application and then, using the same...
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8296
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6539
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...
1
5665
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5350
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...
0
3794
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...
0
3807
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2295
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1394
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.