How to separate the Data Access Layer into smaller classes

Question

I have a design problem with a 3 Tier application. Usually my database layer is only 1 class like this one:

public class DA
{
    string _connString = "";

    public DA()
    {
         _connString = ConfigurationManager.ConnectionStrings["MyConnString"].ToString();

    }
    public DA(string connString)
    {
        _connString = connString;
    }

    private SqlConnection GetConnection()
    {
       ...
    }

    public Employee GetEmployee(int idEmployee)
    {
        ...
    }
    public Whatever GetWhatever(int idWhatever)
    {
        ...
    }

    ...

But now I have a pretty big project and I would like to separate the DA class into smaller classes like DA_Employee, DA_Whatever, etc.

I would like to instantiate only DA one time and access the other classes like that:

DA db = new DA(connString);

db.Employee.GetEmployee(12);
db.Whatever.GetWhatever(89);
db.Whatever.UpdateWhatever(89, "newname");

I would prefer NOT having something like this:

DA db = new DA(connString);
DA_Employee dbEmployee = new DA_Employee(connString);
DA_Whatever dbWhataver = new DA_Whatever(connString);

I think I can instantiate all my classes in my main constructor and have some properties to access them?

How can I give access to all classes to GetConnection()?

Any help and reference appreciated. Thanks!

score 2 · Answer 1 · answered Sep 23 '17 at 23:41

2

Yes, you make the classes a property of DA.

public interface IRepository<T>
{
    T GetById(int id);
}

public class EmployeeRepository : IRepository<Employee>
{
    private SqlConnection sqlConn;

    public EmployeeRepository(SqlConnection sqlconn)
    {
        this.sqlConn = sqlConn;
    }

    public Employee GetById(int id)
    {
        return new Employee();
    }
}

Pass the SqlConnection as a constructor dependency.

public class DA : IDisposable
{
    private SqlConnection sqlConn;
    private IRepository<Employee> employeeRepo;
    private IReposiotry<Whatever> whateverRepo;

    public DA(string connectionString)
    {
        this.sqlConnection = GetSqlConnection(connectionString);
        this.employeeRepo = new EmployeeRepository(this.sqlConnection);
        this.whateverRepo = new WhateverRepository(this.sqlConnection);
    }

    public IRepository<Employee> Employee { get { return employeeRepo; } }
    public IRepository<Whatever> Whatever { get { return whateverRepo; } }
}

And its usage

using (var db = new DA("connectionString"))
{
    db.Employee.GetById(1);
    db.Whatever.GetById(10);
}

answered Sep 23 '17 at 23:41

Jasen

14,030
3
51
68

Thank you for that! Also you gave me the idea of a Base class with the common functions. – JohnWinner Sep 24 '17 at 00:30
Quick question: is there a reason why I should use global variables for employeeRepo instead of a direct property like public IRepository Employee { get; set; } – JohnWinner Sep 24 '17 at 00:32
One reason I suppose it's better to use private variable instead of "automatic property" (I think it's called that way?) is that I can instantiate my classes only if/when accessed. It's better in term of performance I guess. – JohnWinner Sep 24 '17 at 02:22
If you inject the repository instances instead of newing them, as in this answer, you can fake or mock them (for testing). If you do that then the SqlConnection would also be injected -- which then leads to the question why you even need DA. Perhaps your DA class would only produce SqlConnections and you pass these into your repository instances as needed. What is better becomes opinionated and depends on your requirements and perhaps better asked on another SE site. – Jasen Sep 25 '17 at 18:22

How to separate the Data Access Layer into smaller classes

1 Answers1