Performing Inserts and Updates with Dapper

Performing Inserts and Updates with Dapper

We are looking at building a few helpers, still deciding on APIs and if this goes in core or not. See: https://code.google.com/archive/p/dapper-dot-net/issues/6 for progress.

In the mean time you can do the following

val = "my value";
cnn.Execute("insert into Table(val) values (@val)", new {val});

cnn.Execute("update Table set val = @val where Id = @id", new {val, id = 1});

etcetera

See also my blog post: That annoying INSERT problem

Update

As pointed out in the comments, there are now several extensions available in the Dapper.Contrib project in the form of these IDbConnection extension methods:

T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();

Dapper's parameterized Update and Insert?

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

Can Dapper be used to update and insert models?

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

Can I Use Query Method in Dapper To Insert And Update Rows?

First, you need to use the OUTPUT clause in order to return rows from an insert statement.

Second, QueryAsync will return an IEnumerable<Hotel> and not a Hotel, so you won't be able to cast it.

Put together, it would look like this

public async Task<Hotel> CreateHotel(Hotel hotel)
{
    var sql = "INSERT INTO Hotels" +
                " (name, city)" +
                " OUTPUT inserted.name, inserted.city" +
                " VALUES (@name, @city)";

    var newHotel = new Hotel()
    {
        Name = hotel.Name,
        City = hotel.City
    };

    using (var connection = new SqlConnection(CONNECTION_STRING))
    {
        return (await connection.QueryAsync<Hotel>(sql, newHotel)).SingleOrDefault();
    }
}

Can Dapper be used to update and insert models?

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

Is it possible to run an Insert and Get by dapper simultaneously?

UPDATE doesn't return a grid (unless you are using the OUTPUT clause. You can do as many operations as you like in a SQL operation - all that Dapper cares about is the result grids, so yes, you can perform an update and (separately) a select. The only significance of Execute over Query is that Execute expects zero grids. If you're returning a single grid, then Query; if you're returning multiple grids: QueryMultiple. Dapper has no interest in what happens inside the query - it is entirely opaque - all it sees is result grids.

I highly recommend using the formal operation separator (;), though. For example:

const string sql = @"
UPDATE likes AS L
SET L.isLike = @isLike
WHERE L.ItemId = @ItemId AND L.Ip = @Ip;

SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=1;
SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=0;
"
using (var data = await dbConnection.QueryMultipleAsync(sql,
    new { ItemId, isLike, Ip }))
{
    numberOfLikes = data.ReadSingle<int>();
    numberOfDislikes = data.ReadSingle<int>();
}

although to be honest I might be tempted to do, instead, a single final select, using Dapper's positional treatment of value-tuples:

const string sql = @"
UPDATE likes AS L
SET L.isLike = @isLike
WHERE L.ItemId = @ItemId AND L.Ip = @Ip;

SELECT 
    (SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=1),
    (SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=0);
"
(var numberOfLikes, var numberOfDislikes) =
    await dbConnection.QuerySingleAsync<(int,int)>(sql,
    new { ItemId, isLike, Ip });

or something involving group by L.isLike

Dapper many-to-many insert

I believe the code snippet below will work for you.

You should have 3 insert statements, for Product, Attachment and AttachmentProductSpecificationLink. In your sample code you are missing one. I made the assumption, that your aINSERT should be for the Attachment table.

Another thing I noticed, Your ExecuteScalarAsync was returning an object, so I used ExecuteScalarAsync<int> instead to cast correctly.

Your entity ( Product class) variable contains a List of Attachments, Normally I would perform an ExecuteAsync and pass in the List because it is plain Inserts with no returning Clause. But in your case you are returning the Ids so I opted to use a foreach loop to iterate over the attachments for the inserts.

Within the foreach loop I return the returning ID into the variable attachmentId and use it for the following insert into AttachmentProductSpecificationLink. Since we aren't return anything I perform an ExecuteAsync.

using (var connection = GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    string pINSERT = "INSERT INTO prodcat.Product (otherpropertiesremovedforbrevity) " +
                          " VALUES (@otherpropertiesremovedforbrevity) RETURNING Id;";

                    string aINSERT = "INSERT INTO prodcat.Attachment (Name, URL) " +
                          " VALUES (@Name, @Url) RETURNING Id;";

                    string sql = "insert into prodcat.AttachmentProductSpecificationLink (ProductSpecificationId, AttachmentId) values(@Id, @AttachmentId)";

                    var res = await connection.ExecuteScalarAsync<int>(pINSERT, entity, transaction);

                    foreach( var a in entity.Attachment)
                    {
                        var attachmentId = await connection.ExecuteScalarAsync<int>(aINSERT, a, transaction);

                        var arows = await connection.ExecuteAsync(sql, new { Id = res, AttachmentId = attachmentId }, transaction);
                    }

                    transaction.Commit();
                    return Convert.ToInt64(res);

                }
            }

Dapper Contrib Output Parameter

You can try to use DynamicParameters be the parameter that can declare ParameterDirection.Output for the parameter which you want to be output, then we can use Get<int> to get the output value.

var params = new DynamicParameters();
params.Add("@SkipCount",skip);
params.Add("@PageSize",pageSize);
params.Add("@TotalRows", dbType: DbType.Int32, direction: ParameterDirection.Output);

db.Query<Models.Product>("spCustomPaging", params, commandType: System.Data.CommandType.StoredProcedure);

var output = params.Get<int>("@TotalRows");

---

Related Topics