Dapper's parameterized Update and Insert?

Question

I am using Dapper for my windows C# forms application. I noticed that most of their CRUD operations take class name as parameter. For example two tables as below :

    "Employee" Table
        Column Name  | Data Type |
        -------------------------
        EmpName      | string    |
        EmpNo        | string    |
        --------------------------

Employee.cs
[Table("Employee")]
public class Employee
{
   [Key]
   public string EmpNo {get;set;}
   public string EmpName {get;set;}
}

    "User" Table
        Column Name   | Data Type |
        -------------------------
        UserName      | string    |
        UserNo        | string    |
        --------------------------
User.cs
[Table("User")]
public class User
{
   [Key]
   public string UserNo {get;set;}
   public string UserName {get;set;}
}


    eg. var users= connection.Query<User>("select * from User" );
        var employees = connnection.GetList<Employee>();

will do the appropriate tasks. but, as per my knowledge connection.Insert<User>(user); or connection.Update<Employee>(emp); does not exist. Please correct me if I am wrong, is there any work around to have Update and Insert with letting dapper know the class type ? I am well aware about Query() and Execute(), in fact I am using those right now. Is there anyway possible to make it as easy as GetList(ClassName); is ?

You might want to look at the tag info http://stackoverflow.com/tags/dapper/info which has link to [Performing Inserts and Updates with Dapper](http://stackoverflow.com/questions/5957774/performing-inserts-and-updates-with-dapper) — Conrad Frix, Jun 23 '16 at 19:19
Yes, I am aware of Query and Execute functions, what I am looking for is to make Insert and Update as simple as GetList(ClassName) and Get(class) are. — Рахул Маквана, Jun 23 '16 at 19:34
@RahulMakwana You will need write your own wrapper to do that. From what I understand, the people who wrote Dapper, did it as I answered below to leave things open ended. Often times, the code will need to only update specific columns, so passing an object would assume you want to update all columns, which is a big assumption for updates. As it currently it, it is fairly precise code. — Ben Hoffman, Jun 23 '16 at 19:37
?@BenHoffman I do understand that, what if I am fine with updating all the columns each time i make an update. I guess you are right, I need to write my own helper extension to do so. — Рахул Маквана, Jun 23 '16 at 19:50
Have you looked at dapper.contrib? It adds some CRUD features... — Marc Gravell, Jun 23 '16 at 20:36

Ben Hoffman · Answer 1 · 2016-06-23T19:26:23.927

4

Dapper handles things a bit differently than what you are asking for. There is no Insert or Update method. Instead you will want to do it like this for Insert:

var p = new Product { Name = "Sam", Description = "Developer" };
p.Id = cnn.Query<int>(@"insert Products(Name,Description) 
values (@Name,@Description) 
select cast(scope_identity() as int)", p).First();

This is directly from Sam Saffron, https://samsaffron.com/archive/2012/01/16/that-annoying-insert-problem-getting-data-into-the-db-using-dapper.

For Updates, you will want code like this:

public bool Update(Employee employee)
{
    string query = "UPDATE EMPLOYEE SET NAME = @Name WHERE Id = @Id";
    var count = this.db.Execute(query, employee);
    return count > 0;
}

edited Jun 23 '16 at 19:26

answered Jun 23 '16 at 19:17

Ben Hoffman

8,149
8
44
71

1

Or just use `Execute` if you don't need to get the identity back out. – juharr Jun 23 '16 at 19:18
2

Note: dapper.contrib adds a lot of CRUD support – Marc Gravell Jun 23 '16 at 20:37

score 3 · Accepted Answer · edited Jul 27 '16 at 17:29

3

Thanks to Marc Gravell. I found it here. Dapper's open source development do have implementation for Insert<ClassName>(obj) and Update<ClassName>(obj).

edited Jul 27 '16 at 17:29

Su Sa

40
5

answered Jul 18 '16 at 17:09

Рахул Маквана

944
2
15
38

Dapper's parameterized Update and Insert?

2 Answers2