Dapper Syntax - 2016

Question

I'm just beginning my first MVC project using Dapper (and Dapper.Contrib) instead of EF. I'm trying to make sure I'm using the proper syntax when using it. Searching the web, I can see that as Dapper has evolved, so has some of its requirements and syntax. Is there an up to date document that shows best practices for using Dapper in 2016?

Specifically, my questions are:

• Do I need to open and close the connection? It looks like Dapper used to require it but may not any longer.

• Using Dapper.Contrib, do I need to encapsulate my code in a 'using' call or will Dapper take care of disposing itself automatically now?

Which way is recommended?

private string dbconn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
public Company Add(Company company)
    {

        using (SqlConnection cn = new SqlConnection(dbconn))
        {
            cn.Open();
            var id = cn.Insert(company);
            cn.Close();

            company.id = Convert.ToInt16(id);
        }

        return company;
    }

OR

private IDbConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);

public Company Add(Company company)
    {
       var id = cn.Insert(company);
       company.id = Convert.ToInt16(id);

       return company;
    }

Answer 1

With Dapper, there are two ways to manage connection:

• Fully manage yourself: Here, you are fully responsible for opening and closing connection. This is just like how you treat connection while working with ADO.NET. In this case, you should make sure you are disposing connection properly using using block or calling Dispose explicitly.
• Allow Dapper to manage it: Dapper automatically opens the connection (if it was not opened) and closes it (if it was opened by Dapper) for you. This is similar to DataAdapter.Fill() method.

In 2016, you should avoid exposing your connection directly to your application code. You should implement Data Access Layer (preferably using some good ORM like Dapper that you are already learning) using patterns like UnitOfWork, Repository etc. Code sample for UnitOfWork + Dapper could be found here. Please go through it. I am sure it will help you.

If you do not want to implement UnitOfWork, following is what I propose:

• Fully manage yourself:

Following is sample:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    connection.Execute(......);
    //OR
    var data = connection.Query(......);
    //OR whatever
}

If you want to share connection over higher scope and hence could not use using block, then just make sure you Dispose connection properly.

• Allow Dapper to manage it: I personally do not prefer this.

Following is sample:

SqlConnection connection = new SqlConnection(connectionString);
//Dapper will open connection on next line.
var data = connection.Query(......);
//Dapper will close connection in previous call.