What are best practices for database queries?

Question

Hi,

I would like to know what are best practices for database queries. I am using C#, mysql database.

I have one application in which I had hardcoded all the database queries. Now client has modified database structure. So I have to again modify those queries and as all the queries are inside code, I need to test application, build it, create installer and do many more things related to deployment.

How to avoid such problems? Where shall we keep our queries? In some plain text file or inside app.config or somewhere else?

I will prefer queries to be configurable so that when something like this happens only configuration needs to be changed, no need to change code or executables.

We are not using stored procedures and no ORM framework also.

Thanks in Advance

Thank you everyone for your valuable inputs. I think in current situation the best I can do is to modify queries and try to emphasize on stored procedures from next time.

Thanks again.

Answer 1

One way to do this would be to have some kind of communication dll that your program uses, that way you just have to change the assembly that you are calling if the database changes.

This however, unless you write that to be completeley generic, using key value pairs or something for variables you are still going to be subject to the same thing.

Another way would be through stored procedures, do all the work in the database and just call a stored porocedure to do the work.

But yet again this could still be subject to change.

Answer 2

Stored procedures solve most of these problems. Adequate schema planning (before writing code for it) also helps offset it.

The other way would be to have some sort of database API that your program calls. Separate everything out and access it via a shared library of model.

Answer 3

We are not using stored procedures and no ORM framework also.

Those would have been your real options. If you're not using them, you're out of luck.

Maybe the best practice for you would be not technical but organizational. Set up a process regarding modification of the database.

• Changes to the database have to be discussed and agreed upon by all parties involved
• Every party involved should be notified about database changes in advance to have time to react to them

Answer 4

These queries will not stop being "code" even if you put them in config files. They will still need testing regardless of where they are stored.

Answer 5

You could add a data access layer to your project and then add data sets to the data access layer. Data set (.xsd) files will help you organize your queries. You will be able to add all queries to the data set and that will be your one stop shop to edit / delete or add new queries. For example : please go to the following link

http://www.shiningstar.net/aspnet_articles/DataSet/DataSetProject6.aspx