Dapper to return a model(property class) on DML operations

Question

Dapper to return a model(property class) on DML operations

.net c# dapper

Question

I am trying to implement caching on our database transaction and I want them to add,update and delete from cache as equivalent database operations happen.

public class ModelExample
{
        public string code { get; set; }
        public string description { get; set; }
        public string site { get; set; }
}

say I have a method that inserts into the table

int result;
string query = "insert into modeltable(code,description,site) values (@code,@description,@site);
DynamicParameters parameter = new DynamicParameters();
parameter.Add("@code", code, DbType.String);
parameter.Add("@description", description, DbType.String);
parameter.Add("@site", site, DbType.String);
                connection.Open();
result = await connection.ExecuteAsync(query, parameter)

I want the result to actual return the ModelClass, instead of an integer. So basically, I want the results of the insert so that i can add it to the cache.

Thanks

alangilbi

View more on Stack Overflow

KozhevnikovDmitry · Accepted Answer · 10/1/2018 1:56:51 AM

In case of MS SQL you can use OUTPUT clause to access to inserted row:

[Test]
public async Task DapperOutputClauseUsage()
{
    // Arrange
    var conn = new SqlConnection("YourDatabaseConnectionString");
    await conn.OpenAsync();
    var ex = new ModelExample
    {
        code = "code",
        description = "description",
        site = "site"
    };

    // Act
    var result = await conn.QuerySingleAsync<ModelExample>(@"INSERT INTO ModelTable(code, description, site)
                                                             OUTPUT INSERTED.*
                                                             VALUES (@code, @description, @site)", ex);
    // Assert
    Assert.AreEqual(result.code, ex.code);
    Assert.AreEqual(result.description, ex.description);
    Assert.AreEqual(result.site, ex.site);

}

public class ModelExample
{
    public string code { get; set; }
    public string description { get; set; }
    public string site { get; set; }
}

If you have IDENTITY on your primary key, it will be returned as well. You just need to add Id property to your model class. You can also use OUTPUT INSERTED.* and OUTPUT DELETED.* with UPDATE and DELETE queries to get access to changes in a row. Hope it helps.

Dapper to return a model(property class) on DML operations

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator