2

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.

Improve this question
2
  • 1
    "We are not using stored procedures" why not? Commented Sep 24, 2010 at 13:37
  • @GôTô, we are not using stored procedures because that decision came from upper management.... Commented Apr 4, 2012 at 6:59

4 Answers 4

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Shekhar, I agree with @Developer Art. Sounds to me like the org has locked you in to non best practices.
I guess so. I advised about using stored procedures but... anyways, I think in this situation best I can do is to change queries once again.
1

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.