Should queries live inside the classes that need the data? Should queries live in stored procedures in the database so that they are reusable?
In the first situation, changing your queries won't affect other people (either other code or people generating reports, etc). In the second, the queries are reusable by many and only exist in one place, but if someone breaks them, they're broken for everyone.
I suggest placing them as stored procedures in the database. Not only will you have the re-use advantage but also you'll make sure the same query (being a select, update, insert, etc...) it is the same because you are using the same stored procedure. If you need to change it, you'll only change it in one place. Also, you'll be taking advantage of the database server's processing power instead of the server/computer where your application resides. That is my suggestion.
Good luck!
I used to be big on the stored proc solution but have changed my tune in the last year as databases have gotten faster and ORMs have entered the main stream. There is certainly a place for stored procs. But when it comes to simple CRUD sql: one liner insert/update/select/delete, using an ORM to handle this is the most elegant solution in my opinion, but I'm sure many will argue the other way. An ORM will take the SQL and DB connection building plumbing out of your code and make the persistence logic much more fluidly integrated with your app.
It depends / it's situational. There are very strong arguments for and against either option. Personally, I really don't like seeing business logic get split between the database (in stored procedures) and in code, so I usually want all the queries in code to the maximum extent possible.
In the Microsoft world, there are things like Reporting Services that can retrieve data from classes/objects instead of (and in addition to) a database / stored procedures. Also, there are things like Linq that give you more strongly typed queries in code. There are also strong, mature ORMs like NHibernate that allow for writing pretty much all types of queries from code.
That said, there are still times when you are doing "rowset" types of things with your queries that work much better in a stored procedure than they work from code.
In the majority of situations, either/both options work just fine.
From my perspective, I think that stored procs are the the way to go. First, they are easier to maintain as a quick change to one means just running the script not recompiling the application. Second they are far better for security. You can set permissions at the sp level and not directly on the tables and views. This helps prevent fraud because the users cannot do anything directly to the datbase that isn't specified in a stored proc. They are easier to performance tune. When you use stored procs, you can use the database dependency metadata to help determine the affect of database changes on the code base. In many systems, not all data access or or even CRUD operations will take place through the application, having the code there seems to me to be counterproductive. If all the data access is in one place (an idea I support), it should be in the database where it is accessible to all applications or processes that might need to use it.
I've found that application programmers often don't consider the best way for a database to process information as they are focused on the application not the backend. By putting the code for the database in the database where it belongs, it is more likely to be seen and reviewed by database specialists who do consider the database and it's perfomance. We support hundreds of databases and applications here. I can look in any database and find the code that I need to find when something is slow. I don't have to upload the application code for each of hundreds of different applications just to see the part I need to do my job.