Should queries live within the classes that require the information? Should queries reside in saved methods within the database to ensure that they're multiple-use?

Within the first situation, altering your queries will not affect others (either other code or people producing reviews, etc). Within the second, the queries are multiple-use by many people and just exist in one location, but when someone breaks them, they are damaged for everybody.

I had been large around the saved proc solution but have transformed my stay tuned the this past year as databases have become faster and ORMs have joined the primary stream. There's certainly a spot for saved procs. However when it involves simple CRUD sql: one lining place/update/choose/remove, utilizing an ORM additional is easily the most elegant solution for me, but I am sure most argue another way. An ORM will require the SQL and DB connection building plumbing from your code making the persistence logic a lot more fluidly integrated together with your application.

I would recommend placing them as saved methods within the database. You won't just possess the re-use advantage but additionally you'll make certain exactly the same query (as being a choose, update, place, etc...) it's the same since you are utilizing the same saved procedure. If you want to change it out, you'll just have to change it in one location. Also, you will be benefiting from the database server's processing energy rather than the server/computer where the application resides. That's my suggestion.

Best of luck!

It is dependent / it's situational. You will find quite strong arguments for and against either option. Personally, I truly can't stand seeing business logic get split between your database (in saved methods) as well as in code, and so i usually want all of the queries in code towards the maximum extent possible.

Within the Microsoft world, you will find such things as Confirming Services that may retrieve data from classes/objects rather than (and additionally to) a database / saved methods. Also, you will find such things as Linq that provide you more strongly typed queries in code. You will find also strong, mature ORMs like NHibernate that permit writing virtually all kinds of queries from code.

Nevertheless, you will find still occasions when doing "rowset" kinds of things together with your queries that actually work far better inside a saved procedure compared to what they work from code.

In nearly all situations, either/both options work all right.

From the perspective, I believe that saved procs would be the what you want. First, they're simpler to keep like a quick switch to one means just running the script not recompiling the applying. Second they're much better for security. You are able to set permissions in the sp level and never on the tables and sights. This can help prevent fraud since the customers canrrrt do anything straight to the datbase that is not specified by a saved proc. They're simpler to performance tune. If you use saved procs, you should use the database dependency metadata to assist determine the affect of database changes around the code base. In lots of systems, not every data access or as well as CRUD procedures will occur with the application, getting the code there appears in my experience to become detrimental. If all of the data access is in one location (a concept I support), it ought to be within the database where it's available to all programs or processes that should utilize it.

I have discovered that application developers frequently don't consider the easiest way for any database to process information because they are centered on the applying not the after sales. By putting the code for that database within the database where it goes, it is more probably to appear and examined by database specialists that do think about the database and it is perfomance. We support 100s of databases and programs here. I'm able to try looking in any database and discover the code that I have to find when something is slow. I do not have to upload the applying code for every of 100s of various programs simply to begin to see the part I have to do my job.