C# Design Patterns – Best Place to Write SQL Queries

cdesign-patternsnetsql

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.

Best Answer

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.

Related Topic