Can Dapper be used to update and insert models?

Question

I've been playing with Dapper, trying to see if it's a good light-weight alternative to Entity Framework. So far I've been impressed with it's ability to quickly pull an entity <model> from a DB and get it into an IEnumerable of a model, or just create a single instance of a model. Very slick.

But what I'm not seeing is an easy way to update that model back to the db.

Example:

public class Dog
{
    public Guid Id { get; set; }
    public int? Age { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }
} 

We can easily create an IEnumerable of Dog as follows:

IEnumerable<Dog> dogs = connection.Query<Dog>("SELECT * FROM Dog")

Or, for a single instance:

Dog dog = connection.Query<Dog>("SELECT * FROM Dog WHERE DogId = @dogid")

That all works great. But now, if we make changes to "dog" (say, just change it's weight), is there a slick, fast, easy way to get that entity back into the database without having to do a manual UPDATE query, listing out each field?

Thanks!

Solution 1

You can use the SqlMapperExtensions class from Dapper.Contrib.Extensions:

using Dapper;
using Dapper.Contrib.Extensions;

// don't forget the using since Update is an extension method

Dog entity; // you got it from somewhere
entity.Name = "fancy new dog name";
connection.Update(entity);

In order for this to work you need to add the [Key] annotation to your id. Should look something like this:

using System.ComponentModel.DataAnnotations;

public class Dog
{
    [Key]
    public long Id { get; set; }
    public int? Age { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }
} 

Solution 2

Dapper is a Micro - Orm having as a characteristics being simple and fast. The behavior you describe is more related to fully fledged ORM, that imply having some sort off session concept, mapping, and query generation ( with hopefully drivers for different SQL flavours ). This is definitely not the spirit of Dapper, that anyway make the process of updating/inserting easyer that plain ado.net, accepting POCO object as parameters for your query, ie:

.Execute("update mydogs  set age=@Age where id=@Id",dog);

Comments

[Groo]

It seems that passing a List<T> should insert multiple items? Btw stick to parameterized queries (SELECT * FROM Dog WHERE DogId = @DogId), unless you like sql injection attacks.

[Casey Crookston]

How would that syntax work? In looking at the ReadMe on GitHub, I'm not seeing an example of that. github.com/StackExchange/dapper-dot-net

[Groo]

Presumably like the answer I linked to: connection.Execute(@"update Dog set Name=@Name, Age=@Age", list);?

[Casey Crookston]

ah sorry, I totally missed the link in your comment

[Casey Crookston]

Yep, you are right about parameterized queries. I was being lazy in my example

[Casey Crookston]

Ok, this is making sense. Couple of questions. How are (at)Age and (at)Id populated? Are they filled automatically with the ", dog" at the end of the query? (I can't use the AT sign in this comment or stack overflow thinks I'm trying to notify a user)

[Casey Crookston]

Ok! This is much more what I was hoping for. It's going to be a while before I can test this out, but after I do, and when I get it working, I'll mark this as the answer.

[Felice Pollano]

Yes the parameter are populated by taking from the "dog" instyance property value, with corresponding names