I have a class (data access layer) that is used to make all database queries. I use Dapper as micro-ORM.
The "normal" way of doing such queries is :
string req = @"select ...";
using (var db = new OracleConnection(this.con_str))
{
return db.Execute(req, { p1, p2});
}
Now, I have multiples methods in this class and i need to make mutliple insersions. If one insersion fail, I need to rollback everithing that has been done.
So, instead of using, i need to use an existing connection and transaction
IDbTransaction t;
IDbConnection c;
...
dynamic MyInsersion(int p1, int p2){
string req = @"select ...";
return c.Execute(req, new { p1, p2 }, transaction: t);
}
My probleme is that myInsersion need to use the existing connection ONLY if c is not null. If c is null, then just ignore it and create a new connection, without transaction. I need a short code because i have A LOT of methods like this. I tried something like this :
using (var db = connection ?? new OracleConnection(this.con_str))
If the connection is not null, I use it, if not, I create a new connection. The problem is that the "using" statement automatically call db.Dispose(), which call db.close()
So I have to create two almost identical code blocks
if(c != null && c.State == ConnectionState.Open)
{
return c.Execute(req, new { p1, p2 }, transaction: t);
}
else
{
return c.Execute(req, new { p1, p2 });
}
The only way to avoid this code duplication I found is to use a lambda :
Action<IDbConnection, IDbTransaction> A = (db, tr) =>
{
db.Execute(
req,
new { p1, p2 ... },
transaction: tr);
};
if (connection != null)
{
A(connection, transaction);
return;
}
using (var db = new OracleConnection(this.Con_str))
{
A(db, null);
}
but it seems pretty heavy to me, I'd like to know if there was a more elegant way to do that.
Thanks you !
I hope I've been clear enough, tell me if not.
Create new connection when the connection is null. So, using statement does not dispose the connection.
using (var db = connection == null ? new OracleConnection(this.con_str) : null)
{
return (db ?? connection).Execute(req, new { p1, p2 }, transaction: connection == null ? null : t);
}