Tutorial: Saving data to a database
Dapper.SimpleSave is intended to support what EF users would call a "database first" scenario. I.e., you already have a database, and you want an easy way to get data in and out of it from a .NET application.
It doesn't do any kind of code or schema generation for you: you should already have a schema, and you'll write simple POCO entities from which Dapper.SimpleSave will save data to the database. These are also used by Dapper or Dapper.SimpleLoad to load data.
If you are unfamiliar with Dapper, StackExchange's micro-ORM, it's highly recommended that you familiarise yourself with it by reading the docs at https://github.com/StackExchange/dapper-dot-net. (This will only take a few minutes so is definitely worth it.)
Your general workflow in using Dapper.SimpleSave is:
-
Create database schema using your favourite tool, be that SSDT, SQL Server Management Studio, or whatever.
-
Define your POCO entities.
-
Decorate your POCO entities with attributes to allow Dapper.SimpleSave to generate SQL to save them.
-
Use the
IDbConnectionextension methods that Dapper.SimpleSave introduces toINSERT,UPDATE, andDELETEdata in your database.
Each of these four steps is straightforward but you need to bear in mind that Dapper.SimpleSave is an extension, or a helper library, for Dapper.
It is NOT EF. It's much lower level and doesn't do many of the things, or support many of the scenarios, that EF does - nor is it intended to. Dapper.SimpleSave gives you more direct control, partly because you can easily fall back to using Dapper itself or even raw ADO.NET, but it also requires you to do more work.
Let's say we have created a database with the simplified [user] schema shown in figure 1 below. As you can see, there are just four tables.
Figure 1. Simplified user schema, showing how we map a user to departments and logins. (Right-click and Open in new tab for full size.)
What we want to be able to do is save and load users. Loading is covered by the Dapper.SimpleLoad documentation.
Now we need to define POCO entities for the above. These are pretty simple, as you can see in the script below.
using System;
using System.Collections.Generic;
namespace SimpleSavePocoEntities
{
public class UserDao
{
public int UserKey { get; set; }
public Guid UserGuid { get; set; }
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Username { get; set; }
// More public properties here - we're going to ignore them because they're mostly not interesting
// Here are the interesting ones
public IList<DepartmentDao> Departments {get; set;}
public IList<LoginDao> Logins {get; set;}
}
public class DepartmentDao
{
public int DepartmentKey { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public bool IsExternal { get; set; }
public bool IsSalesChannel { get; set; }
}
public class LoginDao
{
public int LoginKey { get; set; }
public int UserKey { get; set; }
// This property represents a foreign key relationship against a table that, in this
// scenario, we don't care about.
public int AllowedIpKey { get; set; }
// N.B. In the database this column is actually an INT. Dapper supports
// automatic mapping between C# enums and numeric columns in the database.
// The C# enum must extend the simple type most closely corresponding to
// the database numeric type. E.g., if your database column is an INT your
// C# enum should extend Int32 (the default).
//
// If you need to map to a string in the database you must use a CustomPropertyTypeMap.
// See http://stackoverflow.com/questions/8902674/manually-map-column-names-with-class-properties/12615036#12615036
// for more information.
//
// (Another possible solution is to use a shadow property to save and load the string value.
// E.g., http://stackoverflow.com/questions/6192512/store-enum-as-string-in-database)
public UserLoginResultEnum LoginResultKey { get; set; }
public string IpAddress { get; set; }
public DateTimeOffset Timestamp { get; set; }
// Ignoring UpdateDate and UpdateUserKey because these are just audit columns
}
public enum UserLoginResultEnum : int
{
Success = 0,
Failed = 1,
PasswordFailed = 2,
PasswordLocked = 3,
AdministrativeLock = 4,
RemoteAccessDenied = 5,
RemoteAccessValidationPrompt = 6,
AccountUnlocked = 7,
UserIsImpersonated = 8,
UserPasswordChanged = 9,
PasswordHasBeenReset = 10
}
}Effectively they're just bags of properties, but note that both departments and logins are represented as lists.
In the case of departments this is interesting, because a user can be a member of multiple departments, but also departments can contain many users. In other words we have a many to many relationship between users and departments. In our schemas this is implemented using the `[user].[USER_DEPARTMENT_LNK]' table. In code we don't really want this extra level of indirection though. Apart from anything else it's just clunky. For every user, we just want a list of the departments of which they're a member and that's it.
To be honest this is now most of the work done, but we're still not ready to save these DAOs using SimpleSave because it would have no idea where to write them in the database, or how the relationships between them work. This is where the attributes come in.
##Decorating our POCO entities with attributes
Dapper.SimpleSave's attributes exist to allow us to give it enough information to generate valid SQL to save our objects in the database. It knows nothing about the database beyond what we tell it - it never looks at the meta-data itself.
This might sound complex but is actually pretty simple because it doesn't need to worry about a lot of the things that go to make up a typical SQL table definition. Constraints and indexes being two obvious examples.
Here's what it does care about:
- Tables
- Primary keys
- Foreign key relationships between tables, and their cardinalities (one to many, many to one, etc.)
- Any properties that map to columns with a different name from the property name
- Any properties or types that should be treated as read only/reference data (and, likewise, any tables that contain only reference - also referred to as static - data)
- Any column that is used to mark a row as being deleted
- Any columns you don't want to save
All we need to do is mark up our POCOs with attributes that correspond to any of the above. Let's take a look at these in more detail.
###Tables
At the most basic level Dapper.SimpleSave needs to know which POCOs map to which database tables. This is achieved with the [Table] attribute, which takes a single string parameter called schemaQualifiedTableName in its constructor.
The clue really is in the name here: you must qualify your table name with the schema it belongs to. I'd also recommend you surround each part of the identifier with square braces to avoid issues with colliding with SQL keywords. For example, [Table("[user].[USER_MST]")]. This is because Dapper.SimpleSave will spit verbatim whatever you put in here into the SQL it generates.
A single POCO type can only map to a single database table or view, but there's nothing to stop you defining a POCO type that maps only to a subset of a table's columns, or multiple POCO types that all map to the same table, perhaps using different subsets of columns.
So what do our POCOs look like now?
using System;
using System.Collections.Generic;
namespace SimpleSavePocoEntities
{
[Table("[user].[USER_MST]")]
public class UserDao
{
...
}
[Table("[user].[DEPARTMENT_ENUM]")]
public class DepartmentDao
{
...
}
[Table("[user].[LOGIN_TRN]")]
public class LoginDao
{
...
}
// Strictly, we don't need this, but it allows us to
// supply this type to the ReferenceDataRepository
// (see later) to retrieve a list of possible values
// for use, e.g., in a web interface
[Table("[user].[LOGIN_RESULT_ENUM]")]
public enum UserLoginResultEnum : int
{
...
}
}So far, so good. On to primary keys...
###Primary keys
Every table that Dapper.SimpleSave writes to must have a single primary key column. This is so that SimpleSave can uniquely identify a single row when issuing UPDATE and DELETE statements.
Compound keys - i.e., keys that span multiple columns - are not supported.
Furthermore, only columns of type INT, BIGINT and UNIQUEIDENTIFIER are supported as primary keys.
If you don't have a primary key column on your table, you'll need to add one.
If you do have a primary key column, and it isn't an INT, BIGINT or UNIQUEIDENTIFIER, or it spans multiple columns, you'll need to redefine it against a single column that is either an INT, BIGINT or UNIQUEIDENTIFIER.
In many cases the values in primary key columns are generated by the database. For example, like this:
...
[UserKey] INT NOT NULL IDENTITY(30,1)
CONSTRAINT [PK_User_KEY] PRIMARY KEY,
...Or like this:
...
[ApplicationGUID] UNIQUEIDENTIFIER
DEFAULT (newsequentialid()) ROWGUIDCOL NOT NULL,
...
CONSTRAINT [PK_APPLICATION_MST] PRIMARY KEY NONCLUSTERED
([ApplicationGUID] ASC),
...Because of this any properties in C# corresponding to primary key columns in the database must therefore be declared as int?, long? or Guid?, because they may not have a value initially (and because both 0 and Guid.Empty are entirely valid values for primary keys so shouldn't be assigned the same meaning as NULL).
(Note that even in situations where the primary key values aren't generated by the database this is still a requirement at present.)
They must also be marked with Dapper.SimpleSave's [PrimaryKey] attribute.
If primary key values are assigned in the application layer, rather than the database, the attribute's constructor accepts a parameter called isUserDefined, which can be set true to indicate this: [PrimaryKey(true)]. This will stop Dapper.SimpleSave retrieving identity or GUID values from the database after each INSERT. It's important to remember to do this otherwise it might populate your POCOs with spurious primary key values.
If primary key values are assigned by the database, Dapper.SimpleSave will retrieve the newly generated primary key value after each insert and populate it back into your POCO.
In our case all primary key values are assigned by the database.
Let's decorate our POCOs to see how this looks.
using System;
using System.Collections.Generic;
namespace SimpleSavePocoEntities
{
[Table("[user].[USER_MST]")]
public class UserDao
{
[PrimaryKey]
public int? UserKey { get; set; }
...
}
[Table("[user].[DEPARTMENT_ENUM]")]
public class DepartmentDao
{
[PrimaryKey]
public int? DepartmentKey { get; set; }
...
}
[Table("[user].[LOGIN_TRN]")]
public class LoginDao
{
[PrimaryKey]
public int? LoginKey { get; set; }
...
}
// Strictly, we don't need this, but it allows us to
// supply this type to the ReferenceDataRepository
// (see later) to retrieve a list of possible values
// for use, e.g., in a web interface
[Table("[user].[LOGIN_RESULT_ENUM]")]
public enum UserLoginResultEnum : int
{
...
}
}Note that we've now made all our primary key properties nullable, as well as adding the [PrimaryKey] attribute to all of them.
###Foreign key relationships
Foreign keys are used to describe relationships between tables. In C# code we can obviously directly reference other POCO types through properties. Dapper.SimpleSave supports the use of single values or lists.
Only foreign key relationships that reference the primary key column of the referenced table, via a column of the same type in the referencing table, are supported.
Dapper.SimpleSave needs to know a little more about these relationships in order to be able to determine which POCO represents the referencing table and which the referenced. This enables it to work out in which order INSERTs, UPDATEs, and DELETEs should be done. For this reason it has a number of cardinality attributes with which you can decorate properties that reference other POCO types:
-
[OneToMany]indicates that there are potentially many child object rows for each parent object row. In this case the foreign key relationship is from the child table to the parent table. The child table will contain a column that references the primary key column of the parent table. The parent table will not contain a column referencing the child table. -
[ManyToOne]indicates that there are potentially many parent object rows for each child object row. In this case the foreign key relationship is from the parent table to the child table, and the parent table will contain a column that references the primary key column of the child table. -
[ManyToMany]indicates that each parent object may reference many child objects and vice versa. This is modelled in the database using a link table that has foreign key columns pointing at both parent and child tables. For this to work we need to tell Dapper.SimpleSave what the link table is called. We also need the names of the foreign key columns in the link table to match those of the primary key columns in the parent and child tables (yet another reason why using the name "ID" for all INT primary key columns isn't always the best idea). Dapper.SimpleSave will ignore any other columns in the link table. This means that if there are other columns you care about you should model the link table with its own POCO type. You can then reference it from the parent POCO using a [OneToMany] relationship, and add a [ManyToOne] relationship from the link POCO to the child POCO. -
[OneToOne]indicates that each parent object row references at most one child object row. In this case the foreign key column can be on either the parent or the child. If it's on the child you'll need to pass in a string to the attribute's constructor telling Dapper.SimpleSave what the column name is.
In addition to the above there's also a [ForeignKeyReference] attribute that you use to decorate any properties representing foreign key columns. The constructor for this takes the Type of the POCO the column references. This attribute removes ambiguity where multiple columns of the same data type exist, and is also used by Dapper.SimpleSave to determine which properties to update when referenced rows are inserted. Properties marked with this attribute should be int?, long? or Guid? - the same as for primary keys.
In our example we have a couple of interesting relationships:
-
A many to many relationship between users and departments, which uses a link table. We can use a
[ManyToMany]attribute for this. -
A one to many relationship between users and logins. No link table here obviously, so we'll just using
[OneToMany], along with a[ForeignKeyReference].
Here's what that looks like in code:
using System;
using System.Collections.Generic;
namespace SimpleSavePocoEntities
{
[Table("[user].[USER_MST]")]
public class UserDao
{
[PrimaryKey]
public int? UserKey { get; set; }
...
[ManyToMany("[user].[USER_DEPARTMENT_LNK]")]
public IList<DepartmentDao> Departments {get; set;}
[OneToMany]
public IList<LoginDao> Logins {get; set;}
}
[Table("[user].[DEPARTMENT_ENUM]")]
public class DepartmentDao
{
[PrimaryKey]
public int? DepartmentKey { get; set; }
...
}
[Table("[user].[LOGIN_TRN]")]
public class LoginDao
{
[PrimaryKey]
public int? LoginKey { get; set; }
[ForeignKeyReference(typeof(UserDao))]
public int? UserKey { get; set; }
...
}
// Strictly, we don't need this, but it allows us to
// supply this type to the ReferenceDataRepository
// (see later) to retrieve a list of possible values
// for use, e.g., in a web interface
[Table("[user].[LOGIN_RESULT_ENUM]")]
public enum UserLoginResultEnum : int
{
...
}
}See how we've added cardinality attributes to UserDao's' Departments and Logins properties. We haven't had to make any changes to DepartmentDao because it doesn't have any properties representing foreign key columns. LoginDao, on the other hand, has a UserKey property, which represents a foreign key column referencing the [user].[USER_MST] table representing by UserDao. We therefore add a [ForeignKeyReference(typeof(UserDao))] attribute to this property.
It's extremely unlikely that this would happen in the scenario we have here, but say we wanted to insert a new UserDao with a corresponding LoginDao at the same time. The decorations we've just described allow Dapper to determine that it should INSERT the UserDao row first, then set the primary key value from the inserted row in the LoginDao's UserKey property, before then INSERTing the LoginDao.
###Mapping properties to columns with a different name
We don't have an example where we need to do this here, but sometimes it's helpful to give your property a different name to the underlying database column. That's absolutely fine but Dapper.SimpleSave still needs to know what the underlying column is called. You can do this by adding the [Column] attribute to your property. For example:
[ManyToOne]
[ForeignKeyReference(typeof(PhoneNumberDao))]
[Column("OfficeNumberKey")]
public PhoneNumberDao OfficeNumber { get; set; }OfficeNumber is obviously a better fit for a property that returns a POCO than OfficeNumberKey and with the [Column] attribute we can achieve better naming in our C# code.
Note the use of the [ForeignKeyReference] attribute here, even though it doesn't look like it should be necessary because the property is a POCO type rather than, say, just an int?. For now it's a limitation of Dapper.SimpleSave that this is still required, although we hope to remove the need for it in the future - see this issue.
###Handling read-only/reference/static data
If we think about our user sub-domain, it seems likely that the company would have a finite number of well-defined departments (e.g., sales, pro-support, IT, customer service, marketing, etc.) and that we might not want some spurious update to a user to be able to create a new department. We probably want to manage those separately, and treat the departments as read-only in this case. I.e., users can be linked to existing departments, but we don't want people creating and deleting departments by adding and removing them from users.
This is where the [ReferenceData] attribute comes in. Marking a POCO type as reference data means it'll be treated as read-only by SimpleSave. In other words it won't try to make any updates to it, but will just link these rows to rows in other tables that reference them.
The one exception here is that your [ReferenceData] table is allowed to have an updateable foreign key column that points to a row in another table. This can be handy in situations such as where you have a finite pool of phone numbers to assign to people and each phone number can be assigned to at most one person. You don't want to change the phone numbers themselves - just update the foreign key indicating which user they belong to. If you want to do this you'll need to pass hasUpdateableForeignKeys = true into the attribute's constructor when you add it to the type.
You can also mark properties with the [ReferenceData] type if you want the POCO type of the property to be treated as reference/static data only in that context.
Going back to our example, adding the [ReferenceData] attribute gives us:
using System;
using System.Collections.Generic;
namespace SimpleSavePocoEntities
{
[Table("[user].[USER_MST]")]
public class UserDao
{
[PrimaryKey]
public int? UserKey { get; set; }
...
[ManyToMany("[user].[USER_DEPARTMENT_LNK]")]
public IList<DepartmentDao> Departments {get; set;}
[OneToMany]
public IList<LoginDao> Logins {get; set;}
}
[Table("[user].[DEPARTMENT_ENUM]")]
[ReferenceData]
public class DepartmentDao
{
[PrimaryKey]
public int? DepartmentKey { get; set; }
...
}
[Table("[user].[LOGIN_TRN]")]
public class LoginDao
{
[PrimaryKey]
public int? LoginKey { get; set; }
[ForeignKeyReference(typeof(UserDao))]
public int? UserKey { get; set; }
...
}
// Strictly, we don't need this, but it allows us to
// supply this type to the ReferenceDataRepository
// (see later) to retrieve a list of possible values
// for use, e.g., in a web interface
[Table("[user].[LOGIN_RESULT_ENUM]")]
public enum UserLoginResultEnum : int
{
...
}
}We've now done everything we need to start saving objects to the database with Dapper.SimpleSave.
So far I haven't talked about using columns to mark rows as "deleted", or ignoring properties. We don't need those features in this situation but I'll explain them at the end, just so you know how to use them if and when you do need them.
##Using IDbConnection extension methods to save objects
OK, let's write some data to the database. First off...
###Creating a new user record
Here's some outline code to create a new user in the database:
var user = new UserDao {
FirstName = "emmett",
LastName = "Brown",
Username = "doc.brown",
...
Departments = new List<DepartmentDao> {
// Create, or more likely assign, DepartmentDaos;
// I'm newing one up here just for illustration
new DepartmentDao {
DepartmentKey = 1,
Name = "Mad Scientists",
Description = "Time machines are our speciality.",
IsExternal = false,
IsSalesChannel = false
}
}
}
using (var connection = new SqlConnection("your connection string here"))
{
connection.Create(user);
}First and most important point. Note that we have not assigned a value to the primary key property of UserDao. If, however, we were to inspect the value of user.UserKey in the debugger after the call to connection.Create we would see that it has been assigned a new value. This new value is the primary key value of the INSERTed row in the database.
The DepartmentDao has a primary key value because it's reference data and we need it to add a row to the mapping table correctly.
Note that we haven't bothered adding any LoginDaos at this point. I've also skipped out setting most of the boring properties.
With the DepartmentDaos I've created one purely for illustrative purposes. In reality you'd probably keep track of a list of them and assign them from there. Note that the DepartmentDao does have a value for its DepartmentKey primary key property because we're just creating a mapping in the [user].[USER_DEPARTMENT_LNK] link table.
And this is an important point.
Dapper.SimpleSave treats [ManyToMany] and [ManyToOne] relationships in a special way.
First, let's deal with the situation we have here: the child type in the relationship is marked as [ReferenceData]. In this scenario:
- For a
[ManyToMany]relationship, SimpleSave will simply create, update, or delete mappings in the link table passed to the[ManyToMany]attribute - it will never make changes to the child row in the database. - For a
[ManyToOne]relationship, SimpleSave will reference the child row from the parent row but will never create, update, or delete child rows themselves.
What about when the property or child POCO type isn't marked as [ReferenceData]?
In this case because child objects in these relationships can be referenced by many other rows in the database SimpleSave will:
- create new rows for child POCOs that have no primary key value
- update child POCOs that have property changes
What it won't ever do is delete rows corresponding to child POCOs in [ManyToMany] or [ManyToOne] relationships because it has no way of knowing whether or not they're referenced from anywhere else.
IMPORTANT NOTE: if child objects hold foreign key references to ancestor objects other than there immediate parents you'll need to wire these up manually after the save.
###Updating a user record
Let's say we want to update a user who already exists in the database. To do this SimpleSave needs access to the old version of the object (the version currently saved in the database), and the new version of the object, which contains the modifications. It needs this because it needs to know what columns to update.
So where do we get the old objects from? There are a various approaches:
- Load the old object from the database, or from a cache∗.
- Use a deep clone of the object for modifications, and keep the original hanging around as the old object.
- A hybrid of the above, where you load the object from the database, clone it, modify the clone, and then issue the update.
∗Be careful with cached instances. If you end up modifying the cached instance as your "new object" you many find that the old and new objects are identical. If you want to use a cached instance you may be better off with approach (2).
In our case I'm going to use the hybrid approach (3), just because it allows me to show the complete process more easily.
How you choose to load the object from the database is up to you: you could use raw ADO.NET, Dapper or, as I'm going to do here, Dapper.SimpleLoad.
I'm also going to do something you probably wouldn't do in reality, which is change the username. Here's the code:
...
// Automapper config for cloning
Mapper.CreateMap<UserDao, UserDao>();
Mapper.CreateMap<DepartmentDao, DepartmentDao>();
Mapper.CreateMap<LoginDao, LoginDao>();
...
public void ChangeUsername(int userKey, string newUsername)
{
using (IDbConnection connection = new SqlConnection("your connection string here"))
{
var oldUser = connection.AutoQuery<UserDao, DepartmentDao, LoginDao>(
new {UserKey = userKey}).FirstOrDefault();
var newUser = Mapper.Map<UserDao, UserDao>();
newUser.Username = newUsername;
connection.Update(oldUser, newUser);
}
}Note that I'm very lazily using Automapper to create my deep clone. If you want to know more about this, please check out the Automapper documentation.
All SimpleSave is going to do in this case is issue an UPDATE statement to change the username. Note that if the old object doesn't exist, the result will be an INSERT of the new object.
This is pretty simple, but what if I do something more complex like change the contents of the Departments collection?
Here's how that would look:
using (IDbConnection connection = new SqlConnection("your connection string here"))
{
// Assume that this is our "doc.brown" user we created above, who is
// part of the "Mad Scientists" department, and that he has a primary
// key value of 1. We're going to promote him to the "Directors"
// department.
var oldUser = connection.AutoQuery<UserDao, DepartmentDao, LoginDao>(
new {UserKey = 1}).FirstOrDefault();
var newUser = Mapper.Map<UserDao, UserDao>();
newUser.Departments.Clear();
// Again, here I'm newing up purely for illustrative purposes
newUser.Departments.Add(new DepartmentDao {
DepartmentKey = 2,
Name = "Directors",
Description = "Masters of the Universe.",
IsExternal = false,
IsSalesChannel = false
});
connection.Update(oldUser, newUser);
}
}Basically identical. The only difference here, apart from the way we've chosen to modify the object, is in the scripts that SimpleSave will execute against the database. In this case it will issue two command. Firstly, a DELETE against the [user].[USER_DEPARTMENT_LNK] table to remove the original mapping to the "Mad Scientists" department. Secondly, an INSERT against the same table to add the new mapping to the "Directors" department.
Your new user might be an object sent back to you by a client, in which case you can usually just pull the old user directly from the database (or from a cache), and you won't need to clone it. What you will need to do is validate the new user before you save it, because you can't trust anything from a client (remember fallacy 4?). You need to make sure that everything you expect to be there is, and that anything that shouldn't be there isn't.
##Deleting a user record
Deleting objects with SimpleSave is also straightforward but there is perhaps one non-obvious nuance: you need the whole object to correctly perform a delete∗. You can't just pass in a primary key value because SimpleSave may need to remove child objects.
Remember: it won't remove child objects referenced via [ManyToMany] or [ManyToOne] relationships. It will remove link table records for [ManyToMany] relationships though. In addition, nothing marked as [ReferenceData] will be removed.
Here's what a simple DeleteById implementation might look like:
public void DeleteUserById(int userKey)
{
using (IDbConnection connection = new SqlConnection("your connection string here"))
{
var oldUser = connection.AutoQuery<UserDao, DepartmentDao, LoginDao>(
new {UserKey = userKey}).FirstOrDefault();
connection.Delete(oldUser);
}
}∗ There is one exception: if you've marked foreign key relationships in child tables with ON DELETE CASCADE, in which case you can just issue a delete against the parent record using ADO.NET (or write a deletion service to do this for you) and all child objects will also be deleted. Many people don't favour ON DELETE CASCADE though, due to the potential for unforeseen (and unsafe) consequences.
##Marking columns used to indicate a row has been "deleted"
OK, but what if you don't really want to delete records from your database? Many systems mark records as deleted without actually removing them from the database.
This is known as a "soft delete" and is something Dapper.SimpleSave provides basic support for via its SoftDelete extension methods. Find out more here.
What this looks like in code is this:
// This goes in your DAO
[SoftDeleteColumn(true)]
public bool IsDeleted {get; set;}
...
// This is what you call when you want to delete the object
connection.SoftDelete(myDaoInstance);All this is going to do is UPDATE the row corresponding to your top level DAO with the value indicating it's been deleted. It won't change any child objects because, if you decide you want to undelete the object later (say, if someone removes it by mistake) you probably want everything associated with it back as well.
##What if I don't want to save some properties with Dapper.SimpleSave?
Again, this is really simple. Our UserDao example actually provides a great candidate for this.
Let's say that every time the user logs in a new LoginDao gets added to the UseDao.Logins collection. If they log in once or twice every day it's not going to take long before this collection contains hundreds of items, so you probably don't want to load them all for most cases when you load the user's record from the database, unless of course you really do want to look at their login history.
If you haven't loaded the full history, you probably don't want to save it either, because that could corrupt the users login history.
In this case you just use the [SimpleSaveIgnore] attribute on the property, and SimpleSave won't try to save it - won't even look at it when it works out what changes there are to be saved, in fact.
Here's how that looks in code:
[SimpleSaveIgnore]
[OneToMany]
public IList<LoginDao> Logins {get; set;}You can get rid of the [OneToMany] attribute if you want; I tend to leave things like that in just to make it clear to other developers that there is a relationship in the database, and that this isn't just a calculated property. (It can also be helpful if you want SimpleSave to ignore something, but don't want SimpleLoad to ignore it.)
##What if I really do want to delete in child object in a [ManyToMany] or [ManyToOne] relationship?
If you really do need to delete a child object in a [ManyToMany] or [ManyToOne] relationship then you need to delete it separately after you've deleted the parent object. You can still use SimpleSave to delete the child object(s) obviously.
##What if I need to put some custom logic in my saving, or handle circular dependencies?
That's fine. One of the ways you can do this is to break up the save into multiple steps by ignoring certain properties using [SimpleSaveIgnore]. You then save any associated child objects separately, and you can perform any custom logic you need between each step.
Some IMPORTANT points:
-
You'll need to think about the order in which you save things based on the cardinality of relationships:
-
[OneToMany]- children should beINSERTed after parents, butDELETEd before them;UPDATEs can be performed at any time, unless you need the primary key of the parent, in which case they should be performed after any parentINSERT -
[ManyToOne]- children should beINSERTed before parents, andDELETEd after them;UPDATEs can be performed at any time - if you need the parent PK you'll need to do them after any parentINSERT -
[ManyToMany]- children should beINSERTed before parents, andDELETEd after them;UPDATEs as above -
[OneToOne]-
If foreign key column is in parent object proceed as for
[ManyToOne] -
If foreign key column is in child object, proceed as for
[OneToMany]
-
-
-
Make sure you create and manage your own transaction if you break up a save into multiple steps - I recommend using a
TransactionScopefor this, rather than anIDbTransaction, because then you don't have to worry about passing around an object representing the transaction. -
You'll need to wire up primary keys yourself afterwards between objects saved in the different steps; you may also need to do some wire-up between steps depending upon how objects are created. If you're dealing with a circular reference (a pox be upon them) you'll also need to save again after you've done the wire-up. Don't worry: only a single SQL command should be issued so it won't be expensive.
##Transactions
I mentioned transactions above. Dapper.SimpleSave is always transactional. Every call to an extension method uses a transaction, but there are many cases where you'll want (or need) to manage transactions yourself. Please see the documentation on transactions for more information.
##Conclusion
That's it for our introductory tutorial. You should now be in a good position to start using SimpleSave.
Take a look at the other sections in the documentation for more detailed information.
Also, consider checking out Dapper.SimpleLoad to help you load objects as well.