How Dapper.SimpleSave can help me

Dapper makes it really easy to load rows from a database into POCOs.

However, once you move beyond basic CRUD operations involving a single, or maybe two or three, objects, the code you need to write quickly becomes a lot more complex.

Step in Dapper.SimpleSave...

Let's take the concept of a user within an organisation:

• A user in our putative organisation:

  • has one office phone number,

  • has one position (c.f., job title),

  • is a member of one or more departments,

  • and one or more teams,

  • and may have additional roles,

  • as well as a bunch of basic properties like FirstName, LastName, Username, Email, etc.

Furthermore:

• a User's roles are defined by their position and departments, as well as any additional roles they might be assigned,

• Office phone numbers can only be assigned to a single User,

• many users might hold the same position,

• whilst departments, teams, and roles all have many users.

So we can see that the relationship between users and office phone numbers is 1:1, whereas that between users and departments, teams, and roles are all M:N (i.e., many to many).

In a relational database system M:N relationships are modelled using link tables. So, for example, to link the User table to Departments, we might have a Lnk_UserDepartment table whose rows contain pairs of foreign key references to rows in User and Department. This can be seen in Figure 1, below.

Figure 1. Visualisation of partial user schema. (Right-click and Open Image in New Tab to view full sized version.)

In code, however, we don't really want to be dealing with the hassle of link tables. Something more OO would be better:

    public class UserDao
    {
        ....
        public string FirstName { get; set; }
        public string LastName { get; set; }
        ...
        public OfficeNumberDao OfficeNumber { get; set; }
        ...
        public PositionDao Position { get; set; }
        public IList<DepartmentDao> Department { get; set; }
        public IList<RoleDao> AdditionalRoles { get; set; }
        public IList<TeamDao> Team { get; set; }
    }

Note the absence of anything resembling the modelling of link tables. Instead we use lists of objects, such as Departments, to link the user directly to the departments of which they're a member.

When we edit a user we might change a load of their basic properties (LastName, Email, etc.), assign them to a new Position, move them to one or more new Departments, and change their Team membership (imagine they got married, came back from honeymoon, and immediately got a big promotion... or something).

The point being that we want to save all these changes in one go without having to write loads of code to do it - calculating which objects need adding/updating/deleting, in what order, and what SQL we need to execute to do this - for each different object type we want to save.

This is where Dapper.SimpleSave comes in. It exists to:

• Simplify saving complex objects to the database,

• Quickly,

• Reliably,

• With minimal configuration,

And to allow us to make sensible design decisions both in our code and in our database schemas.

It means we can write code like this to save complex objects, like the user we've just been talking about:

// Creating a user
connection.Create(user);

// Updating a user
connection.Update(oldVersionOfUser, newVersionOfUser);

// Deleting a user
connection.Delete(user);

We don't have to worry about figuring out what needs to be saved and it what order. SimpleSave does that for us.

It particularly exists for those who love the simplicity of Dapper but want to write less code to save data. Likewise Dapper.SimpleLoad is for those who love Dapper but who'd prefer to avoid having to write their own multi-mapping code.

To find out how to use SimpleSave take a look at our first example.