I have an MVC3 project that uses SQL Server.I use data from the SQL database all the time and I often find that I'm reusing/duplicating some SQL queries. I thought I'd solve this problem by creating a few static helper classes that just contain a bunch of static methods for retrieving common things.GetAllUsers(database As MyEntity)GetAllNonActivatedUsers(database As MyEntity)GetUser(database As MyEntity, userId As Integer)The problem is that this made things slightly worse. Now instead of having SQL queries all over my controller actions, there are large numbers of them in these helper classes. The names of these methods are becoming silly.GetPendingUserApplicationByApplicationId(database as MyEntity,    applicationId As Integer, userId As Integer)At this stage I'm thinking of scrapping the helper classes and going back to just random SQL queries throughout my controller actions.Where have I gone wrong and how do people manage their SQL queries?Is it ok to duplicate SQL queries?
How do I handle having so many SQL queries?
code quality;refactoring;asp.net mvc;vb.net;code smell
Well, first of course you are going to have lots of queries because you expect the aplication to do lots of things. Databases have a couple of things that can help but you can make things worse by using them badly. ORMs are one tool that will help write the queries for you. But you will still have a lot of queries if you havea lot of database work that needs to be done.Next you can use Views to create some of the main things you will want to use over and over. Views are good for complex things that you wil need to make sure have the same calculations in multiple situations. For instance we have views that collect all the data from various table for financials whcih ensures that all finaicial data is based on the same set of busines logic. Do not however use views to call views.You can use stored procedures and then just call them in multiple places. (Make sure you put them in source control though, sps are code!).However, you may have to learn to live with the fact that there is lots of code. Our database has well over a thousand stored procs. Organizing them by schema has helped. If your database doesn't have schemas, organizing by using a systematic naming convention heps. that way you know allthe things that relate to finance have the word finaince in them and those relating to users have user in them. That helps you to find the one you arelooking for when you need to reuse it. Code reuse can be a wonderful thing, but there is a very strong caveat when querying databases. If the queries are slightly different, then they must be two separate queries rather than one that sends more data than the application needs at that point. Adding fields you don't need to queries so you can use them elsewhere can cause horrible performance issues that are very hard to correct (I just spent several days trying to performance tune such a mess and ended up eliminating over 400 lines of unnecesary SQL! I also improved performance by cutting the time to excute more than 60%). So don't go on that path just to have fewer queries. More specific queries is generally better than fewer general ones for database performance.  