ASP.NET MVC Manage SQLConnection with Dapper

Question

I am giving the new Dapper Micro ORM released by Stack Overflow/Sam Saffron a quick go using MVC. I'm wondering what is the simplest way to manage a SQLConnection object inside my controller? I am doing something simple like this just to spin through some data and test out Dapper, but is it idea to be opening/closing the connection like so?

public class HomeController : Controller
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ajh"].ConnectionString);

    public HomeController()
    {
    }

    public ActionResult Index()
    {
        // get me all comments
        conn.Open();
        var comments = conn.ExecuteMapperQuery<Comment>("select * from Comment");
        conn.Close();

        return View(comments);
    }
}

LukeH LukeH · Accepted Answer · 2011-04-19T13:40:04

Just create, open and close the connection as locally as possible:

public class HomeController : Controller
{
    public HomeController()
    {
    }

    public ActionResult Index()
    {
        List<Comment> comments;
        using (var conn = new SqlConnection(/* ... */))
        {
            conn.Open();
            comments = conn.ExecuteMapperQuery<Comment>("select * from Comment");
        }
        return View(comments);
    }
}

Although it's best practice to avoid direct data-access in your controllers. Bury your data-access methods inside a CommentsService class or similar and call that from your controller.

ASP.NET MVC Manage SQLConnection with Dapper

2 Answers