c#/.net project how to save/organize database queries

In my first c# project, I need to connect to a database server for multipl开发者_StackOverflowe read only queries. Would anyone share experiences on how to organize the queries into the project? currently I just hardcoded query strings in the c# source files whenever needed. but it is hard to maintain and once something changes on the database server side I am in trouble. Or should I put all query strings in the .config file using appsettings? Are there better ways? I do not have rights to save stored procedures on the server. thanks.

There are different answers with varying levels of sophistication based on your needs. Except in the very smallest of projects, I create two class library projects for database access: one that contains the data model and queries and another test project that exercises the first project's queries. In simple solutions, you use this library in an ASP.NET or other project.

You should strongly consider learning an ORM like NHibernate or VS 2008/.NET 3.5's Linq-To-SQL or Entity Framework. Minimally, you MUST remember to use parameterized queries if you have a web-facing app.

In more sophisticated solutions you will completely encapsulate the database into it's own service, or tier. In my experience I had a data access tier that ran in it's own Windows Communication Foundation service, as a Windows Service, and it was the only service that could talk directly to the database or knew the database's data model. It would do all the interaction with the database, and then transform the data into different data models that are read by the other tiers. I typically create a project called "Contracts" that contains all the interfaces and data models that are communicated from the data tier to the rest of the system. The reason you do this is so that you avoid the pain you have mentioned: you can update the underlying database, ORM layer, and "common data models" and then not change the other tiers at all.

If this is your first project, try to keep thinks simple. If you add too much variables probably you'll end thinking more in technology than in solutions.

That said, if your queries don't expect to change it's parameters, you can use stored procedures. This approach also will help boost your queries as the execution plan will be kept in the database.