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!

It seems that passing a `List` should [insert multiple items](http://stackoverflow.com/a/6500834/69809)? Btw stick to parameterized queries (`SELECT * FROM Dog WHERE DogId = @DogId`), unless you like sql injection attacks. — vgru, Mar 24 '16 at 13:37
How would that syntax work? In looking at the ReadMe on GitHub, I'm not seeing an example of that. https://github.com/StackExchange/dapper-dot-net — Casey Crookston, Mar 24 '16 at 13:39
Presumably like the answer I linked to: `connection.Execute(@"update Dog set Name=@Name, Age=@Age", list);`? — vgru, Mar 24 '16 at 13:42
Yep, you are right about parameterized queries. I was being lazy in my example — Casey Crookston, Mar 24 '16 at 13:43

score 15 · Accepted Answer · answered Mar 24 '16 at 13:53

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; }
}

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. — Casey Crookston, Mar 24 '16 at 13:57

score 9 · Answer 2 · answered Mar 24 '16 at 13:43

9

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);

answered Mar 24 '16 at 13:43

Felice Pollano

32,832
9
75
115

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 Mar 24 '16 at 13:52
1

Yes the parameter are populated by taking from the "dog" instyance property value, with corresponding names – Felice Pollano Mar 25 '16 at 07:54

Can Dapper be used to update and insert models?

2 Answers2