1
\$\begingroup\$

I started a new ASP.NET project in an effort to better learn SQL and Dapper ORM framework.

My idea for data access: Have POCO entities, for each entity have DAO object. When I want to manipulate with data in database, access them through repository, which uses these DAO objects to manipulate single objects.

My entities (I know CaseOf is strange name, it makes sense in the context of the app):

public abstract class Entity
{
    public int Id { get; set; }
}

public class CaseOf : Entity
{
    public CaseOf()
    {
        Steps = new List<Step>();
    }

    public string Description { get; set; }
    public ICollection<Step> Steps { get; set; } 
}

public class Step : Entity
{
    public string Numbering { get; set; }
    public string Description { get; set; }

    // FK to CaseOf
    public int CaseOfId { get; set; }
}

With DAO's for CaseOf and Step:

public class CaseOfDAO : IGenericDAO<CaseOf>
{
    private readonly string _insertQuery = "INSERT INTO caseof (Description) " +
                                   "VALUES (@Description);";

    private readonly string _findQuery = "SELECT * FROM caseOf WHERE Id = @id";

    public int Store(CaseOf entity)
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            var id = connection.Query<int>(_insertQuery, entity).Single();
            return id;
        }
    }

    public CaseOf Get(int id)
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            return connection.Query<CaseOf>(_findQuery, new { id }).FirstOrDefault();
        }
    }
}

public class StepDAO : IGenericDAO<Step>
{
    private readonly string _insertQuery = "INSERT INTO step (Numbering, Description, CaseOfId) " +
                           "VALUES (@Numbering, @Description, @CaseOfId);";

    private readonly string _findQuery = "SELECT * FROM step WHERE Id = @id";

    private readonly string _findAllCaseOfIdQuery = "SELECT * FROM step WHERE CaseOfId = @id";

    public int Store(Step entity)
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            var id = connection.Query<int>(_insertQuery, entity).Single();

            return id;
        }
    }

    public Step Get(int id)
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            return connection.Query<Step>(_insertQuery, new {id}).FirstOrDefault();
        }
    }

    public IEnumerable<Step> GetAllWithCaseOfId(int caseOfId)
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            return connection.Query<Step>(_findAllCaseOfIdQuery, new {caseOfId});
        }
    }
}

IGenericDAO is interface with 2 methods: Store() and Get(id).

My repository for CaseOf looks like this:

public class CaseOfRepository : IRepository<CaseOf>
{
    private CaseOfDAO _caseOfDAO = new CaseOfDAO();
    private StepDAO _stepDAO = new StepDAO();

    public int Add(CaseOf entity)
    {
        var id = _caseOfDAO.Store(entity);
        foreach (var step in entity.Steps)
        {
            step.CaseOfId = id;
            _stepDAO.Store(step);
        }

        return id;
    }

    public CaseOf GetById(int id)
    {
        var ret = _caseOfDAO.Get(id);
        var steps = _stepDAO.GetAllWithCaseOfId(id);

        ret.Steps = new List<Step>(steps);

        return ret;
    }

    // rest of methods from IRepository that are not yet implemented
}

I've been using EF6 as my ORM framework of choice, so I haven't ever used DAO's, since EF6 managed that for me.

Is this good design? If I ever decide to extend Step with another one-to-many, I can just change this line var steps = _stepDAO.GetAllWithCaseOfId(id); to call to a Step repository, that would do similar operation as my CaseOf repository does with CaseOf-Step relationship right now.

Same way, making CaseOf part of another relationship would just result in another DAO object and call to retrieve it.

Changing properties of entities requires just modification to the POCO and queries in their respective DAO.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.