Good practices with Dapper

Question

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!

You should always use a `using` statement with objects implementing `IDisposable`. It has nothing to do with "disposing the request" or being good because "its web api". — , Jul 28 '17 at 13:14
You should have asked three separate questions. You could hava gotten better answers to each of them, and it would be of more help to others running into the same questions. — Palle Due, Jul 31 '17 at 12:44

acelent · Answer 1 · 2018-02-23T14:47:03.047

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.

I benchmarked the new `SqlConnection` creation and the difference on a single `select 1` query was essentially 10-20us; so very much negligible, especially if DB server is across network: https://github.com/pasisavolainen/Teshtink#creating-new-sqlconnection-vs-reusing-an-already-opened-one — Pasi Savolainen, Mar 21 '19 at 13:49

Amit Joshi · Accepted Answer · 2018-01-04T14:21:14.027

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.

Good practices with Dapper

2 Answers2