I'm a Beginner with Dapper and I have some doubts about best practices. My project is a Asp.net WebApi.
Opening Connection String
In this thread the connection with the database is opened like this,inside of Controller, but it is a simple project, not meant to be a WebService :
static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerConnString"].ConnectionString);
But I found other examples with using
statement :
using (IDbConnection connection = new SqlConnection(stringConnection))
{
//do something
}
Since this project is a WebApi the using statement would be better cu'z it would Dispose
the request ?
Listing Data
In the same thread above shows how to retrieve a list based on static IDbConnection db
property :
var res = (List<ShippDetails>)db.Query<ShippDetails>(query, new { id });
Or would be better to use .AsList()
?
var res = connection.Query<ShippDetails>(query, new { id }).AsList();
The Action of the Controller
For all my Action it goes like :
[Route("FF")]
[HttpGet]
public async Task<HttpResponseMessage> get()
{
var response = new HttpResponseMessage();
int id = 1;
var res = (List<ShippDetails>)db.Query<ShippDetails>(query, new { id });
if (res.Count > 0)
{
response = Request.CreateResponse(HttpStatusCode.OK, res);
}
else
{
response = Request.CreateResponse(HttpStatusCode.NoContent);
}
var task = new TaskCompletionSource<HttpResponseMessage>();
task.SetResult(response);
return await task.Task;
}
It could cause some kinda of Delay? Or the way I'm handling my Action is "Good"? Thanks!
Using using
block is always best practice. This may not be applicable in all the cases though. As you are using WebApi, consider using UnitOfWork if your transaction is spread across multiple classes or methods. Refer this answer for code sample just in case you are interested.
using
only disposes the object that implemented IDisposable
; in your case, a database connection. It does not dispose the request.
About your second question, AsList()
should be good practice.
About "Action of the Controller", it is not good or bad. I do not see any reason for causing any delay there.
SqlConnection
is actually based on a pool of internal connections, so when you create and dispose them, you're getting from and returning to the pool except when there are not enough connections, e.g. the first connections.
So, you should use using
with SqlConnection
. It's actually dangerous to use a static variable to hold a connection, since a connection's instance methods are not guaranteed to work across multiple threads.
As for the data you get with Dapper, .AsList()
will force transferring the results. It's a clear statement of "I want the results in memory". If you don't use it, you may get an IEnumerable<T>
which lazily gets each row.
Regarding your controller, you're casting the result of Query<T>
to List<T>
. This may not work, you should stick to .AsList()
. Another thing is that you're not actually taking any benefit of async
in your controller. What you should to is var res = await db.QueryAsync<T>(...).AsList();
and simply return response;
at the end, the TaskCompletionSource<T>
is superfluous there.