Data Access Objects
Gather all the SQL for one table into a Data Access Object (DAO), so the rest of the program asks for models and never sees the database.
Journeyman · C# · T-SQL · Updated
After this lesson you can
- Explain what a Data Access Object is and what belongs in it
Key takeaways
- A Data Access Object (DAO) holds all the SQL and mapping for one table, and its public methods take and return models.
- Most DAOs have a method for each CRUD job (create, read, update, delete), such as
GetAll,GetById,Add,UpdateandDelete. - With a DAO, the rest of the program has no SQL in it, so a bug in a query or a change to the table is fixed in one place.
- A repository is nearly the same idea as a DAO: an object that stores and fetches models and hides how.
The connection, the SQL and the mapping are the same every time a program needs, say, all the categories. Instead of copying that code wherever it's needed, the standard practice is to put it in a class of its own: a Data Access Object, or DAO.
One DAO per table
A DAO is the program's expert on one table. It knows the table's SQL, how to map its rows to models, and nothing else. Its public methods speak in models:
| CategoryDao method | Does |
|---|---|
List<Category> GetAll() |
returns every category |
Category? GetById(int categoryId) |
returns one category, or null if there's none with that id |
int Add(Category category) |
inserts a category, and returns its new id |
void Update(Category category) |
saves changes to a category |
void Delete(int categoryId) |
deletes a category |
Those five jobs (create, read, update and delete) are known together as CRUD. Most DAOs have a method for each, plus searches the program needs, such as GetByCategory for products.
The rest of the program asks; the DAO answers
With a DAO, the code that shows categories is short and free of SQL:
CategoryDao categoryDao = new CategoryDao(connectionString);
foreach (Category category in categoryDao.GetAll())
{
Console.WriteLine(category.CategoryName);
}
This is the same idea as encapsulation: the DAO keeps the database details inside itself, and offers simple methods to everyone else. If a query has a bug, there's one place to fix it. If the table changes, only its DAO changes.
DAOs and repositories
You'll also hear this kind of class called a repository. The two names are used for nearly the same idea: an object that stores and fetches models for the rest of the program, hiding how and where. Later in the course, ASP.NET Core apps use the same pattern, and the Dapper and Entity Framework Core chapters show shorter ways to write what goes inside.
Check yourself
1 question to try on the lesson page.
So what
A DAO gives each table a single home for its SQL. The rest of your program stays clean, testable C#, and the database work is all in one place.