The best way to organize work with database

Question

I wonder what is the best way to organize working with database from C# code.

I used to have different approaches for that:

1. Each object has Save, Update and Delete methods, which implemented all the logic.
2. There was some static class that has static methods Get, GetList, Update -- almost the same method as previous, but database logic was extracted from data domain class.

Now I think it will be great to have some non-static classes for each of datadomain class I need to store somewhere, that will do the storage.

What is the best way to do it? Are there any good libraries that provide good API for storing objects?

Answer 1

Use a Object/Relation Mapper (ORM). My personal favorite is nhibernate. Use Entity Framework if you want a Microsoft product.

ORM's usually implement UnitOfWork and Repository patterns which makes it easier for you to handle database operations and follow the Single Responsibility Principle.

You also mentioned singletons. I would avoid them if possible. Have you tried to unit test a class that uses singletons? It's impossible unless the singleton is a proxy for the actual implementation. The easiest way to remove singletons is to invert dependencies by using dependecy injection. There are several containers available.

Answer 2

The recommended way to work with databases these days is through an ORM.

Look at nHibernate (community project) and Entity Framework (Microsoft provided) for some of the most popular choices for .NET, though there are many many more.

These are libraries that provide you with all the data access that you need, with some configuration needed.

Answer 3

The repository pattern is a very common way to structure your data-access logic.

Answer 4

Both methods get you under scrutny review or fired where I work.

See:

• Separation of concerns. An object should not deal with loading or savint itseld.

• Static methos for that mean you never can have the same class in two different databases.

This is a hard problem - that is solved for 20 years using things like UnitOfWork patterns or Repository patterns. TONS of projects around for that - remove "C#" (no, guy, the world does not resuolve around one langauge) and look up Object/Relational mappers. I remember using one 20 years ago with samlltalk and writing one 10 years ago in C# at times .NET developers at large thought string manipulation is state of the art.

NHibernate, Entity Framework, heck, even BlToolkit as my preferred lightweight toolkit these days show you proper patterns.