What are best practices for database queries?

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

I have开发者_如何学Python 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.

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

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.

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.

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