Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I've been working on this project for my company. Currently I am embedding my SQL statements inside the program itself as and when they are needed. I do have two seperate classes -

  1. QueryBuilder Class (Contains all the queries)
  2. DbConnection Class (Executes the queries against the database, takes care of connectivity and such)

    What this does, is whenever I need to make the smallest adjustment in the query, I have to rebuild the entire application.

I'm hoping there are better ways to deal with this. Somewhere I can store these queries, get them, pass the parameters as command parameters, then execute them and if I have to, change them without having to rebuild my application.

There are a couple of ideas in my head.

  1. Resource files.
  2. Seperate DLL/Class files - Disadvantage is that, I'll have to rebuild that class if I make a change to that code. But the seperation does have it's advantage.
  3. Text files, May get unmanageable when queries increase.

I'd like to know if there is a better way to deal with this issue and if others have any way of circumventing this.

share|improve this question
2  
What is wrong with rebuilding the app after material changes? –  Wyatt Barnett May 12 '13 at 18:36
    
hope your using a connection pool. we generally have sql inside the code. how often do you think you would need to change the sql and not the code? another way would be to call stored procedures for all SQL –  tgkprog May 12 '13 at 19:09
3  
You haven't even mentioned some of the most popular choices, like object-relational mapping, or a service-oriented architecture, or stored procedures, or LINQ... There's a lot of options out there, and this kind of architectural choice is worth a bit of deliberation. –  Kilian Foth May 12 '13 at 19:25
    
I second the comment by @KilianFoth, you should have a think about Entity Framework and others... having hand-crafted SQL queries is generally viewed as a last resort these days, particularly on the Microsoft stack. Their entire strategy is built on top of EF, including their entire reporting stack. –  guysherman May 12 '13 at 19:41
1  
I can't use EF or LINQ for this project. A lot of our projects still use good old SQL queries. And yes, I'm using parameterized queries. –  Abijeet Patro May 13 '13 at 4:58
show 3 more comments

1 Answer

up vote 5 down vote accepted

Resource files, as well as a simple App.config file may do the work.

Having the queries in a separate library is a good way too, especially if you're using it as a plugin (and so you don't have to recompile the application itself, nor even restart it if it's currently running). Since the library contains only constants, compiling it would take at most a few milliseconds.

As for text files, I don't see any benefit, given that the drawback is that you have to reinvent the wheel, i.e. invent your own format, while resource files or App.config already provide that.


Note: be very, very careful. Changing queries is something that should be taken seriously, since it can radically change the behavior of the application (and, why not, wipe out the whole database or table because of a mistake like a forgotten where in a delete from). I would highly recommend not only to recompile the application, but also to rerun the integration tests (and unit tests, if there are unit tests which deal with the database directly).

Security is another concern. You have to be sure that only authorized persons would be able to modify the queries. For example, it would be a disaster to put SQL queries in a way that an ordinary user of a desktop application can modify them: users would imagine they are experts in SQL, modify the queries in a way that it will wipe out their database, and once a disaster occurs, they will blame your product instead of their own stupidity.

share|improve this answer
    
It's not so likely that users will wipe out the database with their modifications, but it is pretty likely that they will write monstrously inefficient queries that bring the entire server down under the load. –  Aaronaught May 12 '13 at 20:15
    
Thank you, I'll wait for a day for some more suggestions before selecting this as an answer. I like the idea of having a seperate library. –  Abijeet Patro May 13 '13 at 4:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.