0

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.

Improve this question
10
  • 2
    What is wrong with rebuilding the app after material changes?
    – Wyatt Barnett
    May 12, 2013 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, 2013 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, 2013 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, 2013 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, 2013 at 4:58

2 Answers 2

Reset to default
8

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.

Improve this answer
2
  • 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, 2013 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, 2013 at 4:58
0

Why not build stored procedures and have your query library use them? As long as you have a defined set of parameters that you don't change, and read from named output fields not column ID's, you should be golden if you need to to tweak the SQL in the queries.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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