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
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.