Working with Dapper (Part 4)

In order to guide developers on how to define repositories, I created CRUDS interfaces with expandable signatures and support for structured return values. This article describes how to use the Mendz.Data classes to create a database context, a POCO, a repository and, finally, to use them in an application.

First, you need a database context. For the sake of this article, we will define a database context named TestDbDataContext. In this sample, the assumption is that you have an SQL Server instance with a database named TestDB that has a table named Test with fields TestID (PK), Name and Description containing a record with TestID = 1.

public class TestDbDataContext : DbDataContextBase
{
    protected override IDbConnection BuildContext()
    {
        return new SqlConnection(
            ConnectionStringOptions.Instance["TestDB"]);
    }
}

Second, you need a POCO class. For the sake of this article, we will define a POCO class named Test.

public class Test
{
    public int TestID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

Next, you can define the Test class's repository and call it TestRepository.

public class TestRepository
    : DbRepositoryBase<TestDbDataContext>, IDbDataReadable<Test>
{
    public TestRepository()
        : base()
    {
    }

    internal TestRepository(IDbDataContext dbDataContext)
        : base(dbDataContext)
    {
    }
}

This code defines a TestRepository that inherits DbRepositoryBase and IDbDataReadable. The IDbDataReadable interface must be implemented.

    public Test Read(Test model, dynamic expansion, 
        out List<ResultInfo> returnValues)
    {
        returnValues = new List<ResultInfo>();
        string spName = "TestRead";
        var p = new DynamicParameters();
        p.Add("@testID", model.TestID);
        p.Add("@affectedCount", dbType: DbType.Int32, ParameterDirection.Output);
        model = DbDataContext.Context.Query<Test>(spName p, 
            commandType: CommandType.StoredProcedure)
            .FirstOrDefault();
        returnValues.Add(new ResultInfo(spName, p.Get<int>("@affectedCount")));
        return model;
    }

This implementation shows Dapper being used with a stored procedure named TestRead. It is important that the implementation aligns with the stored procedure's input and output parameters. For Dapper's data mapping magic to work, it is important that the query returns the fields aliased to match the names of Test's property members.

CREATE PROCEDURE TestRead
    @testID INT,
    @affectedCount INT OUT
AS
BEGIN
    SELECT *
    FROM Test
    WHERE TestID = @testID;
    SET @affectedCount = @@ROWCOUNT;
END;

Finally, the application code can be written. In order for this to work, the application must initialize the singleton ConnectionStringOptions. Then, TestRepository can be used directly.

    using (TestRepository r = new TestRepository())
    {
        Test test = r.Read(new Test { TestID = 1; }, null, 
            out List<ResultInfo> returnValues);
    }

This code shows the application using TestRepository.Read() to get an instance of a Test with TestID = 1, and the rest of its properties filled with test record #1's data from the database. Note that the application knows only about the classes Test and TestRepository. The application does not know anything about TestDBDataContext, which only TestRepository knows about.